实体框架 - 使用存储过程获取多个表中的记录 [英] Entity Framework - get records in multiple tables using stored procedure

问题描述

我正在努力掌握实体框架，并且要求从世界各地的距离订购结果。我已经决定使用存储的以前的建议我已经成功完成了一个观点的程序。但是我需要返回多个表，我明白我不能直接使用实体框架上的存储过程。如果这是不正确的，如果有人可以告诉我如何做到这一点，我将不胜感激。

无论如何，我已经定义了一个简单的sp（ SELECT id FROM table ），然后要执行一个linq查询要加入我的模型中的等效对象，如下所示：

  var sp = db.StoredProcedure（）; 
 
 var ret = from x in db.X 
在x上加入y在x.ID等于y.ID 
 select x; 
  

但是，当我执行此操作时，会得到以下查询引起的异常：

无法创建一个类型为System.Collections.Generic.IEnumerable'1的常量值。在此上下文中仅支持原始类型（例如Int32，String，Guid）。

为什么会发生这种情况？这是正确的做法吗？ （请注意，我的最后一个sp将会更复杂，我将从' ret 中的选择返回多个类'）

解决方案

使用EF扩展

存储过程真的很糟糕支持EF。即使他们返回实体结果，它们也不提供任何名称映射，所以您必须自己重命名存储过程中的列。

但是。有一个名为实体框架扩展程序的项目可以使存储过程成为各种不同的场景。 / p>

使用EF扩展可以使用任何您想要的存储过程：

• 您可以在自定义实现器中执行列重新映射（因此您的存储过程返回与数据库中相同的列，而不需要将列重命名为实体属性名称）


• 可以返回多个结果


• 你可以使用标量存储过程，甚至不返回任何东西的程序


• 可以从存储过程中消耗结果，每个行返回多个实体（当两个之间具有1：1关系时）


• 还可以使用输出参数如果您创建一个执行分页的存储过程（返回一个recover的子集），那么很棒rds作为实体并返回总计数的参数）


• 等。

你可以做准备很多东西我们在一些项目中使用了EF Extensions，取得了很大的成功。我们为我们的存储过程返回的实体编写了自己的实体（基本上是一个lamba表达式）。然后我们实现了他们的结果。

我不认为EF4将支持存储过程到这个级别，所以熟悉EF扩展永远是有价值的。 >

I am trying to get to grips with the Entity framework, and have a requirement to order results by distance from a point on the globe. I have decided on previous advice to do this using a stored procedure which I have successfully done populating a view. However I need to return multiple tables, which I understand I cannot do directly using stored Procedures on the Entity Framework. If this is not correct, I would be grateful if someone could advise how I might do this.

Anyway I therefore have defined a simple sp (SELECT id FROM table) and then wanted to perform a linq query to join this with the equivalent object in my model as follows:

var sp = db.StoredProcedure();

var ret = from x in db.X
          join y in sp on x.ID equals y.ID
          select x;

However when I perform this I get the following exception resulting from the query:

"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable'1'.Only primitive types('suchas Int32, String, Guid') are supported in this context."

Why is this happening? Is this the right approach? (Note that my final sp will be more complex, and I will be returning multiple classes from the select in 'ret')

解决方案

Use EF Extensions

Stored procedures are really badly supported in EF. Even if they return entity results, they don't provide any name mappings, so you have to rename columns in stored procedures yourself.

But. There's project called Entity Framework Extensions that will make all kinds of different scenarios with stored procedures possible.

Using EF extensions you can use stored procedures in any way you want:

• you can do column remappings in your custom materializer (so your stored procedure returns same columns as they are in the DB without the need to rename columns to entity property names)
• you can return multiple result sets (great for 1:* and : relations)
• you can use scalar stored procedures or even procedures that don't return anything
• you can consume results from a stored procedure that returns multiple entities per row (when having 1:1 relation between two of them)
• you can also use output parameters which is great if you create a stored procedure that does paging (returns a subset of records as entities and returns out parameters with total count)
• etc.

You can do preety much anything. We've used EF Extensions with much success on a some project. We wrote our own materializers (basically a lamba expression) for entities that our stored procedures returned. And then we materialized their results.

I don't think EF4 will support stored procedures to this level anyway, so getting acquainted to EF Extensions is always valuable.

这篇关于实体框架 - 使用存储过程获取多个表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！