Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Unexpected results for SelectMany().Count
 

Pages: 1
LLBLGen Pro Runtime Framework
Unexpected results for SelectMany().Count
Page:1/1 

  Print all messages in this thread  
Poster Message
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 20-May-2016 12:27:29.  
Against Northwind
Code:
from c in Customer
select new
{
    c.CompanyName,
    c.ContactName,
    c.ContactTitle,
    c.CustomerId,
    OrderDetailsCount = c.Orders.SelectMany(o=>o.OrderDetails).Count()
}
Gives an OrderDetailsCount of 2135 for every row.
The same query in memory
Code:
from c in Customer
.WithPath(cp => cp.Prefetch<OrderEntity>(c => c.Orders).SubPath(i => i.Prefetch(inv => inv.OrderDetails))).List()
select new
{
    c.CompanyName,
    c.ContactName,
    c.ContactTitle,
    c.CustomerId,
    OrderDetailsCount = c.Orders.SelectMany(o=>o.OrderDetails).Count()
}
gives the expected order details count, ALFKI has 12, ANATR has 10 etc

Shouldn't both queries give the same result?

Version 4.2.16.0414
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 20-May-2016 17:10:14.  
We'll look into it. Likely the SelectMany which in general converts to a cross join. Will be next week.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 23-May-2016 01:25:24.  
Otis wrote:
We'll look into it. Likely the SelectMany which in general converts to a cross join. Will be next week.
Thanks, of course you can rewrite
Code:
CustomerOrderDetailsCount = c.Orders.SelectMany(o=>o.OrderDetails).Count()
as
c.Orders.Sum(o=>o.OrderDetails.Count())
which works but still I would expect the first to work as well
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 23-May-2016 14:16:18.  
The root cause is that the correlation relationship Customer-Order is used for the join and therefore isn't seen as a correlation relationship which _also_ should be used for the where clause to correlate inner with outer query. If you do:

OrderCount = c.Orders.Count()

it works because the correlation relationship isn't used in anything and it's used to correlate inner with outer. There is code for this, as multiple hop navigations do work in this context, it's now a quest to see how it can be used with SelectMany calls as well.

I hope to find a solution, but as SelectMany is a pain to deal with, it might be problematic.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 23-May-2016 14:48:52.  
E.g. this query works fine:
Code:

[Test]
public void CountOnMultipleHopRelatedCollectionInProjection()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from od in metaData.OrderDetail
                select new
                {
                    od.OrderId,
                    MiscCount = od.Order.Employee.EmployeeTerritories.Count()
                };
        foreach(var v in q)
        {
            Assert.IsFalse(v.MiscCount > 100);
        }
    }
}


Here, the correlation relationship is both used in the join and in the where to correlate the inner query. So it _should_ in theory be easy to fix.. Wink
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 23-May-2016 16:16:16.  
It's sadly not possible, or at least I have no idea how to do this. The main issue is that in the case of SelectMany(), the source for the Aggregate (count in this case) is a join / query. I first thought the correlation relation of the left side was usable as it's what's tying the query to the root type, but that's not the case: the correlation relation isn't usable in this case, as the correlation is implicit: it's due to the usage of 'c' in the selectmany source that the query is correlated to the outer query, which also fetches customer rows.

And here lies the problem: handling the aggregate, there's no information about the outer query: I have the join, the projection on it and the c.Orders correlation relation but no idea how to tie 'c' to the outer query as I don't know the alias of the outer query, so I can't create the correlation predicate.

With the example I gave with Count() on a navigator it works because the correlation relationship is removed from the join and is used as a where predicate, which is a different situation than this query, which somehow has to tie inner with outer over something that's not there...

I don't even how to fix it with a hack, as all there is is a relationship (in this case Customer - Order) and that somehow has to tell me that the 'Customer' part of that relationship is also the type of the outer query, but that's unknown at that level.

It pains me to say this, but I can't fix this. Sorry.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 06-Jul-2016 12:18:29.  
Otis wrote:
It pains me to say this, but I can't fix this. Sorry.
I feel your pain, even so its maybe worth a mention in https://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_remarkslimitations.htm

Speaking of pain: a variation on this is
Code:
from c in Customer
select new
{
    c.CompanyName,
    c.ContactName,
    c.ContactTitle,
    c.CustomerId,
    CustomerOrderDetailsCountViaSum = c.Orders.Sum(o=>o.OrderDetails.Count()),
    CustomerOrderDetailsCountViaCount = OrderDetail.Count(od => od.Order.CustomerId == c.CustomerId)
}
Which works fine in SQL Server but blows up in Oracle because of the CustomerOrderDetailsCountViaSum line for reasons discussed here: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=23841

Can use the last line as work-around but it always hurts when I can't use a navigatorSad
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 06-Jul-2016 13:50:30.  
Sorry for that inconvenience Dissapointed

We'll add a notion to that limitation, as indeed it should be up there.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.