Converting SQL query having sub query to LInQ

Mohammad Nasir Uddin 41 Reputation points

I have the below SQL query.

       value = case  
                 when( a.value is not null and a.value != t.value ) then a.value else t.value  
From  (select as preferenceid,  
 value = case  
 when( o.value is not null and o.value != p.value ) then o.value else p.value  
From preference as p  
Left join organizationpreference as o on = 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 = '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)  
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)  

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?

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
479 questions
{count} votes