Mapping stored procedure results in Entity Framework

.net c# entity-framework

Question

For the last several days, I've searched the internet for a subject related to my inquiry. I had to ask myself this question at last.

Using EF 4.3.1 and code-first technique In order to save the results of the stored procedures, I developed a context class, entity classes, and classes. There are methods in the context class that run certain stored operations utilizingSqlQuery<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;
}

My data returns as I'm tracing my debug, and it's assigned to properties with the same name. The result, however, includes a column with a"/" in the name (for instance:Info/Data ). Since I obviously can't name a property like that, I decided to use the column attribute to map the result ([Column("Info/Data")] ):

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

Even attempting to use the verbatim operator[Column(@"Info/Data")] enclosing the text in[] ([Column("[Info/Data]")] I experimented with both ([Column(@"[Info/Data]")] When running the code step by step, I can see that properties with matching column names are assigned, however properties with the column attribute are ignored and skipped through.

Additionally, I experimented with fluent-api for each entity column.

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

nonetheless, that raises the following caveat:

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.

My project isNameSpace.Result (Name altered for security) is a class, andInfoDatais is the property that I'm attempting to map with fluent-api (the analogous SQL column for this has a / in it, for example: Info/Data).

Has anybody had this problem?

Please let me know if my issue is unclear or if it has already been addressed.

1
12
4/13/2012 9:58:17 PM

Accepted Answer

I am aware that this question has been asked before, however because the OP just responded to it, it has received more attention.

Given that you're using SQL to call the stored procedure directly and it's producing column names that are incompatible with EF, if you're stuck with it as isSqlQuery Could you define a table variable (with more suitable column names), INSERT-EXEC the stored proc into the table variable, and then pick from the table variable as your result set using the INSERT-EXEC method?

I thought I'd propose it as a thought experiment to see if there is a way around the problem, even though it would be cumbersome SQL to feed in and may not be a desirable solution.

This is a nice article on the subject: http://www.sommarskog.se/share_data.html - The INSERT-EXEC technique stands out as a potential solution without the need to modify anything at the db level, but the most of the ways aren't useful since you couldn't update your stored proc (and presumably have little to no access to the db structure or make changes to it at all?).

3
6/12/2014 11:49:46 AM

Popular Answer

Thinking about the issue again, one of the most straightforward solutions—though maybe not the cleanest or most effective—is to wrap the whole result set in a new method. Of course, your best option is to change the technique if you can do so without destroying anything. If you are unable to alter the output, one option is to use OPENROWSET (see Aaron's response) to take the output of the process into a table and pick each column with a functional new alias that complies with cleaner programming concepts.

I'm hoping that this will be useful in the future.

NOTE:
I haven't verified if the latest EF version has fixed this problem.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow