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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

  1. 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>();
  1. 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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x