使用 Linq to Entities 的存储过程中的动态表
我有关于实体框架和 Linq to Entities 的问题。我的.NET版本是4.0。我正在重构现有应用程序的数据库访问层,并且计划使用 Linq to Entities(而不是今天的 DataReaders 和 SQL 字符串)。数据库的结构无法更改。
我的问题来自一个存储过程,该过程经过简化,如下所示:
CREATE PROCEDURE getElement @tableid as int, @elementid as int AS
BEGIN
DECLARE @tablename as varchar(50)
SELECT @tablename = tablename FROM tables WHERE tableid = @tableid
EXEC('SELECT * FROM ' + @tablename + ' WHERE elementid = ' + @elementid)
END
我知道返回的行将有一个名为 elementid 的列,并且根据此值,我知道其他什么值得期待的专栏。
如今,这个问题已通过 SqlDataReader 解决,它对 elemendid 元素进行类似字典的查找。
public Element getElement(SqlDataReader dr)
{
switch((int)dr["elementid"])
{
case 1:
return getTextElement(dr);
case 2:
return getImageElement(dr);
//...
}
}
Element
是一个抽象基类。 getTextElement
返回一个 TextElement : Element
,getImageElement
返回一个 ImageElement : Element
。
如何在实体框架中对此进行建模?复杂类型似乎并不能解决这个问题,因为它似乎不支持动态属性。我还研究过 EntityObject Generator,但我在定制 T4 代码方面并不是很有经验(也许我应该学习这个问题?)。对我来说,完美的解决方案是让导入的存储过程返回一个 dynamic 类型的对象,但 Entity Framework 4 似乎不支持这一点。
I have a question about Entity Framework and Linq to Entities. My .NET version is 4.0. I'm refactoring the database access layer of an existing application, and I plan to use Linq to Entities (instead of today's DataReaders and SQL strings). The structure of the database cannot be altered.
My problem comes from a stored procedure, which, simplified, looks like the following:
CREATE PROCEDURE getElement @tableid as int, @elementid as int AS
BEGIN
DECLARE @tablename as varchar(50)
SELECT @tablename = tablename FROM tables WHERE tableid = @tableid
EXEC('SELECT * FROM ' + @tablename + ' WHERE elementid = ' + @elementid)
END
I know that the row(s) returned will have a column named elementid, and based on this value, I know what other columns to expect.
Today, this is solved with an SqlDataReader which does a dictionary-like lookup of the elemendid element.
public Element getElement(SqlDataReader dr)
{
switch((int)dr["elementid"])
{
case 1:
return getTextElement(dr);
case 2:
return getImageElement(dr);
//...
}
}
Element
is an abstract base class. getTextElement
returns a TextElement : Element
, and getImageElement
returns an ImageElement : Element
.
How do I model this in Entity Framework? Complex types does not seem to cut it, since it does not seem to support dynamic properties. I have also looked at an EntityObject Generator, but I'm not really all that experienced with customizing T4 code (maybe I ought to learn for this problem?). The perfect solution for me would be to have the imported stored procedure return an object with the dynamic type, but Entity Framework 4 does not seem to support this.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为您遇到的问题是 EF 和 Linq to Sql 设计者根据已知的表结构生成模型。您的 sproc 使用
EXEC
计算结果,因此无法分析该过程以找出结果模型的样子。我不确定可以使用 ORM 工具解决这个问题,您可能需要专门化两个存储过程,一个用于显式返回TextElement
模型,另一个用于ImageElement
模型。I think the problem you are encountering is that the EF and Linq to Sql designers generate models based on the known structure of a table. You sproc evaluates the result using
EXEC
, so the procedure cannot be analysed to figure out what the result model will look like. I'm not sure this can be solved using an ORM tool, you may need to specialise two stored procedures, one for explicitly returningTextElement
models, and one forImageElement
models.我只是想补充一下我是如何解决这个问题的。
我创建了几个模拟 Linq to Entities 行为的帮助程序类,并将它们用于我的特殊存储过程。它远非完美,甚至还不够好,但它使生成的代码看起来与 Linq to Entities 非常相似。这对我来说很重要,因为我的数据库层的其余部分将使用 Linq to Entities。
在完美的世界中,我将能够制定对 Linq to Entities 的查询,然后使用与我现在所做的有点相似的结果。
开始吧...
代码的使用方式如下:
如您所见,行的枚举看起来与我使用 Linq to Entities 而不是
SqlCommandHelper
时的情况类似。SqlCommandHelper
类的代码如下:如您所见,神奇之处在于
DynamicSqlRow
。需要注意的是,您需要导入System.Dynamic
命名空间,以便DynamicSqlRow
进行编译。我希望这段代码对其他人有用,或者它能让某人想到更好的解决方案。
对我来说有用的链接是 演练:创建和使用动态对象(来自 MSDN)。
小心
I just thought that I would add how I solved this.
I created a couple of helper classes that emulates the behaviour of Linq to Entities, and use them on my special stored procedures. It's far from perfect, or even good, but it makes the resulting code look quite similar to Linq to Entities. This is important for me as the rest of my database layer will use Linq to Entities.
In the perfect world, I would be able to formulate a query to Linq to Entities, and then use the result somewhat similar to what I'm doing right now.
Here we go...
The code is used as follows:
As you can see, the enumeration of the rows looks similar to how it would be if I had used Linq to Entities instead of
SqlCommandHelper
.The
SqlCommandHelper
class is the following code:As you can see, the magic lies within
DynamicSqlRow
. I thing to note is that you'll need to import theSystem.Dynamic
namespace forDynamicSqlRow
to compile.I hope that this code might be useful for anyone else, or that it makes someone think of a better solution.
A useful link for me was Walkthrough: Creating and Using Dynamic Objects from MSDN.
Take care