Title should be "Problem with projection with multiple ManyToOne navigations to any entity and further ManyToOne navigation from that entity." but I can't edit it alas.

Hopefully this test says it all:

[TestMethod, Description("Tests entity A with multiple ManyToOne relations to entity B" +   
"(which itself has a ManyToOne relation to entity C) can navigate to C (via all the navigators to B) correctly")]
public void TestMultipleRelationsToTheSameTableWithHopToRelated()
{
            var billOfMaterialProjection = from b in MetaSingletons.MetaData.BillOfMaterial
                                           where b.BillOfMaterialsID == 1404
                                           select new
                                                    {
                                                        b.BillOfMaterialsID,
                                                        b.UnitMeasureCode,
                                                        b.ComponentID,
                                                        ProductComponentSubcategoryID = b.ProductComponent.ProductSubcategoryID,
                                                        ProductComponentSubcategoryIDViaNav = b.ProductComponent.ProductSubcategory.ProductSubcategoryID,
                                                        b.ProductAssemblyID,
                                                        ProductAssemblySubcategoryID = b.ProductAssembly.ProductSubcategoryID,
                                                        ProductAssemblySubcategoryIDViaNav = b.ProductAssembly.ProductSubcategory.ProductSubcategoryID,
                                                    };
            var firstBillOfMaterialProjectionProjection = billOfMaterialProjection.First();
            Assert.AreEqual(firstBillOfMaterialProjectionProjection.ProductComponentSubcategoryID,
                            firstBillOfMaterialProjectionProjection.ProductComponentSubcategoryIDViaNav, "ProductComponentSubcategoryID");
            Assert.AreEqual(firstBillOfMaterialProjectionProjection.ProductAssemblySubcategoryID,
                            firstBillOfMaterialProjectionProjection.ProductAssemblySubcategoryIDViaNav, "ProductAssemblySubcategoryID"); //Fails
}

Last assert fails when it shouldn't.

AdventureWorks DB, SelfServicing LinqSupportClasses v3.1.11.0518 ORMSupportClasses v3.1.11.0721 Test is here: http://rapiddevbookcode.codeplex.com/SourceControl/changeset/view/91730#1678315

Try to rewrite it with joins. The b.ProductComponent. navigation for example is there more than once, which can go wrong, as you might know due to the alias assignment to these navigational properties.

Otis wrote:

The b.ProductComponent. navigation for example is there more than once, which can go wrong, as you might know due to the alias assignment to these navigational properties.

? - It still goes wrong even if it is only there once:

from b in BillOfMaterial
where b.BillOfMaterialsID == 1404
select new
{
  ProductComponentSubcategoryIDViaNav = b.ProductComponent.ProductSubcategory.ProductSubcategoryID,
  ProductAssemblySubcategoryIDViaNav = b.ProductAssembly.ProductSubcategory.ProductSubcategoryID,
}

Results in ProductComponentSubcategoryIDViaNav ==ProductAssemblySubcategoryIDViaNav==10 which is wrong, ProductAssemblySubcategoryIDViaNav should be 14 which it is if ProductComponentSubcategoryIDViaNav is removed or placed after ProductAssemblySubcategoryIDViaNav.

Using a join of course works:

from b in BillOfMaterial
where b.BillOfMaterialsID == 1404
join pa in Product on b.ProductAssemblyID equals pa.ProductID
join pasc in ProductSubcategory on pa.ProductSubcategoryID equals pasc.ProductSubcategoryID
select new
{
    ProductComponentSubcategoryIDViaNav = b.ProductComponent.ProductSubcategory.ProductSubcategoryID,
    ProductAssemblySubcategoryIDViaJoin = pasc.ProductSubcategoryID,
}

which gives 10 and 14 respectively.

Hi Jeremy,

The approx. sql query you want to execute is this:

SELECT 
    pSubCatComponent.ProductSubCategoryId ComponentSubCat,
    pSubCatAssembly.ProductSubCategoryId AssemblySubCat
FROM
    Production.BillOfMaterials bom
        INNER JOIN Production.Product pComponent on bom.ComponentId = pComponent.ProductId
        INNER JOIN Production.Product pAssembly on bom.ProductAssemblyId = pAssembly.ProductId
        INNER JOIN Production.ProductSubcategory pSubCatComponent on pSubCatComponent.ProductSubcategoryId = pComponent.ProductSubcategoryId
        INNER JOIN Production.ProductSubcategory pSubCatAssembly on pSubCatAssembly.ProductSubcategoryId = pAssembly.ProductSubcategoryId
WHERE bom.BillOfMaterialsId = 1404

however you get this: (RTL I used: 3.1.11.721)

SELECT TOP(1) 
    [LPA_L3].[ProductSubcategoryID] AS [ProductSubcatIdViaNav], 
    [LPA_L3].[ProductSubcategoryID] AS [AssemblySubcatIdViaNav] 
FROM 
    (
        (
            (   -- component -> product
                [AdventureWorks].[Production].[Product] [LPA_L1]  
                INNER JOIN [AdventureWorks].[Production].[BillOfMaterials] [LPA_L2]  
                    ON  [LPA_L1].[ProductID]=[LPA_L2].[ComponentID]
            ) 
            -- productSubCat -> product (???) joins only to product LPA_L1 (joined to component)
            LEFT JOIN [AdventureWorks].[Production].[ProductSubcategory] [LPA_L3]  
                ON  [LPA_L3].[ProductSubcategoryID]=[LPA_L1].[ProductSubcategoryID]
        ) 
        
        -- assembly -> product
        LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]  
            ON  [LPA_L4].[ProductID]=[LPA_L2].[ProductAssemblyID]
    ) 

WHERE ( ( ( ( ( ( [LPA_L2].[BillOfMaterialsID] = 1404))))))

As you can see, the problem is in the second join, it's used just once joined to LPA_L1 (which is joined to Components, not to assembly). Then in the SELECT, all SubcategoryId's are obtained form this second relation, that's why you obtain the same SubcategoryId.

This has been a known issue since some time: the member (ProductSubcategory) is aliased in the first occurrence. The second occurrence, it has to get a different alias as it has a different path to the member. However this isn't done, the member gets the same alias, which goes wrong. See this related thread: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=17758&StartAtMessage=0&#99406

To overcome this you should use explicit joins in your linq query (which Frans advised above). Example:

[TestMethod]
public void TestMultipleRelationsToTheSameTableWithHopToRelated2()
{
    var adapter = new DataAccessAdapter();
    var metaData = new LinqMetaData(adapter);

    // query
    var billOfMaterialProjection = from bom in metaData.AssemblyPart
                                        
                                    // component related joins
                                    join componentProduct in metaData.Product on bom.ComponentId equals componentProduct.ProductId 
                                    join componentSubCategory in metaData.ProductSubcategory on componentProduct.ProductSubcategoryId equals componentSubCategory.ProductSubcategoryId
                                        
                                    // assembly part related joins
                                    join assemblyProduct in metaData.Product on bom.ProductAssemblyId equals assemblyProduct.ProductId
                                    join assemblySubCategory in metaData.ProductSubcategory on assemblyProduct.ProductSubcategoryId equals assemblySubCategory.ProductSubcategoryId
                                        
                                    where bom.BillOfMaterialsId == 1404
                                    select new
                                    {
                                        ProductSubcatIdViaNav = componentSubCategory.ProductSubcategoryId,
                                        AssemblySubcatIdViaNav = assemblySubCategory.ProductSubcategoryId
                                    };

    // fetch
    var result = billOfMaterialProjection.First();

    // tests
    Assert.AreEqual(10, result.ProductSubcatIdViaNav, "ProductComponentSubcategoryID");
    Assert.AreEqual(14, result.AssemblySubcatIdViaNav, "ProductAssemblySubcategoryID");
}

daelmo wrote:

This has been a known issue since some time: the member (ProductSubcategory) is aliased in the first occurrence. The second occurrence, it has to get a different alias as it has a different path to the member. However this isn't done, the member gets the same alias, which goes wrong. See this related thread: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=17758

And obviously you didn't get around to fixing it in 3.x like Frans hoped, shame. Given that I think it's worth an entry in the "Generated code - Linq to LLBLGen Pro, Remarks and limitations" section of the documentation - 'lame' as the workaround maybe.

Thanks for the response.

We did fix the majority of cases, but after v3 shipped we discovered (I think it was you who brought it to our attention, not sure) that there are cases which aren't fixed, because the fix we implemented requires more information to fix the other situations as well: it needs the path to the navigator to understand that it's a different situation and thus should get a different alias, as there's also a situation where the navigator, when occurring more times than once, should get the same alias.

You have to know that it pains me deeply that you ran into this problem, and I'm deeply sorry that this is occurring, once again . As it's extremely frustrating to find these kind of bugs in Linq, we wrote queryspec, which comes close to linq's expression power but also has the advantage that things always work, as it's determinable code: the building blocks are always known and when needed you can supply the alias yourself. The problem with Linq is that it's never known whether the code covers all situations, and because of that it falls into trial&error programming, which is never good.

I'd encourage you to use queryspec in these cases (you can use it next to linq queries) to avoid loss of time when you run into the rare, but still annoying issues in our linq provider.

Fixing this is not easy, to say the least. With query spec we did implement a way to determine expression subtree equality so we can re-use that to find paths to navigators, but it's unclear at this point whether that will fix 100% of the cases (as it is hard with linq to prove the code covers all cases, which is the frustrating part).

Ok, I'll give it one shot, with the expression key generator we wrote for queryspec, so we can determine a unique key for an expression sub tree. This is something we can use to determine whether the source of a memberexpression is new or already found, and use that to obtain an alias.

I have no idea whether this will solve it, but it's worth a shot.

(Edit) more deeper explanation about the problem: The query executed:

SELECT TOP 1 [LPA_L2].[BillOfMaterialsID]   AS [BillOfMaterialsId],
             [LPA_L2].[UnitMeasureCode],
             [LPA_L2].[ComponentID]       AS [ComponentId],
             [LPA_L1].[ProductSubcategoryID] AS [ProductComponentSubcategoryID],
             [LPA_L3].[ProductSubcategoryID] AS [ProductComponentSubcategoryIDViaNav],
             [LPA_L2].[ProductAssemblyID]   AS [ProductAssemblyId],
             [LPA_L4].[ProductSubcategoryID] AS [ProductAssemblySubcategoryID],
             [LPA_L3].[ProductSubcategoryID] AS [ProductAssemblySubcategoryIDViaNav]
FROM   ((( [AdventureWorks].[Production].[Product] [LPA_L1]
           INNER JOIN [AdventureWorks].[Production].[BillOfMaterials] [LPA_L2]
               ON [LPA_L1].[ProductID] = [LPA_L2].[ComponentID])
         LEFT JOIN [AdventureWorks].[Production].[ProductSubcategory] [LPA_L3]
             ON [LPA_L3].[ProductSubcategoryID] = [LPA_L1].[ProductSubcategoryID])
        LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
            ON [LPA_L4].[ProductID] = [LPA_L2].[ProductAssemblyID])
WHERE  (((((([LPA_L2].[BillOfMaterialsID] = 1404 /* @p1 */))))))

b.ProductComponent.ProductSubcategory and b.ProductAssembly.ProductSubcategory

ProductComponent and ProductAssembly both return a 'ProductEntity' type, and the v3 fix is that they indeed get different aliases, so at least something works there, however the navigator 'ProductSubcategory' is different too, as the Product instance is different. However, as you can see in the query, it doesn't see it that way, as the linq provider sees 'ProductSubcategory' and looks whether it already has an alias for that member and the type 'Product'. It indeed has in the second occurrence, namely the one it issued out for the first occurrence.

This leads to a join of ProductSubcategory with the same alias, which is the same relation which is already there, and therefore not added to the query.

This is why the 'path' to the navigator is important too, as that path (b.ProductComponent. and b.ProductAssembly.) could distinguish one Product.ProductSubcategory from the other one. As this is undoable without being able to determine whether an expression subtree is equal to another, we couldn't fix this properly.

With query spec, in the lambda compiler cache we needed this and we found a way to do it. I'll now try to see whether I can re-use that unique key creator code to determine whether the expression subtree of a memberexpression can be checked against known trees for the same navigator so aliases can be issued for it properly.

I now get a succeeding test and this query: (removed irrelevant fields to have less member expressions to wade through)

SELECT TOP 1 [LPA_L1].[ProductSubcategoryID] AS [ProductComponentSubcategoryID],
             [LPA_L3].[ProductSubcategoryID] AS [ProductComponentSubcategoryIDViaNav],
             [LPA_L4].[ProductSubcategoryID] AS [ProductAssemblySubcategoryID],
             [LPA_L5].[ProductSubcategoryID] AS [ProductAssemblySubcategoryIDViaNav]
FROM   (((( [AdventureWorks].[Production].[Product] [LPA_L1]
            INNER JOIN [AdventureWorks].[Production].[BillOfMaterials] [LPA_L2]
                ON [LPA_L1].[ProductID] = [LPA_L2].[ComponentID])
          LEFT JOIN [AdventureWorks].[Production].[ProductSubcategory] [LPA_L3]
              ON [LPA_L3].[ProductSubcategoryID] = [LPA_L1].[ProductSubcategoryID])
         LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
             ON [LPA_L4].[ProductID] = [LPA_L2].[ProductAssemblyID])
        LEFT JOIN [AdventureWorks].[Production].[ProductSubcategory] [LPA_L5]
            ON [LPA_L5].[ProductSubcategoryID] = [LPA_L4].[ProductSubcategoryID])
WHERE  (((((([LPA_L2].[BillOfMaterialsID] = 1404 /* @p1 */))))))

As you can see, productsubcategory is joined twice as it should have been.

It's implemented a bit shaky now, as the datastructures used memberinfo, and now have two parameters, so I have to make sure just 1 is used everywhere. This will take some time, and also have to make sure all tests work so I hope to have a fixed dll later today, otherwise tomorrow.

You have to know, I never thought it would be fixed, but the expression key generator visitor of queryspec was the key (pun? ) to the solution.

(edit) this now also works:

[Test]
public void ParentParentParenttest()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);

        var q = from emp in metaData.Employee
                where emp.EmployeeId == 6
                select new
                {
                    id = emp.EmployeeId,
                    id2 = emp.Employee.EmployeeId,
                    id3 = emp.Employee.Employee.EmployeeId
                };

        var v = q.First();
        Assert.AreEqual(6, v.id);
        Assert.AreEqual(5, v.id2);
        Assert.AreEqual(4, v.id3);
    }
}

Otis wrote:

I now get a succeeding test.

It's implemented a bit shaky now, as the datastructures used memberinfo, and now have two parameters, so I have to make sure just 1 is used everywhere. This will take some time, and also have to make sure all tests work so I hope to have a fixed dll later today, otherwise tomorrow.

You have to know, I never thought it would be fixed, but the expression key generator visitor of queryspec was the key (pun? ) to the solution.

Awesome , from a technical point of view I bet you wish you wrote queryspec before your Linq provider. No hurry for a solution from our end.

TomDog wrote:

Otis wrote:

I now get a succeeding test.

It's implemented a bit shaky now, as the datastructures used memberinfo, and now have two parameters, so I have to make sure just 1 is used everywhere. This will take some time, and also have to make sure all tests work so I hope to have a fixed dll later today, otherwise tomorrow.

You have to know, I never thought it would be fixed, but the expression key generator visitor of queryspec was the key (pun? ) to the solution.

Awesome , from a technical point of view I bet you wish you wrote queryspec before your Linq provider.

Well, I could re-use ideas from the linq provider in queryspec, so it wasn't totally wasted, plus, when we released the linq provider, everyone wanted to do linq, so I don't regret writing it, I just hate the way MS wrote it and how complex things are. Queryspec is IMHO definitely better, simply because it leads to better predictable queries, but alas, not everyone thinks that's important

No hurry for a solution from our end.

Some tests fail, so I think it will be tomorrow, but perhaps I'm lucky. I'll post an update with a temp build when we're done

Fixed!

There's one caveat: it doesn't always work, namely in situations where a source is wrapped into something else, which mostly happens inside groupby's, e.g. when you do group by {a, b, c} where a, b and c are entities obtained from a join.

In these situations, it falls back to the original system and it could be it fails again, but for the rest (read: the majority of cases the issue of this thread pops up) this fix should make it work. See attached dll

Yep that did the trick. Very encouraging - you must be happy.

Yes, I'm glad I finally got this bug fixed, without having to rewrite a lot of code