CODEWITHSUNDEEP
c#linqlinq-to-sqlrefactoring
bflemi3
bflemi3

Reputation: 6790

Refactoring linq-to-sql method that returns matched values

I have a simple linq-to-sql statement that matches values in a string[] to the values in a table and returns the matched table values.

_table is of type System.Data.Linq.Table<TEntity>

public IEnumerable<ZipCode> match(IEnumerable<string> values) {
    HashSet<string> valueSet = new HashSet<string>(values);
    return _table.Where(x => valueSet.Contains(x.zipcode);
}

How can I refactor this so that I can pass the field to be used into the Contains()?

My failed attempt:

public IEnumerable<ZipCode> match<T>(IEnumerable<T> values, 
        Expression<Func<ZipCode, T>> field) {
    HashSet<T> valueSet = new HashSet<T>(values);
    return _table.Where(x => valueSet.Contains(field(x)));
}

This produces the error:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

My thought was that I could use the expression tree to give the field for the Contains() but confused on how or if this is even the correct way to use expressions. There is probably a better solution altogether.

Upvotes: 2

Views: 188

Answers (2)

a little sheep
a little sheep

Reputation: 1466

I was able to use the following (in .NET 4.5, but I believe it will work in earlier versions)

static IEnumerable<TTable> Match<TTable, TField>(IQueryable<TTable> table, IEnumerable<TField> values, Expression<Func<TTable, TField>> fieldSelector)
{
    var valuesConstant = Expression.Constant(values.Distinct());

    var callContains = Expression.Call(typeof(Enumerable), "Contains", new[] { typeof(TField) }, new Expression[] { valuesConstant, fieldSelector.Body });

    var whereExpression = Expression.Lambda<Func<TTable, bool>>(callContains, (fieldSelector.Body as MemberExpression).Expression as ParameterExpression);

    return table.Where(whereExpression);
}

You should be able to remove the table parameter I used and use the member variable you have.

Upvotes: 2

david.s
david.s

Reputation: 11403

Instead of Expression<Func<ZipCode, T>> field use Func<ZipCode, T> field:

public IEnumerable<ZipCode> match<T>(IEnumerable<T> values, Func<ZipCode, T> field)
{
    HashSet<T> valueSet = new HashSet<T>(values);
    return _table.Where(x => valueSet.Contains(field(x)));
}

Upvotes: 0

Related Questions