Object Mapping from stored procedure using the col

Question

I have an existing db that I am using entityframework 6 Code-First on to work with. A requirement though is that all work with the db has to be via stored procedures. So I started out using the mapping that is new in 6.0:

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

The issue is that this only supports mapping Insert, Update, and Delete sp's not the select sp's, I need to be able to us a sp to select. (I do not have access to edit any of the existing sp's)

My poco's have attributes on them specifying the column name's to use using the column attribute. Apparently though the built in mapping does not support using those unless you are doing a direct selection on the table via a dbset object.

Originally I had (which worked):

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

Then to switch it to the sp I tried (using the database sqlquery call):

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

This returned valid objects, but none of the columns marked with the Column attribute had anything in them.

Then I tried (thinking since it was against the actual dbset object I'd get the column mapping):

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

Nope, this instead gave me an error that one of the properties in the POCO object (one of the Column attribute ones) was not found in the returned recordset.

So the question is, in entity framework (or best solution outside of that) what is the best way to call a stored procedure and map the results to objects and have that mapping respect the column attribute on the properties?

I would even be willing to use an old school Table object and a SqlCommand object to fill it, if I had a fast easy way to then map the objects that respects the Column Attribute.

Answer 1

SqlQuery does not honor Column attribute. If the names of the columns of the returned result set match the names of the properties of the entity the properties should be set accordingly. Note however that SqlQuery does only minimal amount of work (for instance it does not support relationships (.Include)) so you are limiting yourself if you decide using stored procedures for queries. Enhancing SqlQuery to use ColumnName attributes is being tracked here: https://entityframework.codeplex.com/workitem/233 - feel free to upvote this codeplex item.