EF Core 5.0.2 - Group Join Count

Question

I know there are a few topics about this already but none seem to help or solve my problem. I am trying to perform a query with a one to many relationship and simply provide a count. I've tried writing this query several ways but can't seem to get it to work. I'm using EF Core 5.0.2.

Example: I had used this before, it had worked. Now doesn't appear to since my change to NET5.

var query = (from p in _db.Devices
join variant in _db.DeviceVariations.DefaultIfEmpty() on p.Id equals variant.DeviceID
into variants
select new DeviceBasicView.Model
{
    DeviceID = p.Id,
    DeviceName = p.DeviceName,
    DeviceType = p.DeviceTypeIdentifier,
    Manufacturer = p.DeviceManufacturer,
    Status = p.Status,
    VariantCount = variants.Count()
}).ToList();

I've tried the following too, short of pulling the entire data set into memory, I don't know how to get this to work. Any help would be appreciated. Even if it means changing my Entity classes to have ICollections? (I'm a little new to EF Core, normally use EF6)

var test = (from p in _db.DeviceVariations.Include("Device")
group p by new { p.Device } into deviceGrouped
select new
{
    Device = deviceGrouped.Select(s => s.Device),
    Variants = deviceGrouped.Count()
}).ToList();


var query2 = (from p in _db.Devices
join s in _db.DeviceVariations on p.Id equals s.DeviceID 
into variants
from s in variants.DefaultIfEmpty()
group s by new { s.DeviceID } into deviceGrouped
select new
{
    Device = deviceGrouped.Select(s => s.Device),
    Variants = deviceGrouped.Count()
}).ToList();

Answer 1

Having resorted to post on here, I've just managed to solve it. I added the below into my device entity (already having the FK reference in my variants entity) and modified my query as below. Guess I just needed to fully map the relationships.

Device Entity

 public List<DeviceVariationEntity> Variants { get; set; }

Variant Entity

  [ForeignKey("DeviceID")]
  public DeviceEntity Device { get; set; }

Working Query

var query = (from p in _db.Devices.Include("Variants")                                         
select new DeviceBasicView.Model
{
   DeviceID = p.Id,
   DeviceName = p.DeviceName,
   DeviceType = p.DeviceTypeIdentifier,
   Manufacturer = p.DeviceManufacturer,
   Status = p.Status,
   VariantCount = p.Variants.Count()
 }).ToList();