从具有动态sql的存储过程创建实体 [英] Creating entities from stored procedures which have dynamic sql

问题描述

我有一个存储过程，它使用几个表，并创建交叉表结果集。为了创建交叉表结果集，我使用基于表中记录动态生成的CASE语句。

是否可以从此SP生成实体使用ADO.NET实体框架？ Cuz每次尝试获取特定SP的列信息时，它表示所选存储过程不返回任何列。

任何帮助将不胜感激。

解决方案

团队最近遇到这样的情况，存储过程生成各种动态SQL并返回计算列，因此数据上下文不知道该做什么。我还没有尝试过，但这是他所声称的解决方案：

解决方案只是把行
SET FMTONLY OFF;进入proc。
这允许数据上下文
实际生成返回类。
这个工作在这种情况下，只是因为
，proc只是在查询
数据。

全部详细信息：
& -return-a-table /rel =noreferrer> http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an- int-when-it-should-return-a-table /

您只需要
中的SET FMTONLY OFF足以生成
类。然后，您可以发表评论。

I have a stored procedure which uses a couple of tables and creates a cross-tab result set. For creating the cross-tab result set I am using CASE statements which are dynamically generated on basis of records in a table.

Is it possible to generate an entity from this SP using ADO.NET Entity framework? Cuz each time I try Get Column Information for the particular SP, it says that The selected stored procedure returns no columns.

Any help would be appreciated.

解决方案

A member of my team recently encountered something like this, where a stored procedure was generating all kinds of dynamic SQL and returning calculated columns so the data context didn't know what to make of it. I haven't tried it myself yet, but this was the solution he claimed worked:

The solution is simply to put the line "SET FMTONLY OFF;" into the proc. This allows the Data Context to actually generate the return class. This works in this case, only because the proc is doing nothing but querying data.

Full details here: http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/

You only need the "SET FMTONLY OFF" in the proc long enough to generate the class. You can then comment it out.

这篇关于从具有动态sql的存储过程创建实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！