CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
tylkonachwile
tylkonachwile

Reputation: 2267

How to rewrite sql query to linq

I'm trying to rewrite sql query to linq but can't do it myself. The most problem for me is to get I,II and III aggregated values. Sql query:

  select  o.Name,t.TypeID, SUM(e.I),SUM(e.II),SUM(e.III) from Expenditure e
  join Finance f on f.FinanceId = e.FinanceId
  join FinanceYear fy on fy.FinanceYearId = f.FinanceYearId and fy.StatusId = 1
  join Project p on p.ProjectId = fy.ProjectId
  join Organization o on o.OrganizationId = p.OrganizationId
  join Type t on t.TypeID = p.TypeID
  where fy.Year = 2018
  group by o.Name,s.TypeID

and what I have done so far is:

    var x = (from e in _db.Expenditures
             join f in _db.Finances on e.FinanceId equals f.FinanceId
             join fy in _db.FinanceYears on f.FinanceYearId equals fy.FinanceYearId and fy.StatusId = 1 // this does not work, cant join on multiple conditions?
             join p in _db.Projects on fy.ProjectId equals p.ProjectId
             join o in _db.Organizations on p.OrganizationId equals o.OrganizationId
             join s in _db.Types on p.TypeId equals s.TypeId
             group new { o, s } by new { o.OrganizationId, s.TypeId }
             into grp
             select new AggModel
             {
                 OrganizationId = grp.Key.OrganizationId,
                 TypeId = grp.Key.TypeId,
                 I = ?,
                 II = ?,
                 III = ?,
             }
             );

Upvotes: 2

Views: 238

Answers (2)

SBFrancies
SBFrancies

Reputation: 4240

Try something like this:

group new { e, o, s } by new { o.OrganizationId, s.TypeId }
into grp
select new AggModel
{
    OrganizationId = grp.Key.OrganizationId,
    TypeId = grp.Key.TypeId,
    I = grp.Sum(a => a.e.I),
    II = grp.Sum(a => a.e.II),
    III = grp.Sum(a => a.e.III),
}

You'll need to adjust the right side of the lambda to navigate to the correct property.

Upvotes: 2

ZunZiAc
ZunZiAc

Reputation: 31

You Need to use the Group by for aggregation methods. Check the below link for more Knowledge.

How to use aggregate functions in linq with joins?

Upvotes: 0

Related Questions