Performance issue with fluent query in EF vs SharpRepository

Question

I was having some performance issues using SharpRepository, and after playing around the SQL Query Profiler I found the reason.

With EF I can do stuff like this:

var books = db.Books.Where(item => item.Year == '2016');
if (!string.IsNullorEmpty(search_author))
        books = books.Where(item => item.Author.Contains(search_author);
return (books.ToList());

EF will not really do anything until books is used (last line) and then it will compile a query that will select only the small set of data matching year and author from the db.

But SharpRepository evaluates books at once, so this:

var books = book_repo.Books.FindAll(item => item.Year == '2016');
if (!string.IsNullorEmpty(search_author))
        books = books.Where(item => item.Author.Contains(search_author);
return (books.ToList());

will compile a query like "select * from Books where Year == '2016'" at the first line, and get ALL those records from the database! Then at the second line it will make a search for the author within the C# code... That behaviour can be a major difference in performance when using large databases, and it explains why my queries timed out...

I tried using repo.GetAll().Where() instead of repo.FindAll().... but it worked the same way.

Am I misunderstanding something here, and is there a way around this issue?

Answer 1

You can use repo.AsQueryable() but by doing that you lose some of the functionality that SharpRepository can provide, like caching or and aspects/hooks you are using. It basically takes you out of the generic repo layer and lets you use the underlying LINQ provider. It has it's benefits for sure but in your case you can just build the Predicate conditionally and pass that in to the FindAll method.

You can do this by building an Expression predicate or using Specifications. Working with the Linq expressions does not always feel clean, but you can do it. Or you can use the Specification pattern built into SharpRepository.

ISpecification<Book> spec = new Specification<Book>(x => x.Year == 2016);

if (!string.IsNullorEmpty(search_author)) 
{
    spec = spec.And(x => x.Author.Contains(search_author));
}

return repo.FindAll(spec);

For more info on Specifications you can look here: https://github.com/SharpRepository/SharpRepository/blob/develop/SharpRepository.Samples/HowToUseSpecifications.cs