2

I have these method in my Generic Repository :

public IQueryable<T> Query()
{
    return _dbSet.AsQueryable();
}

public IEnumerable<T> Enum()
{
    return _dbSet.AsEnumerable();
}

If I call these method in my service layer

_repo.Query().Where(w => w.IsActive == true);
_repo.Enum().Where(w => w.IsActive == true);

The Queryable method will call this TSQL Syntax in the database, profiler below :

SELECT 
    [Extent1].[ProjectID] AS [ProjectID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[IsActivity] AS [IsActivity], 
    [Extent1].[IsActive] AS [IsActive]
    FROM [dbo].[Project] AS [Extent1]
    WHERE 1 = [Extent1].[IsActive]

which is expected, but the Enumerable method will select everything in the database without where condition, profiler below :

SELECT 
    [Extent1].[ProjectID] AS [ProjectID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[IsActivity] AS [IsActivity], 
    [Extent1].[IsActive] AS [IsActive]
    FROM [dbo].[Project] AS [Extent1]

It means that the Enumerable will greedily call every record in the database including IsActive = 0 record. Why is this happening? I thought IEnumerable is suppose to be deffered which means it won't do anything until something like ToList() is called.

Any help will be appreciated and apologize for bad english.

tickwave
  • 3,335
  • 6
  • 41
  • 82
  • Are you absolutely certain you're in no way consuming the return value of `Enum()`? (Why would you call it at all if you're not using the result?) Even your `Query` method shouldn't call the database, if you're not using the result. I think there's more to this than you've shown. – Jon Skeet Jul 08 '15 at 07:49
  • 4
    It is deferred but, by switching to `IEnumerable`, you're explicitly saying "I want to do what follows in memory". If you want to apply filters and you want those filters to make it down to the database, you need to keep working with the `IQueryable` that takes *expressions* that it knows how to convert into SQL. – Damien_The_Unbeliever Jul 08 '15 at 07:52
  • @JonSkeet: yeah, because my design pattern is the service layer have full control over the `DbSet`, for example if I need something like `GetActiveProject`, then I will call `_repo.Enum().Where(w => w.IsActive == true);` – tickwave Jul 08 '15 at 07:53
  • 1
    But my point is that you'll be *doing* something with the return value of `Where`, surely - but you're not in the code you've shown. – Jon Skeet Jul 08 '15 at 07:54

0 Answers0