EF Core 5 - Including only one entity from collection

Question

How do you include just a single entity per "main" entity in a query where the navigational property is a collection?

await _database.Table1
    .AsQueryable()
    .Include(t1 => t1.Table2.Where(t2 => t2.Id == t1.SingleTable2Id))
    .FirstOrDefaultAsync();

The above doesn't seem to work. I get the following exception:

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This is not possible in EF 5 - if you want only one of the related entities you must use a separate query to retrieve it — Nannanas, Jan 07 '22 at 14:37
@Nannanas What exactly is possible in EF core 6? Please take a careful look at the query: `t2.Id == t1.SingleTable2Id`. — Gert Arnold, Jan 07 '22 at 19:40
@Arkuni the query he build would be working in EF 6 as filtered includes are supported there - see https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include — Nannanas, Jan 07 '22 at 21:45
@Nannanas They're also supported in EF core 5. But this predicate is not supported, neither in EF core 6. — Gert Arnold, Jan 08 '22 at 08:27
@GertArnold Sorry. I meant that it should only include one Table2 row PER Table1 row. — Arkuni, Jan 08 '22 at 20:42
I get this exception when trying: could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. — Arkuni, Jan 09 '22 at 11:27
`t1.SingleTable2Id` can't be used in the `Include` predicate. See [here](https://stackoverflow.com/a/61147681/861716) under "The filter expression". — Gert Arnold, Jan 10 '22 at 08:08

Golden Lion · Answer 1 · 2022-01-10T15:43:46.017

Try this:

  IQueryable query=  _dbcontext.Table1
.AsQueryable();

As Queryable creates an expression tree that is not evaluated until fetch

you can add where expressions to your tree dynamically and assess include tables automatically.

Product[] products =
        {
            new Product {ProductId=1, Name="Kayak",Category="Watersports",Price=275m, Company=new Company{ CompanyName="Abc Corp",States=new String[]{"Ut","Id" } }  },
            new Product {ProductId=2, Name="Lifejacket", Category="Watersports",Price=48.95m, Company=new Company{ CompanyName="X Corp",States=new String[]{"Ca","Az" } }},
            new Product {ProductId=3, Name="Soccer Ball", Category="Soccer",Price=19.50m, Company=new Company{ CompanyName="Y Corp",States=new String[]{"Tx","Wa" } }},
            new Product {ProductId=4, Name="Corner Flag", Category="Soccer",Price=34.95m, Company=new Company{ CompanyName="Z Corp",States=new String[]{"Co","Wy" } }}
             };


        IQueryable<Product> query = products.
            Where(e => e.Name == "Kayak")
            .AsQueryable<Product>();
        

        Product item = query.FirstOrDefault<Product>();
        _output.WriteLine($"Item Name: {item.Name} Company: {item.Company.CompanyName}");

EF Core 5 - Including only one entity from collection

1 Answers1