Is there a way to customize the default behavior of entity queries?

Question

I have two tables and one is self-referencing like this:

Job (id, description)
JobAssembly (id, jobid, parentassemblyid)

I have two domain objects in similar fashion:

public class Job
{
    public int Id { get; set; }
    public string Description { get; set; }

    public virtual List<JobAssembly> Assemblies { get; set; }
}

public class JobAssembly
{
    public int Id { get; set; }

    public int JobId { get; set; }
    public virtual Job { get; set; }

    public int? ParentAssemblyId { get; set; }
    public virtual JobAssembly ParentAssembly { get; set; }

    public virtual List<JobAssembly> SubAssemblies { get; set; }
}

Here is the problem. When I use EF:

using (var db = new JobContext())
{
     var job = db.Jobs.Find(1);
}

I get, as expected, the job requested. But it comes with ALL of the assemblies - not just the parents but the sub assemblies. Again this is expected.

My question is: How do I instruct EF to only bring in JobAssemblies which have no subassemblies... as the default behavior? I know how to query EF for said parent assemblies. But is there a way to set the mappings, or some other way, to set the default query behavior to only get the assemblies whose parentassemblyid == null?

Thanks :)

EDIT:

Allow me to illustrate:

I have a Job with id = 1. It has one Assembly with id = 1. Assembly 1 has two sub assemblies with ids = 2 and 3 respectively. When var job = db.Jobs.Find(1) is executed, EF populates the object graph like so:

The job has all three assemblies (because jobid on all three == 1). JobAssembly with id 1 has its subassemblies populated appropriately.

All of this is expected, but it would be nice if I could customize how EF is loading the objects. Job should not have every JobAssembly where JobId == 1, but only where JobId == 1 and ParentAssemblyId == null.

Can you show the code where you populate the `Assemblies` property? There is no `virtual` keyword so it is not lazy-loaded. So you must eager load it somehow otherwise you would not get any assemblies far less ALL of the assemblies — Colin, Mar 27 '14 at 10:42
My fault. Let me show the virtual keyword. I will make the edit. — Price Jones, Mar 27 '14 at 11:45
Do you want to bring in "just the parents" - i.e. the roots of the tree. Or do you want to bring in "JobAssemblies which have no subassemblies" - i.e. the leaves? — Colin, Mar 27 '14 at 14:11
I need the entire tree, but with some custom limitations. I explain in the edit above in more detail. — Price Jones, Mar 27 '14 at 15:07

score 2 · Accepted Answer · edited May 23 '17 at 12:28

If I understand correctly, you want Job.Assemblies to contain only those Assemblies that have no parent (i.e. those Assemblies that are direct children of the Job, rather than grandchildren etc).

The "normal" way to do this would be to only have the direct children reference the Job via foreign key, and have the grandchildren etc. reference only their parents.

It seems likely to me that the Assemblies table has been created like this in order to optimise data reads (i.e. so you only have to query once on JobId, then you can create the tree structure in memory). I'm going to assume that's the case, rather than telling you to change your database structure. If that's not the case, let me know.

There are a few ways that you can get only the direct children of your Job. The simplest way would be to just have a property of your Job class do the filtering for you:-

public class Job
{
  public int Id { get; set; }
  public string Description { get; set; }

  public virtual List<JobAssembly> Assemblies { get; set; }

  public IEnumerable<JobAssembly> DirectChildren
  {
    get
    {
      return this.Assemblies == null
        ? null
        : this.Assemblies.Where(x => x.ParentAssemblyId == null);
    }
  }
}

but if you're going to take this approach you need to be really really careful that you're not lazy loading data in a silly way. Some people, when faced with a problem, think "I know, I'll use an O/RM". Now they have N+1 problems ;)

A more robust solution would be to use a separate ViewModel to encapsulate the tree structure you want in your application tier. This prevents Select N+1 issues because your data tier takes responsibility for pulling the whole list of Assemblies in a single query, then maps them into a tree for your application tier:-

public class JobViewModel
{
  public int Id { get; set; }
  public string Description { get; set; }

  public virtual List<JobAssemblyViewModel> Children { get; set; }
}

public class JobAssemblyViewModel
{
  public int Id { get; set; }

  public virtual List<JobAssemblyViewModel> Children { get; set; }
}

If you do this a lot, you may want to consider using e.g. AutoMapper to project your queries onto your view models for you.

score 0 · Answer 2 · answered Mar 27 '14 at 15:44

Here's an idea using inheritance to distinguish between RootAssemblies and SubAssemblies:

public abstract class JobAssembly
{
    public int Id { get; set; }

    public virtual List<SubAssembly> SubAssemblies { get; set; }
}

public class SubAssembly : JobAssembly
{
    public int ParentAssemblyId { get; set; }

    public virtual JobAssembly ParentAssembly { get; set; }
}

public class RootAssembly : JobAssembly
{
    public int JobId { get; set; }

    public virtual Job Job { get; set; }
}

public class Job
{
    public int Id { get; set; }

    public string Description { get; set; }

    public virtual List<RootAssembly> Assemblies { get; set; }
}

Is there a way to customize the default behavior of entity queries?

2 Answers2