sql实体框架我无法创建linq查询

yrdbyhpb  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(377)

这是我的表格场景
用户可以互相转账。转移时有两种选择。一种是直接向用户预算,另一种是项目预算供用户使用。

|- TransferTable -|              |- ProjectEnum.cs -|      |- TransferProjectTable -|
  |TransferId|                      |Budget|                   |TransferProjectId|
  |senderAccountId|                 |Project|                  |TransferId|
  |recipientAccountId|                                         |ProjectId|
  |ProjectEnum|
  |Date|
  |Amount|
  |SenderUserId|

|- AccountTable - |          |- ProjectTable -|
   |AccountId|                  |ProjectId|
   |BankName|                   |Name|
   |UserId|
   |IBAN|

一个用户可以有多个银行帐户。所以,我想根据收件人帐户id查看传输表的列表。这意味着,要列出到用户的所有帐户的传输。
如果转移到项目,则返回项目名称。
它必须返回那个类

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

我尝试了这个代码,但我被卡住了,它返回了空列表。

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;
lbsnaicq

lbsnaicq1#

join 将执行内部联接,因此如果没有 Transfer 或者 TransferProjects 你会得到一张空名单。你需要执行左外连接。类似这样的内容(不确定它是否可以编译,但您应该了解它的要点):

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();
7gcisfzg

7gcisfzg2#

我有一个新问题,用户应该能够看到他/她发送的传输和他/她收到的传输。
那么,我怎样才能为context.transfers表连接条件创建这样的多重连接条件呢?

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

下面的代码只显示收件人的转账列表,我希望用户在她/他发送的转账中显示。

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();

相关问题