diffrence between running query with Func and Expression Func [duplicate]

Question

I have been searching on internet to find out the difference between Func and Expression Func, somehow i got the point,the first one is just a function gets the data and then apply the function in memory but the second,translate it to sql and run it in the database,after i run this two queries:

 public IEnumerable<T> SelectAll(Expression< Func<T, bool>> predicate)
    {         
        return table.Where(predicate).ToList();
    }

     public IEnumerable<T> SelectAll(Func<T, bool> predicate)
    {

        return table.Where(predicate).ToList();

    }

i put the breakpoint on return,for first one it returns 12 rows,for second it returns 1200 row,the predicate is :

s=>s.id="12345"

the second one,apply the predicate after it get the data,my question is,we usualy should use the expression func when we deal with DB?

Dislocate

"but the second,translate it to sql" more precisely translates to something that can ultimately be translated into SQL (or something else) – Peeress

While the code in the two methods looks the same, it's actually calling different static extension Where methods - from Queryable and from Enumerable. – Dysgenic

Answer 1

my question is,we usualy should use the expression func when we deal with DB?

Yes, because you want to give as much information to the SQL server so that it can optimize the database access and you want to minimize the data that is returned from the SQL Server (removing all the unnecessary data, and aggregating the data that can be easily aggregated by the SQL Server) because moving data from the SQL Server to the .NET machine is even that a "work".

Clearly if something is very difficult to do in SQL (like string manipulation), then moving it to .NET is acceptable.

Note that with LINQ you don't have fine control on the resulting query, that can easily become a "multi-level-beast", nor you can be really sure which part of the query will be executed on the SQL server and which part will be executed locally (EF Core for example often executes GROUP BY locally), nor can access many advanced features of SQL (all the partitioning methods for example).

Giuseppe

thanks for the nice explanations,so according to you and if i got it right,imagine i put a stopwatch on queries using the both scenarios,retrieving time could be diffrent? – Dislocate

@mortezasol Yes, but you'll see the difference only if the query is a little complex, or the projection (select) removes many columns, or the where removes many rows... In the end the difference is normally based on how much data the SQL can not return. – Giuseppe

Answer 2

A Func<T> is a delegate while a Expression<Func<T>> is an expression.

Expressions are kind of abstractions.

An Expression<T> can be compiled into a Func<T>:

Expression<Func<int, int>> expr = a => a + 1;
Func<int,int> func = expr.Compile();
var res = func(1); // (1+1) = 2

So think of an expression as kind of a tree that represents an expression.

The tree is abstract and you can do lot's of things with it.

So how can we gain leverage of this behavior when it comes to SQL?

You can create expression that someone (Entity Framework) will translate to SQL.

So when you have an expression like:

users.Where(u => u.Name.StartsWith("a"));

It could be translated to SQL like:

select * from Users where Name like 'a%'

Since someone wrote code that translate expressions into sql, it supports numerous methods but not all. So sometimes it will tell you that it cannot translate you method into SQL and you will have to provide an Expression<Func<T>> instead or run the method after the SQL runs (using Linq to Objects).

Bottom line, in most cases, you would prefer to work with Expressions over IQueryables and create SQL queries with filters that will run on the DB rather than fetching lot's of DB entries and filtering them in your code.

Kellby