Dynamic tables from a stored procedure using Linq to Entities

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 hav开发者_Go百科e 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.

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.

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:

var connectionString = new SqlConnectionStringBuilder
{
    DataSource = @"C:\Temp\Northwind.mdf"
};

var commandText = "select * from Customers";

using (var rows = new SqlCommandHelper(connectionString.ToString(), System.Data.CommandType.Text, commandText))
{
    foreach (dynamic row in rows)
    {
        try
        {
            Console.WriteLine(row.Fax ?? "Emtpy");
        }
        catch (IndexOutOfRangeException)
        {
            Console.WriteLine("Invalid column name");
        }
    }
}

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:

class SqlCommandHelper : IEnumerable<DynamicSqlRow>, IDisposable
{
    private SqlConnection connection;
    private SqlCommand command;

    public SqlCommandHelper(string connectionString, System.Data.CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        connection = new SqlConnection(connectionString);
        command = new SqlCommand
        {
            CommandText = commandText,
            CommandType = commandType,
            Connection = connection
        };

        command.Parameters.AddRange(parameters);
    }

    public IEnumerator<DynamicSqlRow> GetEnumerator()
    {
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return new DynamicSqlRow(reader);
            }
        }            
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }

    public void Dispose()
    {
        command.Dispose();
        connection.Dispose();
    }
}

As you can see, the magic lies within DynamicSqlRow. I thing to note is that you'll need to import the System.Dynamic namespace for DynamicSqlRow to compile.

class DynamicSqlRow : DynamicObject
{
    System.Data.IDataReader reader;

    public DynamicSqlRow(System.Data.IDataReader reader)
    {
        this.reader = reader;
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        var row = reader[binder.Name];

        result = row is DBNull ? null : row;

        return true;
    }
}

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