Lambda expression: Where clause for Include list

Question

So I got something like this:

var myObj = db.SomeObject
              .Include("Tasks")
              .SingleOrDefault(x => x.Id == someObjectId);

if (myObj != null)
{
    myObj.Tasks = myObj.Tasks.OrderBy(x => x.Number).ToList();
}

Here I want to be able to put a condition (where) on my Include, for instance: .where task.IsDeleted == false

Thusfar I have not found a solution.

I am aware of the fact that I could use the where together with where I order the tasks but this however, does not run on the database but in stead uses memory. I want it to run on the database.

Does anyone here know how I can do this? If so, is there also a way to put the order by condition to the included list of tasks?

Answer 1

Something like this, returs you your original object with its child collection filtered and sorted.

SomeObject a = db.SomeObjects.Where(x => x.Id == someobjectid)
                      .Select(
                          x =>
                          new
                              {
                                  someObject = x,
                                  task = x.Tasks.Where(task => task.IsDeleted == false)
                                                .OrderBy(task => whatever)
                              })
                      .Select(x => x.someObject).Single();

It's actually loosing the collection of activities in the last select so you can do this :

SomeObject a = db.SomeObjects.Where(x => x.Id == someobjectid)
                      .Select(
                          x =>
                          new
                              {
                                  someObject = x,
                                  task = x.Tasks.Where(task => task.IsDeleted == false)
                                                .OrderBy(task => whatever)
                              });
 return a.FirstOrDefault().someObject;

Answer 2

To do this is EF, you need to specify a projection with a Select clause.

Something like this will get just the data you want from the db:

var anonymous = db.SomeObject.Where( x => x.Id == someObjectId )
  .Select( x => new
    {
      SomeObject = x,
      Tasks = x.Tasks
        .Where( o => !o.IsDeleted )
        .OrderBy( o => ... )
    }
  )
  .SingleOrDefault()
;

You will end up with an instance of the anonymous type , but you can easily fix that up on the client:

MyObject myObject = anonymous.SomeObject;
myObject.Tasks = anonymous.Tasks;

Answer 3

The simple answer is: You can't do that.

Why? Because you query SomeObject.
Each returned SomeObject contains all referenced data, because if it wouldn't it wouldn't represent the actual object in the database.

Answer 4

What about getting them separately:

var myObj = db.SomeObject
              .SingleOrDefault(x => x.Id == someObjectId);


var tasks = db.SomeObject
              .Where(x => x.Id == someObjectId)
              .SelectMany(x => x.Tasks)
              .Where(x => !x.Deleted);