Options for code sharing in Linq2SQL expressions

Question

I have a pair of Linq to SQL queries which contain the same complex Where clause, specifically:

where ((range.MinimumFrequency <= minFreq &&  minFreq <= range.MaximumFrequency)
|| (range.MinimumFrequency <= maxFreq && maxFreq <= range.MaximumFrequency)
|| (range.MinimumFrequency <= minFreq && maxFreq <= range.MaximumFrequency)
|| (range.MinimumFrequency >= minFreq && maxFreq >= range.MaximumFrequency))

And rather than copy and paste this chunk of code all over the place, I wanted to refactor it out into something else that can be shared. I know I can't do this with a normal method as it cannot be translated into SQL, but I also can't get the

Expression < Func<...>>

things described here to work either.

If I simplify the where clause for the purpose of my sanity here, I want to turn

where (range.MinumumFrequency < minFreq)

into an expression, so I tried:

public static Expression<Func<FreqRange, bool>> Overlaps(decimal minMHz, decimal maxMHz)
{
   return (range => range.MinimumFrequency <= minMHz);
}

This appears to compile, but I can't seem to get the where statement to work, I have tried the following:

where FreqRange.Overlaps(minMHz, maxMHz)

but this gives me a compile time error:

Cannot implicitly convert type 'System.Linq.Expressions.Expression>' to 'bool'

Any ideas? Also, assuming we get this working, can I simply extend the lambda expression in the Expression < Func <>> to include the other conditions?

Answer 1

If you use the language-builting LINQ syntax, you're implicitly declaring a lamba expression - and that lamba expression is an Expression<Func <>> - so you don't need the where clause to return the Expression, you need it to be the expression.

e.g.

var q = from row in myTable
        where row.a < aMax
        select row.b;

// which translates to

var q = myTable.Where(row => row.a < aMax).Select(row => row.b);

Now you need to "cache" row => row.a < aMax - not just the value of row.a < aMax, if you will. So, if you were to write something like this...

Expression<Func<?,bool>> cachedExpression = row => row.a < aMax;

var q = from row in myTable
        where cachedExpression
        select row.b;

Well, you're saying "where, given a row, it is true that (given a row, it is true that row.a is less than aMax)". This is nonsense, and won't compile if cachedExpression is of type Expression<Func<?,bool>> and myTable is a LinqToSql provided table. It would translate to something like this:

Expression<Func<?,bool>> cachedExpression = row => row.a < aMax;

//nonsense .Where:
var q = myTable.Where(row => cachedExpression).Select(row => row.b);
//effectively says this:
var q = myTable.Where(row => (row0 => row0.a < aMax)).Select(row => row.b);

Now, this is legal in the sense that it is possible for a linq query provider to implement a whereclause with a non-boolean value, but it's a very odd thing to do; certainly no standard linq providers (such as Linq to Sql) do such a thing.

So what should you do? Something like:

//not .Where(row=>cachedExpression)!
var q = myTable.Where(cachedExpression).Select(row => row.b);

//or, alternatively:
var q = from filteredRow in myTable.Where(cachedExpression)
        select filteredRow.b;

Answer 2

You can also create a method that takes and sends IQueryable. I have used these like views but purely in code.

public class WithProps
{
    public string Prop1 { get; set; }
    public string Prop2 { get; set; }
    public override string ToString()
    {
        return string.Format("{0}-{1}", Prop1, Prop2);
    }
}

public static class Tools
{
    public static IQueryable<WithProps> MyFilter(this IQueryable<WithProps> props)
    {
        return props.Where(p => p.Prop1 == "hi");
    }
}

class Program
{
    static void Main(string[] args)
    {
        var propList = new List<WithProps>()
        {
            new WithProps(){ Prop1="hi", Prop2="there"},
            new WithProps(){ Prop1="hi", Prop2="bye"},
            new WithProps(){ Prop1="hello", Prop2="world"},
            new WithProps(){ Prop1="bye", Prop2="friend"}
        };

        var filtered = propList.AsQueryable().MyFilter();

        Console.WriteLine("propList ===");
        foreach (var item in propList)
            Console.WriteLine(item.ToString());
        Console.WriteLine("filtered ===");
        foreach (var item in filtered)
            Console.WriteLine(item.ToString());
    }
}

...results...

propList ===
hi-there
hi-bye
hello-world
bye-friend
filtered ===
hi-there
hi-bye