How to include with where clause in EntityFrameworkCore?

Question

I am having a problem trying to combine a where clause with an include in EntityFrameworkCore. This is how the class I am trying to do this for looks:

public partial class TEntity
{
    public TEntity()
    {
        TData = new HashSet<TData>();
    }

    public decimal Id { get; set; }
    publis string TypeName { get; set; }

    public virtual ICollection<TData> TData { get; set; }
}

This is how I am trying to get all entities of a specific type:

List<TEntity> entityList = dbContext.TEntity.Include("TData").Where(x => x.Type == typeName).ToList();

How can I add a filter to this so the collection of TData only contains specific entries based on my filter criteria?

Right now, it always returns all of the TData entries for the entity but I need to add a filter so only the entries with a specific date are included. Otherwise, thousands of TData entries are returned and the query takes forever to execute which is not acceptable.

Use this - List entityList = dbContext.TEntity.Include("TData").Where(x => x.TypeName == typeName).ToList(); — Serge, Feb 10 '21 at 13:28
@Sergey But how can I filter the TData-entries then? The attribute that I need to filter by belongs to the TData-class. — Chris, Feb 10 '21 at 13:29
With EF Core 5 : https://stackoverflow.com/a/61147681/2703673 — vernou, Feb 10 '21 at 13:31
Does this answer your question? [Filtering on Include in EF Core](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core) — vernou, Feb 10 '21 at 13:33
@Vernou I am stuck on EF Core 3. Are there any prerequisites for updating? — Chris, Feb 10 '21 at 13:33
[Breaking changes in EF Core 5.0](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/breaking-changes) — vernou, Feb 10 '21 at 13:38
@Vernou Are there any tutorials/documentations on how to upgrade? I tried upgrading the NuGet packages to EF Core 5 but it simply isn't possible. After somehow figuring out in which order I need to upgrade everything, it turns out that the Oracle.EntityFrameworkCore provider that I am using does not support version 5 of Microsoft.EntityFrameworkCore.Relational. — Chris, Feb 10 '21 at 14:46

score 0 · Answer 1 · edited Feb 10 '21 at 14:10

0

Try this one

var entityList = dbContext.TEntity
    .Include(t => t.TData.Where(x => x.Type == typeName))
    .ToList();

edited Feb 10 '21 at 14:10

Svyatoslav Danyliv

21,911
3
16
32

answered Feb 10 '21 at 13:33

Serge

40,935
4
18
45

Replace `Select` with `Where` – Svyatoslav Danyliv Feb 10 '21 at 14:05

How to include with where clause in EntityFrameworkCore?

1 Answers1