尝试使用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;
我希望有人可以推荐我使用什么或该怎么做。
预先感谢您。
这是要映射的奇数结构。从结构上讲,它看起来像1-0..1,但是从概念上讲,它应该是1-1对。对于一对多的人,我会期望一个桌子结构更像:
申请人(申请人|名称)
pepersantNote (pepersantNoteId | pepersantid | note)
这将在ef中映射,例如:
public class Applicant
{
public int ApplicantId { get; set; }
public string Name { get; set; }
public virtual ICollection<ApplicantNote> { get; set; } = new List<ApplicantNote>();
}
public class ApplicantNote
{
public int ApplicantNoteId { get; set; }
public virtual Applicant Applicant { get; set; }
}
public class ApplicantConfig : EntityTypeConfiguration<Applicant>
{
public ApplicantConfig()
{
ToTable("Applicant");
HasKey(x => x.ApplicantId);
HasMany(x => x.ApplicantNotes)
.WithRequired(x => x.Applicant)
.Map(x => x.MapKey("ApplicantId"));
}
}
public class ApplicantNoteConfig : EntityTypeConfiguration<ApplicantNote>
{
public ApplicantNoteConfig()
{
ToTable("ApplicantNote");
HasKey(x => x.ApplicantNoteId);
}
}
您拥有的更像是一个包含音符的申请人表,但是还有一个额外的表可以容纳一个额外的额外笔记。
申请人(pepersantId | name | note)
extairapplicantnote (pepersantId | note)//不需要名称。
1-0..1中的看起来像:
public class Applicant
{
public int ApplicantId { get; set; }
public string Name { get; set; }
public string Note { get; set; }
public ExtraApplicantNote ExtraApplicantNote { get; set; }
}
public class ExtraApplicantNote
{
public int ApplicantId { get; set; }
public string Note { get; set; }
public virtual Applicant Applicant { get; set; }
}
public class ApplicantConfig : EntityTypeConfiguration<Applicant>
{
public ApplicantConfig()
{
ToTable("Applicant");
HasKey(x => x.ApplicantId);
HasOptional(x => x.ExtraApplicantNote)
.WithRequired(x => x.Applicant);
}
}
public class ExtraApplicantNoteConfig : EntityTypeConfiguration<ExtraApplicantNote>
{
public ExtraApplicantNoteConfig()
{
ToTable("ExtraApplicantNote");
HasKey(x => x.ApplicantId);
}
}
此额外的申请人记录将其作为可选的相关实体加入申请人。选择作为实体图:
var applicant = context.Applicants
.Include(x => x.ExtraApplicantNote)
.Single(x => x.ApplicantId == applicantId);
例如...然后通过applicant.Note
和applicant?.ExtraApplicantNote.Note
访问注释(S),以说明额外的申请书是可选的。
要产生所有注释的输出,均具有申请人的详细信息,1到MONLY的结构要远得多:
var notes = context.ApplicantNotes.Select(x => new
{
x.Applicant.ApplicantId,
x.Applicant.Name,
x.Note
}).ToList();
用1-0..1做同样的事情,涉及更多:
var notes = context.Applicants.Select(x => new
{
x.ApplicantId,
x.Name,
x.Note
}).Union(context.ExtraApplicantNotes.Select(x => new
{
x.ApplicantId,
x.Applicant.Name,
x.Note
})).ToList();
这涉及首先从第一表中提取笔记,然后使用联合从第二个表中的可选记录中加入相同的详细信息。
**编辑**对不起,我重新阅读了问题,您希望第二个表覆盖第一个。
在这种情况下,类似于上述:
var notes = context.ExtraApplicantNotes.Select(x => new
{
x.ApplicantId,
x.Applicant.Name,
x.Note
}).Union(context.Applicants
.Where(x => x.ExtraApplicant == null)
.Select(x => new
{
x.ApplicantId,
x.Name,
x.Note
})).ToList();
我会选择一个 inner 与 .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
*/
作为旁注,您的第二个表包含ApplicantName
是否有任何原因?为什么不将其放在申请人表中?
编辑:重新阅读问题后,我意识到您需要左列表中的无与伦比的条目也。因此,这应该是left outer join
,您可以使用.GroupJoin()
和.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
*/