时间戳

首页 > 解决方案 > Linq用4个不同的外键加入同一张表

c# - Linq用4个不同的外键加入同一张表

问题描述

所以我得到了我的地址表和我的合同表。我想遍历所有合约并从中生成一些 Dto，但使用地址名称和编号而不是 id(guid)。我的问题是合同表中的地址表上有 4 个外键。我仍然可以通过某种方式加入，甚至可以使用多个加入吗？

这就是我目前正在尝试的：

  var contracts = context.Contracts
    .Join(context.Addresses, 
       (con => con.AddressId), 
       (adr => adr.AddressId), 
       (con,adr) 
       => new { contractInfo = con, addressInfo = adr });

      foreach(var a in contracts) {
          new MyDto() {
            AddressId = a.addressInfo.AddressNumber,
            AddressName = a.addressInfo.FullName,
            Date= a.contractInfo.Date,
            AnotherDate = a.contractInfo.AnotherDate,
            Text = a.contractInfo.Text,
            VerweisAdrId = a.addressInfo. // ???
            VerweisAdrName = a.addressInfo. // 2 more cases like this would follow
          }
        }

我的其他方法可能会产生太多的查询：

  var addresses = context.Addresses;
  var contracts = context.Contracts;

  foreach(var a in contracts) {
      new MyDto() {
        AddressId = addresses.Where(x => a.AddressId == x.AddressId).Select(y => y.AddressNumber);
        AddressName = addresses.Where(x => a.AddressId == x.AddressId).Select(y => y.FullName);
        Date= a.Date,
        AnotherDate = a.AnotherDate,
        Text = a.Text,
        VerweisAdrId = addresses.Where(x => a.VerweisAdrId == x.AddressId).Select(y => y.AddressNumber);
        VerweisAdrName = addresses.Where(x => a.VerweisAdrId == x.AddressId).Select(y => y.FullName);
      }
  }

标签： c#linqforeign-keys

解决方案

var contracts = context.Contracts
    .Join(context.Addresses, 
       (con => con.AddressId), 
       (adr => adr.AddressId), 
       (con,adr) 
       => new { contractInfo = con, addressInfo = adr })
    .Join(context.Addresses,
        ca => ca.contractInfo.VerweisAdrId,
        adr => adr.AddressId,
        (ca, adr) => new { ca.contractInfo, ca.addressInfo, verweisAdr = adr })
    // ... and so on
    .Select(cavwx => new MyDto() {
        AddressId = cavwx.addressInfo.AddressNumber,
        AddressName = cavwx.addressInfo.FullName,
        VerweisAdrId = cavwx.verweisAdr.AddressNumber,
        VerweisAdrName = cavwx.verweisAdr.FullName,
        // ... and so on
     });

推荐阅读