Can't get Sum() working in Northwind example

Question

Hi,

The following code is generating a runtime error and I have no idea why.

from o in Orders
group o by o.Employee into employeeOrders
select new {
    employeeOrders.Key.EmployeeID,
    employeeOrders.Key.FirstName,
    Orders = 
        from eord in employeeOrders
        orderby eord.OrderID
        select new {
            eord.OrderID,
            eord.OrderDate,
            OrderTotal=eord.OrderDetails.Sum (od => od.UnitPrice)
        }
}

The error is

Member access 'System.Decimal UnitPrice' of 'LINQPad.User.OrderDetails' not legal on type 'LINQPad.User.Orders

I've also tried this in VS2010 with a standard drag and drop data context and same thing.

Thanks in advance

Answer 1

I can't fully explain it at the moment(I don't get the info I need from the debugger), but from what I've seen in the msdn and trying out a bit, apperently, without the let, the EntitySet<OrderDetail> representing the OrderDetail Property on Orders isn't loaded (as the EntitySet is used for deferred loading) and thus can't be accessed.

The let now directly invokes the EntitySet and thus enables querying. This also comes together with the comment from H. Holterman.

I'll try to find a more sophisticated explantion.

edit

The difference between the two is:

with let

    .Call System.Linq.Enumerable.Sum(
        ($<>h__TransparentIdentifier0.eord).Order_Details,
        .Lambda #Lambda6<System.Func`2[northwindtest.Order_Detail,System.Decimal]>))

(Lambda6 is the lambda in the Sum, $<>h__TransparentIdentifier0.eord is the variable defined by the let)

without let

    .Call System.Linq.Enumerable.Sum(
        $eord.Order_Details,
        .Lambda #Lambda5<System.Func`2[northwindtest.Order_Detail,System.Decimal]>))

Be aware that this is solely interpretation. The let evaluates a subquery over the EntitySet, by this filling it with data (-> deferred loading). This enables us to use the data, even outside the variable declared by let (again, deferred loading). I have no idea why this acts this way because I can't seem to get the state of the EntitySet<OrderDetail> at different stages of the Expression tree.

The Exception gets caused by this code in System.Data.Linq.SqlClient.SqlMember.Expression.Set

if (!this.member.ReflectedType.IsAssignableFrom(value.ClrType) && !value.ClrType.IsAssignableFrom(this.member.ReflectedType))
{
    throw Error.MemberAccessIllegal(this.member, this.member.ReflectedType, value.ClrType);
}

after a long row of Expression Translates. From that I draw the conclusion, that it is impossible to Render the expression tree to sql without a subquery of somesort. Why it doesn't do this on it's own? I have no clue. I don't think I can go any further on analysing it, I don't really know if that is the real reason, maybe ask somebody over at MS for a clearer (and probably correct winks) explanation.

Hope I could at least help a bit. Fema