Infosys Microsoft Alliance and Solutions blog

« Controlling home appliances from outside - “I can be home when I’m not.” | Main | Webinar on Advanced Collaborative Supply Management »

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)
};

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.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles

Infosys on Twitter