entity-framework - 使用 Join 获取更新的 Table Linq 实体框架

Question

尝试使用 Linq 加入。我应该使用什么？左连接还是右连接？

    FIRST LIST                                 SECOND LIST

APPLICANT_ID|Applicant_Name| NOTES | |APPLICANT_ID|Applicant_Name  | NOTES |
      1     |  RAY HEAVENS | Note1 | |    2       |  BEN TULFO     | NoteA 
      2     |  BEN TULFO   | Note2 | |    3       |  ERNIE BARON   | NoteB 
      3     |  ERNIE BARON | Note3 | | 
      4     |   SUPERMAN   | Note4 | |
      5     |  MARK LAPID  | Note5 | |  

期望的输出：

APPLICANT_ID | Applicant_Name |   NOTES 
-------------+----------------+---------
      1      |   RAY HEAVENS  |  Note1
      2      |   BEN TULFO    |  NoteA
      3      |   ERNIE BARON  |  NoteB
      4      |   SUPERMAN     |  Note4
      5      |   MARK LAPID   |  Note5

这是我在控制器中的代码：

 var applicantList = (from a in db.Profiles 
                      where a.isDeleted == false
                      select a ).ToList();

 var GetNewNotes = (from a in db.ProfilesNotes 
                    where a.isDeleted == false
                    select a).ToList();

 var lst = (from lst1 in applicantList 
            where !GetNewNotes.Any(x => x.APPLICANT_ID == lst1.APPLICANT_ID )
            select lst1).ToList();

ViewBag.updatedNotes = lst;

我希望有人可以推荐我使用什么或做什么。

先感谢您。

标签： entity-frameworklinqasp.net-mvc-3

Answer 1

I would go for an inner join with .Join():

var lst = applicantList.Join(GetNewNotes,
    (a) => a.APPLICANT_ID,
    (n) => n.APPLICANT_ID,
    (a, n) => return new
    {
        a.APPLICANT_ID,
        a.Applicant_Name,
        n.Notes
    });

/*
lst:
2 | BEN TULFO   | NoteA,
3 | ERNIE BARON | NoteB
*/

As a side note, is there any reason your second table contains ApplicantName? Why not keep this in Applicant table only?

EDIT: After re-reading the question, I realized that you need the unmatched entries from the left list too. So, that should be left outer join instead, which you achieve with .GroupJoin() and .SelectMany():

var lst = applicantList.GroupJoin(GetNewNotes,
    (a) => a.Id,
    (n) => n.Id,
    (a, n) => new
    {
        Id = a.Id,
        Name = a.Name,
        Notes = a.Notes,
        ApplicantNotes = n
    })
    .SelectMany(
        g => g.ApplicantNotes.DefaultIfEmpty(),
        (g, applicantNotes) => new
        {
            Id = g.Id,
            Name = g.Name,
            Notes = applicantNotes?.Notes ?? g.Notes
        });

/*
lst:
1 | RAY HEAVENS | Note1
2 | BEN TULFO   | NoteA
3 | ERNIE BARON | NoteB
4 | SUPERMAN    | Note4
5 | MARK LAPID  | Note5
*/