locked
EF Core 2.2 Left Outer Join with Group By Aggregation RRS feed

  • Question

  • User1548699010 posted

    Hi there,

    I have one entity, Property, with a value object Address, by using EF Core Code First, I got a table of Property and Address with a primary key point to the PropertyId. What I need to do is to find the number of properties on each city in the address, here is my code:

    var query = _contex.Property.Include(a => a.Address)

                         .Where (s => s.Status == "Occupied")

                          group p by p.Address.City into n

                          select new ViewModel

                          {

                                City.=n.Key.ToString(),

                                 Count = n.Count()

                           }

                   return query

    All looks good, but one thing, if there is no property with this status for a city, the city will NOT appear in the result. What I need is, in this situation, something like City: Seattle Count: 0. I kind of understand I need to use OUTER Join, just need help in this situation.

    I googled a while, but not found anything that helps, can you guys help or point me to some resource.

    Thanks and really appreciate.

    Bob

    Tuesday, February 4, 2020 4:36 AM

All replies