Dynamic tables from a stored procedure using Linq to Entities

Question

Hi,

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.

Answer 1

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 returning TextElement models, and one for ImageElement models.