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

问题描述

我想获得与实体框架交手，并没有要求从地球上的一个点按距离的效果。我已经决定先前建议做到这一点使用存储过程我已经成功地做了填充视图。不过，我需要返回多个表，这点我明白，我不能用在实体框架的存储过程，直接做的。如果这是不正确的，我将不胜感激，如果有人可以建议我怎么可能做到这一点。

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.

反正所以我定义了一个简单的SP（ SELECT ID FROM表），然后想执行LINQ查询在我的模型相当于对象加入到这个如下：

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:

无法创建类型的恒定值System.Collections.Generic.IEnumerable'1'.Only原始类型（'suchas的Int32，字符串的Guid'）在这方面的支持。

"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."

这是怎么回事？这是正确的做法？ （请注意，我最后的SP将更加复杂，我将在返回多个类的选择在 RET '）

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')

推荐答案

存储过程是实在太差了支持EF。即使他们返回实体结果，他们不提供任何名称映射，所以你必须重新命名在存储过程中自己列。

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.

使用EF扩展，你可以在任何你想要的方式使用存储过程：

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

• 您可以做列重映射在您的自定义materializer（所以你的存储过程返回相同的列，因为它们是在数据库，而不需要将列重命名为实体属性名称）


• 您可以返回多个结果集（伟大的1：*和的 的关系）


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


• 您可以消耗从返回每行的多个实体的存储过程的结果（含1时：他们两人之间1关系）


• 您还可以使用输出参数，这是伟大如果你创建一个存储过程，做分页（返回记录作为实体的一个子集，并返回输出参数与总数）


• 等

• 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.

您可以做preety任何东西。我们使用EF扩展上一个项目的一些很成功。我们写我们自己的materializers（基本上是兰巴表达式）为我们的存储过程返回的实体。然后我们兑现他们的结果。

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.

我不认为EF4支持存储过程，以这个水平，无论如何，因此结识到EF扩展总是有价值的。

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

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