实体框架 - 使用存储过程强烈加载对象图 [英] Entity Framework - Eagerly load object graph using stored procedures

问题描述

背景

我正在将项目中的LINQ-to-SQL代码更改为Entity Framework。大部分的改变比较简单，但是我遇到了一个相当大的问题。使用LINQ to SQL，我可以使用如下所示的存储过程加载整个对象图（对于B型）：

  ViewModel.Model = MyDbContext.usp_ModelA_GetByID（AId）.Single（）; 
列表< ModelB>详细信息= 
（从MyDbContext.usp_ModelB_GetByID（BId）中的b 
在MyDbContext.usp_ModelC_GetAll（）中加入c 
 on b.CId等于c.CId 
选择新的ModelB（）
 {
 BId = b.BId，
 CId = b.CId，
 C = c 
}）ToList（）; 
 ViewModel.Model.ModelBs.AddRange（Details）; 
  

但是，将代码转换为EF后，在访问ViewModel.Model.ModelBs的行上，我得到错误EntityCommandExecutionException与内部异常解释为对象ModelBTable拒绝了SELECT权限。显然，即使我已经从数据库中加载了它们，EF试图为ModelA提取ModelB。虽然我不完全明白为什么它试图加载实体，即使我添加了它们，我只能假设，因为它没有加载它们自己，它不相信他们是完全加载，可能查看所有的对象我加载到它作为新。

为了绕过EF尝试获取对象本身，我决定将我的代码更改为：

  ViewModel.Model = MyDbContext.usp_ModelA_GetByID（AId）.Single（）; 
列表< ModelB>详细信息= 
（从MyDbContext.usp_ModelB_GetByID（BId）中的b 
在MyDbContext.usp_ModelC_GetAll（）中加入c 
 on b.CId等于c.CId 
选择新的ModelB（）
 {
 BId = b.BId，
 CId = c.CId，
 C = c 
}）ToList（）; 
 ViewModel.Model.ModelBs = new EntityCollection< ModelB>（）; 
 foreach（DetailsB Details in Details）
 {
 ViewModel.Model.ModelBs.Attach（detail）; 
} 
  

进行此更改后，我现在遇到错误InvalidOperationException EntityCollection的消息无法初始化，因为EntityCollection所属的对象的关系管理器已经附加到ObjectContext。只有在对象图反序列化期间才应调用InitializeRelatedCollection方法来初始化一个新的EntityCollection。 / p>

这是令人困惑的，因为我使用相同的上下文来加载所有的实体，所以我不确定为什么不允许我将它们组合在一起。我可以在其他ORM中做到这一点，没有问题。

在研究了这个错误后，我决定尝试一种方法，我希望会欺骗EF考虑整个对象图形由相同的上下文加载，所以我重写了我的代码：

  ViewModel.Model = 
在MyDbContext.usp_ModelA_GetByID（AId）
选择新的A（）
 {
 AId = a.AId，
 ModelBs =（从MyDbContext.usp_ModelB_GetByID（BId）中的b 
加入c在MyDbContext.usp_ModelC_GetAll（）
在b.CId等于c.CId 
选择新的ModelB（）
 {
 BId = b.BId，
 CId = b.CId，
 C = c 
}）ToEntityCollection（）
}）。 
  

ToEntityCollection是我创建的扩展方法，如下所示：

  public static EntityCollection< TEntity> ToEntityCollection< TEntity>（
 this IEnumerable< TEntity> source）其中TEntity：class，IEntityWithRelationships 
 {
 EntityCollection< TEntity> set = new EntityCollection< TEntity>（）; 
 foreach（来源中的TEntity实体）
 {
 set.Attach（entity）; 
} 
 return set; 
} 
  

现在，我收到错误InvalidOperationException，消息为请求操作当相关End的所有者为null时，不允许相关的对象使用默认构造函数创建的对象只能在序列化期间用作容器。。

研究这些错误，我仍然无法找到与我的问题有关的解决方案。

问题

所以，我的问题是：当每个对象使用Entity Framework 4有自己的存储过程时，如何加载整个对象图？

更新

所以，根据迄今为止的答案，我觉得我需要在这里加入以下注意事项：

1. 我不是在寻找一个使用单个存储过程加载整个对象图的答案。我正在寻找一种使用每个实体获取存储过程来加载对象图的方法。我意识到，使用单个存储过程加载对象图可能理论上表现得更好，但是在这个时候，我对代码库的更小的更改更感兴趣，特别是关于数据库的结构方式。




2. 如果您的解决方案需要直接编辑edmx，那将不是一个可以接受的答案。由于这是一个自动生成的文件，因此直接编辑edmx基本上意味着，通过设计人员的任何修改，需要重新完成相同的更改。

更新2

所以，经过一番审议，我想出了一个工作。我所做的是将ViewModel更改为具有使用存储过程连接提取数据的List ModelBs属性，在我看来，我只是将此属性设置为数据源。这绝对不是我认为是最佳解决方案，因为现在我的ViewModel的作用更像是ModelModel，而且我不能再遍历我的ModelA类型来获取ModelB的列表，但它可以工作！我仍然不明白为什么我可以做：

 （从B在MyDbContext.usp_ModelB_GetByID（BId）
加入c in MyDbContext.usp_ModelC_GetAll（）
 on b.CId等于c.CId 
选择新的ModelB（）
 {
 BId = b.BId，
 CId = b .CId，
 C = c // <------设置导航属性，EF计算出它属于
}）。ToList（）; 
  

但我不能做：

 （从MyDbContext.usp_ModelA_GetByID（AId）
中选择一个新的ModelA（）
 {
 AId = a.AId，
 ModelBs = MyDbContext.usp_ModelB_GetByID（BId）.ToEntityCollection（）//< ----当导航属性是集合时，不允许我设置导航属性
}）。 
  

解决方案

好的，经过进一步审议，一个解决方案，适用于我想要的。由于我在一个Web环境中，并且不需要懒惰地加载对象，所以将整个DbContext的EnableLazyLoading转换为false。然后，使用称为魔法关系修复的EF功能，我可以执行以下操作：

  ViewModel.Model = MyDbContext.usp_ModelA_GetByID（AID）。单（）; 
 var Details = 
（从MyDbContext.usp_ModelB_GetByID（BId）中的b 
在MyDbContext.usp_ModelC_GetAll（）中加入c 
 on b.CId等于c.CId 
 select新的ModelB（）
 {
 BId = b.BId，
 CId = b.CId，
 C = c 
}）ToList（）; 
 // ToList（）执行proc并将这些细节投入到
 //图中，从来没有尝试从数据库中选择，因为LazyLoadingEnabled是
 // false。然后，神奇的关系修正使我能够遍历我的对象图
 //使用ViewModel.Model.ModelBs，它返回加载到与我的ModelA相关的图形
 //中的所有ModelB。 
  

Background

I am changing my LINQ-to-SQL code in my project to Entity Framework. Most of the change over was relatively simple, however, I have run into a fairly major issue. With LINQ-to-SQL, I was able to load an entire object graph (for Model B) using stored procedures like so:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
       on b.CId equals c.CId
    select new ModelB()
    {
        BId = b.BId,
        CId = b.CId,
        C = c
    }).ToList();
ViewModel.Model.ModelBs.AddRange(Details);

However, after converting this code to EF, on the line where ViewModel.Model.ModelBs is accessed, I get the error "EntityCommandExecutionException" with the inner exception explaining that "The SELECT permission was denied on the object 'ModelBTable'." Obviously, EF is attempting to fetch the ModelBs for the ModelA even though I have already loaded them from the database. While I don't fully understand why it's trying to load the entities even though I have added them, I can only assume that because it didn't load them itself, it doesn't believe they are fully loaded and probably views all of the objects I loaded into it as "New".

In an effort to bypass EF attempting to fetch the objects itself, I decided to change my code to:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
        on b.CId equals c.CId
     select new ModelB()
     {
        BId = b.BId,
        CId = c.CId,
        C = c
     }).ToList();
ViewModel.Model.ModelBs = new EntityCollection<ModelB>();
foreach (ModelB detail in Details)
{
    ViewModel.Model.ModelBs.Attach(detail);
}

After making this change, I now run into the error "InvalidOperationException" with a message of "The EntityCollection could not be initialized because the relationship manager for the object to which the EntityCollection belongs is already attached to an ObjectContext. The InitializeRelatedCollection method should only be called to initialize a new EntityCollection during deserialization of an object graph.".

This is confusing enough because I am using the same context to load all of the entities so I'm unsure as to why it won't allow me to combine them together. I am able to do this in other ORMs without issue.

After researching this error, I decided to attempt an approach that I hoped would trick EF into thinking that the entire object graph was loaded by the same context so I rewrote my code to be:

ViewModel.Model = 
    (from a in MyDbContext.usp_ModelA_GetByID(AId)
    select new A()
    {
        AId = a.AId,
        ModelBs = (from b in MyDbContext.usp_ModelB_GetByID(BId)
                  join c in MyDbContext.usp_ModelC_GetAll()
                      on b.CId equals c.CId
                  select new ModelB()
                  {
                      BId = b.BId,
                      CId = b.CId,
                      C = c
                  }).ToEntityCollection()
    }).Single();

with ToEntityCollection being an extension method I created like so:

public static EntityCollection<TEntity> ToEntityCollection<TEntity>(
     this IEnumerable<TEntity> source) where TEntity : class, IEntityWithRelationships
{
    EntityCollection<TEntity> set = new EntityCollection<TEntity>();
    foreach (TEntity entity in source)
    {
        set.Attach(entity);
    }
    return set;
}

Now, I get the error "InvalidOperationException" with a message of "Requested operation is not allowed when the owner of this RelatedEnd is null. RelatedEnd objects that were created with the default constructor should only be used as a container during serialization.".

After extensively researching each of these errors, I was still unable to find a solution pertaining to my problem.

Question

So, after all of that, my question is: How do I load an entire object graph when each object has its own stored procedure using Entity Framework 4?

Update

So, based on the answers so far, I feel I need to include the following caveats here:

1. I am not looking for an answer that uses a single stored procedure to load an entire object graph. I am looking for a way to load an object graph using a get stored procedure per entity. I realize that loading the object graph using a single stored procedure could, theoretically perform much better, but at this time, I am more interested in smaller changes to the code base especially with regards to the way the database is structured.

2. If your solution requires editing the edmx directly, it will not be an acceptable answer. Since this is an auto-generated file, editing the edmx directly essentially means that those same changes would need to be re-done upon any modification through the designer.

Update 2

So, after some deliberation, I came up with a work around. What I did was change my ViewModel to have a List ModelBs property that pulls the data using the stored procedure joins and in my view, I am just setting this property as the datasource. This is definitely not what I would consider to be an optimal solution because now my ViewModel is acting more like the Model than a ViewModel and I can no longer traverse my ModelA type to get the list of ModelBs, but it works! I still don't understand why I can do:

(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
    on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c //<------Setting a navigation property and EF figures out that it belongs
}).ToList();

but I can't do:

(from a in MyDbContext.usp_ModelA_GetByID(AId)
select new ModelA()
{
    AId = a.AId,
    ModelBs = MyDbContext.usp_ModelB_GetByID(BId).ToEntityCollection() //<----Won't let me set the navigation property when the navigation property is a collection.
}).Single();

解决方案

Okay, so after even further deliberation, I figured out a solution that works for what I am wanting. Since I am in a web environment and have no need to lazily load objects, I turned EnableLazyLoading to false for the entire DbContext. Then, using an EF feature called the magical relationship fix-up, I am able to do the following:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
var Details = 
(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
   on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c
}).ToList();  
//ToList() executes the proc and projects the plate details into the object 
//graph which never tries to select from the database because LazyLoadingEnabled is
//false.  Then, the magical relationship fix-up allows me to traverse my object graph
//using ViewModel.Model.ModelBs which returns all of the ModelBs loaded into the graph
//that are related to my ModelA.

这篇关于实体框架 - 使用存储过程强烈加载对象图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！