使用Database.SqlQuery（）的实体框架，其中列名是无效的C＃成员名称

• Entity Framework 6.1
• 实体框架6.1

I have a stored procedure that returns the following result:

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

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

所以这些字段名称自然不是我修改为POCO的有效标识符：

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.

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

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.

所以我想要解决的是我如何执行SP调用但返回一些泛型类型，以便我可以在返回到我的Adhoc_UspGetPassengerCountByAgeReturnDto之间进行转换。

Can someone point me in the right direction please?

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

1 个解决方案

#1

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.

不幸的是，列的映射是不可能的。这已被要求多年，并且最终获得支持，最终使其成为codeplex上的“Proposed”状态，但它还没有使用SqlQuery。

Link: http://entityframework.codeplex.com/workitem/233?PendingVoteId=233

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

Update with work-around

通过解决方案进行更新

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:

如果您可以修改存储过程，那么这是使字段匹配的最佳方法。如果那是不可能的，那么你可以创建一个表变量，它包含你想要的输出列以匹配你的poco，然后INSERT / EXEC进入表变量，最后是表*变量中的SELECT *。然后您的SQL命令变为：

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