Against Northwind

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

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

We'll look into it. Likely the SelectMany which in general converts to a cross join. Will be next week.

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

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

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.

E.g. this query works fine:

[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..

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.

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

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 navigator

Sorry for that inconvenience

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