EF include with where clause db first

Question

I am aware of the other similar question at the link below, but those answers do not work as I am db first and the entity framework plus solution is very slow and appears to just get every record and filter it out afterwards. EF: Include with where clause

drivers and passengers are navigational properties on busses. preferredBrands is a List<String>. So basically what I want is all busses where typeid = 2, all drivers where their preferredbrand is listed in the preferredbrand list, and all passengers. The query below throws exception:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.
Parameter name: path

Query:

var result =  db.busses.Where(h => h.bussesTypeId.Equals(2))
.Include(h => h.drivers.Where(p => preferredBrands.Contains(p.PreferredBrand)))
.Include(h => h.passengers).ToList();

score -1 · Answer 1 · answered Nov 21 '17 at 18:59

'Include' only accepts string parameter and not delegate. You have to place the Relational property in quote (string). Do this instead

var result =  db.busses
.Include("drivers.preferredBrands")
.Include("passengers")
.Where(h => h.bussesTypeId.Equals(2));

To access drivers, use

result.drivers.where(p => preferredBrands.Contains(p.PrefferedBrand));

However, if you must use delegate in your include, then
1. Reference Z.EntityFramework from Nuget
2. Then you can use 'IncludeFilter' instead as follows;

  using Z.EntityFramework.Plus;

  var result =  db.busses
  .IncludeFilter(h => h.drivers.Where(p => preferredBrands.Contains(p.PrefferedBrand)))
  .IncludeFilter(h => h.passengers)
  .Where(h => h.bussesTypeId.Equals(2)).ToList();

Note: You must use the Include or IncludeFilter before the where clause. It must be used directly on the EF Entity and not just on any IEnumerable

EF include with where clause db first

1 Answers1