Entity Framework using Database.SqlQuery() where column names are invalid C# member names

Question

• Entity Framework 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:

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.

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?

Answer 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.

Link: 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:

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