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

 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.
LINQ
Sample Data

LINQ

LINQ

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. 

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

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


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

Output

LINQ

Join with single field

Fetch all orders showing order number with list of products 

  1. //Using linq,  

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

  8. //Using lambda,  
  9. var lresult2 = context.OrderMasters
  10.                .Join(context.OrderDetails
  11.                , od => od.OrderId
  12.                , o => o.OrderId
  13.                , (o, od) => new {  
  14.                        o.OrderNo, od.ProductName, o.OrderDate  
  15.                 })  
  16.                 //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))  
  17.                 .Select(s => s);  


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

Output

LINQ

Joins with multiple fields

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

  1. //Using linq,  
  2. var result3 = from order in context.OrderMasters  
  3.             join orderdetail in context.OrderDetails  
  4.             on new {  
  5.                 order.OrderId, order.UserId  
  6.             }  
  7.             equals new {  
  8.                 orderdetail.OrderId, orderdetail.UserId  
  9.             }  
  10.             //where order.OrderDate < DateTime.Now.AddDays(-10)  
  11.             select new {  
  12.                order.OrderNo, orderdetail.ProductName, order.OrderDate  
  13.             };  

  14. //Using lambda,  
  15. var lresult3 = context.OrderMasters
  16.                .Join(context.OrderDetails
  17.                , od => new {  
  18.                     od.OrderId, od.UserId  
  19.                 }
  20.                , o => new {  
  21.                     o.OrderId, o.UserId  
  22.                 }
  23.                , (o, od) => new {  
  24.                      o.OrderNo,  
  25.                      od.ProductName,  
  26.                      o.OrderDate  
  27.              })  
  28.              //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))  
  29.              .Select(s => s);  

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

Output

LINQ

Group by

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

  1. //Using linq,  
  2.   
  3. var result4 = from orderdetail in context.OrderDetails  
  4.                where orderdetail.UnitPrice > 1000  
  5.                group orderdetail by orderdetail.OrderId into grp  
  6.                orderby grp.Key  
  7.                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, 

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

  5. //Using lambda,  
  6. var lresult4 = context.OrderDetails
  7.                .GroupBy(orderdetail => orderdetail.OrderId)
  8.                .OrderBy(d => d.Key);
  9.   
  10. Console.WriteLine(string.Format("Order Id"));  
  11. foreach(var item in lresult4) {  
  12.     Console.WriteLine(string.Format("{0}", item.Key));  
  13. }   

Output

LINQ

Group by with multiple fields

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

  1. //Using linq,  
  2. var result5 = from orderdetail in context.OrderDetails  
  3.             where orderdetail.UnitPrice > 1000  
  4.             group orderdetail by new {  
  5.                 orderdetail.OrderId, orderdetail.UserId  
  6.             }  
  7.             into grp  
  8.             orderby grp.Key.OrderId  
  9.             select grp;  

  10. //Using lambda,  
  11. var lresult5 = context.OrderDetails
  12.                .GroupBy(orderdetail => new {  
  13.                    orderdetail.OrderId, orderdetail.UserId  
  14.                })
  15.                .OrderBy(d => d.Key.OrderId)
  16.                .Select(s => s);
  17.   
  18. Console.WriteLine(string.Format("Order Id \t User Id"));  
  19. foreach(var item in lresult5) {  
  20.     Console.WriteLine(string.Format("{0} \t {1}", item.Key.OrderId, item.Key.UserId));  
  21. }   

Output

LINQ

GroupJoin

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

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

  10. //Using lambda,  
  11. var lresult6 = context.OrderMasters
  12.                .GroupJoin(context.OrderDetails
  13.                   , od => od.OrderId
  14.                   , o => o.OrderId
  15.                   , (o, od) => new {  
  16.                        Order = o, OrderD = od  
  17.                     //,o.OrderDate  
  18.                 })  
  19.                 //.Where(a => a.OrderDate < DateTime.Now.AddDays(-100))  
  20.                 .Select(s => s);
  21.   
  22. foreach(var item in lresult6) {  
  23.     Console.WriteLine(string.Format("=> Order No: {0}", item.Order.OrderNo));  
  24.     Console.WriteLine(string.Format("Qty \t Price \t Product"));  
  25.     foreach(var i in item.OrderD) {  
  26.         Console.WriteLine(string.Format("{0} \t {1} \t {2}", i.Qty, i.UnitPrice, i.ProductName));  
  27.     }  
  28. }   

Output

LINQ

Aggregate function

Calculate total amount of each order using aggregate function 

  1. //Using linq,  
  2. var result7 = from orderdetail in context.OrderDetails  
  3.                join order in context.OrderMasters on orderdetail.OrderId equals order.OrderId  
  4.                group orderdetail by new {  
  5.                    orderdetail.OrderId, order.OrderNo  
  6.                }  
  7.                into grp  
  8.                orderby grp.Key.OrderNo  
  9.                select new {  
  10.                    OrderNo = grp.Key.OrderNo, TotalAmt = grp.Sum(f => (f.UnitPrice * f.Qty))  
  11.                };  

  12. //Using lambda,  
  13. var lresult7 = context.OrderDetails
  14.                .Join(context.OrderMasters
  15.                   , orderdetail => orderdetail.OrderId
  16.                   , order => order.OrderId
  17.                   , (orderdetail, order) => new {  
  18.                          order,  
  19.                          orderdetail  
  20.                   }).GroupBy(od => new {  
  21.                       od.orderdetail.OrderId, od.order.OrderNo  
  22.                   })
  23.                   .OrderBy(d => d.Key.OrderNo)
  24.                   .Select(grp => new {  
  25.                       OrderNo = grp.Key.OrderId,  
  26.                       TotalAmt = grp.Sum(f => (f.orderdetail.UnitPrice * f.orderdetail.Qty))  
  27.                });
  28.   
  29. Console.WriteLine(string.Format("OrderNo \t TotalAmt"));  
  30. foreach(var item in result7) {  
  31.     Console.WriteLine(string.Format("{0} \t {1}", item.OrderNo, item.TotalAmt));  
  32. }   

Output

LINQ

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.