Geek asks and answers

Linq combines 2 tables into one list

I have 2 tables in db - one EmploymentRecordsoneEmploymentVerificationRecords

I want to query both tables and return one List

I have a model (simplified example):

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

I want to have some type of query in LINQ, for example:

  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

see. - I also need a new one Record(), added to the results for each record found in the second table, for these results there IsVerificationwill beTrue

+3
source share
1 answer

You can select everything from the database as you select it (but I would prefer to use it join/intofor this), and then smooth the results into one large collection using LINQ to Objects.

The following should do the trick:

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));
+2
source

More articles:


All Articles