EF Core LINQ Where with extension mehods are not translated to SQL

.net-core c# entity-framework-core linq linq-to-entities

Question

Given the following query:

context.ToolingOrders
.Include(r => r.ToolingOrderDetails)
.Include(r => r.PurchaseOrder)
.Where(r => r.VendorId.EqualsOrNull(filter.VendorId) && 
            r.PoNumber.ContainsOrEmpty(filter.PoNumber))

I use these extension methods to save some code:

 public static class FilterExtensions
 {
        public static bool ContainsOrEmpty(this string source, string toCheck)
        {
            return string.IsNullOrWhiteSpace(toCheck) || source?.IndexOf(toCheck, StringComparison.OrdinalIgnoreCase) >= 0;
        }

        public static bool EqualsOrNull(this int source, int? toCheck)
        {
            return !toCheck.HasValue || source == toCheck;
        }
 }

The problem is, due to these extension methods, the Where part is not translated into SQL.

I use .Net Core 2.2, wich has this client side evaluation feature, which totally hides this issue, but the SQL profiler shows it anyway.

Is there any way to make this work, or I have to write every piece of the where part explicitly?

1
0
2/13/2020 3:28:11 PM

Popular Answer

You could extend the IQueryable for your specific object.
Take the following object for instance:

public class MyObject
{
    public string MyProperty;
}

You could write an extension like this:

public static class MyQueryExtension
{
    public static IQueryable<MyObject> WhereMyPropertyNull(this IQueryable<MyObject> queryable)
    {
        return queryable.Where(obj => obj.MyProperty == null);
    }
}

And use it like this:

var queryable = new List<MyObject>().AsQueryable();
var result = queryable.WhereMyPropertyNull().ToList();

EDIT

Based on some feedback i updated my answer to handle generics.

public static class Extensions
    {
        public static IQueryable<TEntity> EqualOrNull<TEntity, TProperty>(this IQueryable<TEntity> source, Func<TEntity, TProperty> selector, TProperty match)
        {
            return source.Where(entity => Match(selector.Invoke(entity), match));
        }

        private static bool Match<TEntity, TProperty>(TEntity entity, TProperty match)
        {
            if (entity == null) {
                return true;
            } else {
                return entity.Equals(match);
            }
        }
    }

It can be used to pass the value of a property to the where statement:

            var list = new List<MyObject>();
            list.Add(new MyObject {MyProperty = "Test"});
            list.Add(new MyObject {MyProperty = "NoMatch"});
            list.Add(new MyObject {MyProperty = null});

            var result = list.AsQueryable()
                .EqualOrNull(o => o.MyProperty, "Test")
                .ToList();
0
2/13/2020 3:28:55 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