Linq-to-SQL Table Coalesce

Question

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? :)

Answer 1

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

Answer 2

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