c# - 为什么在从 DbSet.SqlQuery 映射实体时忽略我的 DbModelBuilder 配置？

Question

我有一个 DbModel像这样配置:

modelBuilder.Entity<WishlistLine>()
            .HasKey(w => w.PersistenceKey)
            .Property(w => w.PersistenceKey)
            .HasColumnName("WishlistLineId");

我有一个通过以下两种方法运行的查询:

public IEnumerable<WishlistLine> FetchWishlistLinesUsingLogonName(string logonName)
{
        return GetFromRawSql(@"
    SELECT wl.* FROM WishlistLines wl
    INNER JOIN Accounts a ON wl.AccountId = a.AccountId 
    LEFT JOIN Users u ON u.AccountId = a.AccountId
    WHERE u.LogonName = @p0", logonName);
}

protected IEnumerable<TEntity> GetFromRawSql(string sqlQuery, params object[] parameters)
{
    return _dbSet.SqlQuery(sqlQuery, parameters).ToList();
}

我可以“保存”WishlistLines通过EF进入数据库没有任何问题。当我运行这个查询时，我得到了这个错误:

The data reader is incompatible with the specified 'DataAccessLayer.DatabaseContext.WishlistLine'. A member of the type, 'PersistenceKey', does not have a corresponding column in the data reader with the same name.

我知道使用 DbSet<T>.SqlQuery()会将返回的数据映射到实体，但它似乎忽略了 DbModel配置。根据错误信息判断(猜测)使用了错误的数据读取器。

所以:

A) 我做错了什么吗？

B) 有没有办法利用 EF 的 DbModel感知实体映射器？

Answer 1

实际上，当您执行原始 SQL 查询时，列名映射会被忽略。这里有两个引用:This非常不满意的线程只是为了好玩，但以下是 EF 团队的严肃回答:

引自 http://entityframework.codeplex.com/workitem/233 :

The SqlQuery method is designed not to take any mapping into account, including mapping that is applied using attributes. It simply matches the column names from the results with property names in the object. If the column names don't match you will need to use a column alias (AS keyword in SQL Server) to rename the column in the results.

We agree that it would be useful to have the option to make SqlQuery honor Column attributes so we're keeping this issue open and putting it on our backlog for future consideration.

因此，唯一的解决方法似乎是在 SQL 查询中使用显式 AS 别名而不是 * 将属性名称指定为列别名:

return GetFromRawSql(@"
    SELECT wl.WishlistLineId AS PersistenceKey,
           wl.SomeOtherColumn AS SomeOtherProperty,
           ...
    ..."
    // ...