Entity Framework Core (Postgres) Multiple Includes creates ghost property

Question

I'm having an issue with a series of Include/ThenInclude in a query.

Here is my EntityFrameworkCore Query :

var fund = await funds.Where(x => x.Id == fundId)
                       .Include(f => f.Compositions.Where(compo => compo.Date == compositionDate))
                       .ThenInclude(c => c.CompositionItems)
                       .ThenInclude(item => item.Asset)
                       .FirstOrDefaultAsync(token)
                   ?? throw new NotFoundException(nameof(Fund), fundId);

I recieve a 'CompositionDate does not exists' error. As you can see the CompositionDate property is at the Compositions Level.

When I check the SQL generated I get this in a subquery Select statement :

SELECT f1."CompositionFundId", f1."CompositionDate", f1."AssetId", f1."Amount", a."Id", a."CountryCode", a."Currency", a."FundCompositionDate", a."FundCompositionFundId", a."Isin", a."Name", a."SecurityType", a."Ticker", a."Coupon", a."GicsSector", a."InvestmentCase", a."IpoDate", a."Theme"
          FROM "FundCompositionItem" AS f1
          INNER JOIN "Asset" AS a ON f1."AssetId" = a."Id"

Those 2 properties a."FundCompositionDate", a."FundCompositionFundId" doesn't exists at the 'Asset' level. They exists in the parent (at the 'Where' level on the first Include).

I'm using Postgres provider for EFcore. Could this be the issue?

Should I be using the select anonymous type .Select(x => new { Fund = x, Compo = x.Compo.Where(...), etc... }? I would like to preserve the navigation properties if possible. (accessing assets from compositionItems)

Any help would be much appreciated.

Edit: Models as requested by Atiyar:

public class Portfolio : AuditableEntity
{
    public Guid Id { get; set; }
    public Guid Name{ get; set; }
}

 public class Fund : Portfolio
{
    // Irrelevant properties
    public IList<FundComposition> Compositions { get; } = new List<FundComposition>();
}

public class FundComposition
{
    public Fund Fund { get; set; }

    // Primary key / Foreign key
    public Guid FundId { get; set; }

    // Primary Key
    public DateTime Date { get; set; }

    public List<FundCompositionItem> CompositionItems { get; set; } = new();
    
}

public class FundCompositionItem
{
    public FundComposition Composition { get; set; }

    // Primary Key
    public Guid CompositionFundId { get; set; }

    // Primary Key
    public DateTime CompositionDate { get; set; }

    public Asset Asset { get; set; }

    // Primary Key
    public Guid AssetId { get; set; }

    public double Amount { get; set; }
}

 public class Asset : BaseEntity
{
    // Primary Key
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Currency { get; set; }
    // more properties
}

Please also share the actual exception – James Lieu Mar 23 '21 at 08:36 — James Lieu, Mar 23 '21 at 08:36

score 0 · Answer 1 · answered Mar 22 '21 at 21:22

0

In my experience, I've applied the Include() and ThenInclude() first and then applied the any conditional clauses afterwards. I'm also not sure if using Where inside of an include method does what you expect it to.

You can also apply your conditional in the first parameter of .FirstOrDefaultAsync().

var fund = await funds.Where(x => x.Id == fundId)
    .Include(f => f.Compositions)
    .ThenInclude(c => c.CompositionItems)
    .ThenInclude(item => item.Asset)
    .FirstOrDefaultAsync(x => 
        x.Id == fundId && x.Compositions.Any(compo => compo.Date == compositionDate), 
        token
    )

answered Mar 22 '21 at 21:22

James Lieu

176
2
6

Hi, Thanks for your answer. I'll give your suggestion a try. For the Where clause inside an Include concern, it is now possible in .NET 5. https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core – R.Haughton Mar 22 '21 at 22:18
I just tried by puting the where clause at the end and also directly in the FirstofDefaultAsync. I still get the same exception. I even removed the .Where in the include statement just to be sure. but nothing has changed. – R.Haughton Mar 22 '21 at 22:29

Entity Framework Core (Postgres) Multiple Includes creates ghost property

1 Answers1