CODEWITHSUNDEEP
c#.netentity-framework
Samir Banjanovic
Samir Banjanovic

Reputation: 400

Entity Framework stored procedure results mapping

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.

Upvotes: 12

Views: 17109

Answers (3)

Samir Banjanovic
Samir Banjanovic

Reputation: 400

Going back and thinking about this problem one of the simplest (potentially not cleanest or best solutions) is to wrap the entire result set in a new procedure. Of course if you have the ability to modify the procedure without breaking anything that's your best solution. However, if you can't modify the output one solution is to use OPENROWSET (Look at Aaron's answer) to grab the procedure output into a table and select each column with a new alias that works and adheres to cleaner programming principles.

I hope this helps anyone in the future.

NOTE:
I've not checked if new version of EF has resolved this issue.

Upvotes: 0

PulseLab
PulseLab

Reputation: 1579

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...

Upvotes: 3

Patrick Huber
Patrick Huber

Reputation: 756

Might not be possible to do what you want. When using Raw SQL, the mapping portion of EF is bypassed.

How to use Entity Framework to map results of a stored procedure to entity with differently named parameters

Same thing with ExecuteStoreQuery http://social.msdn.microsoft.com/Forums/pl/adonetefx/thread/d524b005-12a4-4300-a974-1e0582de876b

You could use the ObjectQuery to get a list of DbDataRecord objects. Then use the Linq "Select" function to map the results to your object type. If you wrap that mapping in an extension function that takes an IEnumerable and returns a IEnumberable.

Return Custom Object <List T> from Entity framework and assign to Object Data Source

Upvotes: 0

Related Questions