Using Linq DynamicQuerable for a Not IN sql type operation

Question

I am using the Linq DynamicQuerable code. I have an array of integers and a string representing the field I want to use as the excluding filter.

For example

IQuerable GetItemsWithoutExcluded(IQuerable qry, string primaryKey, List<int> excludedItems) {
        // psuedo code
        return qry.Where("p=>! p.primaryKey in excludedItems");
    }

Any idea how I would accomplish this using DynamicQuerable?

score 2 · Answer 1 · answered Aug 17 '12 at 13:33

You can do this without dynamic-LINQ. Dynamic-LINQ is not a full fledged citizen of the framework. And by changing the way you accomplish this task you can use a more effective filter. The way you have it now the method needs to be called for each exclusion.

Try something like this instead

        List<int> allKeys = new List<int>{1,2,3,4,5,6};            
        List<int> excluded = new List<int>{ 1, 2, 3 };

        var query = from included in allKeys
                    where !excluded.Contains(included)
                    select included;

        foreach (var item in query)
            Console.WriteLine(item);

The problem here is I would have to execute the query and hit the database then filter. The goal is to filter in the original query. — Kenoyer130, Aug 17 '12 at 14:53

Lukazoid · Answer 2 · 2012-08-17T15:10:39.197

I would implement this like the following using ordinary LINQ:

public static IQueryable<T> Exclude<T>(this IQuerable<T> qry, Expression<Func<T, int>> keySelector, List<int> excludedItems)
{
    var keyGroups = qry.GroupBy(keySelector);
    var includedGroups = keyGroups.Where(g => !excludedItems.Contains(g.Key));
    return includedGroups.SelectMany(g => g);
}

Then it can be used like so:

public class MyClass
{
    public int Key { get; set; }
}

IQueryable<MyClass> source = // Get source data (DataContext/ObjectContext/ISession etc.)
var excludedKeys = new List<int> { 1, 3, 11 };
var result = source.Exclude(item => item.Key, excludedKeys);

Update for Dynamic LINQ

IQuerable GetItemsWithoutExcluded(IQuerable qry, string primaryKey, List<int> excludedItems)
{
    if(excludedItems.Count == 0)
        return qry;

    var keyChecks = excludedItems.Select(i => String.Format("p.{0} != {1}", primaryKey, i));
    var constraint = String.Join(" && ", keyChecks )
    return qry.Where("p => " + constraint);
}

@Kenoyer130 Posted an updated answer, it probably isn't the most efficient but it should work and is hopefully enough to get rid of that downvote :) — Lukazoid, Aug 17 '12 at 15:13

Using Linq DynamicQuerable for a Not IN sql type operation

2 Answers2