为什么EF 4.1不支持复杂的查询以及linq-to-sql？ [英] Why does EF 4.1 not support complex queries as well as linq-to-sql?

问题描述

我正在将现有数据库中的内部Web应用程序从Linq-To-Sql转换为EF CodeFirst。我最近一直对Linq-To-Sql的限制感到恼火，更新一个非常交织在一起的数据库表后，不得不更新edmx，最终让我感到沮丧，我不得不切换到EF。

$ b $不过，我遇到了几种情况，使用Linq-to-Sql的linq比最新的Entity Framework更强大，我想知道有没有人知道它的推理？大多数似乎是处理转型。例如，以下查询在L2S中工作，但不在EF中：

  var client =（from _ in _context.Clients 
其中c.id == id 
选择ClientViewModel.ConvertFromEntity（c））。 
  

在L2S中，这可以从数据库中正确检索客户端，并将其转换为 ClientViewModel 类型，但在EF中，这个例外表示Linq to Entities不会识别方法（这在我写的时候是有道理的。

为了让这个工作在EF中，我必须在 First（）调用之后移动选择。 p>

另一个例子是我的查询来检索客户端列表。在我的查询中，我将其转换成一个匿名结构，以转换为 JSON

  var clients =（from c in _context.Clients 
 orderby c.name ascending 
选择新
 {
 id = c.id，
 name = c.name，
 versionString = Utils.GetVersionString（c.ProdVersion），
 versionName = c 。 ProdVersion.name，
 date = c.prod_deploy_date.ToString（）
}）
 .ToList（）; 
  

不仅我的 Utils.GetVersionString（）方法导致EF中不受支持的方法异常， c.prod_deploy_date.ToString（）也导致一个，它是一个简单的 DateTime 。像以前一样，为了修复它，我不得不在 ToList（）之后进行选择转换。

---

编辑：另一个我刚刚遇到的情况是，EF无法处理那些比较L2S的实体的子句没有问题。例如查询

  context.TfsWorkItemTags.Where（x => x.TfsWorkItem == TfsWorkItemEntity）.ToList（）
  

抛出异常，而我必须执行

  context.TfsWorkItemTags.Where（x => x.TfsWorkItem.id == tfsWorkItemEntity.id）.ToList（）
  

---

编辑2 ：我想添加另一个我发现的问题。显然你不能在EF Linq查询中使用数组，这可能比任何事情都更糟。所以例如，现在我将一个表示一个版本的实体转换成一个 int [4] 并尝试查询它。在Linq-to-Sql中，我使用以下查询：

  return context.ReleaseVersions.Where（x => x.major_version == ver [0]&& x.minor_version == ver [1] 
&& x.build_version == ver [2]&& x.revision_version == ver [3]） 
 .Count（）> 0; 
  

以下异常失败：

  LINQ to Entities不支持LINQ表达式节点类型ArrayIndex。 
  

---

编辑3 我发现了EF的不良Linq实现的另一个实例。以下是在L2S中工作但不在EF 4.1中的查询：

  DateTime curDate = DateTime.Now.Date; 
 var reqs = _context.TestRequests.Where（x => DateTime.Now>（curDate + x.scheduled_time.Value））ToList（）; 
  

这将抛出一个 ArgumentException code> DbArithmeticExpression参数必须具有数字通用类型。

---

为什么它似乎降级在EF中的Linq查询的能力比L2S中的查询能力

解决方案

编辑（9/2/2012） strong>更新以反映.NET 4.5并添加了更多缺少的功能

这不是答案 - 这不可能是因为唯一有资格的人可以回答你的问题ADO.NET团队的产品经理。

如果您检查旧数据集的功能集，那么linq-to-sql，然后是EF，您会发现关键功能被删除较新的API，因为较新的API在短时间内开发出来，因此能够提供新颖的功能。

只列出DataSet中可用的一些关键功能，但在以后的API中不可用：

• 批处理


• 唯一键

Linq-to-Sql中提供的功能，在EF中不支持（可能列表不完全正确，长时间没有使用L2S）：

• 记录数据库活动


• 自第一个版本（EF）起，延迟加载属性


• 左外连接（ DefaultIfEmpty ）自从EFv4起）


• 全球渴望加载定义


• AssociateWith - 例如急需加载数据


• 自第一个版本以来的代码


• IMultipleResults 支持存储过程返回多个结果集（EF在.NET 4.5中，但没有设计师支持此功能）


• 支持表值函数（EF在.NET 4.5中具有此功能）


• 还有一些其他

现在我们可以列出EF ObjectContext API（EFv4）中可用的功能，并在DbContext API（EFv4.1）中缺少：

• 映射存储过程


• 条件映射


• 映射数据库函数


• 定义查询，QueryViews，模型定义函数


• 除非您转换 DbContext 返回 ObjectContext


• 除非您转换 DbContext 返回 ObjectContext


• 使用 MergeOption.OverwriteChanges MergeOption.PreserveChanges 是不可能的，除非您将 DbContext 转换回 ObjectContext


• 还有一些其他

我个人对此的感觉只是很大的悲伤。缺少核心功能并删除了以前API中存在的功能，因为ADO.NET团队显然没有足够的资源来重新实现它们 - 这在许多情况下使迁移路径几乎不可能。整个情况更糟，因为缺少功能或迁移障碍并不直接列出（甚至ADO.NET团队甚至不知道他们，直到有人报告他们）。

因为我认为DbContext API的全部想法是管理失败。目前，ADO.NET团队必须保持两个API - DbContext 不成熟以替代 ObjectContext ，它实际上可以'因为它只是一个包装器，因为 ObjectContext 不能死。可用于EF开发的资源大概减半。

有更多的问题相关。一旦我们离开ADO.NET团队，从MS产品套件的角度来看这个问题，我们将会看到如此多的差异，我有时甚至怀疑是否有任何全球战略。

简单地说，EF的提供者以不同的方式工作，在Linq-to-sql中工作的查询不必与EF一起工作。

I am in the process of converting our internal web application from Linq-To-Sql to EF CodeFirst from an existing database. I have been getting annoyed with Linq-To-Sql's limitations more and more lately, and having to update the edmx after updating a very intertwined database table finally frustrated me enough to switch to EF.

However, I am encountering several situations where using linq with Linq-To-Sql is more powerful than the latest Entity Framework, and I am wondering if anyone knows the reasoning for it? Most of this seems to deal with transformations. For example, the following query works in L2S but not in EF:

        var client = (from c in _context.Clients
                      where c.id == id
                      select ClientViewModel.ConvertFromEntity(c)).First();

In L2S, this correctly retrieves a client from the database and converts it into a ClientViewModel type, but in EF this exceptions saying that Linq to Entities does not recognize the method (which makes sense as I wrote it.

In order to get this working in EF I have to move the select to after the First() call.

Another example is my query to retrieve a list of clients. In my query I transform it into an anonymous structure to be converted into JSON:

        var clients = (from c in _context.Clients
                       orderby c.name ascending
                       select new
                       {
                           id = c.id,
                           name = c.name,
                           versionString = Utils.GetVersionString(c.ProdVersion),
                           versionName = c.ProdVersion.name,
                           date = c.prod_deploy_date.ToString()
                       })
                       .ToList();

Not only does my Utils.GetVersionString() method cause an unsupported method exception in EF, the c.prod_deploy_date.ToString() causes one too and it's a simple DateTime. Like previously, in order to fix it I had to do my select transformation after ToList().

---

Edit: Another case I just came across is that EF cannot handle where clauses that compare entities where as L2S has no issues for with it. For example the query

context.TfsWorkItemTags.Where(x => x.TfsWorkItem == TfsWorkItemEntity).ToList()

throws an exception and instead I have to do

context.TfsWorkItemTags.Where(x => x.TfsWorkItem.id == tfsWorkItemEntity.id).ToList() 

---

Edit 2: I wanted to add another issue that I found. Apparently you can't use arrays in EF Linq queries, and this probably annoys me more than anything. So for example, right now I convert an entity that denotes a version into an int[4] and try to query on it. In Linq-to-Sql I used the following query:

return context.ReleaseVersions.Where(x => x.major_version == ver[0] && x.minor_version == ver[1]
                                          && x.build_version == ver[2] && x.revision_version == ver[3])
                              .Count() > 0;

This fails with the following exception:

The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities.

---

Edit 3: I found another instance of EF's bad Linq implementation. The following is a query that works in L2S but doesn't in EF 4.1:

        DateTime curDate = DateTime.Now.Date;
        var reqs = _context.TestRequests.Where(x => DateTime.Now > (curDate + x.scheduled_time.Value)).ToList();

This throws an ArgumentException with the message DbArithmeticExpression arguments must have a numeric common type.

---

Why does it seem like they downgraded the ability for Linq queries in EF than in L2S?

解决方案

Edit (9/2/2012): Updated to reflect .NET 4.5 and added few more missing features

This is not the answer - it cannot be because the only qualified person who can answer your question is probably a product manager from ADO.NET team.

If you check feature set of old datasets then linq-to-sql and then EF you will find that critical features are removed in newer APIs because newer APIs are developed in much shorter times with big effort to deliver new fancy features.

Just list of some critical features available in DataSets but not available in later APIs:

• Batch processing
• Unique keys

Features available in Linq-to-Sql but not supported in EF (perhaps the list is not fully correct, I haven't used L2S for a long time):

• Logging database activity
• Lazy loaded properties
• Left outer join (DefaultIfEmpty) since the first version (EF has it since EFv4)
• Global eager loading definitions
• AssociateWith - for example conditions for eager loaded data
• Code first since the first version
• IMultipleResults supporting stored procedures returning multiple result sets (EF has it in .NET 4.5 but there is no designer support for this feature)
• Support for table valued functions (EF has this in .NET 4.5)
• And some others

Now we can list features available in EF ObjectContext API (EFv4) and missing in DbContext API (EFv4.1):

• Mapping stored procedures
• Conditional mapping
• Mapping database functions
• Defining queries, QueryViews, Model defined functions
• ESQL is not available unless you convert DbContext back to ObjectContext
• Manipulating state of independent relationships is not possible unless you convert DbContext back to ObjectContext
• Using MergeOption.OverwriteChanges and MergeOption.PreserveChanges is not possible unless you convert DbContext back to ObjectContext
• And some others

My personal feeling about this is only big sadness. Core features are missing and features existing in previous APIs are removed because ADO.NET team obviously doesn't have enough resources to reimplement them - this makes migration path in many cases almost impossible. The whole situation is even worse because missing features or migration obstacles are not directly listed (I'm afraid even ADO.NET team doesn't know about them until somebody reports them).

Because of that I think that whole idea of DbContext API was management failure. At the moment ADO.NET team must maintain two APIs - DbContext is not mature to replace ObjectContext and it actually can't because it is just a wrapper and because of that ObjectContext cannot die. Resources available for EF development was most probably halved.

There are more problems related. Once we leave ADO.NET team and look on the problem from the perspective of MS product suite we will see so many discrepancies that I sometimes even wonder if there is any global strategy.

Simply live with the fact that EF's provider works in different way and queries which worked in Linq-to-sql don't have to work with EF.

这篇关于为什么EF 4.1不支持复杂的查询以及linq-to-sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！