select from (select语句)

Question

如何将其转换为linq？塔克斯

SELECT AccNo, AccName FROM ( 
SELECT fileNo AS AccNo, fileName AS AccName,
(SELECT oldID FROM USER WHERE USER.id = ACCOUNT.id) AS oldID,  
(SELECT newID FROM USER WHERE USER.id = ACCOUNT.id) AS newID FROM FILE inner join ACCOUNT ON AccNo= FileAccNo) TblAllTable  
WHERE AccNo="abc"
GROUP BY AccNo, AccName

Answer 1

这里：

var TblAllTable = FILE
    .Join(ACCOUNT, f => f.accNo, a => a.fileNo, (f, a) => new
    {
        AccNo = a.fileNo,
        AccName = f.fileName,
        oldID = USER.Where(u => u.ID.Equals(a.id)).First().oldID,
        newID = USER.Where(u => u.ID.Equals(a.id)).First().newID
    });

var result = TblAllTable
    .Where(a => a.AccNo.Equals("abc"))
    .GroupBy(a => new { 
        AccNo = a.AccNo,
        AccName = a.AccName
    }) 
    .Select(a => new
    {
        AccNo = a.Key.AccNo,
        AccName = a.Key.AccName
    }); 

// If you're just selecting grouped keys, 
// it would be better to use Distinct method instead of GroupBy