Entity Framework Core LINQ Tree Expression Problem with Concat

Question

So I have a IQueryable extension that does a little more than this block of code. Essentially I'm combining of bunch of strings and afterwards doing a Contains on them. The problem I'm running into is that Entity Framework Core doesn't support System.String.Concat, and executes that part of the query locally which is definitely not what I want.

Here's the little loop I was using to add these different strings together:

                List<Expression> stringExpressionsToConcat = new List<Expression>();
                foreach (var member in memberExpressions)
                {
                    stringExpressionsToConcat.Add(member);
                    stringExpressionsToConcat.Add(spaceConstant);
                }
                //call the concat to create the final term to search on
                NewArrayExpression arrayExpression = Expression.NewArrayInit(typeof(string), stringExpressionsToConcat);
                searchStringExpression = Expression.Call(concatMethod, arrayExpression);

This is working client side, but will not get compiled for Entity to SQL. I had the same problem on an Order By clause where I was doing something like this:

.ThenByDescending(e => string.Concat(e.FirstName, " ", e.LastName))

This obviously also did not translate to Entity to SQL, since it's exactly what I'm building in my expression tree. However, changing it to this....

.ThenByDescending(e => e.FirstName + " " + e.LastName)

Does translate to Entity to SQL. So I'm wondering how I can create the same expression represented in the code above that correctly gets sent to SQL. I've tried using Expression.Add but add is not supported on string types in the expression builder. Is this possible or is there some extra code in Entity Framework that makes this possible? I've tried to explore the source code on GitHub but it's a little bit overwhelming to find exactly where it is happening.

Richard Cawthon Richard Cawthon · Accepted Answer · 2020-03-11T19:50:13

Aha! I have figured it out!! This expression tree exhibits the same behavior and sends the data to SQL!

Modified code below:

//more than one member has the property, we need to combine them with spaces in between
List<Expression> stringExpressionsToConcat = new List<Expression>();
foreach (var member in memberExpressions)
{
    stringExpressionsToConcat.Add(member);
    stringExpressionsToConcat.Add(spaceConstant);
}

searchStringExpression = stringExpressionsToConcat[0];
for (int i = 1; i < stringExpressionsToConcat.Count; i++)
{
    searchStringExpression = Expression.Add(searchStringExpression, 
    stringExpressionsToConcat[i], typeof(string).GetMethod("Concat", new[] { 
    typeof(string), typeof(string) }));
}

That change goes from Entity Framework throwing the warning in 2.2 (and the error in 3.1) to translating it into the below SQL code!

([e].[MemberExpression1] + N' ') + [e].[MemberExpression2]) + N' ') + [e].[MemberExpression3]) + N' ')

This is exactly how Entity Framework is generating the SQL clause I talked about in my answer with the Order By! I'm not sure what the reasoning is...yet, but if you're looking to create your own expression trees that add together strings that the Entity Framework Core tree visitor can translate into sql this is it!

Credit to this answer for pointing me in the correct direction: https://stackoverflow.com/a/3858421/5245385

Entity Framework Core LINQ Tree Expression Problem with Concat

2 Answers