How to make a LINQ query for this?

Question

Okay, stuck for a day now, please somebody give a helping hand!

I want to select the quantity of EVERY possible item that a specific customer has, regardless of whether they have ordered the item or not. So if they haven't ordered it (i.e. it is not in the Orders table) then return a value of 0 for the quantity.

Orders CustomerId ItemId Quantity

Items ItemId ItemName

Customers CustomerId CustomerName

I'm guessing that this will involve some kind of subquery, but really not sure.

from c in customers
join o in Orders
on c.CustomerId == o.CustomerId
select c.CustomerName, o.Quantity
where c.CustomerId == customerId

I don't follow what you mean by "that a specific customer has". How does a customer "have" an item? — Pieter Geerkens, Mar 02 '13 at 23:16
@TimSchmelter updated. I did have a more complex one but got rid of it because it didn't work. The issue with the one I have just typed up is that it will only return the quantities for the items that the user already has, but I need to also select the items that the user hasn't ordered and set their quantity to 0 — Deniz, Mar 02 '13 at 23:26
@TimSchmelter I meant customer towards the end, not user! lol — Deniz, Mar 02 '13 at 23:33

score 2 · Answer 1 · answered Mar 03 '13 at 00:06

In SQL, this is quite simple:

select  c.name
,       i.name
,       isnull(sum(o.quantity),0)
from    customers c
cross join
        items i
left join
        orders o
on      o.customerid = c.customerid
        and o.itemid = o.itemid
group by
        c.name
,       i.name

Translated to LINQ, this becomes:

from c in Customers
from i in Items 
join o in Orders 
    on new { c.Customerid, i.Itemid } 
    equals new { o.Customerid, o.Itemid } 
    into o1
from o2 in o1.DefaultIfEmpty()
group o2.Quantity
    by new { CustomerName = c.Name, ItemName = i.Name }
    into q
select new { 
    q.Key.CustomerName, 
    q.Key.ItemName, 
    Quantity = q.Sum() ?? 0 
}

I hope someone else posts a simpler LINQ version, because this looks insanely complex :)

score 2 · Accepted Answer · answered Mar 03 '13 at 00:15

This one caught my interest so I wrote a console app to do it.

Andomar answered a few moments before I did (+1) from me. This is my independent solution but very close to his and no simpler...

class Program {
    private class CustomerDto {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
    }
    private class ItemDto {
        public int ItemId { get; set; }
        public string ItemName { get; set; }
    }
    private class OrderDto {
        public int Id { get; set; }
        public int ItemId { get; set; }
        public int CustomerId { get; set; }
        public int Quantity { get; set; }
    }
    private class CustomerOrderDto {
        public int CustomerId { get; set; }
        public int ItemId { get; set; }
        public int TotalQuantity { get; set; }
    }

    static void Main(string[] args) {
        List<CustomerDto> Customers = new List<CustomerDto>() { 
            new CustomerDto() { CustomerId = 1, CustomerName = "one"},
            new CustomerDto() { CustomerId = 2, CustomerName = "two"},
            new CustomerDto() { CustomerId = 3, CustomerName = "three"}
        };
        List<ItemDto> Items = new List<ItemDto>() { 
            new ItemDto() { ItemId = 1, ItemName = "item one"},
            new ItemDto() { ItemId = 2, ItemName = "item two"},
            new ItemDto() { ItemId = 3, ItemName = "item three"}
        };
        // customer1 has 2 orders for item 1, 0 for item 2 or 3
        // customer2 has 1 order for item 2, 0 for item 1 or 3
        // customer3 has 1 order for item 2, 1 order for item 3 and 0 for item 1
        List<OrderDto> Orders = new List<OrderDto>() { 
            new OrderDto() { Id = 1, CustomerId = 1, ItemId = 1, Quantity = 3 },
            new OrderDto() { Id = 1, CustomerId = 1, ItemId = 1, Quantity = 5 },
            new OrderDto() { Id = 1, CustomerId = 3, ItemId = 2, Quantity = 5 },
            new OrderDto() { Id = 1, CustomerId = 3, ItemId = 3, Quantity = 5 },
            new OrderDto() { Id = 1, CustomerId = 2, ItemId = 2, Quantity = 5 }
        };
        List<CustomerOrderDto> results = (from c in Customers
                                          from i in Items
                                          join o in Orders on
                                            new { c.CustomerId, i.ItemId } equals
                                            new { o.CustomerId, o.ItemId } into oj
                                          from o in oj.DefaultIfEmpty()
                                          let x = o ?? new OrderDto() { CustomerId = c.CustomerId, ItemId = i.ItemId, Quantity = 0 }
                                          group x by new { x.CustomerId, x.ItemId } into g
                                          select new CustomerOrderDto() {
                                              CustomerId = g.Key.CustomerId,
                                              ItemId = g.Key.ItemId,
                                              TotalQuantity = g.Select(x => x.Quantity).Sum()
                                          }
                                          ).ToList();
        foreach (var result in results) {
            Console.WriteLine("Customer {0} purchased {1} units of item {2}",
                result.CustomerId, result.TotalQuantity, result.ItemId);
        }
        Console.ReadKey(true);
    }
}

+1 Interesting use of let statement. Nice test case btw (I built a test case in SQL Server and Linqpad) — Andomar, Mar 03 '13 at 00:18
you guys are very smart :) i am now trying to understand the query now — Deniz, Mar 03 '13 at 00:24

score 2 · Answer 3 · edited May 23 '17 at 11:52

The LINQ expresssion

from c in Customers
from i in Items
select ...

is a cross join of all customers and items. So the only thing left to do is to sum up the quantities for each:

var result = from c in Customers
             from i in Items
             select new
             {
                 Customer = c,
                 Item = i,
                 Quantity = (from o in Orders
                             where o.CustomerId == c.CustomerId && o.ItemId == i.ItemId
                             select o.Quantity).Sum(),
             };

Note that Sum returns 0 for an empty IEnumerable<int> (i.e. no orders found).

spajce · Answer 4 · 2013-03-03T00:43:27.660

Linq

from t in Customers
join t2 in Orders on t.CustomerId equals (int)t2.CustomerId into t2Join
from t2 in t2Join.DefaultIfEmpty()
group t by t into t_g
select new 
      { 
       CustomerId = t_g.Key.CustomerId, 
       OrdersId = t_g.Key.OrdersId,
       Quantity = t_g.Key.Orders.Where(c => c.CustomerId == t_g.Key.CustomerId)
                                .Sum(c => c.Quantity) 
      };

This is not tested but you can try.

How to make a LINQ query for this?

4 Answers4