0

Hello I need to join two tables (MainTransaction and Subtransaction), the problem here is I also want to get all the record of Maintransaction that's not in the Subtransaction, I am stuck in this part, how can I achieve this ? 

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;
}
ekad
  • 14,436
  • 26
  • 44
  • 46
William
  • 5,526
  • 6
  • 20
  • 42

1 Answers1

1

I think this is the query that you want:

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,
};
Enigmativity
  • 113,464
  • 11
  • 89
  • 172
  • Hi Thanks for your answer, However, what I want to achieve is like the SQL Left Join. The result must return all the record of the Maintransaction table even though the SubTransaction table does not have the maintransactionId inserted yet, and those column for the subtransaction table will be filled as null in the result. SELECT * FROM maintransaction m LEFT JOIN subtransaction s ON m.MainTransactionId = s.MainTransactionId – William Aug 23 '11 at 05:20
  • @user660810 - Yes, that's what my query does. The `into sts` does that. If there are no associated subtransactions then `sts` will be an empty list and you will still have the main transaction returned from the query. – Enigmativity Aug 23 '11 at 10:02
  • I've been having a similar problem to this and `into` can only be used with `group join`. As I understand it `join` correlates to sql inner join and `group join` correlates to sql left join, roughly. – pseudocoder Jun 25 '13 at 18:23