Linq语法连接和分组

Question

你好，我需要连接两个表(MainTransaction和Subtransaction)，这里的问题是我还想获得MainTransaction的所有记录，而不是在Subtransaction中，我被困在这部分，我如何才能实现这一点?

protected object SelectMainTbl()
{
    var mainIdAndSum = from st in t.subtransaction
                       group st by st.MainTransactionId into g
                       select new
                       {
                           Sum = (from r in g
                                  select r.Amount).Sum(),
                           MainId = g.Key
                       };
    var mainTbl = from main in t.maintransaction
                  join sub in mainIdAndSum on main.MainTransactionId equals sub.MainId
                  where main.IsEnabled == true && (sub.Sum - main.Amount != 0)
                  select main;
    return mainTbl;
}

Answer 1

我认为这是你想要的查询:

from mt in t.maintransaction
join st in t.subtransaction
    on mt.MainTransactionId equals st.MainTransactionId
    into sts
where mt.IsEnabled
where sts.Sum(x => x.Amount) - mt.Amount != 0
select new
{
    MainTransaction = mt,
    Subtransactions = sts,
};