Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
764 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?