Object Mapping from stored procedure using the columnname attribute in EntityFramework CodeFirst

columnattribute ef-code-first entity-framework-6

Question

I'm working with an existing database and entityframework 6 Code-First. All database-related work must, however, be done using stored procedures. I therefore began by utilizing the updated 6.0 mapping:

modelBuilder.Entity<Post>().MapToStoredProcedures();

The problem is that only Insert, Update, and Delete SPs are supported; I need to be able to use an SP for selection. (I am unable to edit any of the current SPs.)

On my POCOs, there are properties that allow me to define the column names to utilize. However, it appears that employing those is not supported by the built-in mapping unless you are performing a direct selection on the table using a dbset object.

Initially, I had (and it functioned):

return (from c in DataContext.Current.AgeRanges orderby c.StartAge select c);

I then tried (using the database sqlquery command) to change it to the sp:

return DataContext.Current.Database.SqlQuery<AgeRange>("[DIV].[GetAgeRangesList]").AsQueryable();

This produced legitimate objects, but none of the columns designated by the Column attribute included any data.

Then I tried (hoping to get the column mapping since it was against the actual dbset object):

return DataContext.Current.AgeRanges.SqlQuery("[DIV].[GetAgeRangesList]").ToList().AsQueryable();

Instead, I received a message saying that one of the POCO object's properties (a Column attribute property) was missing from the recordset that was returned.

What is the best approach to call a stored procedure, transfer the results to objects, and have that mapping respect the column attribute on the properties in entity framework (or best solution outside of that)?

If there was a quick and simple technique to map the objects that respected the Column Attribute, I would even be willing to use an outdated Table object and a SqlCommand object to fill it.

1
2
12/17/2013 7:08:32 AM

Popular Answer

Column attribute is not honored by SqlQuery. If the column names in the provided result set correspond to the names of the entity's properties, the properties should be set appropriately. However, keep in mind that SqlQuery only performs a small amount of work (for example, it does not support relationships (.Include)), so if you choose to use stored procedures for queries, you are restricting yourself. Please feel free to upvote this codeplex item, which is being tracked to improve SqlQuery to include ColumnName attributes: https://entityframework.codeplex.com/workitem/233.

3
12/17/2013 2:19:51 PM


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