LINQ

Group by Multiple Columns using Anonymous Types in LINQ to SQL

I believe LINQ is a very nice advancement in creating database oriented applications as it allows us to separate application logic from the database. I have been using LINQ to SQL in my recent reporting application and have used various queries with ease.

With the power of Anonymous Types, I was able to create a number of reports with different grouping senarios. Anonymous Types as defined in the C# programming guide:

Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to first explicitly define a type. The type name is generated by the compiler and is not available at the source code level. The type of the properties is inferred by the compiler.

Let’s look at a few ways of Grouping using Anonymous Types. Before we start, displayed below is the sample data used in the queries below:

Sample Data

Simple Group by Anonymous Type – Grouping by Month and Year in a Date

The purpose of this query is to group transactions in the table and retrieve a list of unique months and years in a simple list.

var months = from t in db.TransactionData

    group t by new { month = t.Date.Month, year = t.Date.Year } into d

    select new { t.Key.month, t.Key.year };

 

Transactions Grouped By Month and Year

Group by Database Column and then by Anonymous Type – Grouping by Expense Category and Monthly Total

The purpose of this query is to group all database records by Category first and then display a Total Expense based on the month in a simple list.

var categoryExpense = from t in db.TransactionData

    group t by t.Category into g

    select new

    {

        Category = g.Key,

        Items = from i in g

            group i by new { month = i.Date.Month, year = i.Date.Year } into d

            select new { Date = String.Format("{0}/{1}", d.Key.month, d.Key.year ), Amt = d.Sum(s => s.Amount) }         

    };

 

Multiple Groups

Group by Anonymous Type and then by Database Column – Grouping by Month and then by Expense Category and Category Total

The purpose of this query is the opposite of the group query 2. We will first group by the Month and then group by the Category Total:

var monthlyExpenses = from t in db.TransactionData

    group t by new { month = y.Date.Month, year = y.Date.Year } into g

    select new

    {

        Month = String.Format("{0}/{1}", g.Key.month, g.Key.year),

        Items = from i in g

        group i by new { i.Category } into d

        select new

        {

            Amt = d.Sum(s => s.Amount) 

        }

    };

 

Multiple Groups

With SQL you can seamlessly do all of the above on XML, CSV files or .NET Objects as well.

Recommend Resources to Learn more about LINQ to SQL

Finding Min or Max Date using LINQ to SQL

In one of my recent project, I had to retrieve Minimum and Maximum date from the result set using LINQ to SQL. I was really surprised how easy it was:

//Retrieve Minimum Date

var MinDate = (from d in dataRows select d.Date).Min();

 

//Retrieve Maximum Date

var MaxDate = (from d in dataRows select d.Date).Max(); 

In its simplest form, all you have to do is have .Min() or .Max() at the end of your LINQ query. That’s it.