Entity Framework - Conditionally include related entities

Question

I maintain an API that, based on a request for a list of people, returns a different result set based on the request. For example, some API clients want to get a list of people and a list of their interactions, others want people and a list of their metadata. All this can be specified int he request to the API method that returns people.

This does not appear to work:

using (var dbcontext = new ExampleEntities())
{
    var query = dbcontext.People.AsQueryable();
    //determined in earlier application logic based on request
    if(includeMetadata)
    {
        query = query.Include("metadata");
    }
    //determined in earlier application logic based on request
    if(includeInteractions) 
    {
        query = query.Include("interactions");
    }
    /* ...SNIP... */
}

What I don't want to do is this:

var query = dbcontext.People.Include("Metadata").Include("interactions");

which will mean every request to get a person will include ALL their related entities, even if the requesting API client does not need them.

I also don't want to code every possible combination of logic:

if(includeMetadata && includeInteractions)
{
    var query = dbcontext.People.Include("Metadata").Include("interactions");

}
else if(includeMetadata)
{
    var query = dbcontext.People.Include("Metadata");
}
else if(includeInteractions)
{
    var query = dbcontext.People.Include("Interactions");
}
else
{
    var query = dbcontext.People;
}

This will result in hard-to-maintain code, however, I realize I could code generate this if needed.

VahidN · Accepted Answer · 2013-12-10 21:24:00Z

12

You can chain the IQueryable's

using (var dbcontext = new ExampleEntities())
{
    var query = dbcontext.People.AsQueryable();
    if(includeMetadata)
    {
        query = query.Include("metadata");
    }
    if(includeInteractions) 
    {
        query = query.Include("interactions");
    }    
}

answered Dec 10, 2013 at 21:24

VahidN

19.4k8 gold badges80 silver badges124 bronze badges

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

3 Comments

Julia McGuigan Over a year ago

Is this only available in a different version of Entity Framework? I get the following error: Error 37 'System.Linq.IQueryable<People>' does not contain a definition for 'Include' and no extension method 'Include' accepting a first argument of type 'System.Linq.IQueryable<People>' could be found (are you missing a using directive or an assembly reference?) Perhaps a missing extensions assembly, as well?

VahidN Over a year ago

Chaining is a standard feature of LINQ. But Include is an EF extension method (msdn.microsoft.com/en-us/library/gg696450%28v=vs.103%29.aspx) and needs a reference to EntityFramework.dll assembly and adding using System.Data.Entity; also remove that AsQueryable().

Julia McGuigan Over a year ago

Okay. I was confused since I could call .Include on dbcontext.SomeTable but not someQuery. After I added the required import, it worked fine. I was already refeencing EntityFramework.dll

Aducci · Accepted Answer · 2013-12-10 21:23:21Z

0

Your first example should work if you replace u with query

 u = u.Include("metadata");

with

 query = query.Include("metadata");

answered Dec 10, 2013 at 21:23

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

1 Comment

Julia McGuigan Over a year ago

typo - sorry! The typo only existed in the question

ajd · Accepted Answer · 2013-12-10 21:52:55Z

Works fine here... checking the sql statements with the EF 6 Log handler

[TestClass]
public void SomeTestClass
{
    [TestMethod]
    public void ShouldLoadOnlyRequiredCollections()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<HomesContext>());

        var db = new HomesContext();

        Assert.IsFalse(db.Homes.Any());

        var home = db.Homes.Create();

        db.Homes.Add(home);

        home.Staff.Add(new Staff { Name = "wilma" });
        home.Staff.Add(new Staff { Name = "betty" });

        home.Residents.Add(new Resident { Name = "fred" });
        home.Residents.Add(new Resident { Name = "barney" });

        db.SaveChanges();

        db = null;

        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<HomesContext>());

        var sb = new StringBuilder();
        db = new HomesContext();

        db.Database.Log = ((s) => { sb.Append(s + "\r"); });

        Assert.IsTrue(db.Homes.Any());

        string log;

        log = sb.ToString();

        Assert.IsTrue(sb.ToString().Contains("FROM [dbo].[Homes]"));

        sb = new StringBuilder(); //ok get residents

        var q = db.Homes.Include("Residents");

        Assert.IsTrue(string.IsNullOrEmpty(sb.ToString()));

        var lst = q.ToList();

        log = sb.ToString();

        Assert.IsTrue(sb.ToString().Contains("[dbo].[Homes]"));
        Assert.IsTrue(sb.ToString().Contains("[dbo].[Residents]"));
        Assert.IsTrue(!sb.ToString().Contains("[dbo].[Staff]"));

        sb = new StringBuilder(); //get staff

        q = db.Homes.Include("Staff");

        Assert.IsTrue(string.IsNullOrEmpty(sb.ToString()));

        lst = q.ToList();

        log = sb.ToString();

        Assert.IsTrue(log.Contains("[dbo].[Homes]"));
        Assert.IsTrue(!log.Contains("[dbo].[Residents]"));
        Assert.IsTrue(log.Contains("[dbo].[Staffs"));



        sb = new StringBuilder(); //get residents and staff

        q = db.Homes.Include("Staff");

        q = q.Include("Residents");

        lst = q.ToList();

        log = sb.ToString();

        Assert.IsTrue(log.Contains("[dbo].[Homes]"));
        Assert.IsTrue(log.Contains("[dbo].[Residents]"));
        Assert.IsTrue(log.Contains("[dbo].[Staffs]"));




    }
}

public class HomesContext:DbContext
{
    public DbSet<Home> Homes { get; set; }
}

public class Home
{
    public Home()
    {
        Staff = new List<Staff>();
        Residents = new List<Resident>();
    }
    public int HomeId { get; set; }
    public string HomeName { get; set; }

    public int MaxResidents { get; set; }
    public int MaxStaff { get; set; }

    public int CurrentResidents { get; set; }

    [NotMapped]
    public int CurrentStaff { get; set; }

    public IList<Staff> Staff { get; set; }
    public IList<Resident> Residents { get; set; }
}

public class Staff
{
    public int StaffId { get; set; }
    public string Name { get; set; }
    public int HomeId { get; set; }
    public Home Home { get; set; }
}

public class Resident
{
    public int ResidentId { get; set; }
    public string Name { get; set; }

    public int HomeId { get; set; }
    public Home Home { get; set; }
}

Not sure what you mean by that. How would I check. It's pulling off a view.
I will check the version of EntityFramework and try upgrading it if necessary
You can do msg = q.ToString() and that will give you the SQL that the query will be trying to fire off, or you can run Sql Profiler. But EF 6 is good..

Collectives™ on Stack Overflow

Entity Framework - Conditionally include related entities

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related