Issues with nested association property in query

[techseat]

Based on Test Database (Northwind), Consider we are trying to query Order Details table to get related records to an specific customer:
Note: I declared one missed association field Order in Entity OrderDetails (that already exists in database) before writing this test

        public void TestOrderDetailsByCustomer()
        {
            var q = from d in db.OrderDetails
                    where d.Order.Customer.CustomerID == "VINET"
                    select d;

            OrderDetail[] so = q.ToArray();
            ...
        }

Excuting this query will fail with Exception:

The LINQ expression node of type 1017 is not supported

This is one of common circumstances in some kind of Apps (with one-to-one relation, used to expand fields into another table, etc) same as what already exits in Northwind Database.

Is there a mistake or bug?

[techseat]

When I tried to add some more new tests by select over association properties, I encountered another exceptions and or invalid Translations.

For example, Consider to query Order Details table in Northwind Database to list related customer names, executing the test query below:

public void TestCustomerFromOrderDetails()
{
    var q = from od in db.OrderDetails
            where od.Order != null
            group od by od.Order.Customer into odc
            select odc.Key.CustomerID;
    ...
}

Or

public void TestCustomerFromOrderDetails()
{
    var q = from od in db.OrderDetails
            where od.Order != null
            group od by od.Order.Customer.CustomerID into odc
            select odc.Key;
    ...
}

Translated Sql statement is:

SELECT t0.[OrderID]
FROM [Order Details] AS t0
LEFT OUTER JOIN [Orders] AS t1
  ON (t1.[OrderID] = t0.[OrderID])
WHERE t1.[OrderID] IS NOT NULL
GROUP BY t0.[OrderID], t0.[ProductID]

SELECT t0.[CustomerID], t0.[OrderDate], t0.[OrderID]
FROM [Orders] AS t0
WHERE (t0.[OrderID] = @n0)

Which seems to trying to group by on Order rather than Customer.

After removing of group by clause, invalid behavior persists. Executing below query:

public void TestCustomersFromOrderDetails()
{
    var q = from d in db.OrderDetails
            where d.Order != null
            select d.Order.Customer.ContactName;
    ...
}

Was introduced Null Reference Exception:

Object reference not set to an instance of an object

Even after making query more simpler and query for Customer through OrderDetails, invalid behavior persists, And returns a list of null after execution:

db.OrderDetails.Select(i => i.Order.Customer);

Translated to:

SELECT t1.[OrderID] AS [OrderID1], t1.[CustomerID], t1.[OrderDate]
FROM [Order Details] AS t0
LEFT OUTER JOIN [Orders] AS t1
  ON (t1.[OrderID] = t0.[OrderID])

these behaviors may be related and caused from one wrong thing.

[techseat]

After a deeper digging to it, I found a thing that may help to resolve this problem. (I hope at least in some cases)
Consider test query below:

var q = db.OrderDetails.Where(i => i.Order != null).Select(i => i.Order.Customer.CompanyName);
var result = q.ToArray();

Executing this test will fail with Exception:

Object reference not set to an instance of an object

But if we edit Method VisitMemberAccess in class RelationshipBinder from:

        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            Expression source = this.Visit(m.Expression);
            EntityExpression ex = source as EntityExpression; 
            ...
        }

To:

        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            Expression source = this.Visit(m.Expression);
            EntityExpression ex = source as EntityExpression?? (source as ProjectionExpression)?.Projector as EntityExpression; 
            ...
        }

Deep relation properties issue in select will be fixed (probably as a temporary workaround) because automatic joins will be added and Therefore true Translation and true Result will be retuned (also for this kind of queries). Although, issues in 'group by' clause and some problems in where probably will be persist.
What I observed in Method VisitMemberAccess (not modified version) is:
It seems result returned from visiting expression m.Expression (assigned to variable named source) sometimes that is expected to be processed as an entity expression, is of type ProjectionExpression and is not directly an EntityExpression, though its projector is, (source.Projector is EntityExpression). But currently, we are skipping it in the method and the chain of relations will break here and another invalid next behaviors will occur.

Is it expected or is a mistake exists here or elsewhere caused this?
What is the right address causes generating this issue and we must change where to came on it truly?

[techseat]

Dear @mattwar , I'm interested to the beautiful toolkit IQtoolkit and your interesting work here, and I wish IQtoolkit to be better and better than it is now.

As I feel you are busy, I will glad if I can take engaged resolving issues, Specially this one that I feel is important for production use. However, I might not be so specialist and experienced in this context versus you, But I had some experiments in processing expression trees and SQL in ORMs programming and I has motivation to this.

Can you provide some explanation and comments specially about this issue and feedback me?

[mattwar]

I have not looked at it yet, but my guess is that the translation of the association is being removed by one of the steps that tries to simplify the SQL query. Group by is one of the most complicated translations. I'm not surprised it has bugs related to embedded associations.

[mattwar]

My guess on the problem with EntityExpression is that this translator (meaning me when I wrote it) is assuming that member expressions are going to be imply field/property references and not associations. Associations will generate entire sub-queries which is why ProjectionExpression is showing up.

[mattwar]

I fixed the problem in the first example. It was the Relationship binder not understanding how to deal with the OuterJoinedExpression.

The group by and select problem have to do with the translation in the QueryBinder that happens long before the RelationshipBinder is run. This will take more thought.

[techseat]

It seems ComparisonRewriter not operates truly in group by in case of deep associations when there is need to check for outer-joined entity comparing against null (in VisitBinary when it calls Compare method to make is-null through MakeIsNull, it sticks to the first association and makes is null for that one, and leaves next ones alone). Probably, Some of the group by problems is to ComparisonRewriter when rewrites some of conditions invalid.

[mattwar]

The problem with deep 1-to-1 associations in Select has been fixed. But still a problem with GroupBy. Did I say GroupBy is complicated?

[afruzan]

I have similar problem with group by. for example:

var q = db.OrderDetails.Select(o => new { o.Order, x = new { o.Product.ID, o.ProductID } }).Where(i => i.x.ProductID == i.x.ID).GroupBy(i => i.x.ID).Select(i => new { Date = i.Key, Count = i.Count() });
var qtext = this.GetProvider().GetQueryText(q.Expression);

results in invalid query text. I used Select(..) before GroupBy(...) to overcome the problem but the problem still exist.
But

var q = db.OrderDetails.Select(o => new { o.Order, x = new { o.Product.ID, o.ProductID } }).Where(i => i.x.ProductID == i.x.ID).GroupBy(i => i.x.ProductID).Select(i => new { Date = i.Key, Count = i.Count() });
var qtext = this.GetProvider().GetQueryText(q.Expression);

results in true query text. Note i.x.ProductID is not actually from an association.