Entity Framework ThenInclude filtered from original dataset-CodePudding

I have a query where my ThenInclude Where needs to access data in the original data set e.g.

var bikes = context.Bikes
.Include(bike => bikes.Models)
.ThenInclude(model => model.Spec.Where(spec=> spec.SpecYear == bike.YearCreated ))   
.ToList();

I currently have this in two separate queries but it would save a lot of time if I completed this request on the database.

CodePudding user response：

Try to use projection instead of Include:

var bikes = context.Bikes
    .Select(bike = new Bike
    {
        Id = bike.Id,
        // ... other fields

        Models = bike.Models.Select(m => new Model
        {
            Id = m.Id
            // ... other fields

            Spec = m.Spec
                .Where(spec => spec.SpecYear == bike.YearCreated)
                .ToList()
        })
    })
    .ToList();