Relationship equality - a not-so-simple enhancement

Jan 18, 2011 at 10:11 PM
I ran into another issue (or at least it will be an issue for us). I have some POCOs set up to represent some tables, as well as an XML mapping file that sets up the associations between them. While my experience was with our internal database I'll distill the issue into Northwind syntax in the hopes that somebody can comment. Let's say I want to have a method that accepts a Product object and it wants to return a list of all OrderDetails that include that object. You could write a query like var results = from orderdetail in northwind.OrderDetails where orderdetail.ProductID == productToCheck.ProductID select orderdetail; I might have expected a result similar to this: var results = from orderdetail in northwind.OrderDetails where orderdetail.Product.ProductID == productToCheck.ProductID select orderdetail; It would be preferable (and more intuitive) to write something like var results = from orderdetail in northwind.OrderDetails where orderdetail.Product == productToCheck select orderdetail; However, trying this with my schema produces an exception. Instead of treating this as a condition on the primary key (as seen in the first and second examples) it treats it as an outer join to the Product table. I'm not afraid to get my hands dirty to fix this and submit the changes, but due to the size and scope of the project in conjunction with the usage of reflection it's incredibly difficult to get a firm grip on what code I should even be looking at to alter this behavior. I've read through every single one of the tutorials (a daunting and exhausting task, I assure you) but the codebase has undergone extensive changes and it scarcely resembles what is presented in parts 1-10. Help would be greatly appreciated.
Jan 21, 2011 at 5:35 AM

First off, much thanks to Matt for his quick reply via e-mail. I fear that these discussion boards are becoming something of a ghost town.

There is an existing test that checks whether a similar case works correctly (TestCompareEntityEqual). It basically builds a partial Customer with only the Primary Key populated and retrieves the remaining data using comparison operators. If you change that slightly to attempt to get all orders for a partial customer

        public void TestCompareEntityAssociationEqual()
        {
            Customer alfki = new Customer { CustomerID = "ALFKI" };
            var list = db.Orders.Where(o => o.Customer == alfki).ToList();
            this.AssertValue(6, list.Count);
            this.AssertValue("ALFKI", list[0].Customer.CustomerID);
        }

the test will fail. The immediate point where this happens is when the formatter is attempting to get the proper variable declaration for @p0. A glance at the expression showed me a woefully mangled construction:

{SELECT t0.CustomerID, t0.OrderDate, t0.OrderID
FROM Orders AS t0
LEFT OUTER JOIN Customers AS t1
  ON (t1.CustomerID = t0.CustomerID)
WHERE (?1017?(t1.CustomerID?1005?(?MemberInit?(?new?Customer()t1.Cityt1.CompanyNamet1.ContactNamet1.Countryt1.CustomerIDt1.Phone))) = @p0)}

This has a few errors. If this is handled as a join, it would have to be an inner join in order to return the correct result set and the Where clause should be (t1.CustomerID = @p0).

Even better would be to realize that with the join and where clauses written as corrected everything simplifies down to not joining to anything with a where clause of (t0.CustomerID = @p0). I believe that if the previous error were fixed some of the existing rewriters would handle this part as a welcome side effect.

I'm still trying to discover where the query is being mangled as shown - tracking down bugs in such an inheritance and recursion heavy library is a difficult endeavor.

Mar 17, 2011 at 1:44 PM
Edited Mar 17, 2011 at 1:45 PM

Is there any news about this?

Also got a problem with this (only a bit different)

var persons = from a in tables.Persons() where tables.Languages().Where(l => l.LanguageName == "Dutch").Select(l => l.LanguageId).Contains(a.PersonUser.LanguageId) select a;

// Second query:

var ppersons = from a in tables.Persons() where a.PersonUser.Languages.LanguageName == "Dutch" select a;

Those should give the same queries, but the last one doesn't get converted

The first one will give the right subquery:

WHERE t1.[LanguageId] IN ( SELECT t6.[LanguageId] FROM [Languages] AS t6 WHERE (t6.[LanguageName] = @p0) )

The second one will fail and give:

WHERE (?1017?(t2.[PersonId]?1005?(?MemberInit?(?new?PersonUser()t1.[LanguageId]t2.[PersonId]t1.[UserActive]t1.[UserId]t1.[UserPassword]t1.[UserUsername]t1.[UserWindowsAccount])).Languages.LanguageName) = @p0)

Hope there is something to fix this problem, besides this it's working great!

Dec 23, 2011 at 2:40 AM
Edited Dec 23, 2011 at 2:42 AM

The problem mythgarr reported seems to be that the customer variable 'alfki' is a constant, and constants aren't presumed to have any mapping in the database. In this case, we know which mapping we want for the type Customer, but for the linq query provider to see that mapping, it would need to infer that the Customer type is referenced in the Northwind.cs class by the Customers property inside of it. Based on the attributeMapping scheme that these test cases are using, it would be entirely possible for someone to create several mappings that use the same 'Customer' type, and they could go to completely different tables.

Rewriting the query as follows seems to work, to demonstrate that the feature does work when you actually have a mapping for the keys.

 

from o in db.Orders 

from c in db.Customers

where c.CustomerID == alfki.CustomerID &&

          o.Customer == c

select o;

 

It seems to me like there may be a reasonable way for the linq query builder to infer the mappings we want to use in this scenario. For the attribute mapping in particular, we can't just say that it's of type customer, so it has mapping X, because the same type can be used with different mappings (I haven't actually tried doing this yet it looks like you could do that).


public void TestCompareEntityAssociationEqual()

{

    Customer alfki = new Customer { CustomerID = "ALFKI" };

    var list = db.Orders.Where(o => o.Customer == alfki).ToList();

    this.AssertValue(6, list.Count);

    this.AssertValue("ALFKI", list[0].Customer.CustomerID);

}

To infer the appropriate mapping, querybuilder can look at the binary expression between o.Customer and alfki. It can say that o.Customer has a mapping, and also that o.Customer has the same type as alfki. With these two facts, transitivity kicks in and says that alfki has the same mapping as o.Customer.

So the root cause is identified. What is the appropriate solution?

{SELECT t0.CustomerID, t0.OrderDate, t0.OrderID

FROM Orders AS t0

LEFT OUTER JOIN Customers AS t1

  ON (t1.CustomerID = t0.CustomerID)

WHERE (?1017?(t1.CustomerID?1005?(?MemberInit?(?new?Customer()t1.Cityt1.CompanyNamet1.ContactNamet1.Countryt1.CustomerIDt1.Phone))) = @p0)}

The relationship binder will replace a reference to an entity with, projection, over the entities primary key members (e.g.)

(?1017?(t1.CustomerID?1005?(?MemberInit?(?new?Customer()t1.Cityt1.CompanyNamet1.ContactNamet1.Countryt1.CustomerIDt1.Phone)))

and will also put a new outer join in the from clauses (e.g.)

LEFT OUTER JOIN Customers AS t1

  ON (t1.CustomerID = t0.CustomerID)

Then, the ComparisonRewriter will replace, projection, with a valid comparison. When @p0 is a constant it can't rewrite the comparison  (projection =@p0)

So RelationshipBinder needs to implement VisitBinary() and detect the comparison between a constant and an entity of the same underlying type. When it does detect this scenario, it needs to rewrite that constant into a selection on a table, also placing a join in the from clauses. It also needs to evaluate those members of the constant that comprise the primary key, and select on the newly joined table with those constraints.

Another alternative might have been to rewrite the ComparisonRewriter to look for constants of this type. I decided against this for a few reasons. Firstly is that i am a SQL purist and like to explictly join to tables in relations, thus ensuring that the foreign key is a valid one before bringing back data. Second, without an explicit join across tables, query policies could not be applied to the relations joined over constants.

I've only been playing with this code for a week now so this implementation is probably sub-optimal however all the SqlClient unit tests pass, as do a couple new test cases i've created, so i'm reasonably excited to release my changes.

You can find this patch in the new ticket i created in the issue tracker. http://iqtoolkit.codeplex.com/workitem/17121