LINQ-to-Entities WHERE condition not executed on sql server

Question

I have followed the book of Adam Freeman to learn about ASP.NET MVC 5. I have implemented the repository pattern as described in the book. I'm using WHERE and log the SQL commands with the SQL-EventProfiler, then I will see no WHERE condition.

This is my example code, for the repository I have used Ninject:

 public interface IHelpRepository {

        IEnumerable<Help> Helps { get; }

    }

    public class HelpRepository : IHelpRepository {
        private HelpDBContext context = new HelpDBContext();

        public IEnumerable<Help> Helps {
            get { return context.Helps; }
        }
    }

    public class Help {

        [Required]
        [Key]
        public int Id { get; set; }

        [Required]
        public string Title { get; set; }

        [Required]
        public string Description { get; set; }
    }

    public class HelpController : Controller {

        private IHelpRepository HelpRepository;

        public HelpController (IHelpRepository helpRepository) {
            this.HelpRepository= helpRepository;
        }

        public ActionResult HelpDescription(int Id) {

            Help help = helpRepository.Helps.Where(h => h.Id == Id).SingleOrDefault<Help>();

            return View("HelpDescription", help);
        }
    }

The resulting SQL-Command (XEventProfiler):

SELECT [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[Description] AS [Description] FROM [dbo].[Helps] AS [Extent1]

Why is the WHERE not included in the resulting SQL command?

A generic Repository is actually an *anti*pattern when used on top of a higher-level ORM like EF. Your problem is one of the consequences. A DbContetx isn't a connection or a database model, it's a Unit-of-Work controlling several entities. A DbSet is *already* a Repository. By casting to `IEnumerable` you're actually executing the query and loading everything in memory — Panagiotis Kanavos, Jan 14 '21 at 10:08
Right now, your repository class simply delegates to the DbSet classes, while preventing you to combine entities. There are no benefits from such anemic repository classes, just problems. Check [No need for repositories and unit of work with Entity Framework Core](https://gunnarpeipman.com/ef-core-repository-unit-of-work/) to see what's wrong — Panagiotis Kanavos, Jan 14 '21 at 10:10

Hammad Shabbir · Accepted Answer · 2021-01-14T10:34:04.773

3

public IEnumerable Helps { get { return context.Helps; } }

You are returning IEnumerable here and at this point the query gets executed. There are two things you can get away with it

First option is to return IQueryable and use where filter and then check profiler like this

public IQueryable<Help> Helps {
            get { return context.Helps; }
        }

Help help = helpRepository.Helps.Where(h => h.Id == Id).SingleOrDefault<Help>();

Second method is to create a repository method which filters in the the method and returns IEnumerable

public IEnumerable<Help> Where(Expression<Func<Help,bool>> where) 
{
     return context.Helps.Where(where);
}

To understand the difference between IQueryable and IEnumerable just go through the following link

IQueryable vs IEnumerable

IQueryable always perform queries on the data source context while IEnumerable works in memory, after data gets fetched from data source provider

edited Jan 14 '21 at 10:34

answered Jan 14 '21 at 09:55

Hammad Shabbir

722
1
6
13

There's not much point in adding methods that simply delegate to the underlying DbContext. That's a problem with the question's code though, not the answer – Panagiotis Kanavos Jan 14 '21 at 10:12
Thank you, this is working. I'm wondering why that this isn't covered in the book. They showing a fully working application, but they never point out that their code return all entries at once... That would have been funny if I had used that in my application with millions of rows in a table, and I would have been wondered about the poor performance. – Phoniex Jan 14 '21 at 10:31

LINQ-to-Entities WHERE condition not executed on sql server

1 Answers1