Where clause on collection property

Question

I am trying to express this sql as a EF Core 8 query

SELECT DISTINCT
    PRICE, UPDAT, f.ID AS fundid, f.coreserie
FROM 
    performance.FUND f
INNER JOIN
    performance.ULTIMO_FUND_NAV_LIMITED u ON u.ID = f.ID
WHERE 
    f.id = 51
    AND (f.startdate IS NULL OR u.updat >= f.startdate)
    AND (f.endDate IS NULL OR u.updat <= f.endDate)
ORDER BY 
    u.UPDAT;

I have mapped the Fund and the UltimoFundNavLimited entities.

This is what I have so fare. But this will not work against a database.

I'm getting a warning

Function is not convertible to SQL and must not be called in the database context

var lst = await _context.Funds
                        .Where(f => f.Id == fundId)
                        .Include(x=>x.UltimoFundNavLimited)
                        .SelectMany(f => f.UltimoFundNavLimited, (f, u) => new { Fund = f, Ultimo = u })
                        .Where(x => x.Ultimo.Update >= x.Fund.StartDate  
                                    && x.Ultimo.Update <= x.Fund.EndDate)
                        .ToListAsync();

These are the model classes:

public class Fund
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CoreSerieId { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public List<UltimoFundNavLimited> UltimoFundNavLimited { get; set; }
}

public class UltimoFundNavLimited
{
    public int FundId { get; set; }
    public DateTime Update { get; set; }
    public decimal Price { get; set; }
    public decimal Value { get; set; }
}

Answer 1

For queries which use more tan one table it is better to use Query syntax:

var query = 
     from f in  _context.Funds
     from u in f.UltimoFundNavLimited
     where f.Id == fundId && 
          (f.StartDate == null || u.Update >= f.StartDate) && 
          (f.EndDate == null || u.Update <= f.EndDate)
     orderby u.Update
     select new 
     { 
          u.Price,
          u.Update,
          fundid = f.Id,
          f.CoreSerieId
     };

var lst = query
     .Distinct()
     .ToList();