-1

How can i write a query in linq c#(EF Core 6) for total price and also map other fields of DTO along with total price.

sql Query:

SELECT (sum(c.ExtraPrice) + (a.PricePerSqM*10)) as TotalPrice FROM dbo.Cities a
JOIN dbo.CityExtras b ON a.CityId = b.CityId
JOIN dbo.Extras c ON b.ExtrasId = c.ExtrasId
where a.CityId = 1
group by PricePerSqM
Pradeep Kumar
  • 1,193
  • 1
  • 9
  • 21
Hareem
  • 1
  • 3
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Oct 17 '22 at 20:38

1 Answers1

0

Try the following query:

var query = 
    from a in ctx.Cities
    from b in a.CityExtras
    where a.CityId == 1
    group new { a, b } by new { a.PricePerSqM } into g
    select new 
    {
        g.Key.PricePerSqM,
        TotalPrice = g.Sum(x => x.b.ExtraPrice) + g.Key.PricePerSqM * 10
    };
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
  • can you please guide in this query syntax how can we add contains() in where clause? e.g .Where(x => extraIds.Contains(x.ExtrasId) && x.CityId == 1) extraIds is an integer list. – Hareem Oct 18 '22 at 00:06
  • `where a.CityId == 1 && extraIds.Contains(b.ExtrasId)` – Svyatoslav Danyliv Oct 18 '22 at 02:45
  • ´ public class QuotationDTO { public string City { get; set; } public int PricePerSqM { get; set; } public List? Extras { get; set; } public List? ExtraPrice { get; set; } public int Totalprice { get; set; } }´ can you please help to fill this DTO from the query , I am trying but ExtraPrice int is not going to the list. – Hareem Oct 18 '22 at 19:18
  • Better update question – Svyatoslav Danyliv Oct 18 '22 at 19:25
  • I have updated the question, please have a look. – Hareem Oct 18 '22 at 20:05