780 questions
Converting SQL query having sub query to LInQ
Mohammad Nasir Uddin
41
Reputation points
I have the below SQL query.
Select
a.userid,
t.preferenceid,
t.preferencetitle,
t.isusercontrolled,
value = case
when( a.value is not null and a.value != t.value ) then a.value else t.value
end
From (select
p.id as preferenceid,
p.preferencetitle,
p.isusercontrolled,
value = case
when( o.value is not null and o.value != p.value ) then o.value else p.value
end
From preference as p
Left join organizationpreference as o on p.id = o.preferenceid
And o.organizationid in (select organizationid from aspnetusers where id = '101')) as t
Left join userpreference a on a.preferenceid = t.preferenceid and a.userid = '101'
Left join aspnetusers u on u.id = '101';
Now I need to convert this sql query to linq. For the subquery I have create another linq query and later I joined with other linq query. Here is the code:
var appUser = await _dbContext.ApplicationUsers.FirstOrDefaultAsync(x => x.Id == userId);
var orgPreferences = await (from pf in _dbContext.Preferences.AsNoTracking()
join opf in _dbContext.OrganizationPreferences.AsNoTracking().Where(x => x.OrganizationId == appUser.OrganizationId)
on pf.Id equals opf.PreferenceId into opfTemp
from orgPref in opfTemp.DefaultIfEmpty()
select new Preference
{
Id = pf.Id,
PreferenceTitle = pf.PreferenceTitle,
Value = (orgPref.Value != null && orgPref.Value != pf.Value ? orgPref.Value : pf.Value)
}).ToListAsync();
var lists = (from orgPref in orgPreferences
join up in _dbContext.UserPreferences.AsNoTracking() on orgPref.Id equals up.PreferenceId into upTemp
from userPref in upTemp.DefaultIfEmpty()
join usr in _dbContext.ApplicationUsers.AsNoTracking() on userPref.UserId equals usr.Id into usrTemp
from user in usrTemp.DefaultIfEmpty()
where user.Id == appUser.Id
select new PreferenceDto
{
UserId = userPref.UserId,
PreferenceId = orgPref.Id,
PreferenceTitle = orgPref.PreferenceTitle,
PreferenceValue = userPref.Value != null && userPref.Value != orgPref.Value
? Convert.ToBoolean(userPref.Value)
: Convert.ToBoolean(orgPref.Value)
}).ToList();
Now I would like to know if the LINQ query is right? Is there any other better way to write the query? Can you please suggest?
Developer technologies | .NET | Entity Framework Core
Sign in to answer