Linq Conditional .Any() Select

Question

How can I perform a conditional select on a column value, where I have a preference over which value is returned. If I can't find the top choice, I settle on the next, if available, and then if not the next, etc. As it looks right now, it would take 3 total queries. Is there a way to simplify this further?

var myResult = string.Empty;

if (myTable.Where(x => x.ColumnValue == "Three").Any())
{
    myResult = "Three"; // Can also be some list.First().Select(x => x.ColumnValue) if that makes it easier;
}
else if (myTable.Where(x => x.ColumnValue == "One").Any())
{
    myResult = "One";
}
else if (myTable.Where(x => x.ColumnValue == "Two").Any())
{
    myResult = "Two";
}
else
{
    myResult = "Four";
}

Aside you can pass a lamda to Any, `.Any(x => x.ColumnValue == "Two"). — Ash Burlaczenko, Aug 14 '13 at 21:48
FYI - you don't need the `.Where`: `.Any(x => x.ColumnValue == "Three")` — Austin Salonen, Aug 14 '13 at 21:48

score 7 · Accepted Answer · edited May 23 '17 at 12:09

7

You could use a string[] for your preferences:

string[] prefs = new[]{ "One", "Two", "Three" };
string myResult = prefs.FirstOrDefault(p => myTable.Any(x => x.ColumnValue == p));
if(myResult == null) myResult = "Four";

Edit Enumerable.Join is a very efficient hash table method, it also needs only one query:

string myResult = prefs.Select((pref, index) => new { pref, index })
    .Join(myTable, xPref => xPref.pref, x => x.ColumnValue, (xPref, x) => new { xPref, x })
    .OrderBy(x => x.xPref.index)
    .Select(x => x.x.ColumnValue)
    .DefaultIfEmpty("Four")
    .First();

Demo

edited May 23 '17 at 12:09

Community

1
1

answered Aug 14 '13 at 21:49

Tim Schmelter

450,073
74
686
939

Still does three queries at worst, no? – Simon Belanger Aug 14 '13 at 21:51
@SimonBelanger: I thought OP's asks for a way to make the code more readable(_"simplify this further"_). It's also more maintanable if the list is actually long. – Tim Schmelter Aug 14 '13 at 21:57
@SimonBelanger: Added another approach which should be less readable but much more efficient. – Tim Schmelter Aug 14 '13 at 22:09
Are you sure you can `Join` a `String[]` to a `DbSet`? (I guess the OP may not be using Entity Framework, but the terminology in the question suggests that he is...) – Steve Ruble Aug 15 '13 at 09:10
@SteveRuble: i think he can use `WHERE(x => prefs.Contains(x.ColumnName))` then and let the database optimize. – Tim Schmelter Aug 15 '13 at 09:17

Austin Salonen · Answer 2 · 2013-08-14T23:15:59.580

I wrote an extension method that effectively mirrors Tim Schmelter's answer (was testing this when he posted his update. :-()

public static T PreferredFirst<T>(this IEnumerable<T> data, IEnumerable<T> queryValues, T whenNone)
{
    var matched = from d in data
                  join v in queryValues.Select((value,idx) => new {value, idx}) on d equals v.value
                  orderby v.idx
                  select new { d, v.idx };

    var found = matched.FirstOrDefault();

    return found != null ? found.d : whenNone;
}

// usage:
myResult = myTable.Select(x => x.ColumnValue)
                  .PreferredFirst(new [] {"Three", "One", "Two"}, "Four");

I've written one that will quit a little more early:

public static T PreferredFirst<T>(this IEnumerable<T> data, IList<T> orderBy, T whenNone)
{
    // probably should consider a copy of orderBy if it can vary during runtime
    var minIndex = int.MaxValue;

    foreach(var d in data)
    {
         var idx = orderBy.IndexOf(d);

         if (idx == 0) return d;  // best case; quit now

         if (idx > 0 && idx < minIndex) minIndex = idx;
    }

    // return the best found or "whenNone"
    return minIndex == int.MaxValue ? whenNone : orderBy[minIndex];
}

Richard Vella · Answer 3 · 2013-08-15T22:24:16.223

I use a weighted approach in SQL where I assign a weight to each conditional value. The solution would then be found by finding the highest or lowest weight depending on your ordering scheme.

Below would be the equivalent LINQ query. Note that in this example I am assigning a lower weight a higher priority:

void Main()
{
    // Assume below list is your dataset 
    var myList =new List<dynamic>(new []{
    new {ColumnKey=1, ColumnValue  ="Two"},
    new {ColumnKey=2, ColumnValue  ="Nine"},
    new {ColumnKey=3, ColumnValue  ="One"},
    new {ColumnKey=4, ColumnValue  ="Eight"}});

    var result = myList.Select(p => new 
                            {
                                ColVal =    p.ColumnValue,
                                OrderKey =  p.ColumnValue == "Three" ? 1 : 
                                            p.ColumnValue == "One"   ? 2 : 
                                            p.ColumnValue == "Two"   ? 3 : 4
                             }).Where(i=> i.OrderKey != 4)
                             .OrderBy(i=>i.OrderKey)
                             .Select(i=> i.ColVal)
                             .FirstOrDefault();

    Console.WriteLine(result ?? "Four");
}

score 0 · Answer 4 · answered Aug 14 '13 at 21:50

0

How about something like this:

var results = myTable.GroupBy(x => x.ColumnValue).ToList();

if (results.Contains("Three")) {
    myResult = "Three"; 
} else if (results.Contains("One")) {
    myResult = "One";
} else if (results.Contains("Two")) {
    myResult = "Two";
} else {
    myResult = "Four";
}

answered Aug 14 '13 at 21:50

Jeroen Vannevel

43,651
22
107
170

Might as well do it in an hashet and do a _O(1)_ lookup (without the grouping I mean) – Simon Belanger Aug 14 '13 at 21:50

Linq Conditional .Any() Select

4 Answers4