I'm migrating data from an old database which uses one-to-one relationships for inheritance. Say the tables are Animal and Bear.

In SQL, I get the correct counts:

select count(*) from Animal -- 3
select count(*) from Bear -- 1

select count(AnimalId) from Animal -- 3
select count(AnimalId) from Bear -- 1

In LINQ, the counts are incorrect when I select by Id. If I select the entities, the results are correct:

(from a in new LinqMetaData().Animal select a).Count(); // 3
(from b in new LinqMetaData().Bear select b).Count(); // 1

(from a in new LinqMetaData().Animal select a.AnimalId).Count(); // 3
(from b in new LinqMetaData().Bear select b.AnimalId).Count(); // 3 >> wrong

Any idea what I might be doing wrong?

I'm using MS SQL Server 2005, LLBLGen 3.1 Final, SelfServicing, C#, ASP.NET 3.5, Runtime Version v2.0.50727.

JoshWright wrote:

I'm using MS SQL Server 2005, LLBLGen 3.1 Final, SelfServicing, C#, ASP.NET 3.5, Runtime Version v2.0.50727.

Hi Josh. That is not the runtime library version. See this for more info.

I remember a fix on this some time ago, so please update your runtime libraries to the latest version.

It's indeed a distinct() issue. It was not added by default in the v3.1 release build, but we added this breaking change later on, as in general devs expected it to be applied.

I've installed the latest LLBLGen. According to my bin\Debug of my production code, my versions are now: ORMSupportClasses.NET20.dll - 3.1.12.1015 LinqSupportClasses.NET35.dll - 3.1.12.0507 SqlServer.NET20.dll - 3.1.11.0706

I created a sample project with a 1-to-1 between Animal and Bear. Updating my runtime version fixed this example:

(from x in new LinqMetaData().Animal select x.AnimalId).Count(); // 3
(from x in new LinqMetaData().Bear select x.AnimalId).Count(); // 1
(from x in new LinqMetaData().Animal select x).Count(); // 3
(from x in new LinqMetaData().Bear select x).Count(); // 1

Unfortunately my real code is still busted. Here's the actual code with results:

(from i in new Old.LinqMetaData().Old_Issuance select i.TransactionId).Count(); // 851857
(from i in new Old.LinqMetaData().Old_Adjustment select i.TransactionId).Count(); // 851857
(from i in new Old.LinqMetaData().Old_Issuance select i).Count(); // 459167
(from i in new Old.LinqMetaData().Old_Adjustment select i).Count(); // 63194

It's worth mentioning that the database I'm working with has fairly deep inheritance structures. In this case, Adjustment and Issuance are the 3rd & 4th tables in terms of depth. Transaction is the root and TransactionId is the common identifier.

I can get you our LLBLGen project file, VS solution, or anything else that would help.

Could you enable DQE tracing (see troubleshooting & debugging in the runtime manual) and post the SQL query executed for the query which fails?

Also the 2nd results which are wrong, what should the counts be and what's the inheritance tree?

Thanks for your help. It looks like when I select the TransactionId, it's returning the count of the root.

The inheritance tree is: Adjustment -> Issuance -> Transfer -> Transaction (root)

The correct counts are: Transaction - 851857 Transfer - 461439 Issuance - 459167 Adjustment - 63194

In all cases (Transfer, Issuance, and Adjustment), the result is 851857 (incorrect).

(from i in new Old.LinqMetaData().Old_Transfer select i.TransactionId).Count();
// 851857

Generated Sql query: 
    Query: SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L3].[TransactionId] FROM ( [Pointfolio].[dbo].[Transaction] [LPA_L2]  LEFT JOIN [Pointfolio].[dbo].[Transfer] [LPA_L3]  ON  [LPA_L2].[TransactionId]=[LPA_L3].[TransactionId])) [LPA_L1]
    Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

It seems to be including null values. For example, if I actually select the ids into a list then 63194 are valid ids and the rest are empty GUIDS.

var ids = (from i in new Old.LinqMetaData().Old_Adjustment select i.TransactionId).ToList();
ids.Count // 851857
ids.Count(i => i == Guid.Empty) // 788663

I think this is related to this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21502&StartAtMessage=0&#121063

That's a known bug, that will be fixed in v.4.x, but check the above thread for a possible workaround. (A type filter is needed).

Thanks Walaa. I didn't see that before, but it is the same issue. Adding a cast fixes it.

(from i in new Old.LinqMetaData().Transfer.Cast<Old_AdjustmentEntity> select i.TransactionId).Count();