LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework
18,957
Updated for the error: You will need to create a new class with the required properties and return it.
var applicantList = (from app in context.APPLICANTs
join a in context.Profiles
on app.Profile_id equals a.PROFILE_ID into output
from j in output.DefaultIfEmpty()
select new { APPLICANT_ID = app.APPLICANT_ID, Applicant_Name = (j == null ? app.Applicant_Name : j.Applicant_Name) }).Take(1000).AsEnumerable();
Related videos on Youtube
Author by
Enrique Gil
Updated on June 04, 2022Comments
-
Enrique Gil almost 2 years
Trying joining using Linq. What should i use ? Left join or right join?
APPLICANT TABLE PROFILE TABLE APPLICANT_ID|profile_id|Applicant_Name| |profile_id|Applicant_Name 1 | NULL | RAY HEAVENS | | 1 | MARK LAPID 2 | NULL | BEN TULFO | | 2 | SUPER MAN 3 | 1 | NULL | | 3 | BRANDON KNIGHT 4 | 2 | NULL | | 5 | 3 | NULL | | DESIRED OUTPUT: APPLICANT_ID | Applicant_Name 1 | RAY HEAVENS 2 | BEN TULFO 3 | MARK LAPID 4 | SUPERMAN 5 | BRANDON KNIGHT
This is my code in my controller:
var applicantList = (from a in context.Profiles join app in context.APPLICANTs on a.PROFILE_ID equals app.Profile_id into output from j in output.DefaultIfEmpty(new APPLICANT()) select j ).Take(1000).AsEnumerable(); applicantdata = applicantList.AsQueryable().OrderBy(v => v.APPLICANT_ID).ToList(); if (applicantdata.Any()) { Cache.Set("applicants", applicantdata, 30); } } return applicantdata; }
I hope someone can recommend me what to use or what to do. Thank you in advance.
-
Mathieu Guindon almost 11 yearsI would believe
.DefaultIfEmpty()
translates into aLEFT JOIN
, have you profiled it?
-
-
Enrique Gil almost 11 yearssir . . now im experiencing Exception
Error 1 Cannot implicitly convert type 'System.Collections.Generic.List<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<Applicant.Models.APPLICANT>'. An explicit conversion exists (are you missing a cast?)
atapplicantdata = applicantList.AsQueryable().OrderBy(v => v.APPLICANT_ID).ToList();
-
Enrique Gil almost 11 yearsSir @cheedep Thank you but now theres a new exception at
applicantdata = applicantList.AsQueryable().OrderBy(v => v.APPLICANT_ID).ToList();
the new exception isNotSupported Exception was unhandled by user code The entity or complex type 'Model.APPLICANT' cannot be constructed in a LINQ to Entities query