c# - linq 将 2 个表合并为一个列表

Question

我在数据库中有 2 个表 - 一个是 EmploymentRecords 一个是 EmploymentVerificationRecords

我想查询两个表并返回一个列表

我有一个模型(简化示例):

Public Class Record
{
  int ID {get; set;}
  string name {get; set;}
  bool IsVerification {get; set;}
}

我想在 LINQ 中进行某种类型的查询，例如:

  var records = from a in _context.EmploymentRecords
        join b in _context.EmploymentVerificationRecords on a.id equals b.id
        where a.UserID = 1
        select new Record() { .ID = a.id, .name = a.name, .IsVerification = false}
        // I also want to select a new Record() for each b found

请参阅 - 我还希望将新的 Record() 添加到第二个表中找到的每条记录的结果中，因为这些结果 IsVerification 将为 True

Answer 1

您可以从现在选择的数据库中选择所有内容(但我宁愿使用 join/into 来执行此操作)，然后使用 LINQ to Objects 将结果展平到一个大集合中。

以下应该可以解决问题:

var records
    = (from a in _context.EmploymentRecords
       join b in _context.EmploymentVerificationRecords on a.id equals b.id into bc
       where a.UserID = 1
       select new {
           a = new Record() { ID = a.id, name = a.name, IsVerification = false},
           b = bc.Select(x => new Record() { ID = x.ID, name = b.name, IsVerification = true })
       }).AsEnumerable()
         .SelectMany(x => (new [] { x.a }).Concat(x.b));