LINQ Queries with GROUP BY, INNER JOIN, COUNT and SUM: Examples

Examples of different LINQ queries in C#: GROUP BY, INNER JOIN, COUNT and SUM

Setup the LINQPad against the Northwind database.

1. LINQ query with GROUP BY, JOIN and SUM.
    Find the quantities required for each product to fulfill all the orders.
    Here the tables we have to use are Products and Order Details. Both of these tables can be joined on    ProductID key and grouped by Product Name to make sure that the Quantity is calculated against a named product instead of ID.
    The SQL query for the above requirement is :

       select A.ProductName,sum(B.Quantity)
        from Products P join "Order Details" OD
        on A.ProductID=B.ProductID
        group by A.ProductName

    The equivalent LINQ query for the above is:

                from p in Products
        join od in OrderDetails
        on p.ProductID equals od.ProductID
        group od by p.ProductName into g
        select new { ProductName=g.Key, Quanti=g.Sum(a => a.Quantity)}
    
2. LINQ Query with GROUP BY and SUM:

          from od in OrderDetails
        group od by od.ProductID into g
        select new {Key=g.Key, Quantity=g.Sum(x => x.Quantity)}

3. LINQ Query with GROUP BY, JOIN and COUNT

    Find the different categories of products and their count.

                from c in Categories
        join p in Products
        on c.CategoryID equals p.CategoryID
        group p by c.CategoryName into g
        select new {ProductType=g.Key, Count=g.Count()}

We can embed WHERE anywhere after join condition so that records can be filtered.

Hope you understand the usage and query result calculation. Please comment below the post for any queries and feedback.

Next post will be regarding INNER JOIN, OUTER JOIN examples with these clauses.

KEEP CODING........