Linq to Entities grouping on multiple fields with an Or operator

Question

I have a list of orders and need to create groups of similar orders in an efficient manner (preferably without pulling back all orders and comparing manually).

Each order has a UserId and Email Address with additional fields of Postcode and Country.

So I need to create groups on the following rules:

If an order has the same (UserId or Email Address) and (postcode and country) place in a group

Given the following entity and data

public class Order
{
        public int UserId { get; set; }
        public int OrderId { get; set; }
        public string Email { get; set; }
        public int PostCode { get; set; }
        public string Country { get; set; }
}

Example data

OrderId UserId  Email   PostCode    Country
1       1       blah1   111         au
2       1       blah2   111         au
3       2       blah1   111         au
4       2       blah2   111         au
5       3       blah3   111         nz
6       3       blah3   111         nz

Example Results

Group 1
1       1       blah1   111         au
2       1       blah2   111         au
3       2       blah1   111         au
4       2       blah2   111         au

Group 2
5       3       blah3   111         nz
6       3       blah3   111         nz

The only way I can seem to think of doing this through manual iteration in memory

Is this possible to do with Linq to entities cleanly?

Update

After researching this for a while, I think I've come to the conclusion the only way to achieve what I want is to do two groupbys and manually combine them in memory.

Update 2

Thinking about this logically there is seemingly no elegant solution for this problem in linq and may need to be done with SQL and CTEs or some other recursive solution.

Rahul · Answer 1 · 2014-10-29T04:41:05.517

Here I have done Group by on more than 1 column,

  OrderViewModel OrderList   =from ord in order
        group ord by new
        {
            ord.PostCode,
            ord.Country,
            ord.UserID,
            ord.Email,
            ord.OrderID
        } into gr
        select new OrderViewModel
        {
            OrderID = gr.Key.OrderID,
            UserID = gr.Key.UserID,
            Email = gr.Key.Email,
            PostCode=gr.key.PostCode,
            Country=gr.key.Country,
            OrderList= gr.ToList()
        };

where OrderViewModel is something ::

public class Order
{
        public int UserId { get; set; }
        public int OrderId { get; set; }
        public string Email { get; set; }
        public int PostCode { get; set; }
        public string Country { get; set; }
        public List<Order> OrderList { get; set; }
}

Where you should have to decide Priority of Grouping data, &Data which you don't want to Group will be taken as a list.

+1 Yes. This can be an another answer. But What is the return type of your result? If i don't wish to use `var` here. — RajeshKdev, Oct 29 '14 at 04:26

RajeshKdev · Answer 2 · 2014-10-29T04:18:59.270

Assuming your Order Class will be like below:

public class Order
{
        public int UserId { get; set; }
        public int OrderId { get; set; }
        public string Email { get; set; }
        public int PostCode { get; set; }
        public string Country { get; set; }
}

Assuming and Grouping the List of Orders Like below:

public List<List<Order>> grouping()
{           
            // List of Orders to Group
            List<Order> orderList = new List<Order>();
            orderList.Add(new Order { UserId = 1, OrderId = 2007, Email = "blah1@test.com", PostCode = 111, Country = "India" });
            orderList.Add(new Order { UserId = 2, OrderId = 2007, Email = "blah1@test.com", PostCode = 111, Country = "India" });
            orderList.Add(new Order { UserId = 3, OrderId = 2007, Email = "blah1@test.com", PostCode = 111, Country = "India" });
            orderList.Add(new Order { UserId = 4, OrderId = 2008, Email = "blah1@test.com", PostCode = 111, Country = "India" });
            orderList.Add(new Order { UserId = 5, OrderId = 2008, Email = "blah1@test.com", PostCode = 111, Country = "India" });
            orderList.Add(new Order { UserId = 6, OrderId = 2001, Email = "blah1@test.com", PostCode = 111, Country = "India" });

            // Grouping
            var groupedOrderList = orderList
                .GroupBy(u => u.OrderId)
                .Select(grp => grp.ToList()).ToList();  // Groping the Records based on the OrderId 

            return groupedOrderList; // The Result will be List<List<Order>>
}

Your group statement will group by OrderId. The Result will be come as you expected like above you showed.

Update:

You can Add additional fields as well to be grouped based on the multiple fields.

Like below :

.GroupBy(u => u.OrderId & u.UserId)

Thanks for your reply, however does this group by UserId or Email, as im not sure how i would put the other conidtions (including the OR) in the group by statment — TheGeneral, Oct 29 '14 at 04:10
If you want you can additionally add that as well. Like `.GroupBy(u => u.UserId)` Or if you want all the fields to be grouped do Like this `.GroupBy(u => u.OrderId && u.UserId && u.Email)` — RajeshKdev, Oct 29 '14 at 04:14
Yeah, im just trying to get my head around things so i can either mark it as correct or fire another question — TheGeneral, Oct 29 '14 at 04:23
+1 and Similar question can be found here [Using-LinQ-to-Group](http://stackoverflow.com/questions/2697253/using-linq-to-group-a-list-of-objects-into-a-new-grouped-list-of-list-of-objects) — RajeshKdev, Oct 29 '14 at 04:27
Thanks for the answer, however the answer doesn't satisfy the criteria of being able to GroupBy UserId OR Email unfortunately, However everything else works great, im starting to think this cant be done easly — TheGeneral, Oct 29 '14 at 04:40

score 0 · Accepted Answer · answered Oct 29 '14 at 14:00

Try this:-

 var query = from ord in orders
                        group ord by new { ord.Country, ord.PostCode } into g
                        select new
                            {
                                Country = g.Key.Country,
                                PostCode = g.Key.PostCode,
                                Orders = g.GroupBy(x => x.OrderId)
                                          .GroupBy(i => i.Count() > 1 ?
                                              new { OrderID = i.Key, Email = default(string) }
                                              : new { OrderID = default(int), i.First().Email })
                                          .Select(o => o.Count() == 1 ?
                                               new { o.Key, Orders = o.First().ToList() }
                                               : new { o.Key, Orders = o.Select(z => z.First()).ToList() })
                            };

Here is the complete Working Fiddle.

This is the closest solution, however i think to get this working correctly ill have to take a slightly different approach. Thanks — TheGeneral, Oct 30 '14 at 01:13

Linq to Entities grouping on multiple fields with an Or operator

Update

Update 2

3 Answers3