Complex query with multiple joins and LINQ, how ?
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Joined: 23-Apr-2009
Though it's the exact same query you posted earlier which I used to reproduce it and also to test to see if the fix worked:
var q = from c in metaData.Customer
join o in metaData.Order on c.CustomerId equals o.CustomerId
join od in metaData.OrderDetail on o.OrderId equals od.OrderId
join p in metaData.Product on od.ProductId equals p.ProductId
group new { c, o, od, p } by new { od.OrderId, c.CompanyName } into g
where g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) > 100.0M
select new { CompanyName = g.Key.CompanyName, Total = g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) };
The only thing which might be different here is that I compared it to a decimal value, not an integer, and perhaps I don't get conversions to decimal where you get them. I'll check where the SQL queries differ.
(edit) uhoh 
. I simplified the unittest to get to the smallest query possible to reproduce the problem (as it's otherwise a major headache to debug this as the expression tree is incredibly big and complex otherwise) and this simplification did work, but your query indeed doesn't. My fault, sorry for that. I'll look into fixing this. I've an idea where this issue is located, so I hope to have a fix soon. A linq provider is unfortunately so massively complex that these kind of issues are hard to avoid, as it's hard to predict if some code is really OK based on analysis. (unittests won't help here, there's always a situation which might make things fail and which is different from the situations tested).
Joined: 23-Apr-2009
Otis wrote:
Though it's the exact same query you posted earlier which I used to reproduce it and also to test to see if the fix worked:
var q = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId join od in metaData.OrderDetail on o.OrderId equals od.OrderId join p in metaData.Product on od.ProductId equals p.ProductId group new { c, o, od, p } by new { od.OrderId, c.CompanyName } into g where g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) > 100.0M select new { CompanyName = g.Key.CompanyName, Total = g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) };The only thing which might be different here is that I compared it to a decimal value, not an integer, and perhaps I don't get conversions to decimal where you get them. I'll check where the SQL queries differ.
(edit) uhoh
Don't worry 
, LLBLGenPro is not the only one having difficulties with LINQ regarding complex queries
DrDelete wrote:
Otis wrote:
Though it's the exact same query you posted earlier which I used to reproduce it and also to test to see if the fix worked:
var q = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId join od in metaData.OrderDetail on o.OrderId equals od.OrderId join p in metaData.Product on od.ProductId equals p.ProductId group new { c, o, od, p } by new { od.OrderId, c.CompanyName } into g where g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) > 100.0M select new { CompanyName = g.Key.CompanyName, Total = g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) };The only thing which might be different here is that I compared it to a decimal value, not an integer, and perhaps I don't get conversions to decimal where you get them. I'll check where the SQL queries differ.
(edit) uhoh
Don't worry
Well, some competitors have released 'linq' providers which can't do much more than simple queries, and it's no surprise they'll fail in more complex queries. They also didn't spend that much time on them, considering the fact the code base of these providers is smaller. We did spend a lot of time on the linq provider to get a provider which can eat any query. If ours fails too, we apparently have spent time for nothing . We know to get the 100% query coverage is almost impossible outside MS, but still we do want to get as close to 100% as possible. As you've seen, we do have workarounds in the form of our native query api, but linq queries should work too.
I'm pretty close to where the problem is. It's related to folding of the groupby source into a derived table when multiple aggregates are used: the fields for the second aggregate's expression aren't placed in the projection of the derived table. For 1 level this works, but for all extra levels this fails (and your complex query is a multi level one). The solution is to add the fields in the expression to aggregate to all projections in the derived tables already folded. (as there may be 3 or more aggregates). It doesn't fail always as we do have multi-aggregate groupby tests which work (also aggregates on aggregates), though these aggregates are on fields only, not on expressions.
Stay tuned.
Ps: which frameworks failed as well? EF? On linq to sql I can run the query without a problem
Joined: 23-Apr-2009
Otis wrote:
DrDelete wrote:
Otis wrote:
Though it's the exact same query you posted earlier which I used to reproduce it and also to test to see if the fix worked:
var q = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId join od in metaData.OrderDetail on o.OrderId equals od.OrderId join p in metaData.Product on od.ProductId equals p.ProductId group new { c, o, od, p } by new { od.OrderId, c.CompanyName } into g where g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) > 100.0M select new { CompanyName = g.Key.CompanyName, Total = g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) };The only thing which might be different here is that I compared it to a decimal value, not an integer, and perhaps I don't get conversions to decimal where you get them. I'll check where the SQL queries differ.
(edit) uhoh
Don't worry
Well, some competitors have released 'linq' providers which can't do much more than simple queries, and it's no surprise they'll fail in more complex queries. They also didn't spend that much time on them, considering the fact the code base of these providers is smaller. We did spend a lot of time on the linq provider to get a provider which can eat any query. If ours fails too, we apparently have spent time for nothing
I'm pretty close to where the problem is. It's related to folding of the groupby source into a derived table when multiple aggregates are used: the fields for the second aggregate's expression aren't placed in the projection of the derived table. For 1 level this works, but for all extra levels this fails (and your complex query is a multi level one). The solution is to add the fields in the expression to aggregate to all projections in the derived tables already folded. (as there may be 3 or more aggregates). It doesn't fail always as we do have multi-aggregate groupby tests which work (also aggregates on aggregates), though these aggregates are on fields only, not on expressions.
Stay tuned.
Ps: which frameworks failed as well? EF? On linq to sql I can run the query without a problem
NHibernate failed also and I had to use HQL for this to get it work, tommorow ADO.EF will be tested.
DrDelete wrote:
Otis wrote:
DrDelete wrote:
Otis wrote:
Though it's the exact same query you posted earlier which I used to reproduce it and also to test to see if the fix worked:
var q = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId join od in metaData.OrderDetail on o.OrderId equals od.OrderId join p in metaData.Product on od.ProductId equals p.ProductId group new { c, o, od, p } by new { od.OrderId, c.CompanyName } into g where g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) > 100.0M select new { CompanyName = g.Key.CompanyName, Total = g.Sum(x => x.od.Quantity * x.p.UnitPrice.Value) };The only thing which might be different here is that I compared it to a decimal value, not an integer, and perhaps I don't get conversions to decimal where you get them. I'll check where the SQL queries differ.
(edit) uhoh
Don't worry
Well, some competitors have released 'linq' providers which can't do much more than simple queries, and it's no surprise they'll fail in more complex queries. They also didn't spend that much time on them, considering the fact the code base of these providers is smaller. We did spend a lot of time on the linq provider to get a provider which can eat any query. If ours fails too, we apparently have spent time for nothing
I'm pretty close to where the problem is. It's related to folding of the groupby source into a derived table when multiple aggregates are used: the fields for the second aggregate's expression aren't placed in the projection of the derived table. For 1 level this works, but for all extra levels this fails (and your complex query is a multi level one). The solution is to add the fields in the expression to aggregate to all projections in the derived tables already folded. (as there may be 3 or more aggregates). It doesn't fail always as we do have multi-aggregate groupby tests which work (also aggregates on aggregates), though these aggregates are on fields only, not on expressions.
Stay tuned.
Ps: which frameworks failed as well? EF? On linq to sql I can run the query without a problem
NHibernate failed also and I had to use HQL for this to get it work, tommorow ADO.EF will be tested.
NHibernate doesn't have a proper linq provider which can do even joins (especially join into (groupjoin)) so no surprise there .
I've fixed it for your query setup (2-layer folding). Multiple layer folding has some small error (multiple times the same fields added to the derived tables) but that should be minor. Almost there! 
Joined: 23-Apr-2009
Joined: 23-Apr-2009
When I convert your initial query to SQL to be equal to the query that's been generated:
SELECT c.CompanyName, SUM(od.Quantity * p.UnitPrice) AS Total
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.OrderID, c.CompanyName
HAVING SUM(od.Quantity * p.UnitPrice) > 100
I get the same results as the generated query. (so with duplicated names, different totals). I.o.w., I can't reproduce what you're seeing.
(edit) hmm. I think the difference is that CompanyName is unique, LastName is likely not unique. I'll rewrite the queries.
(edit) WHen I use 'ContactTitle' (which isn't unique) instead of CompanyName, I get the same number of rows. What might be different in your dataset is that there is more than 1 lastname per order? Is that the case in your situation?
(edit) you also seem to have the summation wrong, am I correct? I mean, don't you want to have the Jansen values aggregated again to 1 value and compare THAT to 100?
Equal to this:
[Test]
public void GetAllCustomersWithAnOrderTotalHigherThan1000ForOrdersWithProductsWithAnIdBiggerThan40()
{
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter);
var q = from c in metaData.Customer
where c.Orders.Sum(o => o.OrderDetails.Where(od => od.ProductId > 40).Sum(od => od.Quantity * od.UnitPrice)) > 1000
select c;
int counter = 0;
foreach(var v in q)
{
counter++;
}
Assert.AreEqual(68, counter);
}
}
Remove the where clause on orderdetails and you're almost where you want to be. (sum over the totals per order (again a sum), per customer)
(edit) hmm, you need the unitprice from 'Product' instead. Using od.Product.UnitPrice doesn't include the join ... 
This is really one of these days where you type in 1 extra element and everything breaks apart ... again. In our own query api we haven't had a bug in ... over a year or so... In linq ... not so much.
var q = from c in lm.Contact
let total = c.Order.Sum(o=>o.OrderRow.Sum(od=>od.Amount * od.Product.Price))
where total > 100
select new {c.LastName, Total = total};
This is the linq query I think. It crashes (here) with the relationship od.Product not being joined. Using od.UnitPrice works here (but I think your model doesn't have the price on orderrow)