How do I improve the efficiency of the queries executed by this generic Linq-to-SQL data access class?

Question

Hi all,

I have a class which provides generic access to LINQ to SQL entities, for example:

class LinqProvider<T> //where T is a L2S entity class
{
    DataContext context;

    public virtual IEnumerable<T> GetAll()
    {
        return context.GetTable<T>();
    }

    public virtual T Single(Func<T, bool> condition)
    {
        return context.GetTable<T>().SingleOrDefault(condition);
    }
}

From the front end, both of these methods appear to work as you would expect. However, when I run a trace in SQL profiler, the Single method is executing what amounts to a SELECT * FROM [Table], and then returning the single entity that meets the given condition. Obviously this is inefficient, and is being caused by GetTable() returning all rows.

My question is, how do I get the query executed by the Single() method to take the form SELECT * FROM [Table] WHERE [condition], rather than selecting all rows then filtering out all but one? Is it possible in this context?

Any help appreciated,

Lee

Answer 1

Replace Func<...> with Expression<Func<...>>.

Answer 2

This is a pretty lengthy article, but it will probably be a good start. It provides several recommendations.

http://visualstudiomagazine.com/articles/2007/11/01/optimize-linq-to-sql-performance.aspx