CODEWITHSUNDEEP
linqentity-frameworklinq-to-sqllinq-to-entities
Naresh
Naresh

Reputation: 2673

LINQ getting distinct records based on an item value

I have following LINQ query

 var unallocatedOrders = (from orderLine in context.OrderLineItemDboes
     where (orderLine.Status == unallocated || orderLine.Status == null)
         && orderLine.orderline.order.order_status_fk == verified
     group orderLine
         by new { orderLine.orderline.ol_id,orderLine.orderline.order.order_id }
         into g
     select new { OrderLineId = g.Key.ol_id, Count = g.Count(), OrderId = g.Key.order_id })
 .ToList();

Above query giving me results in the following way

Order1 ol1 2
order1 ol2 3
order1 ol3 1
order2 ol1 1
order2 ol2 2
order3 ol1 4
order3 ol2 3
order3 ol3 2

I need to iterate through the above list based on order ids and need to fetch corresponding lines and quantity. I need to get this line id and quantity to a Dictionary. Can somebody suggest how can I get it done.

Thanks

Upvotes: 0

Views: 591

Answers (1)

McGarnagle
McGarnagle

Reputation: 102723

Here's how you can select the items using GroupBy. (Your question doesn't really specify how you want to use the lines, so I just output them to the Debug console.)

// group by the OrderId
foreach (var group in unallocatedOrders.GroupBy(row => row.OrderId))
{
    Debug.WriteLine(

        // for each line, output "Order x has lines y1, y2, y3..."
        string.Format("Order {0} has lines {1}", 

            // here the key is the OrderId
            group.Key,

            // comma-delimited output
            string.Join(", ", 

                // select each value in the group, and output its OrderLineId, and quantity
                group.Select(item => 
                    string.Format("{0} (quantity {1})", item.OrderLineId, item.Count)
                )
            )
        )
    );
}

You can get a dictionary lookup by using ToDictionary.

// two-level lookup: 1) OrderId 2) OrderLineId
var lookup = new Dictionary<int, Dictionary<int, long>>();

foreach (var group in unallocatedOrders.GroupBy(row => row.OrderId))
{
    // add each order to the lookup
    lookup.Add(group.Key, group.ToDictionary(

        // key selector 
        keySelector: item => item.OrderLineId,

        // value selector
        elementSelector: item => item.Count()
    ));
}

Upvotes: 1

Related Questions