Whenever I try to test this end to end, by putting values in database of myql in the tables, the variable (var testvar) returns null if I am not filling data in all the 3 tables I am applying left join on. Technically the left join should return the left table to me irrespective of the fact that the other two tables I am putting join on, have data or not.. I am trying to return model class in this method and the model class has all these properties in it – username, password, subject, division and details.
My sql query has a left join and the query goes like –
select u.Username, u.Password , c.Subject, m.Division, m.Details
from UserDetails u left join CandidateDetails c
on u.CandidateId = c.CandidateId
left join ClassDetails m
on u.CandidateId = m.CandidateId;The LINQ code for EF core which I have written is something like this
public async Task<MyModelClass> AuthMethod(long Id)
{
var testvar = (from u in Context.UserDetails
join c in Context.CandidateDetails
on u.CandidateId equals c.CandidateId
into cu
from x in cu.DefaultIfEmpty()
join m in Context.ClassDetails
on u.CandidateId equals m.CandidateId
into mu
from y in mu.DefaultIfEmpty()
where (y.CandidateId == Id
&& u.CandidateId == Id
&& x.CandidateId == Id)
select new MyModelClass
(u.Username, u.Password,x.Subject,m.Division,m.Details)
.FirstOrDefault();
return testvar;
}