How can I convert this SQL query to an Entity Framework query?-CodePudding

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

I want to write this query with an extra select inside to my repository without the use of a SqlCommand but with the use of Entity Framework

CodePudding user response：

        public async Task<List<DefineDocsForCommand>> GetAllWmsDocsWithAction() =>
        await _context.Wmsdefinedocs.Where(d => d.Bzaction == 2).Select(d =>  new DefineDocsForCommand
        {
            id = d.id,
            action = d.action,
            braid = d.braid,
            compid = d.compid,
            desc = d.desc,
            storeid = d.storeid,
            itemdocserid = d.itemdocserid,
            sourcedocserid = d.sourcedocserid,
            configstrdocid = d.configstrdocid,
            qtyisfrompackrem = d.qtyisfrompackrem,
            sourcesrcstatus = d.sourcesrcstatus,
            sourceitems = d.sourceitems,
            sourcestatusfrom = d.sourcestatusfrom,
            userids = d.userids,
            sourcestatusproc = d.sourcestatusproc,
            sourcestatusto = d.sourcestatusto,
            count = _context.Cmptrades.Where(c => c.Bzdocserid == d.sourcedocserid && c.wmsstatus == 0).Count(),

        }).ToListAsync();

I 've made an extra call to my _context to fill the count field of my DTO class. In my case this worked fine.