Hi. This post concerns the outcome of a generated query against a data model which has multiple links to a shared table utilised by different entities.

Please excuse the length of this post - I have tried to explain the scenario in sufficient detail to aid understanding and resolution

Description of the Model Part is a base entity and has TransmissionPart and EnginePart entities as subtypes (using Target-per-entity hierachy) Part also has a 1-many relationship to PartTranslation, which has a many-1 relationship to Culture This facilitates translations for multiple cultures for the same Part A Vehicle entity contains 2 foreign keys, one for TrnasmissionPart and one for EnginePart

Required Data from Query For each Vehicle, return a row containing both the translations of the Transmission and Engine entities

The expected SQL is along the lines of :

SELECT  LPA_L1.EnginePartId, LPA_L5.PartIdentifier AS EnginePartIdentifier, LPA_L6.Size, LPA_L7.Description AS EngineTitle, 
        LPA_L1.TransmissionPartId, LPA_L2.PartIdentifier AS TransmissionPartIdentifier, LPA_L3.GearCount, LPA_L4.Description AS TransmissionTitle
FROM    dbo.Vehicle AS LPA_L1 
INNER JOIN  dbo.TransmissionPart AS LPA_L3 ON LPA_L1.TransmissionPartId = LPA_L3.PartId 
INNER JOIN  dbo.Part AS LPA_L2 ON LPA_L2.PartId = LPA_L3.PartId 
INNER JOIN  dbo.PartTranslation AS LPA_L4 ON LPA_L3.PartId = LPA_L4.PartPartId 
INNER JOIN  dbo.EnginePart AS LPA_L6 ON LPA_L1.EnginePartId = LPA_L6.PartId 
INNER JOIN  dbo.Part AS LPA_L5 ON LPA_L5.PartId = LPA_L6.PartId 
INNER JOIN  dbo.PartTranslation AS LPA_L7 ON LPA_L6.PartId = LPA_L7.PartPartId 
AND LPA_L4.CultureCultureId = LPA_L7.CultureCultureId

Note, that the PartTranslation table is included twice - once for the Transmission and another for the Engine. There are a number of ways to achieve this - using 'regular' Linq where we explicitly specify the join clauses, the following code achieves the desired results:

LinqMetaData linqMetaData = new LinqMetaData();

var list = from v in linqMetaData.Vehicle
           join t in linqMetaData.TransmissionPart on v.TransmissionPartId equals t.PartId
           join tt in linqMetaData.PartTranslation on t.PartId equals tt.PartPartId
           join e in linqMetaData.EnginePart on v.EnginePartId equals e.PartId
           join et in linqMetaData.PartTranslation on e.PartId equals et.PartPartId
           where et.CultureCultureId == cultureId && tt.CultureCultureId == cultureId
           select new {
                        v.EnginePartId,
                        EnginePartIdentifier = e.PartIdentifier,
                        e.Size,
                        EngineTitle = et.Description,

                        v.TransmissionPartId,
                        TransmissionPartIdentifier = t.PartIdentifier,
                        t.GearCount,
                        TransmissionTitle = tt.Description
                      };

However, I have tried to produce the same results using Linq to LLBLGenPro by just including a minimal set of entities and letting the ORM infer the joins required. In this instance, using the Vehicle entity and linking through the Transmission and Engine navigators to reach the PartTranslation for each part.

LinqMetaData linqMetaData = new LinqMetaData();

var list = from v in linqMetaData.Vehicle
           from tt in v.TransmissionPart.PartTranslation
           from et in v.EnginePart.PartTranslation
           where tt.CultureCultureId == cultureId && et.CultureCultureId == cultureId
           select new {
                        v.EnginePartId,
                        EnginePartIdentifier = v.EnginePart.PartIdentifier,
                        v.EnginePart.Size,
                        EngineTitle = et.Description,

                        v.TransmissionPartId,
                        TransmissionPartIdentifier = v.TransmissionPart.PartIdentifier,
                        v.TransmissionPart.GearCount,
                        TransmissionTitle = tt.Description
                      };

The data returned and SQL generated were not as expected. It was returning the same description for both Transmission and Engine entities. Looking at the SQL, it only includes the PartTranslation entity once.

SELECT  LPA_L3.EnginePartId, LPA_L5.PartIdentifier AS EnginePartIdentifier, LPA_L6.Size, LPA_L4.Description AS EngineTitle, LPA_L3.TransmissionPartId, 
        LPA_L1.PartIdentifier AS TransmissionPartIdentifier, LPA_L2.GearCount, LPA_L4.Description AS TransmissionTitle
FROM    dbo.TransmissionPart AS LPA_L2 
INNER JOIN  dbo.Vehicle AS LPA_L3 ON LPA_L2.PartId = LPA_L3.TransmissionPartId 
INNER JOIN  dbo.Part AS LPA_L1 ON LPA_L1.PartId = LPA_L2.PartId 
INNER JOIN  dbo.PartTranslation AS LPA_L4 ON LPA_L1.PartId = LPA_L4.PartPartId 
INNER JOIN  dbo.EnginePart AS LPA_L6 ON LPA_L6.PartId = LPA_L3.EnginePartId 
INNER JOIN  dbo.Part AS LPA_L5 ON LPA_L5.PartId = LPA_L6.PartId
WHERE    (LPA_L4.CultureCultureId = LPA_L4.CultureCultureId)

The ORM is aware of the underlying model structure so I am a little confused why the table is not referenced twice. Can you advise if I am missing something in the query definition - do I need to provide additional information for the query ? Is there some optimisation happening ?

Thanks, Pete

Environment: LLBLGenPro v3.1 Final, SelfServicing Template, SQL Server 2008, C# .Net v4.0, Windows7

Please specify the exact LLBLGenPro runtime libraries versions/ build no. For the ORMSupport dll and the LinqSupport dll.

Thanks.

Thanks. Here are my version numbers:

LLBLGenPro version 3.1 Final SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 3.1.11.907 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 3.1.11.809

You use nested from clauses. These cause duplicate relationships to occur:

         from tt in v.TransmissionPart.PartTranslation
         from et in v.EnginePart.PartTranslation

means a cross join between tt and et and also a navigation from v to transmissionpart to parttranslation. The linq engine then chooses the latter over the former (as it assumes you wanted that). This assumption might cause it, but unless I have a repro case (We'll try with our own inheritance test db) I can't be sure.

I think the problem could also occur in the area where you navigate to a member which is inherited from the supertype, Part: the above statements really look like:

         from tt in Part.PartTranslation
         from et in Part.PartTranslation

to the linq engine, as it looks at the member access expression and sees Part.PartTranslation is accessed. It assigns an alias to that member once, the second time it sees 'I already assigned an alias to that member, re-use it' as it doesn't know the context in which it is used, which in this case is a from clause, but it can also be a projection, and in the projection you want this:

... select new { o.Customer.CompanyName, o.Customer.Country};

So as the second member access is different in the query, for the expression tree it's not, so it's re-using the same aliased member, resulting in the same relationship, which results in a duplicate to from tt in v.TransmissionPart.PartTranslation, so it won't show up.

We'll see if we can reproduce it in our inheritance model tests. if we can't reproduce it, we'll ask for a repro case. We expect to have more updates on this tomorrow (thursday)

Our inheritance test db didn't have the specific 2-relationships to same type inherited by subtypes setup, so I created a new model, according to your description. I can reproduce the behavior:

SELECT [LPA_L3].[EnginePartId],
       [LPA_L5].[PartIdentifier] AS [EnginePartIdentifier],
       [LPA_L6].[Size],
       [LPA_L4].[Description]   AS [EngineTitle],
       [LPA_L3].[TransmissionPartId],
       [LPA_L1].[PartIdentifier] AS [TransmissionPartIdentifier],
       [LPA_L2].[GearCount],
       [LPA_L4].[Description]   AS [TransmissionTitle]
FROM   ((((( [LinqIssue].[dbo].[TransmissionPart] [LPA_L2]
             INNER JOIN [LinqIssue].[dbo].[Vehicle] [LPA_L3]
                 ON [LPA_L2].[Id] = [LPA_L3].[TransmissionPartId])
           INNER JOIN [LinqIssue].[dbo].[Part] [LPA_L1]
               ON [LPA_L1].[Id] = [LPA_L2].[Id])
          INNER JOIN [LinqIssue].[dbo].[PartTranslation] [LPA_L4]
              ON [LPA_L1].[Id] = [LPA_L4].[PartsId])
         INNER JOIN [LinqIssue].[dbo].[EnginePart] [LPA_L6]
             ON [LPA_L6].[Id] = [LPA_L3].[EnginePartId])
        INNER JOIN [LinqIssue].[dbo].[Part] [LPA_L5]
            ON [LPA_L5].[Id] = [LPA_L6].[Id])
WHERE  ((((([LPA_L4].[Id] = @p1)
       AND ([LPA_L4].[Id] = @p2))))) 

Looking into it. It indeed seems to be a re-use of the alias for the same member (as the where clause and projection correctly point to the same parttranslation, even though they're members of a different instance in the query). We did add code for this to determine whether it's the same member or a different one, but this doesn't always work as it's not always possible to determine exactly what's in the code, as the tree might look different (as members used are inherited for example).

It's indeed the alias which is re-used. The reason this is happening here (even though we fixed it in most cases, see: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20148) is that there are a couple of SelectMany() nested into each other, this leads to a source being wrapped in an anonymous type. The problem with this is that the path you see in the C# query (v.<propertyname>.PartTranslation) isn't present in the tree, but a property on an anonymous type which is the result of a selectmany call. We can't calculate the unique key for the path for this, as the actual member is wrapped in the anonymous type. So it might look different but it could be the same thing (this is why Linq actually sucks because the scope of the elements in the tree is based on the method parameters and the result of the method (in this case SelectMany), yet the scope of the elements in the SQL query have to be in the same scope (all joins are done together)). So we fall back on the member (.PartTranslation) to obtain the alias. As this member already got an alias assigned to it (as it's mentioned twice and it's the same member, as it's inherited) its alias is re-used.

Unfortunately, at this point we can't really fix this. We suggest to use the workaround query with the joins in this edge case scenario. see also the thread I linked to above for details of this issue. It's rather complex to explain, and we did a lot to fix it already, but there are still some cases which aren't solveable at this point. Sorry for this.

Thanks for looking into this, Otis.

While it is nice to have a workaround and use the alternate Linq syntax, it would have been good to have been able to use this syntax as it makes the queries appear simpler and uses knowledge that the ORM effectively already has on the data model. I guess I can also use Typed Lists, as these appear to offer good filtering and querying capabilities similar to those available in Linq.

petefitz wrote:

While it is nice to have a workaround and use the alternate Linq syntax, it would have been good to have been able to use this syntax as it makes the queries appear simpler and uses knowledge that the ORM effectively already has on the data model.

So unfortunately you came into this edge case. Maybe in future releases we have fixes for this.

petefitz wrote:

I guess I can also use Typed Lists, as these appear to offer good filtering and querying capabilities similar to those available in Linq.

Totally. You can use TypedLists/TypedViews/DynamicLists and also QuerySpec.

petefitz wrote:

Thanks for looking into this, Otis.

While it is nice to have a workaround and use the alternate Linq syntax, it would have been good to have been able to use this syntax as it makes the queries appear simpler and uses knowledge that the ORM effectively already has on the data model. I guess I can also use Typed Lists, as these appear to offer good filtering and querying capabilities similar to those available in Linq.

Yes, it's not something we're happy with, but unfortunately it's what we can do at this point today, otherwise we've to rewrite a lot of the linq provider to see if that can work (extensive expression tree rewriting first before we go in and interpret the tree). That's so much work that we won't do it now.

We wrote query spec which has similar expression power as linq and lets you write queries which always work (no edge cases) as it's somewhat closer to sql. You can do projections to anonymous and custom types so no datatables needed. Please take a look at query spec (it's beta but stable, will be merged into v3.5). It's another way of querying in cases when linq lets you run into an edge case or gives sub optimal queries as linq isn't 1:1 mappable onto SQL.