Linq To SQL FirstOrDefault fetching all rows

Question

I'm making a generic repository and FirstOrDefault is not generating the sql query that I expect.

Below you can see the relevant part. If I for example use ".Find(x => x.Id == id);" on a table using this repo the generated sql is first fetching all the rows and the doing the firstordefault operation. Where as I would have expected it to generate an where clause.

Any ideas about what Im doing wrong?

public class Repo<T> : IRepo<T> where T : class
{
    protected SBContext Context { get; set; }

    public Repo(SBContext context)
    {
        Context = context;
    }

    protected DbSet<T> DbSet
    {
        get
        {
            return Context.Set<T>();
        }
    }

    public T Find(Func<T, bool> predicate)
    {
        return DbSet.FirstOrDefault(predicate);
    }
}

Yeah I tried that too, made no difference. Im using EF6 by the way, don't know if something has changed there. — JuhaKangas, Nov 28 '13 at 17:10

score 3 · Accepted Answer · edited May 23 '17 at 12:00

Try changing your method signature to

public T Find(Expression<Func<T, Boolean>> predicate)

Explanation:

In System.Linq.Queryable, there is an extension method with this signature:

public static TSource FirstOrDefault<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

and in System.Linq.Enumerable, there is an extension method with this signature:

public static TSource FirstOrDefault<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

When you specify that the Find method takes a Func then you are using the Enumerable extensions rather than Queryable extensions.

There is an implicit cast to an IEnumerable<TSource> when you pass the IQueryable Context.Set<T>() to the extension method as the source parameter and when you cast IQueryable to IEnumerable the expression is immediately evaluated and run against the database, the data is pulled into memory and you are now using Linq-to-Objects

References:

Why would you use Expression<Func<T>> rather than Func<T>?

Re-implementing Linq-To_Objects - AsEmumerable()

Great, that did it! I suppose it has something to do with DbSet being a kind of repository in itself and not recognizing the context for the expression? — JuhaKangas, Nov 28 '13 at 17:29

Linq To SQL FirstOrDefault fetching all rows

1 Answers1