CODEWITHSUNDEEP
c#sql-serverentity-frameworkrepository-pattern
ajay
ajay

Reputation: 163

Entity Framework Generic Repository vs Direct Queries on Context

Generic Repository and Entity Framework: I am planning to use a Generic Repository to handle queries on the context and noticed (using SQL server profiler) that the filter expression is not being translated and included in the SQL query made to the database. Just wondering whether this kind of overload is expected when using generic repository? Repository Code:

public class BaseRepositories<TEntity> where TEntity : class
{

    private readonly DbContext _CurrentObjectContext;
    private readonly DbSet<TEntity> _EntityObjectSet;

    public BaseRepositories(DbContext currentObjectContext)
    {
        _CurrentObjectContext = currentObjectContext;
        _EntityObjectSet = _CurrentObjectContext.Set<TEntity>();
    }

    public DbSet<TEntity> EntityObjectSet
    {
        get
        {
            return _EntityObjectSet;
        }
    }

    public IQueryable<TEntity> Query(Func<TEntity, bool> expression, params Expression<Func<TEntity, object>>[] navigationProperties)
    {
        var ReturnSetQueryable = _EntityObjectSet.Where(expression);
        return ReturnSetQueryable.AsQueryable();
    }

}

Calls made to the repository and on context and the SQL query from the profiler a) Using Repository:

Code:

        var UserRepository = new BaseRepositories<user>(new DbContext());


        var UsersQuery = UserRepository.Query(v => v.name == "ajayr");

        var FirstUser = UsersQuery.FirstOrDefault();

Query form the Sql Profiler:

SELECT [Extent1].[guid] AS [guid], [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[password] AS [password], [Extent1].[first_name] AS [first_name], [Extent1].[middle_name] AS [middle_name], [Extent1].[last_name] AS [last_name], [Extent1].[email_address] AS [email_address], [Extent1].[url] AS [url], [Extent1].[account_enabled] AS [account_enabled], [Extent1].[user_id] AS [user_id], [Extent1].[last_update] AS [last_update], [Extent1].[deleted] AS [deleted], [Extent1].[employee_id] AS [employee_id], [Extent1].[rec_source] AS [rec_source], [Extent1].[last_login_date] AS [last_login_date], [Extent1].[last_email_update] AS [last_email_update], [Extent1].[invalid_email_adr] AS [invalid_email_adr] FROM [dbo].[users] AS [Extent1]

b) Direct Query on the Context:

Code: 

       DbContext db = new DbContext();

        DbSet<user> usersset = db.Set<user>();

       var UserQueryWithFilter =  usersset.Where(v => v.name == "ajayr");

       var FirstUser = tesst.FirstOrDefault();

Query form the Sql Profiler:

SELECT TOP (1) [Extent1].[guid] AS [guid], [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[password] AS [password], [Extent1].[first_name] AS [first_name], [Extent1].[middle_name] AS [middle_name], [Extent1].[last_name] AS [last_name], [Extent1].[email_address] AS [email_address], [Extent1].[url] AS [url], [Extent1].[account_enabled] AS [account_enabled], [Extent1].[user_id] AS [user_id], [Extent1].[last_update] AS [last_update], [Extent1].[deleted] AS [deleted], [Extent1].[employee_id] AS [employee_id], [Extent1].[rec_source] AS [rec_source], [Extent1].[last_login_date] AS [last_login_date], [Extent1].[last_email_update] AS [last_email_update], [Extent1].[invalid_email_adr] AS [invalid_email_adr] FROM [dbo].[users] AS [Extent1] WHERE 'ajayr' = [Extent1].[name]

Upvotes: 3

Views: 1863

Answers (1)

Ben
Ben

Reputation: 2484

You provide function, not an expression as a parameter. I think you need to slightly change a syntax

public IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> expression, params Expression<Func<TEntity, object>>[] navigationProperties)
{
    var ReturnSetQueryable = _EntityObjectSet.Where(expression);
    return ReturnSetQueryable.AsQueryable();
}

Upvotes: 1

Related Questions