LINQ Question - Query

Question

I have a table which stores cultural variants for a particular key for example.

RecordID   Key    CultureID   Description
1          1      en-GB       Hour
2          1      es-ES       hora
3          2      en-GB       Swimming

I have this as an IEnumerable Given the paremeter of @CultureID="es-ES" I want to return the following. In short If the cultural variant exists then return it for the given parameter, else return the "en-GB" entry ( Which Allways Exists ). Can someone help me with the LINQ query for this.

RecordID   Key    CultureID   Description
2          1      es-ES       hora
3          2      en-GB       Swimming

Thanks

Answer 1

I think you will have to make two queries. One to try to get the value for the cultural variant (using SingleOrDefault()), then check if the returned value is null. If it is, do the same query with en-BG instead of the one given as parameter.

var result = (from i in records where RecordID = 1 && CultureID = "es-ES" select i).SingleOrDefault();

if (result == null) result = = (from i in records where RecordID = 1 && CultureID = "es-ES" select i).Single();

Or maybe using some orderby clause?

Answer 2

There is always another way.

Union:

IEnumerable<Varriant> result = variants
    .Where(x=>x.CultureID   == CultureID)
    .Union(variants.Where(x=> x.CultureID   == "en-GB"))
.ToList();

Variant v = variants.FirstOrDefault();

Other

IEnumerable<Varriant> result = (
     from x in variants
      where x.CultureID   == CultureID || x.CultureID   == "en-GB"
     select x


).ToList();

Varriant r = null;
if(result.Length <= 1){
   r = result.FirstOrDefault();

}else{
     r = result.First(x=>x.CultureID   !="en-GB");
}

Answer 3

You can use the fact that booleans sort as [false, true], so the following query will sort all matches not equal to en-GB first, and then en-GB.

var result = (from x in variants
              where x.CultureID == cultureID || x.CultureID == "en-GB"
              orderby x.CultureID == "en-GB"
              select x).First();

This query will give an error if not even any "en-GB" entry exists for a given key (First demands at least one record), but as you stated, it always exists. If you're not so sure it does, use FirstOrDefault.

Note that many LINQ providers support this idiom, it is not just usable for IEnumerable/Lists. So you can even combine the filter on key and culture as part of the LINQ query, so you could let the database sort it out for you, and you don't get the entire list of translations back, just the one you want:

var result = (from x in db.Translations
              where x.Key == someKey
                && (x.CultureID == cultureID || x.CultureID == "en-GB")
              orderby x.CultureID == "en-GB"
              select x).First();