Feature: Support for EF Core 2.0's EF.Functions.Like()

[ascott18]

Are there any plans to support the Like() function from EF Core 2.0? https://blogs.msdn.microsoft.com/dotnet/2017/08/14/announcing-entity-framework-core-2-0/

I'd like to be able to write something like this (contrived example):

db.Customers
    .Where("EF.Functions.Like(City, @0)", "L%do%")

[StefH]

I'll take a look.

[japerr]

Any idea when this enhancement will be in beta or production?

[StefH]

@japerr Logic is added, get latest version from github.

See console app ConsoleAppEF2.0 for details how to use it.

[czielin]

Will this work in EF 6? If not is there a way to add a version of the SQL LIKE function that will work with work with wildcards?

I'm trying the following...

queryable.Where("parts.any(DynamicFunctions.Like(subpart.type, @0)) ", "%test");

However, I see this error when trying to execute...

System.Linq.Dynamic.Core.Exceptions.ParseException: 'No property or field 'DynamicFunctions' exists in type 'Part''

[fchiumeo]

not working

var j = await UnidadTrabajo
                           .Set<Competidora>()
                           .Where("DynamicFunctions.Like(RazonSocial, \"%a%\")")
                           .ToListAsync();

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where Like([_1].RazonSocial, "%a%")' could not be translated and will be evaluated locally.

@StefH this still not working

[yyjdelete]

One workaround is to add both Microsoft.EntityFrameworkCore.EF and Microsoft.EntityFrameworkCore.DbFunctionsExtensions as customTypes and use it as DbFunctionsExtensions.Like(EF.Functions, "matchExpression", "pattern")

[StefH]

@fchiumeo Make sure to include the NuGet Microsoft.EntityFrameworkCore.DynamicLinq because this functionality is only present in EF Core.

@yyjdelete This is already implemented: https://github.com/StefH/System.Linq.Dynamic.Core/blob/master/src/Microsoft.EntityFrameworkCore.DynamicLinq/DynamicFunctions.cs

[StefH]

@czielin No support possible for EF 6.

[yyjdelete]

@StefH Yes, it can get the same data, but it's evaluated locally instead of be translated to SQL. You can see the same warn as @fchiumeo described in the log output with ConsoleApp_netcore2.0_EF2.0.1(or 2.1), and see no like in the output SQL.

[StefH]

OK. I'm able to reproduce. (Sometimes it seems the console logging is lagging behind or not showing all?)

@yyjdelete Do you have a fix ? Because I tried

public static bool Like(string matchExpression, string pattern) => Microsoft.EntityFrameworkCore.DbFunctionsExtensions.Like(EF.Functions, matchExpression, pattern);

And that does not work.

[yyjdelete]

@StefH I don't have, I just use the extension methods as normal static method directly now.

            public HashSet<Type> GetCustomTypes()
            {
                    return new HashSet<Type>()
                    {
                        //typeof(SqlServerDbFunctionsExtensions),
                        typeof(DbFunctionsExtensions),
                        typeof(EF),
                    };
            }
var dynamicCarsLike1 = context.Cars.Where(config, "DbFunctionsExtensions.Like(EF.Functions, Brand, \"%a%\")");

[StefH]

In that case, the workaround would be just to add these here: https://github.com/StefH/System.Linq.Dynamic.Core/blob/EF_Functions_Like/src/System.Linq.Dynamic.Core/Parser/PredefinedTypesHelper.cs#L66

This works fine: I did trace the sql commands with sql profiler.

[mightyvoodoo]

Hello, @StefH, I wanted to ask is any solution here now? Will be glad for any answers or workarounds... Thanks!

.NET Core 2.1

Doing:

using System.Linq.Dynamic.Core;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.DynamicLinq;

var result = _dbContext.Entry(entry).Collection("related").Query().Where("DynamicFunctions.Like(related, @0)", query);

Getting:

ParseException: No applicable method 'Like' exists in type 'DynamicFunctions'

\ UPD:

Doing:

Where("DbFunctionsExtensions.Like(EF.Functions, related, @0)", query);

Getting:

ParseException: No property or field 'DbFunctionsExtensions' exists in type 'related'

[StefH]

I think I still need to update some code which I started at Jul 29, 2018 in branch https://github.com/StefH/System.Linq.Dynamic.Core/tree/EF_Functions_Like

After this weekend I'll take a look if this works.

[mightyvoodoo]

Thank you for quick response, @StefH

First exception raised because of my fault. I figured out some more:

Where("DynamicFunctions.Like(related, @0)", query);

gives

...could not be translated and will be evaluated locally.

how it was said earlier.

But:

... No property or field...

raises now, when calling like you pointed earlier too.

Probably, I must add specified public function to one of my related classes. Will check it later.

\ If you could take a look, it would be great... :coffee: on me :smiley:

\ UPD: \ Everythink worked like you discussed. Thanks for responsing.

[ghost]

@StefH I don't have, I just use the extension methods as normal static method directly now.

            public HashSet<Type> GetCustomTypes()
            {
                    return new HashSet<Type>()
                    {
                        //typeof(SqlServerDbFunctionsExtensions),
                        typeof(DbFunctionsExtensions),
                        typeof(EF),
                    };
            }
var dynamicCarsLike1 = context.Cars.Where(config, "DbFunctionsExtensions.Like(EF.Functions, Brand, \"%a%\")");

Hey guys !! Thanks for all your work, I used your method (as stated above) and it worked perfectly, however, the generated SQL is not using parameters, its making a Text SQL and the generated query is not re-using query cache, any ideas ?

[doxmo]

Hey guys, this is my solution. I hope that will be useful.

query = query.Where(q => EF.Functions.Like(q.GetType().GetProperty("FieldName").GetValue(q, null).ToString(), "%Pattern%"));

[webpony2016]

@anumeetsoni You're right, the solution works well. @StefH The same question with @anumeetsoni , how to get a parameterized query? The UseParameterizedNamesInDynamicQuery option does not work for this Like case. Thanks.

[SocVi100]

@doxmo This works, and can be parameterized. @StefH It seems a good solution but fails extrangely when the dataset contains some NULLs among the values in the queried column... Any idea why?

[StefH]

@SocVi100 I'll have to read this issue again, I'm a bit lost on the current status and the exact issue...

[SocVi100]

@StefH I'll describe my concrete issue just to put you on precedents:

qry = qry.Where(tipo => EF.Functions.Like(tipo.GetType().GetProperty("FieldOfTheIQueriable").GetValue(tipo, null).ToString(), "%something%"));

where qry is an IQueriable...

The qry performs with no problems if there's no nulls among the values stored in the "FieldOfTheIQueriable" column, otherwise fails with a NullReferenceException.

[Bjego]

Your suggesttions work like a charm - even for other providers like devarts EF extentions...

public class DynamicLinqProvider : IDynamicLinkCustomTypeProvider
  {
    public HashSet<Type> GetCustomTypes()
    {
      HashSet<Type> types = new HashSet<Type>();
      types.Add(typeof(OracleFunctions));
      return types;
    }

    public Type ResolveType(string typeName)
    {
      throw new NotImplementedException();
    }
  }

using the provider:

var whereClause = $"OracleFunctions.ToChar(COLUMNNAME, \"FORMAT\", \"NLS_DATE_LANGUAGE='ENGLISH'\")";

return source.Where(new ParsingConfig { CustomTypeProvider = new DynamicLinqProvider() }, whereClause);

[StefH]

Ok. Closing...

[Bjego]

Maybe you could update the Wiki and explain how to add "Dbhelpers" with such a selfwritten IDynamicLinkCustomTypeProvider. Could reduce the number of new issues here 😉

[hisuwh]

Anyone have any luck getting this to parameterise properly?