Entity Framework stored procedure results mapping

I've looked for a similar topic to my question over the internet for the past few days. I finally resorted to asking this question myself.

在过去的几天里,我通过互联网查找了类似的问题。我终于自己提出这个问题了。

Using code-first methodology and EF 4.3.1 I created a context class, entity classes, and classes to store the stored procedure output. The context class has methods that execute certain stored procedures using SqlQuery<T>.

使用代码优先方法和EF 4.3.1,我创建了一个上下文类,实体类和类来存储存储过程输出。上下文类具有使用SqlQuery 执行某些存储过程的方法。

Example:

public IEnumerable<Results> GetData(int id)
{
   var parameters = new SqlParameter[] { new SqlParameter("@id", id) };
   var result = this.Database.SqlQuery<Result>("Exec dbo.sproc_GetData @id",    parameters);
   var data= result.ToList<Result>();

   return data;
}

As I am tracing my debug my data comes back, and data is mapped to properties with a matching name. However, in the output there is a column with a "/" in the name (example: Info/Data). Obviously I can't name a property like that so I figured I could map the output using the column attribute ([Column("Info/Data")]) :

当我跟踪调试时,我的数据又回来了,数据被映射到具有匹配名称的属性。但是,在输出中有一个名称中带有“/”的列(例如:Info / Data)。显然我不能命名这样的属性所以我想我可以使用列属性([Column(“Info / Data”)])映射输出:

[Column("Info/Data")]
public string InfoData
{
   get { return infoData; }
   set { infoData= value; }
}

I even tried using the verbatim operator ([Column(@"Info/Data")]), wrapping the text with [] ([Column("[Info/Data]")]), and I tried both ([Column(@"[Info/Data]")]). When stepping through the code I see that properties with matching column names are assigned, but properties with the column attribute are ignored and stepped over during assignment.

我甚至尝试使用逐字运算符([Column(@“Info / Data”)]),用[]([Column(“[Info / Data]”)])包装文本,我试了两下([Column( @ “[信息/数据]”)])。单步执行代码时,我会看到分配了具有匹配列名的属性,但是在分配期间会忽略具有column属性的属性。

I also tried fluent-api for every column for the entity.

我还为实体的每一列尝试了fluent-api。

    modelBuilder.ComplexType<Result>().Property(d => d.InfoData).HasColumnName("Info/Data");

but that throws the following exception:

但是会抛出以下异常:

The data reader is incompatible with the specified 'NameSpace.Result'. A member of the type, 'InfoData', does not have a corresponding column in the data reader with the same name.

数据读取器与指定的“NameSpace.Result”不兼容。类型为“InfoData”的成员在数据读取器中没有相应的具有相同名称的列。

In my project NameSpace.Result is a class (name changed for security) and InfoDatais is the property that I try to map using fluent-api (the corresponding sql column has a / in it; ex: Info/Data).

在我的项目中,NameSpace.Result是一个类(名称已更改为安全性),InfoDatais是我尝试使用fluent-api映射的属性(相应的sql列中包含/; ex:Info / Data)。

Has anyone ran into this issue?

有没有人遇到过这个问题?

If my problem isn't clear or it's been asked before please let me know.

如果我的问题不明确或者之前有人问过,请告诉我。

3 个解决方案

#1

I realise this is an old question now, but as it's been bumped up by the OPs recent answer, perhaps there is still interest in it.

我现在意识到这是一个古老的问题,但是由于OP最近的回答已经被提升,或许它仍然有兴趣。

If you're stuck with that stored proc as-is and it's returning column names incompatible with EF, seeing as you're passing in SQL to call the proc direct with SqlQuery, could you use the INSERT-EXEC method to do something like declare a table variable (with more compatible column names), INSERT-EXEC the stored proc into the table variable, then select from the table variable as your result set?

如果你按原样坚持存储过程并且它返回与EF不兼容的列名,当你传入SQL直接使用SqlQuery调用proc时,你是否可以使用INSERT-EXEC方法来执行类似声明的操作一个表变量(具有更多兼容的列名),INSERT-EXEC将存储的proc放入表变量中,然后从表变量中选择作为结果集?

It'd be long-winded SQL to pass in and so my not be a palatable solution, but as a thought exercise to see if that's a way around the issue I thought I'd offer it up.

这是一个啰嗦的SQL传入,因此我不是一个可口的解决方案,但作为一个思考练习,看看这是否解决了这个问题,我认为我会提供它。

Nice article here on this sort of issue: http://www.sommarskog.se/share_data.html - most of the methods aren't helpful as you couldn't change your stored proc (and so presumably have little to no access to the db structure or make changes to it at all?), but the INSERT-EXEC method pops out as a possible workaround without the need to change anything in the db level...

关于这类问题的好文章:http://www.sommarskog.se/share_data.html - 大多数方法没有用,因为你无法改变你的存储过程(因此可能几乎没有访问权限) db结构或对其进行更改?),但INSERT-EXEC方法弹出作为可能的解决方法而无需更改数据库级别中的任何内容...

#2

Might not be possible to do what you want. When using Raw SQL, the mapping portion of EF is bypassed.

可能无法做你想做的事。使用Raw SQL时,绕过EF的映射部分。

How to use Entity Framework to map results of a stored procedure to entity with differently named parameters

如何使用Entity Framework将存储过程的结果映射到具有不同命名参数的实体

Same thing with ExecuteStoreQuery http://social.msdn.microsoft.com/Forums/pl/adonetefx/thread/d524b005-12a4-4300-a974-1e0582de876b

与ExecuteStoreQuery相同的是http://social.msdn.microsoft.com/Forums/pl/adonetefx/thread/d524b005-12a4-4300-a974-1e0582de876b

You could use the ObjectQuery to get a list of DbDataRecord objects. Then use the Linq "Select" function to map the results to your object type. If you wrap that mapping in an extension function that takes an IEnumerable and returns a IEnumberable.

您可以使用ObjectQuery获取DbDataRecord对象的列表。然后使用Linq“选择”功能将结果映射到您的对象类型。如果将该映射包装在一个带有IEnumerable的扩展函数中并返回一个IEnumberable。

Return Custom Object <List T> from Entity framework and assign to Object Data Source

从Entity框架返回自定义对象并分配给对象数据源

#3

Going back and thinking about this problem one of the simplest (potentially not cleanest or best solutions) is to wrap the entire result set in a new procedure. Of course if you have the ability to modify the procedure without breaking anything that's your best solution. However, if you can't modify the output one solution is to use OPENROWSET (Look at Aaron's answer) to grab the procedure output into a table and select each column with a new alias that works and adheres to cleaner programming principles.

回过头来思考这个问题最简单(可能不是最干净或最好的解决方案)之一是将整个结果集包装在一个新程序中。当然,如果您能够修改程序而不破坏任何最佳解决方案。但是,如果您无法修改输出,则一个解决方案是使用OPENROWSET(查看Aaron的答案)将过程输出捕获到表中,并选择每个列,其中包含一个有效的新别名,并遵循更清晰的编程原则。

I hope this helps anyone in the future.

我希望这对未来的任何人都有帮助。

NOTE:
I've not checked if new version of EF has resolved this issue.

注意:我没有检查新版本的EF是否已解决此问题。

#1