当DbSet< T> .SqlQuery映射Entity时，为什么我的DbModelBuilder配置被忽略？ [英] Why is my DbModelBuilder configuration ignored when mapping Entity from DbSet<T>.SqlQuery?

问题描述

我有一个 DbModel 配置如下：

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

我通过以下两种方法运行查询：

I have a query run via the following two methods:

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进入数据库没有任何问题。当我运行这个查询，虽然我得到这个错误：

I can "save" WishlistLines into the database through EF without any problems. When I run this query though I get this error:

数据读取器与指定的DataAccessLayer.DatabaseContext.WishlistLine不兼容。类型为PersistenceKey的成员在数据读取器中没有相同的列，名称相同。

我明白使用 DbSet< T> .SqlQuery（）将返回的数据映射到实体，但似乎忽略了 DbModel 配置。从错误信息中判断（猜测）错误的数据读取器正在被使用。

I understood that using DbSet<T>.SqlQuery() would map the returned data to the entities but it seems to be ignoring the DbModel configurations. Judging (guessing) from the error message the wrong data reader is being used.

so：

A）我有什么问题吗？

A) am I doing anything wrong?

B）有没有办法使用EF的$ code> DbModel -aware实体映射器

B) is there a way to make use of EF's DbModel-aware entity mapper?

推荐答案

确实执行原始SQL查询时，列名映射被忽略。以下是两个参考资料：这个非常不满意的线程只是为了乐趣，但以下是来自EF团队的严肃答案：

Indeed the column name mapping is ignored when you execute a raw SQL query. Here are two references: This pretty dissatisfying thread only for fun, but the following one with a serious answer from the EF team:

从 http://entityframework.codeplex.com/workitem/233 ：

SqlQuery方法被设计为不考虑任何映射，
包括使用属性应用的映射。它只是将
的列名与结果中的对象中的属性名称进行匹配。
如果列名不匹配，则需要使用列别名
（SQL Server中的AS关键字）来重命名结果中的列。

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.

我们同意，选择使SqlQuery
尊重Column属性是有用的，所以我们保持这个问题，并将
放在我们的积压下，以备将来考虑。 >

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.

所以，唯一的解决方法似乎是一个显式的 AS 别名而不是<$您的SQL查询中指定您的属性名称作为列别名c $ c> *

So, the only workaround seems to be an explicit AS alias instead of a * in your SQL query that specifies your property name as a column alias:

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

这篇关于当DbSet&lt; T&gt; .SqlQuery映射Entity时，为什么我的DbModelBuilder配置被忽略？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！