Repetitive call on LinqMetaData doesn't return the same results

Posts   
 
    
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 23-Sep-2008 10:40:15   

Hi,

I think we've found an issue with LinqMetaData.

On some queries, when we perform the same query twice, de second query doesn't return results:


// snip: set up test data
adapter.SaveEntity(td.Functie1, true, true);

// the first time the correct results are returned:
var sns = linqMetaData.Functie
    .Where(f => f.Id == td.Functie1.Id)
    .Select(f => f.Salarisniveauschaal)
    .FirstOrDefault();

Assert.IsTrue(sns != null, "no Salarisniveauschaal returned");

// the second time there is no result:
sns = linqMetaData.Functie
    .Where(f => f.Id == td.Functie1.Id)
    .Select(f => f.Salarisniveauschaal)
    .FirstOrDefault();

// fails 
Assert.IsTrue(sns != null, "second time no Salarisniveauschaal returned");

last bit of the output:


Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [dbo].[tb_ECDGenie_Medewerker_Functie] ([id], [medewerker_guid], [functie_id], [creator_account_id], [versie_startdatum], [versie_nummer], [versie_object_id])  VALUES (@Id, @MedewerkerGuid, @FunctieId, @CreatorAccountId, @VersieStartdatum, @VersieNummer, @VersieObjectId)
    Parameter: @Id : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 67a976d1-bbe6-4bb1-afce-fd8ebb8c0500.
    Parameter: @MedewerkerGuid : AnsiStringFixedLength. Length: 36. Precision: 0. Scale: 0. Direction: Input. Value: "7784328c-9c3b-4669-8095-dfe070901da9".
    Parameter: @FunctieId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6104edff-4f55-49f4-81c8-34f59c95bc7e.
    Parameter: @CreatorAccountId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.
    Parameter: @VersieStartdatum : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 23-9-2008 10:02:53.
    Parameter: @VersieNummer : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
    Parameter: @VersieObjectId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 306670f6-8501-444a-8626-d98e2d66996a.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [dbo].[tb_ECDGenie_Medewerker_Functie].[id] AS [Id], [dbo].[tb_ECDGenie_Medewerker_Functie].[medewerker_guid] AS [MedewerkerGuid], [dbo].[tb_ECDGenie_Medewerker_Functie].[functie_id] AS [FunctieId], [dbo].[tb_ECDGenie_Medewerker_Functie].[creator_account_id] AS [CreatorAccountId], [dbo].[tb_ECDGenie_Medewerker_Functie].[versie_startdatum] AS [VersieStartdatum], [dbo].[tb_ECDGenie_Medewerker_Functie].[versie_einddatum] AS [VersieEinddatum], [dbo].[tb_ECDGenie_Medewerker_Functie].[versie_nummer] AS [VersieNummer], [dbo].[tb_ECDGenie_Medewerker_Functie].[versie_object_id] AS [VersieObjectId] FROM [dbo].[tb_ECDGenie_Medewerker_Functie] (nolock) WHERE ( ( [dbo].[tb_ECDGenie_Medewerker_Functie].[id] = @Id1))
    Parameter: @Id1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 67a976d1-bbe6-4bb1-afce-fd8ebb8c0500.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.FunctieEntity]).Where(f => (f.Id = value(CC.ECDGenie.Dal.UnitTests.LlblgenTests+<>c__DisplayClass0).td.Functie1.Id)).Select(f => f.Salarisniveauschaal).FirstOrDefault()
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT TOP(@top1) @LO11 AS [LPFA_2], [LPLA_1].[salarisniveauschaal_id] AS [SalarisniveauschaalId] FROM [dbo].[tb_ECDGenie_Functie] [LPLA_1] (nolock) WHERE ( ( ( ( ( ( [LPLA_1].[id] = @Id3))))))
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @top1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Id3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6104edff-4f55-49f4-81c8-34f59c95bc7e.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_2].[id] AS [Id], [LPLA_2].[salarisschaal_id] AS [SalarisschaalId], [LPLA_2].[functiejaar_id] AS [FunctiejaarId], [LPLA_2].[kostprijs] AS [Kostprijs], [LPLA_2].[creator_account_id] AS [CreatorAccountId], [LPLA_2].[versie_startdatum] AS [VersieStartdatum], [LPLA_2].[versie_einddatum] AS [VersieEinddatum], [LPLA_2].[versie_nummer] AS [VersieNummer], [LPLA_2].[versie_object_id] AS [VersieObjectId] FROM [dbo].[tb_ECDGenie_Salarisniveauschaal] [LPLA_2] (nolock) WHERE ( ( ( ( [LPLA_2].[id] = @Id1))))
    Parameter: @Id1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4374e80d-a519-430a-a411-bc8ef1cb6c26.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.FunctieEntity]).Where(f => (f.Id = value(CC.ECDGenie.Dal.UnitTests.LlblgenTests+<>c__DisplayClass0).td.Functie1.Id)).Select(f => f.Salarisniveauschaal).FirstOrDefault()
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT TOP(@top1) @LO11 AS [LPFA_2], [LPLA_1].[salarisniveauschaal_id] AS [SalarisniveauschaalId] FROM [dbo].[tb_ECDGenie_Functie] [LPLA_1] (nolock) WHERE ( ( ( ( ( ( [LPLA_1].[id] = @Id3))))))
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @top1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Id3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6104edff-4f55-49f4-81c8-34f59c95bc7e.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_2].[id] AS [Id], [LPLA_2].[salarisschaal_id] AS [SalarisschaalId], [LPLA_2].[functiejaar_id] AS [FunctiejaarId], [LPLA_2].[kostprijs] AS [Kostprijs], [LPLA_2].[creator_account_id] AS [CreatorAccountId], [LPLA_2].[versie_startdatum] AS [VersieStartdatum], [LPLA_2].[versie_einddatum] AS [VersieEinddatum], [LPLA_2].[versie_nummer] AS [VersieNummer], [LPLA_2].[versie_object_id] AS [VersieObjectId] FROM [dbo].[tb_ECDGenie_Salarisniveauschaal] [LPLA_2] (nolock)

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
TestCase 'CC.ECDGenie.Dal.UnitTests.LlblgenTests.TestSelectingRelatedEntity'
failed: Assert.IsTrue failed. second time no Salarisniveauschaal returned.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-Sep-2008 12:24:51   

The WHERE condition is not generated in the second call. Would you please make sure the td.Functie1.Id evaluates similarly in both calls?

Maybe for testing you'll need to replace it with a constant.

wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 23-Sep-2008 12:57:16   

Walaa wrote:

The WHERE condition is not generated in the second call. Would you please make sure the td.Functie1.Id evaluates similarly in both calls?

Maybe for testing you'll need to replace it with a constant.

I've put it in a variable, but yields the same results...

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-Sep-2008 15:41:31   

What's the version of the SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll used?

(Right-click on the file -> properties -> File version)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 23-Sep-2008 15:42:02   

This is a nested query in a projection, so not a normal straightforward query. Very strange though, as all objects are re-created in the LinqMetaData call... Perhaps some internal mapping tables aren't reset properly. Will see if I can repro it.

Frans Bouma | Lead developer LLBLGen Pro
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 23-Sep-2008 15:52:10   

Walaa wrote:

What's the version of the SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll used?

(Right-click on the file -> properties -> File version)

yes sorry forgot that:

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll: 2.6.8.828 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll: 2.6.8.901

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 23-Sep-2008 16:01:35   

Can't reproduce it with latest build (linq support classes build 091108 )


[Test]
public void GetCustomerFromOrderUsingMultipleCalls()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = (from o in metaData.Order
                where o.OrderId == 10254
                select o.Customer).FirstOrDefault();
        Assert.IsNotNull(q);
        q = (from o in metaData.Order
             where o.OrderId == 10254
             select o.Customer).FirstOrDefault();

        Assert.IsNotNull(q);
        Assert.AreEqual("CHOPS", q.CustomerId);
    }
}

also if I use:


var q = metaData.Order.Where(o => o.OrderId == 10254).Select(o => o.Customer).FirstOrDefault();
Assert.IsNotNull(q);
q = metaData.Order.Where(o => o.OrderId == 10254).Select(o => o.Customer).FirstOrDefault();

it works fine.

Frans Bouma | Lead developer LLBLGen Pro
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 23-Sep-2008 16:26:31   

I upgraded to the latest assemblies, but still the same issue.

However I must apologize, I left out some information, we're running the test within a transaction (with isolation level snapshot).

It seems that if I remove the transaction, it does succeed.

Best Regards,

Wiebe Tijsma

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 23-Sep-2008 18:26:08   

Ok.

So I think this happens: - there are two queries ran, as it's a nested query. First the outer query, to get the ID's for the inner query (the data which is actually returned). - the second query gets the IDs as filter. - the second RUN of the code, the first query returns 0 rows. - this means there are no ids to filter on. All data is returned, but no merging can take place as no parents are found -> no data is returned.

It's now a point of investigation why the first query on the second run returns 0 rows. As you run inside a transaction, the connection stays open between queries. I'll retry my test with a transaction.

(edit) when I use a ReadCommitted transaction, it works as above. Your transaction, is that a System.Transactions transaction with a transactionscope?

I'll rewrite my code for adventureworks on 2005 to see what SnapShot adds...

(edit) Also with snapshot on sqlserver 2005, adventure works, no problem:

[Test]
public void GetContactFromOrderUsingMultipleCalls()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        try
        {
            adapter.StartTransaction(System.Data.IsolationLevel.Snapshot, "Test");

            var q = (from o in metaData.SalesOrderHeader
                     where o.SalesOrderId == 43659
                     select o.Contact).FirstOrDefault();

            Assert.IsNotNull(q);

            q = (from o in metaData.SalesOrderHeader
                 where o.SalesOrderId == 43659
                 select o.Contact).FirstOrDefault();

            Assert.IsNotNull(q);
            Assert.AreEqual("Hendergart", q.LastName);
        }
        finally
        {
            adapter.Commit();
        }
    }
}

Frans Bouma | Lead developer LLBLGen Pro
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 23-Sep-2008 18:52:08   

Otis wrote:

Also with snapshot on sqlserver 2005, adventure works, no problem:

Weird... Thanks for looking into it, I'll see if I can make a more isolated unit test...