none
Code First - linq FirstOrDefault() query not create sql WHERE RRS feed

  • Question

  • Hi

    I am creating a simple repository

    public abstract class BaseRepository<T> : IRepository<T> where T:class
    {
    
    	public T Single(Func<T, bool> predicate)
    	{
    		using (NorthWindContext context = new NorthWindContext())
    		{
    			return context.Set<T>().FirstOrDefault(predicate);
    		}
    	}
    	
    }

    I simplified the code, only define the used method
    but when I use

    EmployeeRepository repoEmployee = new EmployeeRepository();
    
    Employee employeeSel = repoEmployee.Single(x => x.EmployeeID == 1);

    I can see the sql query not define the WHERE, the employee is recovered well, but the sql profiler show me

    SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country]
    FROM [dbo].[Employees] AS [Extent1]

    why the EF not define the WHERE ? it recover all rows and then apply single filter in object memory

    is it correct?

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Wednesday, June 19, 2013 9:50 PM

Answers

  • Because when you explicitly pass a Func<T, bool>> to the Where method you are basically calling Enumerable.Where (in-memory filtering) but when you write "item => item.EmployeeID == id" the compiler casts your lambda expression into an Expression<Func<T bool>> and you call IQueryable.Where.

    Only the IQueryable is translated into SQL while IEnumerable.Where is LINQ to Objects rather than LINQ to Entities.

    Monday, June 24, 2013 9:16 PM

All replies

  • Use Enumerable.Where before you call FirstOrDefault:

    return context.Set<T>().Where(predicate).FirstOrDefault();


    Explanation found here: http://stackoverflow.com/questions/3540410/ef-4-0-weird-behaviour-of-firstordefault-method

    Thursday, June 20, 2013 8:20 AM
  • hi

    I tried with this approach, but the result is the same

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Thursday, June 20, 2013 9:26 PM
  • hi

    I continue test and I can see that if I use

            public List<Employee> GetById(int id)
            {
                using (NorthWindContext context = new NorthWindContext())
                {
                    return (from item in context.Employees
                            where item.EmployeeID == id
                            select item).ToList();
    
                }
            }

    or

    return (from item in context.Set<Employee>()
                            where item.EmployeeID == id
                            select item).ToList();

    both generete the query in the profile
    exec sp_executesql N'SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[EmployeeID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=1

    work perfect, but in the generic repository I need to use linq method to assign lambda filter

    Why the linq extension method not generate the correct query ?

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Sunday, June 23, 2013 3:10 PM
  • Using EF5 and the .NET Framework 4.5, the following returns a single item on my machine:

    public virtual T GetSingle(Func<T, bool> where)
            {
                T item = null;
                using (var context = new Entities())
                {
                    item = context.Set<T>().FirstOrDefault(where);
                }
                return item;
            }
    

    Sunday, June 23, 2013 8:16 PM
  • hi Magnus

    thanks for yours answers

    yes, the line:  context.Set<T>().FirstOrDefault(where);

    return only one item, but are you verified the query generated ?

    if you use the sql server profile, EF not define WHERE in the query executed when use FirstOrDefault()

    but if you use linq the WHERE is defined, do you know why?

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Sunday, June 23, 2013 11:17 PM
  • Hi

    I continue with the test, I create the code

        public class EmployeeRepository : BaseRepository<Employee>
        {
    
            public Employee GetById(int id)
            {
                using (NorthWindContext context = new NorthWindContext())
                {
                    return context.Set<Employee>().FirstOrDefault(item => item.EmployeeID == id);
                }
            }
    
            public T GetById<T>(Func<T, bool> predicate) where T : class
            {
                using (NorthWindContext context = new NorthWindContext())
                {
                    return context.Set<T>().FirstOrDefault(predicate);
                }
            }
    
        }

    the method

    public Employee GetById(int id)

    generate the query

    exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[EmployeeID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=1

    the method

    public T GetById<T>(Func<T, bool> predicate) where T : class

    generate the query

    SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country]
    FROM [dbo].[Employees] AS [Extent1]

    why the generic Set<> not define WHERE in the query ?

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Monday, June 24, 2013 1:00 PM
  • Because when you explicitly pass a Func<T, bool>> to the Where method you are basically calling Enumerable.Where (in-memory filtering) but when you write "item => item.EmployeeID == id" the compiler casts your lambda expression into an Expression<Func<T bool>> and you call IQueryable.Where.

    Only the IQueryable is translated into SQL while IEnumerable.Where is LINQ to Objects rather than LINQ to Entities.

    Monday, June 24, 2013 9:16 PM
  • hi

    Excellent

    the Expression<> was the key missing

    many thanks

    greetings


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Tuesday, June 25, 2013 1:15 PM