Filter Child Entities in Controller LINQ

Question

I'm trying to filter out the items I pass through to my View in my Controller, and while I have a solution, I'm keen to know the right way to do it, as I'm sure it's not the best.

I have a table of Work Orders and related Tasks for each Work Order. In my site I do not delete rows, but mark them with a WhenDeleted date, so when populating my Work Order View (single, not list) I want a WorkOrder entity with the child Tasks, but I want to filter out the Tasks marked as deleted.

Here's my code, which works, but it seems like I should be able to filter this out with the initial call to save a trip.

if (db.tblWorkOrder.Any(x => x.ID == id))
{
    tblWorkOrder model = db.tblWorkOrder.First(x => x.ID == id);
    model.tblTask = model.tblTask.Where(x => x.WhenDeleted == null).ToList()
}

Solution: The duplicate question marked here had the answer. There are others with stronger google-fu here, and I think it helped knowing the terminology of what I was trying to do. Anywho, here's the answer for others who find this question first:

db.Configuration.ProxyCreationEnabled = false; // disable lazy-loading
if (db.tblWorkOrder.Any(x => x.ID == id))
{
    tblWorkOrder model = db.tblWorkOrder.Where(b => b.ID == id)
        .Select(b => new
        {
            b,
            tblTask = b.tblTask.Where(p => p.WhenDeleted == null).OrderBy(x => x.Position)
        })
        .AsEnumerable()
        .Select(x => x.b)
        .First();
}

If it's already working you may want to ask for opinions/critique at [codereview](http://codereview.stackexchange.com/) — AlG, Feb 19 '16 at 12:21
My question is if I have 5 Tasks and 3 have a WhenDeleted value, how can I modify tblWorkOrder model = db.tblWorkOrder.First(x => x.ID == id) to exclude these so that model.tblTask only has 2 items? — Red, Feb 19 '16 at 12:24
I can't mark the Duplicate as the answer, so I've updated the question to include a resolution — Red, Feb 22 '16 at 11:04

score 0 · Answer 1 · answered Feb 19 '16 at 12:23

0

This should save a trip to the db because the query is executed only when it hits for SingleOrDefault().

           tblWorkOrder model =  (from p in db.tblWorkOrder
            where p.ID == id
            select new tblWorkOrder
            {
              // other tblWorkOrder properties
               tblTask = from q in p.Tasks where q.WhenDeleted == null select q 
            }).SingleOrDefault();

answered Feb 19 '16 at 12:23

Zippy

1,804
5
27
36

I feel like this is close to what I need, but using the code as above (but correcting to p.tblTasks) I get a conversion error from IEnumerable to ICollection. Adding .ToList() to "from p in db.tblWorkOrder" and wrapping the sub from in brackets with a .ToList() works but returns and empty tblWorkOrder. – Red Feb 19 '16 at 14:07

score 0 · Answer 2 · answered Feb 19 '16 at 12:26

Unfortunately, EF does not support explicitly loading a partial set of child entities once the parent entity has been read into memory. Once you read the tblTask property, all associated tasks are read into memory. One option is to read an anonymous object into memory with the data you want:

db.tblWorkOrder.Where(x => x.ID == id)
   .Select(x => new { x.ID, ..., tasks = x.tblTask.Where(t => t.WhenDeleted == null) })
   .First();

Another approach is to use two explicit queries against the dbContext:

tblWorkOrder model = db.tblWorkOrder.First(x => x.ID == id);
model.tblWorkOrder = db.tblTask.Where(t => t.WhenDeleted == null).ToList();

However the risk of this (as with your original approach) is that by modifying an "active" entity object (model in this case), calling SaveChanges() on the context would now try to update the database, which is not what you want.

Another approach is to create a view for your tasks table which filters out the deleted task, and create a separate EF model and navigation property for that view.

In general, I've found that trying to use EF entity types directly as your view models doesn't work very well because the entity types are strongly tied to the database structure and view models frequently want to diverge (as is the case here). I think the best long-term approach is to create separate classes for your view models and just use the entities to query the database and populate the models.

score 0 · Answer 3 · answered Feb 19 '16 at 12:31

0

Another approach than the other answers would be to create a constructor for your entity:

public TblWorkOrder(TblWorkOrder tbl, IEnumerable<Task> tasks)
{
   this = tbl;
   this.tbltask = tasks;
}

Then you can use that constructor in your Select:

if (db.tblWorkOrder.Any(x => x.ID == id))
{
   tblWorkOrder model = db.tblWorkOrder.Where(x => x.ID == id).AsEnumerable()
     .Select(x => new TblWorkOrder(x,x.tblTask.Where(x => x.WhenDeleted == null))).First();
}

Because of the call to AsEnumerable() the resultset will be processed in memory which enables you to use constructors in your select. This won't be a performance problem as you will only fetch 1 entity anyways (like you do now). Also it would be best to Include() the tasks to save another trip to the database because of lazy loading.

answered Feb 19 '16 at 12:31

Alexander Derck

13,818
5
54
76

This will also take two roundtrips. – Gert Arnold Feb 20 '16 at 21:47
@GertArnold You're right, with an include on the tabletasks it would be solved though? – Alexander Derck Feb 21 '16 at 14:39
Yes, but a next potential problem is that the entities (work order and tasks) are not tracked by the context. This would only work well for read-only scenarios. – Gert Arnold Feb 21 '16 at 19:56
They're for passing to a view so that wouldn't be an issue – Alexander Derck Feb 21 '16 at 21:06

score 0 · Answer 4 · answered Feb 19 '16 at 12:55

There is multiple ways to accomplish this, one way that would limit your chattiness with the database would be:

var result = db.tblTask.Where(x => x.WhenDeleted == null && x.WorkOrderId = id).GroupBy(x => x.WorkOrder).FirstOrDefault();
if(result != null){
   return new {WorkOrder = result.Key, Tasks = result.ToList()};
}

Another and in my opinion cleaner approuch would be to use EntityFramework.Filters where you can configure a filter like this in your context

DbInterception.Add(new FilterInterceptor());
modelBuilder.Conventions.Add(FilterConvention.Create<tblTask>("OnlyActive", (task) => task.WhenDeleted == null));

And apply it before your query like this:

db.EnableFilter("OnlyActive");
tblWorkOrder model = db.tblWorkOrder.First(x => x.ID == id);
db.DisableFilter("OnlyActive");

Filter Child Entities in Controller LINQ

4 Answers4