how to write linq query with join on 3 tables performing sum and multiplication

Question

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

Answer 1

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