使用 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 对象来填充它，如果我有一种快速简单的方法来映射尊重 Column 属性的对象.

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屋！