Issues with nested association property in query about iqtoolkit HOT 9 OPEN

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.

from iqtoolkit.

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.

from iqtoolkit.

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?

from iqtoolkit.

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?

from iqtoolkit.

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.

from iqtoolkit.

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.

from iqtoolkit.

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.

from iqtoolkit.

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?

from iqtoolkit.

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.

from iqtoolkit.