使用EntityFramework CodeFirst中的columnname属性从存储过程进行对象映射 [英] Object Mapping from stored procedure using the columnname attribute in EntityFramework CodeFirst

问题描述

我有一个现有数据库，正在使用EntityFramework 6 Code-First进行处理.但是，一个要求是所有与数据库的工作都必须通过存储过程进行.所以我开始使用6.0中的新映射:

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();

问题是，这仅支持映射插入，更新和删除sp，而不是选择sp，我需要能够让我们选择一个sp. (我无权编辑任何现有的sp)

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)

我的poco上有一些属性，这些属性使用column属性指定要使用的列名称.显然，尽管内置映射不支持使用这些映射，除非您通过dbset对象在表上进行直接选择.

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.

最初我(工作了)

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

然后将其切换到我尝试过的sp(使用数据库sqlquery调用):

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

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

这返回了有效的对象，但是标有Column属性的列中没有任何对象.

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

然后，我尝试了一下(考虑到它与实际的dbset对象相反，所以我会获得列映射):

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();

不是，这给了我一个错误，即在返回的记录集中找不到POCO对象中的一个属性(其中一个Column属性的属性).

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?

如果我有一种快速简便的方法来映射遵循列属性的对象，我什至愿意使用旧式Table对象和SqlCommand对象来填充它.

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.

推荐答案

SqlQuery不支持Column属性.如果返回结果集的列名称与实体的属性名称匹配，则应相应地设置属性.但是请注意，SqlQuery只做很少的工作(例如，它不支持关系(.Include))，因此，如果您决定使用存储过程进行查询，则会限制自己的工作. 在此处跟踪了增强SqlQuery以使用ColumnName属性的情况: https://entityframework.codeplex.com/workitem/233 -可以随时对此Codeplex项目进行投票.

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.

这篇关于使用EntityFramework CodeFirst中的columnname属性从存储过程进行对象映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！