How to refactor multiple similar Linq-To-Sql queries?

Question

Read before downvoting or closing: This almost exact duplicate of a previous question of mine exists with the solely purpose to rephrase the previous question to the Linq-To-Sql scope. All answers contained in the previous question are valid for the Linq scope, but are invalid in the Linq-To-SQL scope.

Suppose I have the two following Linq-To-SQL queries I want to refactor:

var someValue1 = 0;
var someValue2= 0;
var query1 = db.TableAs.Where( a => a.TableBs.Count() > someValue1 )
              .Take( 10 );
var query2 = db.TableAs.Where( a => a.TableBs.First().item1 == someValue2)
              .Take( 10 );

Note that only the Where parameter changes. There is any way to put the query inside a method and pass the Where parameter as an argument?

All the solutions posted in the previous question have been tried and failed in runtime when I try to enumerate the result.

The exception thrown was: "Unsupported overload used for query operator 'Where'"

Answer 1

Absolutely. You'd write:

public IQueryable<A> First10(Expression<Func<A,bool>> predicate)
{
    return db.TableAs.Where(predicate).Take(10);
}

(That's assuming that TableA is IQueryable<A>.)

Call it with:

var someValue1 = 0;
var someValue2= 0;
var query1 = First10(a => a.TableBs.Count() > someValue1);
var query2 = First10(a => a.TableBs.First().item1 == someValue2);

I believe that will work...

The difference between this and the answers to your previous question is basically that this method takes Expression<Func<T,bool>> instead of just Func<T,bool> so it ends up using Queryable.Where instead of Enumerable.Where.

Answer 2

Check out this question and my answer about using the dynamic linq extensions

Answer 3

I just wrote some code on fly, Tags is the table and Tag is the entity type.

    DataClasses1DataContext db = new DataClasses1DataContext();

    Expression<Func<Tag, bool>> c1 = t => true;
    Console.WriteLine(db.Tags.Where(c1).Count());

    Func<Tag, bool> c2 = t => true;
    Console.WriteLine(db.Tags.Where(c2).Count());

Both worked without run time error. I noticed the difference between Expression> and Func. So this is not what I expected.

Jon, any idea?

Answer 4

If you really want reusability you can try to write your own operators. E.g. instead of repeatedly writing:

var query = 
Products
 .Where(p => p.Description.Contains(description))
 .Where(p => p.Discontinued == discontinued);

you can write simple methods:

public static IEnumerable<Product> ByName(this IEnumerable<Product> products, string description)
{
 return products.Where(p => p.Description.Contains(description));
}


public static IEnumerable<Product> AreDiscontinued(IEnumerable<Product> products, bool isDiscontinued)
{
 return products.Where(p => p.Discontinued == discontinued);
}

and then use it like this:

var query = Products.ByName("widget").AreDiscontinued(false);