LINQ - select these if they exist, otherwise fall back to these

Question

Think localized texts, stored in this table:

Table Texts

• TextId
• Language
• Value

Now I want to select a text for TextId 1. If there is not a text for this TextId in "Danish", I want to fall back to "English".

I could do like this:

var texts = MyDb.Texts.Where(x=>x.TextId == 1 & x.Language == "Danish");

if (!texts.Any()){
    texts = MyDb.Texts.Where(x=>x.TextId == 1 & x.Language == "English");
}

...But i have to repeat the rest of the Where clause, which means I am repeating myself (not so bad in this example, but could be many more clauses).

Is there a simpler way to do this?

Answer 1

I'd suggest using a Filter pattern, where you write Extension methods against IEnumerable. That way the bulk of your logic is encapsulated into methods you can use again and again:

public static class TextExtensions
    {
        [System.Runtime.CompilerServices.Extension]
        public static IEnumerable<Text> ByTextId(this IEnumerable<Text> qry, int textId)
        {
            return qry.Where(t => t.TextId == textId);
        }

        [System.Runtime.CompilerServices.Extension]            
        public static IEnumerable<Text> ByLanguage(this IEnumerable<Text> qry, string language)
        {
            return qry.Where(t => t.Language == language);
        }
    }

Your code then becomes:

var texts = MyDB.Texts.ByTextId(1).ByLanguage("Danish");

The repetition then becomes a non-issue. I'd also suggest making yourself a static class to hold the various language values to avoid the hard-coding:

public static class LanguageValues
{
    public static string English
    {
        get
        {
            return "English";
        }
    }

    public static string Danish
    {
        get
        {
            return "Danish";
        }
    }
}

Your code then becomes:

var texts = MyDB.Texts.ByTextId(1).ByLanguage(LanguageValues.Danish);

You can combine this with the DefaultIfEmpty method which gives you:

var texts = MyDB.Texts.DefaultIfEmpty(MyDB.Texts.ByTextId(1).ByLanguage(LanguageValues.English).Single()).ByTextId(1).ByLanguage(LanguageValues.Danish);

Then finish off by putting this into a single Extension method for re-use:

[System.Runtime.CompilerServices.Extension]
public static IEnumerable<Text> GetValueOrDefault(this IEnumerable<Text> qry, int textId, string language)
{
    return qry.DefaultIfEmpty(qry.ByTextId(textId).ByLanguage(LanguageValues.English).Single()).ByTextId(textId).ByLanguage(language);
}

You can now simply call:

var text = MyDB.Texts.GetValueOrDefault(1, LanguageValues.Danish);

Note that this can be used as the final step of any query so something like this would also work:

var text = MyDB.Texts.Where(<some funky clause>).Where(<some other funky clause>).GetValueOrDefault(1,LanguageValues.Danish);

As people have pointed out, if you go to multiple backup languages, there are better approaches than checking one language at a time like in the original question, but this Filter pattern will work cleanly, it's just a matter of defining the right filters for your use case and strategy.

Answer 2

One solution is to retrieve all the texts of the required id and then join on a language preference mapping:

var languages = new[]
                    {
                        new {Language = "Danish", Priority = 1},
                        new {Language = "English", Priority = 2}
                    };
var id = 1;
var text = (from t in db.Texts.Where(t => t.TextId == id).AsEnumerable()
            join l in languages on t.Language equals l.Language
            orderby l.Priority
            select t).FirstOrDefault();

If you only have two languages then this can be done even more simply and avoid returning any unnecessary rows:

var id = 1;
var text = (from t in db.Texts
            let priority = t.Language == "Danish" ? 1 : 2
            where t.TextId == id
            orderby priority
            select t).FirstOrDefault();

If you want to support a dynamic number of languages, you can build your priority expression dynamically (using System.Linq.Expressions). This results in a single database call that will return only the one record you want:

var id = 1;
var text = db.Texts.Where(t => t.TextId == id).OrderBy(CreatePriorityExpression()).FirstOrDefault();

private static Expression<Func<Text, int>> CreatePriorityExpression()
{
    var languages = new[]
                        {
                            new {Language = "Danish", Priority = 1},
                            new {Language = "English", Priority = 2}
                        };

    // Creates an expression of nested if-else statements & translates to a SQL CASE 
    var param = Expression.Parameter(typeof(Text));
    var lang = Expression.PropertyOrField(param, "Language");
    Expression ex = Expression.Constant(languages.Last().Priority);
    foreach (var l in languages.Reverse().Skip(1))
        ex = Expression.Condition(Expression.Equal(lang, Expression.Constant(l.Language)), Expression.Constant(l.Priority), ex);
    return Expression.Lambda<Func<Text, int>>(ex, param);
}