LINQ to Entities does not recognize the method Generic.List(int) to Generic.IEnumerable(int) method

Question

The database contains Orders. Orders can be contained within a group of Orders. For every group of Orders it could contain 1 to many Orders.

However, Orders could have a NULL value assigned GroupOrderId as previous Orders did not have the grouping concept. Only new Orders enforce the concept of being added to a group.

The class structure to be populated in order to perform actions on each Order is

public class OrdersGroup
{
    public int? GroupOrderId { get; set; }
    public List<int> OrderIds { get; set; }
}

The linq statement

var workPacketOrdersList = (from o in db.Orders
                                    where
                                        o.GroupOrderId >= groupOrderIdMin && o.GroupOrderId <= groupOrderIdMax &&
                                        o.IsDeleted == false
                                    orderby o.WorkPacketId ascending
                                    group o by o.WorkPacketId
                                    into grp
                                    select new OrdersGroup
                                               {
                                                   GroupOrderId = grp.Key,
                                                   OrderIds = grp.Select(g => g.OrderId).ToList()
                                               }).ToList();

Full exception

LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.Int32] ToList[Int32](System.Collections.Generic.IEnumerable`1[System.Int32])' method, and this method cannot be translated into a store expression.

I see that the returned type of the linq query is a List<OrdersGroup>.

If the final .ToList() is omitted from the query than the return type becomes an IQueryable<OrdersGroup>

No matter what action is performed next the result is an exception that this method cannot be translated into a store expression.

I have tried to remove the specific select new OrdersGroup into a more generic select new and then perform actions on this result only to find the same store expression exception.

Can someone give some insight into where this linq is incorrect?

Answer 1

this is the part that's failing - grp.Select(g => g.OrderId).ToList() - you can't have a .ToList() in the select clause. remove that and you should be fine.