CODEWITHSUNDEEP
asp.nett-sqllinq-to-sqlgroup-by
Abbas
Abbas

Reputation: 5044

Converting Group By T-Sql To Linq To Sql

I am currently working on a project in which I am using Linq-to-SQL to avoid using stored procedures. I am almost done with avoiding all of the stored procedure, but I have one T-SQL query which I am finding difficult to convert to Linq.

Can anyone help me with the conversion?

select 
    EM.EmployerID, EM.EmployerName, AD.City, AD.County, JR.InstanceValue 
from 
    JobResults JR
inner join 
    Employers EM on JR.EmployerName = EM.EmployerName
inner join 
    Addresses AD on EM.AddressID = AD.AddressID
where 
    JR.IsPremium = 1 
    and JR.FieldName = 'Job Title'
group by 
    EM.EmployerID, EM.EmployerName, AD.City, AD.County, JR.InstanceValue

Update

This is what I had tried:

var query = (from jr in database.JobResults
             join em in database.Employers
                   on jr.EmployerName equals em.EmployerName
             join ad in database.Addresses
                 on em.AddressID equals ad.AddressID
             where jr.FieldName == "Job Title" && jr.IsPremium == true
             select new { jr.JobID, em.EmployerID, em.EmployerName, ad.City, ad.County, jr.InstanceValue }).GroupBy(p => new { p.EmployerID, p.EmployerName, p.City, p.County, p.InstanceValue, p.JobID }).ToList();

I am stuck with the Group By clause with multiple join query

Upvotes: 0

Views: 90

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

var query = (from jr in database.JobResults
             join em in database.Employers
                   on jr.EmployerName equals em.EmployerName
             join ad in database.Addresses
                 on em.AddressID equals ad.AddressID
             where jr.FieldName == "Job Title" && jr.IsPremium == true
             group new { jr, em, ad } by new {
                 jr.JobID,
                 em.EmployerID,
                 em.EmployerName,
                 ad.City,
                 ad.Country,
                 jr.InstanceValue
             } into g
             select new {
                 g.Key.JobID,
                 g.Key.EmployerID,
                 g.Key.EmployerName,
                 g.Key.City,
                 g.Key.Country,
                 g.Key.InstanceValue
             }).ToList();

Upvotes: 2

Related Questions