Entity Framework stored procedure results mapping

Question

I've looked for a similar topic to my question over the internet for the past few days. I finally resorted to asking this question myself.

Using code-first methodology and EF 4.3.1 I created a context class, entity classes, and classes to store the stored procedure output. The context class has methods that execute certain stored procedures using SqlQuery<T>.

Example:

public IEnumerable<Results> GetData(int id)
{
   var parameters = new SqlParameter[] { new SqlParameter("@id", id) };
   var result = this.Database.SqlQuery<Result>("Exec dbo.sproc_GetData @id",    parameters);
   var data= result.ToList<Result>();

   return data;
}

As I am tracing my debug my data comes back, and data is mapped to properties with a matching name. However, in the output there is a column with a "/" in the name (example: Info/Data). Obviously I can't name a property like that so I figured I could map the output using the column attribute ([Column("Info/Data")]) :

[Column("Info/Data")]
public string InfoData
{
   get { return infoData; }
   set { infoData= value; }
}

I even tried using the verbatim operator ([Column(@"Info/Data")]), wrapping the text with [] ([Column("[Info/Data]")]), and I tried both ([Column(@"[Info/Data]")]). When stepping through the code I see that properties with matching column names are assigned, but properties with the column attribute are ignored and stepped over during assignment.

I also tried fluent-api for every column for the entity.

    modelBuilder.ComplexType<Result>().Property(d => d.InfoData).HasColumnName("Info/Data");

but that throws the following exception:

The data reader is incompatible with the specified 'NameSpace.Result'. A member of the type, 'InfoData', does not have a corresponding column in the data reader with the same name.

In my project NameSpace.Result is a class (name changed for security) and InfoDatais is the property that I try to map using fluent-api (the corresponding sql column has a / in it; ex: Info/Data).

Has anyone ran into this issue?

If my problem isn't clear or it's been asked before please let me know.

Answer 1

I realise this is an old question now, but as it's been bumped up by the OPs recent answer, perhaps there is still interest in it.

If you're stuck with that stored proc as-is and it's returning column names incompatible with EF, seeing as you're passing in SQL to call the proc direct with SqlQuery, could you use the INSERT-EXEC method to do something like declare a table variable (with more compatible column names), INSERT-EXEC the stored proc into the table variable, then select from the table variable as your result set?

It'd be long-winded SQL to pass in and so my not be a palatable solution, but as a thought exercise to see if that's a way around the issue I thought I'd offer it up.

Nice article here on this sort of issue: http://www.sommarskog.se/share_data.html - most of the methods aren't helpful as you couldn't change your stored proc (and so presumably have little to no access to the db structure or make changes to it at all?), but the INSERT-EXEC method pops out as a possible workaround without the need to change anything in the db level...