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?

Why don't you just get the tasks? And Then Just the object without the tasks? — lahsrah
– lahsrah, Commented Apr 4, 2013 at 11:53

Skyp · Accepted Answer · 2013-04-04 12:10:50Z

6

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;

answered Apr 4, 2013 at 12:10

Skyp

1,19711 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tikkes Over a year ago

This actually does work and gives me the original object indeed. Thank you!

Nick Butler · Accepted Answer · 2013-04-04 11:58:04Z

5

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;

answered Apr 4, 2013 at 11:58

Nick Butler

24.5k4 gold badges51 silver badges72 bronze badges

2 Comments

Tikkes Over a year ago

This might be working, just testing some stuff with this one still.

ejhost Over a year ago

FYi -- if you don't want to deal with anonymous objects, you can explicitly cast it to a model in your Select via .Select( x=> new YourModelClass

Daniel Hilgarth · Accepted Answer · 2013-04-04 11:51:55Z

2

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.

answered Apr 4, 2013 at 11:51

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

2 Comments

Tikkes Over a year ago

So what would be a better way of approach? since I have for instance 1000 tasks and I do not want to load all of them since, say, 600 have the IsDeleted flag set to true

Daniel Hilgarth Over a year ago

@Tikkes: I think you want a projection in that case. Nicholas Butler shows an example for EF.

lahsrah · Accepted Answer · 2013-04-04 11:57:23Z

1

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);

answered Apr 4, 2013 at 11:57

lahsrah

9,2135 gold badges39 silver badges70 bronze badges

Collectives™ on Stack Overflow

Lambda expression: Where clause for Include list

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related