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

问题描述

我有一个存储过程，它使用一个情侣对表，并创建一个交叉表的结果集。为了创建交叉表的结果集，我使用它们是动态的记录基础表中的产生的CASE语句。

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.

是否有可能产生使用ADO.NET实体框架，该SP的实体？每次因为我尝试获取列信息为特定的SP，它说，选择的存储过程返回没有列。

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.

任何帮助将是AP preciated。

Any help would be appreciated.

推荐答案

我的团队中的一员最近就遇到了这样的事情，在一个存储过程是产生各种动态SQL并返回计算列，因此数据方面没有知道该怎么利用它。我还没有尝试过自己呢，但是这是他声称工作的解决方案：

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:

解决的办法是简单地放线   SET FMTONLY OFF;入PROC。   这使得数据上下文到   实际生成的返回类。   这在这种情况下，仅仅是因为   在proc是做什么，但查询   数据。

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.

完整的细节在这里：   <一href="http://tonesdotnetblog.word$p$pss.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/">http://tonesdotnetblog.word$p$pss.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/

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/

您只需要在SET FMTONLY OFF   在proc足够长的时间来产生   类。然后，您可以发表评论吧。

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

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