Executing SQL query in Entity Framework Core and returns a list of object

Question

I have the below SQL query. Now I would like to execute this query in Entity Framework Core and get the below list of object.

Query:

select   
    A.OrganizationId,  
    P.Id as PreferenceId,  
    P.PreferenceTitle,  
    value = CASE  
                WHEN ( A.value IS NOT NULL AND A.value != P.value ) THEN A.value ELSE P.value  
            end  
from Preference as P  
left join OrganizationPreference as A on P.Id = A.PreferenceId  
And A.OrganizationId = '101'  

Return Object:

public class PreferenceDto  
    {  
        public string UserId { get; set; }  
        public string OrganizationId { get; set; }  
        public  string PreferenceId { get; set; }  
        public string PreferenceTitle { get; set; }  
        public bool PreferenceValue { get; set; }  
    }  

Can anyone tell me how to execute this query in EF Core and returns the list of object? Also I need to send a parameter while executing the query. In here (A.OrganizationId = '101') I have fixed the value 101. But it needs to be passed as parameter while executing the query.

Answer 1

You haven't shown us how you set up your DbContext and what your entity models look like so we're only guessing here. We can assume that you understand how EF works and that you followed along with the beginner's guide to getting data from EF. If you didn't follow that documentation then you need to start there. We cannot tell you everything you need to do to get data from EF in a forum post.

Assumed entities:

   public class Preference  
   {  
      public int Id { get; set; }  
      public string Title { get; set; }  
      public bool Value { get; set; }  
   }  
     
   public class OrganizationPreference  
   {  
      public int OrganizationId { get; set; }  
     
      public int PreferenceId { get; set; }  
      public Preference Preference { get; set; }  
     
      public bool? Value { get; set; }  
   }  

The query in EF.

   //Get all preferences for an organization  
   var preferences = from a in context.OrganizationPreference  
                                                            .Include(x => x.Preference)  
                                where a.OrganizationId = 101  
                                select new PreferenceDto {   
                                      OrganizationId = a.OrganizationId.ToString(),  
                                      PreferenceId = a.PreferenceId.ToString(),  
                                      PreferenceTitle = a.Preference.Title,  
                                      PreferenceValue = a.Value ?? a.Preference.Value  
                                };  

Assuming here that you set up your configuration such that OrganizationPreference properly references Preference table.