How can I write a query in linq c#(EF Core 6) for 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

My Models:

        public class City
        {
            public int CityId { get; set; }
            [Required]
            public string Name { get; set; }
            [Required]
            public int PricePerSqM { get; set; }
            public ICollection<CityExtras> CityExtras { get; set; }
        }
     
    
        public class Extras
        {
            public int ExtrasId { get; set; }
            [Required]
            public string ExtrasName { get; set; }
            public ICollection<CityExtras> CityExtras { get; set; }
        }
    
              public class CityExtras
        {
    
            public int CityId { get; set; }
            public City City { get; set; }
            public int ExtrasId { get; set; }
            public Extras Extras { get; set; }
            public int ExtraPrice { get; set; }
    
        }

CodePudding user response：

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