Linq: Simple Boolean function returns linq Exception

Question

I have a Linq query that looks something like this:

var query = from x in table where SomeFunctionReturnsBool() select;

private bool SomeFunctionReturnsBool()
{
    return true;
}

This returns and exception that says "SomeFunctionReturnsBool has no supported translation to SQL". I get that this is because it wants to treat "SomeFunctionReturnsBool" as an expression to evaluate as SQL, but it can't.

Although this Linq query isn't complicated, the real ones are. How can I accomplish what I'm trying to do here, which is to break out pieces of the query to hopefully make it more readable?

Jeff

UPDATE Good answers. I am trying now to work with expressions instead, but this code gets me "cannot resolve method Where(lambda expression)":

var query = from x in table where SomeFunctionReturnsBool() select x;

private Expression<Func<EligibilityTempTable, bool>> SomeFunctionReturnsBool
{
  return (x) => true;
}

Answer 1

You can do this in LINQ-to-SQL by creating a UDF mapped to the data-context; this involves writing TSQL, and use ctx.SomeFunctionblah(...).

The alternative is to work with expression trees - for example, it could be:

Expression<Func<Customer, bool>> SomeFunc() {
    return c => true; // or whatever
}

and use .Where(SomeFunc()) - is that close enough? You can't use the query syntax in this case, but it gets the job done...

---

Added dodgy Where method to show how you might use it in query syntax. I don't suggest this is fantastic, but you might find it handy.

using System;
using System.Linq;
using System.Linq.Expressions;

static class Program
{
    static void Main()
    {
        using (var ctx = new NorthwindDataContext())
        {
            ctx.Log = Console.Out;
            // fluent API
            var qry = ctx.Customers.Where(SomeFunc("a"));
            Console.WriteLine(qry.Count());

            // custom Where - purely for illustration
            qry = from c in ctx.Customers
                  where SomeFunc("a")
                  select c;
            Console.WriteLine(qry.Count());
        }
    }
    static IQueryable<T> Where<T>(this IQueryable<T> query,
        Func<T, Expression<Func<T, bool>>> predicate)
    {
        if(predicate==null) throw new ArgumentNullException("predicate");
        return query.Where(predicate(default(T)));
    }
    static Expression<Func<Customer, bool>> SomeFunc(string arg)
    {
        return c => c.CompanyName.Contains(arg);
    }
}

Answer 2

Don't use query syntax for this.

var query = table.Where( x => SomeFunction(x) );

Answer 3

I would just break them out like so:

Expression<Func<Table, bool>> someTreeThatReturnsBool = x => true;

var query = from x in table where someTreeThatReturnsBool select x;

You could create functions that pass around expression trees.

Answer 4

Basically, "out of the box", you can't have LINQ-to-SQL execute queries that have custom functions in them. In fact only some native methods that can be translated to SQL can be used.

The easiest way around this can unfortunately affect performance depending on how much data you're bringing back from the DB.

Basically, you can only use custom functions in WHERE statments if the data has already been loaded into memory, i.e, SQL have already executed.

The quickest fix for your example would look like this:

var query = from x in table.ToList() where SomeFunctionReturnsBool() select;

Notice the ToList(). It executes the SQL and puts the data into memory. You can now do whatever you want in the WHERE statement/method.

Answer 5

Another way is to use Expression<Func<YourType, bool>> predicate...

var query = from x in table where SomeFunctionReturnsBool() select;

Edit: I don't usually do it the way I've shown above... I was just getting that from the code above. Here is the way I usually implement it. Because then you can tack on additional Enumerable methods or comment them out during debugging.

var results = table.Where(SomeFunctionReturnsBool())
    .OrderBy(yt => yt.YourProperty)
    //.Skip(pageCount * pageSize) //Just showing how you can easily comment out parts...
    //.Take(pageSize)
    .ToList(); //Finally executes the query...

private Expression<Func<YourType, boo>> SomeFunctionReturnsBool()
{
    return (YourType yt) => yt.YourProperty.StartsWith("a")
        && yt.YourOtherProperty == true;
}

I prefer to use the PredicateBuilder which allows you to build an expression to be used in your Where...