如何使EntityFramework为相关对象生成高效的SQL查询?

我试图找出如何使用。net EntityFramework生成可读和自然代码和高效的SQL查询语句时获取相关实体。例如，给定以下代码优先定义

public class WidgetContext : DbContext
{
    public DbSet<Widget> Widgets { get; set; }
    public DbSet<Gizmo> Gizmos { get; set; }
}
public class Widget
{
    public virtual int Id { get; set; }
    [Index]
    [MaxLength(512)]
    public virtual string Name { get; set; }
    public virtual ICollection<Gizmo> Gizmos { get; set; }
}
public class Gizmo
{
    public virtual long Id { get; set; }
    [Index]
    [MaxLength(512)]
    public virtual string Name { get; set; }
    public virtual Widget Widget { get; set; }
    public virtual int WidgetId { get; set; }
}

我希望能够编写像

这样的代码

using (var wc = new WidgetContext())
{
    var widget = wc.Widgets.First(x => x.Id == 123);
    var gizmo = widget.Gizmos.First(x => x.Name == "gizmo 99");
}

并查看沿着 行创建的SQL查询

SELECT TOP (1) * from Gizmos WHERE WidgetId = 123 AND Name = 'gizmo 99'

这样，选择正确的Gizmo的工作是由数据库执行的。这很重要，因为在我的用例中，每个Widget可能有数千个相关的Gizmos，而在一个特定的请求中，我一次只需要检索一个。不幸的是，上面的代码导致EntityFramework创建像这样的SQL

SELECT * from Gizmos WHERE WidgetId = 123

Gizmo上的匹配。然后通过扫描相关Gizmo实体的完整集合在内存中执行Name。

经过大量的实验，我已经找到了在实体框架中创建高效SQL使用的方法，但只有通过使用不太自然编写的丑陋代码。下面的例子说明了这一点。

using System.Data.Entity;
using System.Data.Entity.Core.Objects.DataClasses;
using System.Linq;
static void Main(string[] args)
{
    Database.SetInitializer(new DropCreateDatabaseAlways<WidgetContext>());
    using (var wc = new WidgetContext())
    {
        var widget = new Widget() { Name = "my widget"};
        wc.Widgets.Add(widget);
        wc.SaveChanges();
    }
    using (var wc = new WidgetContext())
    {
        var widget = wc.Widgets.First();
        for (int i = 0; i < 1000; i++)
            widget.Gizmos.Add(new Gizmo() { Name = string.Format("gizmo {0}", i) });
        wc.SaveChanges();
    }
    using (var wc = new WidgetContext())
    {
        wc.Database.Log = Console.WriteLine;
        var widget = wc.Widgets.First();
        Console.WriteLine("=====> Query 1");
        // queries all gizmos associated with the widget and then runs the 'First' query in memory. Nice code, ugly database usage
        var g1 = widget.Gizmos.First(x => x.Name == "gizmo 99");
        Console.WriteLine("=====> Query 2");
        // queries on the DB with two terms in the WHERE clause - only pulls one record, good SQL, ugly code
        var g2 = ((EntityCollection<Gizmo>) widget.Gizmos).CreateSourceQuery().First(x => x.Name == "gizmo 99");
        Console.WriteLine("=====> Query 3");
        // queries on the DB with two terms in the WHERE clause - only pulls one record, good SQL, ugly code
        var g3 = wc.Gizmos.First(x => x.Name == "gizmo 99" && x.WidgetId == widget.Id);
        Console.WriteLine("=====> Query 4");
        // queries on the DB with two terms in the WHERE clause - only pulls one record, also good SQL, ugly code
        var g4 = wc.Entry(widget).Collection(x => x.Gizmos).Query().First(x => x.Name == "gizmo 99");
    }
    Console.ReadLine();
}

查询1演示了通过实体对象的自然使用生成的"获取所有内容并过滤"方法。

上面的查询2,3和4都生成了我认为是高效的SQL查询-一个返回单行并且在WHERE子句中有两个术语的查询，但是它们都涉及非常生硬的c#代码。

有没有人有一个解决方案，将允许编写自然的c#代码，并在这种情况下生成高效的SQL利用率?

我应该注意到，我已经尝试用我的Widget对象中的EntityCollection替换iccollection，以允许从上面的Query 2代码中删除强制转换。不幸的是，这会导致EntityException告诉我

对象无法添加到EntityCollection或EntityReference。附加到ObjectContext的对象则不能将被添加到不存在的EntityCollection或EntityReference中与源对象相关联。

当我试图检索任何相关对象时。

 public IQueryable<Gizmo> GizmosQuery
 {
     get
     {
         var relationshipManager = ((IEntityWithRelationships)this).RelationshipManager;
         return (IQueryable<Gizmo>) relationshipManager.GetAllRelatedEnds().First( x => x is EntityCollection<Gizmo>).CreateSourceQuery();
     }
 }

var g1 = widget.GizmosQuery.First(x => x.Name == "gizmo 99");