Using Lambda expression from a separate class in SELECT clause of LINQ query c# Projection-CodePudding

How to have the select query projection to a lambda expression in Entity framework..

In repo class

database.Employees.Where(s => s.EmpID == curEmp.ID)
               .Select(s => new EmployeeModel
                   {
                       Value = s.Name,
                       Text = s.ID
                       // I have 40 fields and 10 join table projections here so much code
                   });

Since I have many fields to project, so created a separate class with the lambda expressions for those long select projections.

public class ProjectionQueries
{
      public static readony Fun<Employee, EmployeeModel> GetEmployeeAsModel = (emp) 
            => new EmployeeModel
             {
                Value = s.Name,
                Text = s.ID...        
                ... 
                ..
                ..
                Address = new AddressModel{.....},
                Country = new CountryModel{.....}
              };
}

Then called the above lambda expression from the select clause of the repository method.

database.Employees.Where(s => s.EmpID == curEmp.ID)
               .Select(x => ProjectionQueries.GetEmployeeAsModel(x));

This way it is well segregated the code. But when I do this I get an error saying

The client projection contains a reference to a constant expression of the System.Func<*,*>. This could potentially cause a memory leak; consider assigning this constant to a local variable and using the variable in the query instead.

Can't use this way to make my code less in the repository class. ?

CodePudding user response：

You could separate this out as a static extension method.

 public static class EmployeeExtensions{ 
     public static IQueryable<EmployeeModel> AsEmployeeModels(this IQueryable<Employee> emps){
         return emps.Select(emp=> new EmployeeModel
                     {
                        Value = emp.Name,
                        Text = emp.ID... 
                      });
        }
    }

and use it as:

database.Employees.Where(s => s.EmpID == curEmp.ID).AsEmployeeModels();

CodePudding user response：

The @gokul-panigrahi answer show a solution with extension method, that I advice to use because I found the syntax smoothest.

The error is :

The client projection contains a reference to a constant expression of the System.Func<,>. This could potentially cause a memory leak; consider assigning this constant to a local variable and using the variable in the query instead.

The error indicate EF Core can't translate System.Func<*,*> to SGBD language. EF Core client evaluation. A solution is to execute the untranslatable part in client :

database.Employees
    // 1) Prepare Query
    .Where(s => s.EmpID == curEmp.ID)
    // 2) Execute query on server
    .AsEnumerable() 
    // 3) Next is execute in client
    .Select(x => ProjectionQueries.GetEmployeeAsModel(x));

See the ms-doc for more information about Client vs Server Evaluation

But client evaluation could result in poor performance. In your case, if the projection can limit the number of column read in database (and limit the data transmit on network).

A other solution, it is to modify the type of GetEmployeeAsModel :

public static readony Expression<Fun<Employee, EmployeeModel>> GetEmployeeAsModel = ...

Thus EF Core can try translate the expression to SGBD's language. Try because the expression can use some term untranslatable like Func<*,*>.

What is the difference between Func and Expression<Func>?

A Func is delegate (trivially a pointer to a zone code in memory). EF Core don't know how translate a delegate.

A Expression is a specific structure of instruction, that is possible to explore :

Expression<Func<int,int>> expression = (x) => x % 2;

// Explore the expression
var operation = expression.Body as BinaryExpression;
var leftPart = operation.Left as ParameterExpression;
var rightPart = operation.Right as ConstantExpression;

// Translate
Console.WriteLine("@"   leftPart.Name);
// @x
Console.WriteLine(operation.NodeType);
// modulo
Console.WriteLine(rightPart.Value);
// 2

EF Core do this to translate the expression to SGBD's language, but sometime the expression contains a term unknown by EF Core. Then this produce a error like above.