This is my scenario for tables
Users can transfer money to each others. There are two options when the transferring. One is the direct to users budget, other one is project budget for the user to spend.
|- TransferTable -| |- ProjectEnum.cs -| |- TransferProjectTable -| |TransferId| |Budget| |TransferProjectId| |senderAccountId| |Project| |TransferId| |recipientAccountId| |ProjectId| |ProjectEnum| |Date| |Amount| |SenderUserId| |- AccountTable - | |- ProjectTable -| |AccountId| |ProjectId| |BankName| |Name| |UserId| |IBAN|
a user can have multiple bank accounts. So, I would like to see list for transfer table according to recipter account Id. This means, To list transfers to all accounts of the user.
If transfer to project, It will be return project name.
It have to return that class
TransferListDto.cs TransferId SenderAccountId ReciptientAccountId ProjectEnum ProjectId // If not any, it will be null val ProjectName // if not any, it will be null val Date Amount SenderUserId
I try this code but I got stuck, It empty list is returning.
var result = (from h in context.Accounts
join t in context.Transfers on h.AccountId equals t.RecipientAccountId
join tp in context.TransferProjects on t.TransferId equals tp.TransferId
join u in context.Users on h.UserId equals u.Id
join p in context.Projects on tp.ProjeId equals p.ProjeId
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();
return result;
Advertisement
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();