Linq query for left-join

Question

I have 3 tables:

* UserSettingsType { general, UserSpecific)
* UserSettingsOption { Currency:general, Language:general, Location:Userspecific }
* UserSettingsValue { Currency:USD, Location:US }

If I run the SQL query:

select ust.Name Type, uso.Name Option, usv.Value Value from UserSettingOption uso 
 inner join UserSettingType ust on ust.Id = uso.Type_Id
 left join UserSettingValue usv on usv.Setting_Type_Id = uso.Id

output is:

Type         | Name      | Value
--------------------------------
General      | Currency  | USD
General      | Language  | NULL
UserSpecific | Location  | US

How can I convert the above in Linq format?

Answer 1

You can do the following:

var result = (form uso in Context.UserSettingOption
              join ust in Context.UserSettingType on uso.Type_Id equals ust.Id
              join usv in Context.UserSettingValue on uso.Id equals usv.Setting_Type_Id into tmpusv
              from lusv in tmpusv.DefaultIfEmpty()
              select new
              {
                 Type = ust.Name, 
                 Option = uso.Name, 
                 Value = lusv != null ? lusv.Value : null
              }).ToList();