Writing Complex Queries Using LINQ And Lambda

 I am writing this article based on request by some developers regarding how to write some complex queries using LINQ & Lambda expression which consist of joins / groupby / aggregate functions

Just for demo I have created an OrderMaster & OrderDetail classes. Both are having a relationship between each other via OrderId & UserId column.

Sample Data





Following are types of Queries covered,

• Simple Query
• Joins with single field
• Joins with multiple field
• Group by
• Group by multiple field
• Group Join
• Aggregate function

Simple Query

Fetch all orders whose order date is less than 100 days from today. 

//Using linq,
var result1 = from order in context.OrderMasters
               where order.OrderDate < DateTime.Now.AddDays(-100)
               select order;

//Using lambda,
var lresult1 = context.OrderMasters
               .Where(a => a.OrderDate < DateTime.Now.AddDays(-100)).Select(s => s);


Console.WriteLine(string.Format("OrderNo \t OrderDate"));
foreach(var item in result1) {
    Console.WriteLine(string.Format("{0}\t{1}", item.OrderNo, item.OrderDate));
}

Output



Join with single field

Fetch all orders showing order number with list of products 

//Using linq,

var result2 = from order in context.OrderMasters
               join orderdetail in context.OrderDetails on order.OrderId equals orderdetail.OrderId
               //where order.OrderDate < DateTime.Now.AddDays(-10)
               select new {
                   order.OrderNo, orderdetail.ProductName, order.OrderDate
               };

//Using lambda,
var lresult2 = context.OrderMasters
               .Join(context.OrderDetails
               , od => od.OrderId
               , o => o.OrderId
               , (o, od) => new {
                       o.OrderNo, od.ProductName, o.OrderDate
                })
                //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))
                .Select(s => s);


Console.WriteLine(string.Format("OrderNo \t OrderDate \t Product"));
foreach(var item in lresult2) {
    Console.WriteLine(string.Format("{0}\t{1}\t{2}", item.OrderNo, item.OrderDate, item.ProductName));
}

Output



Joins with multiple fields

Fetch all orders and its details with matching order id & user id. 

//Using linq,
var result3 = from order in context.OrderMasters
            join orderdetail in context.OrderDetails
            on new {
                order.OrderId, order.UserId
            }
            equals new {
                orderdetail.OrderId, orderdetail.UserId
            }
            //where order.OrderDate < DateTime.Now.AddDays(-10)
            select new {
               order.OrderNo, orderdetail.ProductName, order.OrderDate
            };

//Using lambda,
var lresult3 = context.OrderMasters
               .Join(context.OrderDetails
               , od => new {
                    od.OrderId, od.UserId
                }
               , o => new {
                    o.OrderId, o.UserId
                }
               , (o, od) => new {
                     o.OrderNo,
                     od.ProductName,
                     o.OrderDate
             })
             //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))
             .Select(s => s);

Console.WriteLine(string.Format("OrderNo \t OrderDate \t Product"));
foreach(var item in lresult3) {
    Console.WriteLine(string.Format("{0}\t{1}\t{2}", item.OrderNo, item.OrderDate, item.ProductName));
}

Output



Group by

Fetch all unique Order id from orderdetail table with unit price > 1000 

//Using linq,

var result4 = from orderdetail in context.OrderDetails
               where orderdetail.UnitPrice > 1000
               group orderdetail by orderdetail.OrderId into grp
               orderby grp.Key
               select grp;

Note

Basically, it is not necessary to use into after “group by”. But if you want to use more clauses like “orderby” then you need to put your groupby result in to a variable “grp” and then perform query.

Else the query would look like this without orderby, 

//Using linq
var result41 = from orderdetail in context.OrderDetails
               where orderdetail.UnitPrice > 1000
               group orderdetail by orderdetail.OrderId;

//Using lambda,
var lresult4 = context.OrderDetails
               .GroupBy(orderdetail => orderdetail.OrderId)
               .OrderBy(d => d.Key);

Console.WriteLine(string.Format("Order Id"));
foreach(var item in lresult4) {
    Console.WriteLine(string.Format("{0}", item.Key));
}

Output



Group by with multiple fields

Fetch all unique ordered & userid from orderdetail table having unitprice > 1000, 

//Using linq,
var result5 = from orderdetail in context.OrderDetails
            where orderdetail.UnitPrice > 1000
            group orderdetail by new {
                orderdetail.OrderId, orderdetail.UserId
            }
            into grp
            orderby grp.Key.OrderId
            select grp;

//Using lambda,
var lresult5 = context.OrderDetails
               .GroupBy(orderdetail => new {
                   orderdetail.OrderId, orderdetail.UserId
               })
               .OrderBy(d => d.Key.OrderId)
               .Select(s => s);

Console.WriteLine(string.Format("Order Id \t User Id"));
foreach(var item in lresult5) {
    Console.WriteLine(string.Format("{0} \t {1}", item.Key.OrderId, item.Key.UserId));
}

Output



GroupJoin

Fetch Order number & all details under each order in hierarchical result set. 

//Using linq,
var result6 = from order in context.OrderMasters
               join orderdetail in context.OrderDetails
               on order.OrderId equals orderdetail.OrderId
               into grp
               //where order.OrderDate < DateTime.Now.AddDays(-10)
               select new {
                   Order = order, OrderD = grp
               };

//Using lambda,
var lresult6 = context.OrderMasters
               .GroupJoin(context.OrderDetails
                  , od => od.OrderId
                  , o => o.OrderId
                  , (o, od) => new {
                       Order = o, OrderD = od
                    //,o.OrderDate
                })
                //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))
                .Select(s => s);

foreach(var item in lresult6) {
    Console.WriteLine(string.Format("=> Order No: {0}", item.Order.OrderNo));
    Console.WriteLine(string.Format("Qty \t Price \t Product"));
    foreach(var i in item.OrderD) {
        Console.WriteLine(string.Format("{0} \t {1} \t {2}", i.Qty, i.UnitPrice, i.ProductName));
    }
}

Output



Aggregate function

Calculate total amount of each order using aggregate function 

//Using linq,
var result7 = from orderdetail in context.OrderDetails
               join order in context.OrderMasters on orderdetail.OrderId equals order.OrderId
               group orderdetail by new {
                   orderdetail.OrderId, order.OrderNo
               }
               into grp
               orderby grp.Key.OrderNo
               select new {
                   OrderNo = grp.Key.OrderNo, TotalAmt = grp.Sum(f => (f.UnitPrice * f.Qty))
               };

//Using lambda,
var lresult7 = context.OrderDetails
               .Join(context.OrderMasters
                  , orderdetail => orderdetail.OrderId
                  , order => order.OrderId
                  , (orderdetail, order) => new {
                         order,
                         orderdetail
                  }).GroupBy(od => new {
                      od.orderdetail.OrderId, od.order.OrderNo
                  })
                  .OrderBy(d => d.Key.OrderNo)
                  .Select(grp => new {
                      OrderNo = grp.Key.OrderId,
                      TotalAmt = grp.Sum(f => (f.orderdetail.UnitPrice * f.orderdetail.Qty))
               });

Console.WriteLine(string.Format("OrderNo \t TotalAmt"));
foreach(var item in result7) {
    Console.WriteLine(string.Format("{0} \t {1}", item.OrderNo, item.TotalAmt));
}

Output



Note

Whenever you hear the word projection while writing LINQ or lambda queries, it is nothing but your Select statement.

Conclusion

I hope, this article will be helpful to understand how to write complex queries using LINQ or lambda. Please do comment, whether it’s good or bad. Sharing is valuable. Thank you for reading it.