Infosys | Microsoft: Group By Many/Multiple Criteria using LINQ to SQL (L2S)

Developers may find it annoying for not finding an out of the box query operator in L2S to group by many/multiple criteria, which is used very frequently in T-SQL queries. No sweat, there is an easy way out, will try illustrate the same here.

SP written in T-SQL

SELECT

                  InventoryYear.InventoryYearID,
                  SUM(ActivityEmission.CanonicalEmissionAmount) AS CanonicalEmissionAmount,
                  ActivityEmission.CanonicalUnitId,
                  ActivityEmission.GasId
            FROM
                  InventoryYear INNER JOIN EntitySUISInventoryYear
            ON
                  InventoryYear.EntityID = @EntityId AND
                  InventoryYear.EntityID = EntitySUISInventoryYear.EntityID INNER JOIN SUIS
            ON
                  SUIS.InventoryTypeID = @InventoryTypeId AND EntitySUISInventoryYear.SUISID = SUIS.SUISID INNER JOIN ActivitySource
            ON
                  ActivitySource.EntityID = InventoryYear.EntityID INNER JOIN ActivityEmission
            ON
                  ActivityEmission.ActivitySourceID = ActivitySource.ActivitySourceID INNER JOIN EntitySUISInventoryYear ESIY
            ON
                  ESIY.EntitySUISInventoryYearID = EntitySUISInventoryYear.EntitySUISInventoryYearID AND ESIY.IsComplete = 1
      GROUP BY
            InventoryYear.InventoryYearID,
            ActivityEmission.CanonicalUnitId,
            ActivityEmission.GasId

The same can be written in L2S with as follows using Anonymous types:

Let me create a business object called Emission which will store the results retrieved from the query

 public class Emission
    {        
        public int InvId {get;set;}
        public int CanId {get;set;}
        public int GasID {get;set;}
        public decimal? GasAmt {get;set;}        
    }

L2S query to retrieve the results

 List<Emission> emission = (from i in InventoryYears
              from j in EntitySUISInventoryYears
              from k in ActivityEmissions
              from l in ActivitySources
              from m in SUIs                   
              where  i.EntityID == j.EntityID && i.EntityID == 1 &&
                  m.InventoryTypeID == 1 && j.SUISID == m.SUISID &&
                  l.EntityID == i.EntityID &&
                  k .ActivitySourceID == l.ActivitySourceID && 
                  j.IsComplete == true &&
                  j.EntitySUISInventoryYearID == j.EntitySUISInventoryYearID
                  group k by new {i.InventoryYearID,k.GasID,k.CanonicalUnitID} into grouping
                  select new Emission {InvId=grouping.Key.InventoryYearID,GasID=grouping.Key.GasID,
                      CanId=grouping.Key.CanonicalUnitID,GasAmt = grouping.Sum(c=>c.CanonicalEmissionAmount)}).ToList();

The key here is the way data is grouped. We need to just group the columns (the columns grouped using group by in T-SQL) into a grouping variable (in this case it is grouping). Run a select query on the grouping variable (grouping variable will be of the type IEnumerable<T>, in this case it will be IEnumerable<ActivityEmissions>), to select the columns needed.

Note: The intention here was to create a T-SQL group by multiple criteria equivalent in L2S. I had some trouble using join and hence resorted to where, by default where clause is internally mapped to inner joins. Opinions are welcome to make the L2S query better.

Comments

I had a similiar problem. I was able to use the relationships generated by the keys.

SELECT Jobs.JobNo, Jobs.Name, Jobs.ContractAmount, SUM(JobsExtras.Amount) AS Extras, SUM(JobsExtras.Amount) + Jobs.ContractAmount AS TotalContracted,
SUM(Payments.Amount) AS Paid, SUM(JobsExtras.Amount) + Jobs.ContractAmount - SUM(Payments.Amount) AS balance
FROM Jobs LEFT OUTER JOIN
JobsExtras ON Jobs.JobNo = JobsExtras.JobNo LEFT OUTER JOIN
Payments ON Jobs.JobNo = Payments.JobNo
GROUP BY Jobs.JobNo, Jobs.Name, Jobs.ContractAmount

------

Turned into

var jobs = from j in db.Jobs
select new
{
j.JobNo,
Customer = j.Customer.Name,
Job = j.Name,
j.ContractAmount,
Extras = (j.JobsExtras.Sum(p => (decimal?)p.Amount) ?? 0),
Total = (j.JobsExtras.Sum(p => (decimal?)p.Amount) ?? 0) + (j.ContractAmount ?? 0),
Payments = (j.Payments.Sum(p => (decimal?)p.Amount) ?? 0),
Balance = ((j.JobsExtras.Sum(p => (decimal?)p.Amount) ?? 0) + (j.ContractAmount ?? 0)) - (j.Payments.Sum(p => (decimal?)p.Amount) ?? 0)
};

Posted by: Bryan Reynolds | March 21, 2008 12:34 AM

Thanks for your suggestion. That is one of advantages we get using LINQ TO SQL. But sometimes, when we are querying on 5-6 tables relationships may not be very straight forward i.e. getting all the tables information just from one table. This was the case in the above example I blogged. This was the reason I had to resort to somewhat complex looking LINQ To SQL query using where.

The query can also be written using joins instead of where as shown below.

var result = (from i in InventoryYears
join j in EntitySUISInventoryYears
on i.EntityID equals j.EntityID
join k in SUIs
on j.SUISID equals k.SUISID
join l in ActivitySources
on i.EntityID equals l.EntityID
join m in ActivityEmissions
on l.ActivitySourceID equals m.ActivitySourceID
where i.EntityID == 1 && j.IsComplete == true && k.InventoryTypeID == 1
group m by new {i.InventoryYearID,m.GasID,m.CanonicalUnitID} into grouping
select new Emission {InvId=grouping.Key.InventoryYearID,GasID=grouping.Key.GasID,
CanId=grouping.Key.CanonicalUnitID,GasAmt = grouping.Sum(c=>c.CanonicalEmissionAmount)}).ToList();

Note: The above query is not tested as the database schema since I wrote this article has been changed significantly. The essence I want to convey is that I use queries similar to above in all the complex join operations I do using LINQ To SQL. This is better than using where clause because I found that queries written in where clause were converetd to cross joins instead of inner joins.

Posted by: Raghavan | March 25, 2008 5:16 AM