c# Entity Framework Group By with join

Question

I have a custom type that i fill with data from two tables. I've tried the following to join two tables, this normally works. But the group by and count is causing errors:

public List<sLoadingList> GetOrdersforLoadingByWard(string _mywCode, DateTime _orderDate, int _periodId)
{
    var _formattedDate = _orderDate.Date;
    List<sLoadingList> query = (from o in CateringEntities.Orders
                                join i in CateringEntities.OrderItems
                                on o.OrderId equals i.OrderId
                                where o.LocationsLookUp.WardCode.Equals(_mywCode) &&
                                DbFunctions.TruncateTime(o.Date) == _formattedDate && o.Period == _periodId
                                select new sLoadingList()
                               {
                                   ItemId = i.ItemId,
                                   ItemName = i.Item.ItemName,
                                   ItemType = i.ItemType,
                                   Quantity = i.Item.ItemName.Count()
                               }).GroupBy(i => i.ItemId).Select(i => i.FirstOrDefault()).ToList();
    return query.ToList();

This is currently giving error:

{"DbExpressionBinding requires an input expression with a collection 
                                                ResultType.\r\nParameter name: input"}

But if i remove :

Quantity = i.Item.ItemName.Count()

the query runs ok.

SpaceUser7448 · Accepted Answer · 2017-02-17 17:04:09Z

2

Essentially you trying to use an aggregation (the Count()) on an set of data you have not grouped yet.

Try grouping by the ItemId, ItemName and ItemType first and then selecting the section with the Count() in it.

As I commented, without seeing all the tables etc it is hard to give an exact answer but try something like this -

List<sLoadingList> query = 
(
    (
        from o in first
        join i in second 
        on o.OrderId equals i.OrderId
        where ... some condition ...
        select new sLoadingList()
        {
            ItemId = i.ItemId
            , ItemName = i.Item.ItemName
            , ItemType = i.ItemType
        }
    )
    .GroupBy(i => i.ItemId)
    .ThenBy(i => i.ItemName)
    .ThenBy(i => i.ItemType)
    .Select(i => new {KeyData = i.Key, Cnt = i.Item.ItemName.Count()})
)
.GroupBy(i => i.KeyData.ItemId)
.Select(i => i.FirstOrDefault())
.ToList();

Essentially you create a query that pulls out the data you want to summarise, then group it and then you can apply an aggregation such as count.

A couple of tips -

Remember you do not have to do it in one query, try splitting on smaller ones that are easier to understand
Try LinqPad as this is good for testing stuff like this (www.linqpad.net)

edited Feb 17, 2017 at 17:04

answered Feb 17, 2017 at 16:37

SpaceUser7448

1891 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

the_tr00per Over a year ago

I did try grouping using something like : group i by i.Item.ItemName into g.... But i couldnt get the joined tables into the selected object

SpaceUser7448 Over a year ago

With out knowing all the table structures it is hard to comment but I will put together an example.

the_tr00per Over a year ago

ok thanks, basically i have orders table (orderid,name...) that joins to order_items (itemId, itemName....) i'm trying to group by order items on the name and add the count. But also want some info from the orders table in the select.

the_tr00per Over a year ago

ThenBy is not available in the version of entity i'm using. any other alternative?

Collectives™ on Stack Overflow

c# Entity Framework Group By with join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related