score:2

Accepted answer

join will perform inner join, so if there is no Transfer or TransferProjects you will get empty list. You need to perform left outer join. Something like this (not sure that it will compile, but you should get the gist of it):

var result = (from h in context.Accounts
                      join tl in context.Transfers on h.HesapId equals tl.RecipientAccountId into tlG
                      from t in tlG.DefaultIfEmpty()
                      join tpl in context.TransferProjects on t.TransferId equals tpl.TransferId into tplG
                      from tp in tplG.DefaultIfEmpty()
                      join u in context.Users on h.UserId equals u.Id
                      join pl in context.Projects on tp.ProjeId equals pl.ProjeId into plG
                      from p in plG.DefaultIfEmpty()
                      where h.UserId == userId
                      select new TransferListDto()
                      {
                          TransferId = t.TransferId,
                          UserId = u.Id,
                          NameSurname = u.Name + " " + u.Surname,
                          RecipientAccountId = t.RecipientAccountId,
                          SenderAccountId = t.SenderAccountId ,
                          ProjectEnum = t.ProjectEnum,
                          ProjeId = tp.ProjeId,
                          ProjeName = p.Name,
                          Date = t.Date,
                          Amount = t.Amount,

                      }).ToList();

score:0

I have a new problem, The user should be able to see the transfers he / she sent and the transfers he / she received.

So how can I multipler join condition like this one for context.Transfers Table join condition ?

h.AccountId equals t.RecipientAccountId or h.AccountId equals t.SenderAccountId

this bellow code only show the transfer list of recipient, I want the user to show up on transfers sent by her/him.

        var result = (from h in context.Account
                      join t in context.Transfers on h.AccountId equals t.RecipientAccountId
                      join tp in context.TransferProjects on t.TransferId equals tp.TransferId into tpo
                      from tp in tpo.DefaultIfEmpty()
                      join p in context.Projects on tp.ProjeId equals p.ProjeId into po
                      from p in po.DefaultIfEmpty()
                      join u in context.Users on h.UserId equals u.Id
                      join hg in context.Accounts on t.SenderAcctountId equals hg.AccountId
                      join ug in context.Users on hg.UserId equals ug.Id
                      where h.UserId == userId && (startDay != null && endDay != null ? (t.Date >= startDay && t.Date <= endDay) : (t.Date >= last30))
                      select new TransferListDto()
                      {
                          TransferId = t.TransferId,
                          RecipientUserId = u.Id,
                          RecipientNameSurname = u.Name + " " + u.Surname,
                          RecipientAccountId = t.RecipientAccountId,
                          RecipientIban = h.Iban,
                          SenderAdSoyad = ug.Ad + " " + ug.Soyad,
                          SenderUserId = ug.Id,
                          SenderAccountId = t.SenderAccountId,
                          SenderIban = hg.Iban,
                          ProjectEnum = t.ProjeEnum,
                          ProjeId = tp.ProjeId,
                          ProjeName = p.Name,
                          Desc = t.Desc,
                          Date = t.Date,
                          Amount = t.Amount,
                          Status = t.Status

                      }).OrderByDescending(x => x.Date).ToList();

Related Articles