Geek Answers Handbook

Func <t, bool> vs Manual expression efficiency in C # lambda

Please take a look at the following lines:

1. in this case, I print the where statement directly in the method

public List<User> GetUsers() { return _entity.Where(x => x.Id == 1).ToList(); }

Executed sql query:

 SELECT [Extent1].[Id] AS [Id], [Extent1].[Username] AS [Username], [Extent1].[Password] AS [Password], [Extent1].[Email] AS [Email], [Extent2].[Id] AS [Id1] FROM [dbo].[Account_Users] AS [Extent1] LEFT OUTER JOIN [dbo].[Account_Profiles] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId] WHERE 1 = [Extent1].[Id]

2. in this case, I used Func for generic, where the sentence

 public List<User> GetUsers(Func<User, bool> where) { return _entity.Where(where).ToList(); } var users = _acc.GetUsers(x => x.Id == 1);

Executed sql query:

 SELECT [Extent1].[Id] AS [Id], [Extent1].[Username] AS [Username], [Extent1].[Password] AS [Password], [Extent1].[Email] AS [Email], [Extent2].[Id] AS [Id1] FROM [dbo].[Account_Users] AS [Extent1] LEFT OUTER JOIN [dbo].[Account_Profiles] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]

as you can see, in case 2, where the WHERE 1 = [Extent1].[Id] , and therefore entire records are stored in memory. Do you have any ideas why the where clause doesn't translate into sql query?
I want to use Func<t, bool> in .Where() , so it will be generic and there is no need to create functions for each request.
is there any way to use .Where(Func<t, bool>) and also view the sentence translated where in the sql query?

+5
source share
3 answers

If you want your lambda to run in SQL, you need to pass it as an expression, not a function:

 public List<User> GetUsers(Expression<Func<User, bool>> where) { return _entity.Where(where).ToList(); } var users = _acc.GetUsers(x => x.Id == 1);

If you're wondering what the difference is (after all, the lambda itself looks the same), check out this question .

+10
source

Instead

 public List<User> GetUsers(Func<User, bool> where)

you should use

 public List<User> GetUsers(Expression<Func<User, bool>> where)

When you use Expression , the Entity Framework can correctly convert it to SQL. On the other hand, when you use Func , the Entity framework does not know how to translate it into SQL, so it uses in-memory processing.

+3
source

This is because in these two cases, the compiled code contains a call to two different extension methods: Queryable.Where and Enumerable.Where respectively. The Queryable class contains extension methods for processing data from various data sources, including external sources, while Enumerable contains extension methods for processing objects in memory. Queryable version of Where accepts Expression , not Func .

A Lamda expression can be implicitly converted to either Expression or Func , as in other answers you just need to accept an Expression instance as an argument to the function.

+3
source

More articles:


All Articles