SQL to LINQ possible?

Question

可以将此SQL查询翻译成Linq，一个选择？

select ID as SBDID,(select top 1 T.ID from Transactions T where T.SBDID=S.ID order by id desc) as TransID
from SBD S where ID in(223,225)

我想列出每个sbd.id的最后一个事务谢谢

Answer 1

显然您有SBDS和TRANSACTIONS。每个Sbd都有一个主键Id。每个交易都有一个主键Id和属于SbdId中的Sbd的外键：

class Sbd
{
    public int Id {get; set;}      // Primary key
    ...
}
class Transaction
{
    public int Id {get; set;}      // Primary key
    public int SbdId {get; set;}   // Foreign key to the Sbd that this transaction belongs to
    ...
}

现在，您希望所有具有223至225之间的ID的SDB，每个SBD的交易均具有ID的最高值。

每当您看到an object with all or some of its sub-objects的查询，例如School with its Students，Bank with his New York Clients，Customer with his Orders等，请考虑使用GroupJoin

// GroupJoin Sbds and Transactions:
var result = dbContext.Sbds.GroupJoin(dbContext.Transactions, 
    sbd => sbd.Id,                     // from every Sbd, take the Id
    transaction => transaction.SbdId,  // from every Transaction take the SbdId
    // ResultSelector: take the Sbd with all its matching Transactions to make one new:
    (sbd, transactionsOfThisSbd) => new
    {
        Id = sbd.Id,
        // You don't want all transactions of this Sbd, you want only the transaction
        // with the highest Id:
        Transaction = transactionsOfThisSbd
            .OrderByDescending(transaction => transaction.Id)
            .FirstOrDefault(),
    });

或没有所有评论，因此您会看到语句有多小：

    var result = dbContext.Sbds.GroupJoin(dbContext.Transactions, 
    sbd => sbd.Id,
    transaction => transaction.SbdId,
    (sbd, transactionsOfThisSbd) => new
    {
        Id = sbd.Id,
        Transaction = transactionsOfThisSbd
            .OrderByDescending(transaction => transaction.Id)
            .FirstOrDefault(),
    });

Answer 2

我尚未编译代码，但这应该起作用。上下文是您的dbcontext。

from s in context.SBD 
select new {
             id = s.id,
             tid = (from t in context.Transactions
                    where t.SBDID == s.id
                    select t).OrderByDescending().First().Id
            }).ToList();