SQL to LINQ Involving Multiple GroupJoin

Question

I have a SQL Query and would like to convert into LINQ Method Syntax (Lambda) but it seems that I am stuck at the multiple group join and it confuses me. Below is the SQL that I wanted to change into LINQ

select MerchantUserId, usex.Nickname, MAX(OrderTransaction.CreatedDate) as LastOrderDate, sd.Url, st.Name as storename
from OrderTransaction
    left join UserExtend usex
    on usex.Id = OrderTransaction.MerchantUserId
    left join StoreDomain sd 
    on sd.StoreId = usex.StoreId
    left join store st
    on st.id = sd.StoreId
where OrderTransaction.Status <> 8
    and sd.IsPrimary = 1    
group by MerchantUserId,usex.Nickname,sd.Url,st.Name
order by LastOrderDate desc

Below is the query that I tried to link all three tables together. Am I doing it correctly in terms of linking 3 tables together?

    var list = _ApplicationDbContext.UserExtend
.GroupJoin(_ApplicationDbContext.OrderTransaction, usex => usex.Id, ord => ord.MerchantUserId, (usex, ord) => new { usex, ord})
.GroupJoin(_ApplicationDbContext.StoreDomain, usexOrd => usexOrd.usex.StoreId, sd => sd.StoreId, (usexOrdStoreDom, sd) => new { usexOrdStoreDom , sd})
.GroupJoin(_ApplicationDbContext.Store, usexOrdStoreDom => usexOrdStoreDom.usexOrdStoreDom.usex.StoreId, st => st.Id, (usexOrdStoreDomStore , st ) => new { usexOrdStoreDomStore, st })

Answer 1

The equivalent of your SQL query is the following link phrase. The GroupJoin is for when you want to match a value with several values.

For more information, you can refer to this link: Linq to Entities join vs groupjoin

var list = _ApplicationDbContext.UserExtend.Join(_ApplicationDbContext.OrderTransaction,
      usex => usex.Id,
      ord => ord.MerchantUserId,
      (usex, ord) => new { usex, ord })
      .Join(_ApplicationDbContext.StoreDomain,
      usexOrd => usexOrd.usex.StoreId,
      sd => sd.StoreId,
      (usexOrd, sd) => new { usexOrd, sd })
      .Join(_ApplicationDbContext.Store,
      usexOrdStoreDom => usexOrdStoreDom.sd.StoreId,
      st => st.Id,
      (usexOrdStoreDom, st) => new { usexOrdStoreDom, st })
      .GroupBy(a => new { a.usexOrdStoreDom.usexOrd.ord.MerchantUserId, a.usexOrdStoreDom.usexOrd.usex.Nickname, a.usexOrdStoreDom.sd.Url, a.st.Name, a.usexOrdStoreDom.usexOrd.ord.CreatedDate })
      .OrderBy(a => a.Key.CreatedDate)
      .Select(a => new
      {
         MerchantUserId = a.Key.MerchantUserId,
         Nickname = a.Key.Nickname,
         Url = a.Key.Url,
         storename = a.Key.Name,
         LastOrderDate = a.Max(x => x.usexOrdStoreDom.usexOrd.ord.CreatedDate)
      })
      .ToList();