Linq-to-SQL Table Coalesce

Let's say I have three tables:

User

ID | Name
===========
 1 | UserA
-----------
 2 | UserB
-----------

Setting

ID | Name     | Default
=========================
 1 | SettingA | ADefault
-------------------------
 2 | SettingB | BDefault
-------------------------

And

UserSetting

UserID | SettingID | Value
================================
 1     |  1        | Alice
--------------------------------
 1     |  2        | Bob
--------------------------------
 2     |  1        | AOverride
--------------------------------

When I create my dbml file, it appropriately links User to UserSetting and UserSetting to Setting based on the foreign keys set up in the database.

What I'm wondering is if its possible to coalesce back from UserSetting to the Setting table if the user hasn't specifically overridden the value in a way that makes sense.

Specifically, I'm looking for the following pseudo-code:

var user = MyDataContext.Users.SingleOrDefault(u => u.ID == 2);

foreach(var setting in user.UserSettings)
{
  Console.Writeline(setting.ID + "|" + setting.Value);
}

To output something like this:

 1 | AOverride
 2 | BDefault

Without modification, user.UserSettings will only contain the values that have specifically been overridden, so it will only return:

 1 | AOverride

Any ideas? Or could someone with more rep please help me rephrase this, since it probably isn't exactly clear? :)

From stackoverflow

• You could create a view that joins UserSetting and Setting that represents all settings for each user. Then use the view in your DBML file in addition to (or instead of) the UserSettings association (instead of works unless you want to be able to add new settings, in which case you may want the UserSettings association to be around too).

  I don't have a SQL server handy, but the view would probably look something like this:

  SELECT
      User.ID AS UserID,
      Setting.ID AS SettingID,
      COALESCE(UserSetting.Value, Setting.Default) AS Value,
      CASE WHEN UserSetting.UserID IS NOT NULL THEN CAST(0 AS BIT)
          ELSE CAST(1 AS BIT) END AS IsDefault
  FROM User
      CROSS JOIN Setting
      LEFT JOIN UserSetting ON Setting.ID = UserSetting.SettingID
       AND UserSetting.UserID = User.ID
  

• I like to keep the Linq-to-SQL pretty dumbed down, and add any fancy requirements to my partial classes, following is the solution I used.

  I added the following helper to my User class (based on Jose's answer, but fixed so it works):

  public IEnumerable<SettingValue> GetSettingsWithDefaults( IEnumerable<Setting> settings )
  {
      return from s in settings
             join us in this.UserSettings
               on s.ID equals us.SettingID into userSettings
             from us in userSettings.DefaultIfEmpty()
             select new SettingValue
                    {
                        Setting = s,
                        Value = ( us == null ) ? s.Default : us.Value,
                        IsDefault = ( us == null )
                    };
  }
  

  This required a new class:

  public class SettingValue
  {
      public Setting Setting { get; set; }
      public string Value { get; set; }
      public bool IsDefault { get; set; }
  }
  

  I could then do the following:

  foreach( var userSetting in user.GetSettingsWithDefaults( settings ) )
  {
      Debug.WriteLine( string.Format( "Name:{0}, Value:{1}, IsDefault:{2}", 
          userSetting.Setting.Name,
          userSetting.Value,
          userSetting.IsDefault 
      ) );
  }
  

  Which gave me the following output

  Name:SettingA, Value:OverrideA, IsDefault:False
  Name:SettingB, Value:DefaultB, IsDefault:True
  

  jerhinesmith : This worked perfectly. I made one small adjustment that I thought helped. Instead of setting the IsDefault in the linq query, the get in SettingValue just returns (Setting.Default == Value).