Entity Framework - 有条件地包含相关实体

Question

我维护一个API，它基于请求返回人员列表的不同结果集。例如，一些API客户端想要获取人员列表以及他们的互动列表，而其他人则想要人员列表和他们的元数据列表。所有这些都可以在请求中指定给返回人员的API方法。

这似乎不起作用：

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... */
}

我不想做的是：

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

这意味着每个获取人员的请求都会包含其所有相关实体，即使请求的API客户端不需要它们。我也不想编写每种可能的逻辑组合。

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

这将导致难以维护的代码，但是我意识到如果需要的话，我可以进行代码生成。

- Julia McGuigan

Answer 1

您可以链接IQueryable的

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

- VahidN

---

这只有在不同版本的Entity Framework中才可用吗？我收到以下错误：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?)也许还缺少扩展程序集？ - Julia McGuigan

链式编程是LINQ的标准特性。但 Include 是 EF 的扩展方法（http://msdn.microsoft.com/en-us/library/gg696450%28v=vs.103%29.aspx），需要引用 EntityFramework.dll 程序集，并添加 using System.Data.Entity;，同时去掉 AsQueryable()。 - VahidN

好的。我之前有些困惑，因为我可以在 dbcontext.SomeTable 上调用 .Include，但不能在 someQuery 上调用。在我添加了必要的导入后，它正常工作了。我已经引用了 EntityFramework.dll。 - Julia McGuigan

Answer 2

如果你用query替换u，你的第一个例子应该可以工作。

 u = u.Include("metadata");

使用

 query = query.Include("metadata");

- Aducci

---

抱歉！问题中存在拼写错误。 - Julia McGuigan

Answer 3

这里运行正常...使用EF 6日志处理程序检查SQL语句

[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; }
}

- ajd

---

不确定您的意思。我该如何检查？它正在提取一个视图。 - Julia McGuigan

我将检查EntityFramework的版本，如果有必要，尝试升级它。 - Julia McGuigan

你可以使用 msg = q.ToString() 来获取查询将要执行的 SQL 语句，或者运行 Sql Profiler。但是 EF 6 是很好的选择。 - ajd