使用Database.SqlQuery（）的实体框架，其中列名是无效的C＃成员名 [英] Entity Framework using Database.SqlQuery() where column names are invalid C# member names

问题描述

• 实体框架6.1

我有一个存储过程返回以下结果：

I have a stored procedure that returns the following result:

很自然地，这些字段名称不是我修改为的结果POCO的有效标识符。

So naturally those field names are not valid identifiers for the resulting POCO which I have modified to:

public class AdHoc_UspGetPassengerCountByAgeReturnDto
{
    public Int32? _0_18 { get; set; }
    public Int32? _19_30 { get; set; }
    public Int32? _31_40 { get; set; }
    public Int32? _41_50 { get; set; }
    public Int32? _51_60 { get; set; }
    public Int32? _61_70 { get; set; }
    public Int32? _71_80 { get; set; }
    public Int32? _81plus { get; set; }
}

由于属性名称的变化，请更改以下内容：

As a result of the property name change the following:

public List<AdHoc_UspGetPassengerCountByAgeReturnDto> AdHoc_UspGetPassengerCountByAge(out int aProcResult)
{
    SqlParameter procResultParam = new SqlParameter {ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output};

    List<AdHoc_UspGetPassengerCountByAgeReturnDto> procResultData =
            Database.SqlQuery<AdHoc_UspGetPassengerCountByAgeReturnDto>("EXEC @procResult = [AdHoc].[usp_GetPassengerCountByAge] ", procResultParam).ToList();

    aProcResult = (int) procResultParam.Value;
    return procResultData;
}

返回空值，因为名称不匹配。

returns null values because the names don't match.

所以我要尝试的是如何执行SP调用，但是返回一些通用类型，以便我可以在返回给Adhoc_UspGetPassengerCountByAgeReturnDto的内容之间进行转换。

So what I am trying to work out is how I perform the SP call but return some generic type so that I can do the translation between what is returned to my Adhoc_UspGetPassengerCountByAgeReturnDto.

有人可以指出我正确的方向吗？

Can someone point me in the right direction please?

推荐答案

不幸的是，列的映射是不可能的。这已经被请求了多年，并且最终获得支持，使其在Codeplex上达到建议状态，但是SqlQuery尚不支持。

Unfortunately, mapping of columns is not possible. This has been requested for years, and is getting support finally making it to "Proposed" status on codeplex, but it's just not there yet with SqlQuery.

链接： http://entityframework.codeplex.com/workitem/233?PendingVoteId=233

使用变通办法进行更新

如果您可以修改存储的proc，那是制作此文件的最佳方法字段匹配。如果那不可能，那么您可以创建一个表变量，其中包含所需的输出列以匹配您的poco，然后将 INSERT / EXEC 放入表变量中，最后 SELECT * 来自表变量。然后，您的SQL命令将变为：

If you can modify your stored proc, that's the best way to make the fields match. If that's not possible, then you can create a table variable that contains your desired output columns to match your poco, then INSERT/EXEC into the table variable, and finally SELECT * from the table variable. Your SQL command then becomes:

DECLARE @Data TABLE (
    _0_18 INT,
    _19_30 INT,
    _31_40 INT,
    _41_50 INT,
    _51_60 INT,
    _61_70 INT,
    _71_80 INT,
    _81plus INT
)
INSERT @Data
    EXEC @procResult = [AdHoc].[usp_GetPassengerCountByAge]
SELECT * FROM @Data

这篇关于使用Database.SqlQuery（）的实体框架，其中列名是无效的C＃成员名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！