如何将指定SQL查询转换为Entity Framework查询？

原SQL查询 #

SELECT 
    ID, ACTION, BRAID, COMPID, CONFIGSTRDOCID, DESCRIPTION,  
    ITEMDOCSERID, QTYISFROMPACKREM, SOURCEDOCSERID, SOURCEITEMS, 
    SOURCESTATUSFROM, SOURCESTATUSPROC, SOURCESTATUSTO, STOREID, 
    USERIDS, 
    COUNT = (SELECT COUNT(*) FROM TRADES 
             WHERE DOCSERID = DEFINEDOCS.SOURCEDOCSERID 
               AND TRADES.STATUS = 0)  
FROM 
    DEFINEDOCS 
WHERE 
    (ACTION = 2) 
ORDER BY 
    DESC

实现步骤 #

1. 定义DTO类

因为需要返回包含自定义COUNT字段的结果，先创建一个DTO来承载查询输出：

public class DefinedocWithTradeCountDto
{
    public int ID { get; set; }
    public int ACTION { get; set; }
    public string BRAID { get; set; }
    public int COMPID { get; set; }
    public int CONFIGSTRDOCID { get; set; }
    public string DESCRIPTION { get; set; }
    public int ITEMDOCSERID { get; set; }
    public bool QTYISFROMPACKREM { get; set; }
    public int SOURCEDOCSERID { get; set; }
    public string SOURCEITEMS { get; set; }
    public int SOURCESTATUSFROM { get; set; }
    public int SOURCESTATUSPROC { get; set; }
    public int SOURCESTATUSTO { get; set; }
    public int STOREID { get; set; }
    public string USERIDS { get; set; }
    public int COUNT { get; set; }
}

2. 仓储层LINQ实现

在仓储类中编写对应的查询逻辑，EF会自动将LINQ转换为等价的SQL：

public IEnumerable<DefinedocWithTradeCountDto> GetDefinedocsWithTradeCount()
{
    // 假设你的DbContext实例为_dbContext
    var query = from definedoc in _dbContext.DEFINEDOCS
                where definedoc.ACTION == 2
                select new DefinedocWithTradeCountDto
                {
                    ID = definedoc.ID,
                    ACTION = definedoc.ACTION,
                    BRAID = definedoc.BRAID,
                    COMPID = definedoc.COMPID,
                    CONFIGSTRDOCID = definedoc.CONFIGSTRDOCID,
                    DESCRIPTION = definedoc.DESCRIPTION,
                    ITEMDOCSERID = definedoc.ITEMDOCSERID,
                    QTYISFROMPACKREM = definedoc.QTYISFROMPACKREM,
                    SOURCEDOCSERID = definedoc.SOURCEDOCSERID,
                    SOURCEITEMS = definedoc.SOURCEITEMS,
                    SOURCESTATUSFROM = definedoc.SOURCESTATUSFROM,
                    SOURCESTATUSPROC = definedoc.SOURCESTATUSPROC,
                    SOURCESTATUSTO = definedoc.SOURCESTATUSTO,
                    STOREID = definedoc.STOREID,
                    USERIDS = definedoc.USERIDS,
                    // 嵌入子查询实现COUNT统计
                    COUNT = _dbContext.TRADES
                        .Count(trade => trade.DOCSERID == definedoc.SOURCEDOCSERID && trade.STATUS == 0)
                };

    // 原SQL的ORDER BY缺少排序字段，这里示例按ID倒序，可根据实际需求调整
    return query.OrderByDescending(dto => dto.ID).ToList();
}

3. 可选优化：使用导航属性

如果你的实体类中已经定义了DEFINEDOCS和TRADES的关联导航属性（比如DEFINEDOCS有ICollection<TRADES> Trades），可以简化Count的写法：

COUNT = definedoc.Trades.Count(trade => trade.STATUS == 0)

EF会自动识别关联关系，生成和原SQL一致的查询语句。

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