EF Core中SQL字符串查询结果无法映射到自定义POEntry模型

错误原因很明确：POEntry不是数据库上下文（DbContext）中注册的实体类型，所以EF Core无法为它创建DbSet。下面是三种可行的解决方法：

方法一：将POEntry注册为无键实体（推荐） #

这是最贴合EF Core设计的方案，只需在DbContext中把POEntry配置为无键实体，EF Core就会允许用它映射SQL查询结果。

步骤1：配置DbContext #

在你的DbContext类的OnModelCreating方法中添加以下配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 将POEntry标记为无键实体
    modelBuilder.Entity<POEntry>().HasNoKey();
}

步骤2：修改查询代码 #

注意原方法是异步的，要使用ToListAsync而非ToList：

public async Task<List<POEntry>> GetClosedTransferInsEntries()
{
    var TRInsClosed = @"SELECT e.ID as ID,e.PurchaseOrderID as OID,e.ItemID as ItemID,e.ItemDescription as ItemDescription,i.Quantity as QuantityOnHand, 
    (case when po.Remarks like 'COR%' then 'COR' else '' end) as COR,
    e.QuantityOrdered,sl.MasterPackQuantity as MasterPackQuantity,
    i.ItemLookupCode as ItemLookupCode,e.QuantityReceivedToDate as QuantityReceived,
    1 as ReasonID,
    (case when po.ConfirmingTo = 'Done' then CAST(1 AS BIT) when po.ConfirmingTo = '' then CAST(0 AS BIT)  end) as IsSyncHQ,
    (case when i.ItemType = 6 then CAST(1 AS BIT) when ((i.ItemType = 0 or i.ItemType = 3) and i.ItemLookupCode like '20%') then CAST(1 AS BIT) else CAST(0 AS BIT) end ) as Weight,
    (case when (i.DepartmentID = 289 or i.DepartmentID = 258) then CAST(0 AS BIT) else CAST(1 AS BIT) end ) as HasDate, 
    i.SupplierID as SupplierID, (case when  (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID) is null then 0 else (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID)  end) as TotalQuantity ,(case when aa.Alias is null then ''  else aa.Alias end )as Alias, (case when IsNull(CONVERT(datetime,  hp.ExpireDate),'')='' then ' ' else Convert(varchar(50), hp.ExpireDate,101) end) as ExpireDate ,
    (case when IsNull(CONVERT(datetime,  hp.ProductionDate),'')='' then ' ' else Convert(varchar(50), hp.ProductionDate,101) end) as ProductionDate 
    FROM PurchaseOrderEntry AS e Inner JOIN PurchaseOrder AS po ON e.PurchaseOrderID = po.ID Inner JOIN Item AS i ON e.ItemID = i.ID
    Left JOIN Alias as aa  ON e.ItemID = aa.ItemID  Left JOIN HH_Production as hp  ON po.ID = hp.PurchaseOrderID  and hp.ItemID = e.ItemID Inner JOIN SupplierList as sl  ON e.ItemID=sl.ItemID 
    WHERE(po.POType = 2 or po.POType = 4) AND(po.Status = 2) and po.SupplierID = 0 And (po.DateCreated > DATEADD(DAY, -10, GETDATE())) order by po.DateCreated ";

    return await db.Set<POEntry>().FromSqlRaw(TRInsClosed).ToListAsync();
}

方法二：直接用Database.SqlQuery查询（EF Core 5+支持） #

如果不想修改DbContext的配置，可以直接通过数据库连接执行查询并映射到自定义模型：

public async Task<List<POEntry>> GetClosedTransferInsEntries()
{
    var TRInsClosed = @"SELECT e.ID as ID,e.PurchaseOrderID as OID,e.ItemID as ItemID,e.ItemDescription as ItemDescription,i.Quantity as QuantityOnHand, 
    (case when po.Remarks like 'COR%' then 'COR' else '' end) as COR,
    e.QuantityOrdered,sl.MasterPackQuantity as MasterPackQuantity,
    i.ItemLookupCode as ItemLookupCode,e.QuantityReceivedToDate as QuantityReceived,
    1 as ReasonID,
    (case when po.ConfirmingTo = 'Done' then CAST(1 AS BIT) when po.ConfirmingTo = '' then CAST(0 AS BIT)  end) as IsSyncHQ,
    (case when i.ItemType = 6 then CAST(1 AS BIT) when ((i.ItemType = 0 or i.ItemType = 3) and i.ItemLookupCode like '20%') then CAST(1 AS BIT) else CAST(0 AS BIT) end ) as Weight,
    (case when (i.DepartmentID = 289 or i.DepartmentID = 258) then CAST(0 AS BIT) else CAST(1 AS BIT) end ) as HasDate, 
    i.SupplierID as SupplierID, (case when  (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID) is null then 0 else (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID)  end) as TotalQuantity ,(case when aa.Alias is null then ''  else aa.Alias end )as Alias, (case when IsNull(CONVERT(datetime,  hp.ExpireDate),'')='' then ' ' else Convert(varchar(50), hp.ExpireDate,101) end) as ExpireDate ,
    (case when IsNull(CONVERT(datetime,  hp.ProductionDate),'')='' then ' ' else Convert(varchar(50), hp.ProductionDate,101) end) as ProductionDate 
    FROM PurchaseOrderEntry AS e Inner JOIN PurchaseOrder AS po ON e.PurchaseOrderID = po.ID Inner JOIN Item AS i ON e.ItemID = i.ID
    Left JOIN Alias as aa  ON e.ItemID = aa.ItemID  Left JOIN HH_Production as hp  ON po.ID = hp.PurchaseOrderID  and hp.ItemID = e.ItemID Inner JOIN SupplierList as sl  ON e.ItemID=sl.ItemID 
    WHERE(po.POType = 2 or po.POType = 4) AND(po.Status = 2) and po.SupplierID = 0 And (po.DateCreated > DATEADD(DAY, -10, GETDATE())) order by po.DateCreated ";

    return await db.Database.SqlQuery<POEntry>(TRInsClosed).ToListAsync();
}

方法三：使用Dapper轻量ORM #

如果不想依赖EF Core的实体机制，可以用Dapper直接执行SQL并映射结果：

1. 先安装Dapper NuGet包：Install-Package Dapper
2. 修改查询代码：

using Dapper;
using System.Data;

public async Task<List<POEntry>> GetClosedTransferInsEntries()
{
    var TRInsClosed = @"SELECT e.ID as ID,e.PurchaseOrderID as OID,e.ItemID as ItemID,e.ItemDescription as ItemDescription,i.Quantity as QuantityOnHand, 
    (case when po.Remarks like 'COR%' then 'COR' else '' end) as COR,
    e.QuantityOrdered,sl.MasterPackQuantity as MasterPackQuantity,
    i.ItemLookupCode as ItemLookupCode,e.QuantityReceivedToDate as QuantityReceived,
    1 as ReasonID,
    (case when po.ConfirmingTo = 'Done' then CAST(1 AS BIT) when po.ConfirmingTo = '' then CAST(0 AS BIT)  end) as IsSyncHQ,
    (case when i.ItemType = 6 then CAST(1 AS BIT) when ((i.ItemType = 0 or i.ItemType = 3) and i.ItemLookupCode like '20%') then CAST(1 AS BIT) else CAST(0 AS BIT) end ) as Weight,
    (case when (i.DepartmentID = 289 or i.DepartmentID = 258) then CAST(0 AS BIT) else CAST(1 AS BIT) end ) as HasDate, 
    i.SupplierID as SupplierID, (case when  (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID) is null then 0 else (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID)  end) as TotalQuantity ,(case when aa.Alias is null then ''  else aa.Alias end )as Alias, (case when IsNull(CONVERT(datetime,  hp.ExpireDate),'')='' then ' ' else Convert(varchar(50), hp.ExpireDate,101) end) as ExpireDate ,
    (case when IsNull(CONVERT(datetime,  hp.ProductionDate),'')='' then ' ' else Convert(varchar(50), hp.ProductionDate,101) end) as ProductionDate 
    FROM PurchaseOrderEntry AS e Inner JOIN PurchaseOrder AS po ON e.PurchaseOrderID = po.ID Inner JOIN Item AS i ON e.ItemID = i.ID
    Left JOIN Alias as aa  ON e.ItemID = aa.ItemID  Left JOIN HH_Production as hp  ON po.ID = hp.PurchaseOrderID  and hp.ItemID = e.ItemID Inner JOIN SupplierList as sl  ON e.ItemID=sl.ItemID 
    WHERE(po.POType = 2 or po.POType = 4) AND(po.Status = 2) and po.SupplierID = 0 And (po.DateCreated > DATEADD(DAY, -10, GETDATE())) order by po.DateCreated ";

    using (var connection = db.Database.GetDbConnection())
    {
        return (await connection.QueryAsync<POEntry>(TRInsClosed)).ToList();
    }
}

内容的提问来源于stack exchange，提问作者Mina Latif