Converting SQL query having sub query to LInQ

Mohammad Nasir Uddin 41 Reputation points
2022-08-23T18:09:28.647+00:00

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?

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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.