Where Clause is not applied when converting the query from LINQ (EF) to SQL

automapper entity-framework-core linq sql sql-server-profiler

Question

StudentHelper.cs

I've helper class called StudentHelper where It calls Repository Class to get all the student details. Below is the code to get the student details.

model = await _repo.FilterAsync<StudentRbCasesLink>(s =>
containsTest(options, list, s, countyId) && s.student.Deleted = false && s.student.studentId !=0, 
s=> s.student,
s => s.studentRbCase,
s=> s.studentRbCase.CurrentCounty,
s=> s.studentRbCase.Abawdtype);

dto => _mapper.Map<IEnumerable<Objects.DTO.StudentRbCases>>(model);

containsTest is a method which does all the filtering based on the input parameters. This method gives back Boolean value based on the filter that is applied and that Boolean value is sent to repo class.

 private bool containsTest(string options, Dictionary<string,string> list, 
 StudentRbCasesLink s,int countyId){
   if(options.contains("FirstName")){
   firstName = list["FirstName"].ToLower().ToString();
   predicate = firstName != "&county" ? 
   (s.student.FirstName.ToLower().contains(firstName)).Tostring() : 
    "false";
    }
    if(options.contains("LastName")){
    lastName  = ............................
    }
    ........................
    return convert.ToBoolean(predicate);
 }

Below is the actual FilterAsync Method that is in repo class. RepositoryClass.cs

public async Task<IQueryable<T>> FilterAsync<T>(Expression<Func<T,bool>> predicate, params Expression<Func<T,object>>[] includes) where T : class
{
  return await Task.Run(()=> {
    var query = _context.Set<T>().where(predicate).AsQueryable();
    return includes.Aggregate(query, (current, includeProperty)
                    => current.Include(includeProperty).asQueryable());
  });
}

Let me describe the problem clearly. I'm doing a search by parameters functionality here. As soon as the studenthelper class is getting all parameters, it is hitting filterasync in studenthelper which in turn hits the actual method filterasync in Repository class. So, when I see the SQL profile for the converted SQL, It is showing the SQL Query with all the joins that are included in the includes in filterasync but coming to where condition it is applying only s.student !=0 condition in SQL which is making the query very slow (Not applying all the conditions/filters in where condition making it slow). It is not applying any conditions that are mentioned in containsTest method at the time SQL is generated but once the cursor hits the next Auto-mapper( is used to convert models to dtos) line, cursor is hitting containsTest Method and doing all the filters. Under the hoods, SQL is getting all the records and putting them in-memory and applying the filters when hitting Auto-mapper.

I've seen other posts where people suggested to put Expression> predicate instead of Func predicate. But, my code already have Expression. Can any one help me how to write the containsTest Method , so that where condition is applied while it is converted to SQL Query.Note that EF used is EntityFrameworkcore(1.1.2) Thanks for the help.

1
1
7/30/2018 5:06:44 PM

Accepted Answer

Here is how I fixed the issue: First of all, As per the suggestions above, I’ve removed the containsTest method and written everything in same method.

 var predicate = PredicateBuilder.True<StudentRbcasesLink>();
 if (options.Contains("FirstName"))
 {
   firstName = list["FirstName"].ToLower().ToString();
   Expression<Func<StudentRBbasesLink, bool>> expressionFirstName = x => 
   x.Student.FirstName.StartsWith(firstName);
   if (firstName != "&county") {
      predicate =  predicate.And(x => x.Student.FirstName.StartsWith(firstName));
   }                  
 }

 if (options.Contains("LastName"))
 {
   lastName = list["LastName"].ToLower().ToString();
   Expression<Func<StudenRbcasesLink, bool>> expressionLastName = x => 
               x.Student.LastName.StartsWith(lastName);
   predicate = !string.IsNullOrEmpty(firstName) ? predicate.And(x => 
               x.Participant.LastName.StartsWith(lastName)) : expressionLastName;
   ...........
 }
 ...............................

The big question is, how to dynamically do Logical And, Logical OR on expressions.Below is the code that made it possible.( PredicateBuilder is the class that is used to dyanmically do the logical operations on expression predicates).

public static class PredicateBuilder
    {
        /// <summary>    
        /// Creates a predicate that evaluates to true.    
        /// </summary>    
        public static Expression<Func<T, bool>> True<T>() { return param => true; }

        /// <summary>    
        /// Creates a predicate that evaluates to false.    
        /// </summary>    
        public static Expression<Func<T, bool>> False<T>() { return param => false; }

        /// <summary>    
        /// Creates a predicate expression from the specified lambda expression.    
        /// </summary>    
        public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

        /// <summary>    
        /// Combines the first predicate with the second using the logical "and".    
        /// </summary>    
        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.AndAlso);
        }

        /// <summary>    
        /// Combines the first predicate with the second using the logical "or".    
        /// </summary>    
        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.OrElse);
        }

        /// <summary>    
        /// Negates the predicate.    
        /// </summary>    
        public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
        {
            var negated = Expression.Not(expression.Body);
            return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
        }

        /// <summary>    
        /// Combines the first expression with the second using the specified merge function.    
        /// </summary>    
        static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // zip parameters (map from parameters of second to parameters of first)    
            var map = first.Parameters
                .Select((f, i) => new { f, s = second.Parameters[i] })
                .ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with the parameters in the first    
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // create a merged lambda expression with parameters from the first expression    
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        class ParameterRebinder : ExpressionVisitor
        {
            readonly Dictionary<ParameterExpression, ParameterExpression> map;

            ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
            {
                this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
            }

            public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
            {
                return new ParameterRebinder(map).Visit(exp);
            }

            protected override Expression VisitParameter(ParameterExpression p)
            {
                ParameterExpression replacement;

                if (map.TryGetValue(p, out replacement))
                {
                    p = replacement;
                }

                return base.VisitParameter(p);
            }
        }
    }  

The problem before was, I was doing logical operations on booleans instead of expressions. And logical operations on expressions can only be acheived through the above prediateBuilder class.

Below is the code for FilterAsync

model = await _repo.FilterAsync<StudentRbCasesLink>(
predicate
s => s.student.studentId !=0, 
s => s.student,
s => s.studentRbCase,
s => s.studentRbCase.CurrentCounty,
s => s.studentRbCase.Abawdtype);

dto => _mapper.Map<IEnumerable<Objects.DTO.StudentRbCases>>(model);

After changing the code, I went to SQL Server Profiler and tested how the query is formed. It has all the necessary joins with where clauses provided where clause is changing dynamically as per the search.

After this, the search is hapenning in 3 seconds when combinations(meaning 2 or 3 feilds at a time) were given.Note :Previously, Search used to take 1 to 2 minutes. Creating some indexes and statistics for the necessary columns reduced it to less than 1 second.

0
9/20/2018 10:35:44 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow