Efcore.pg: Fix type inference for ILikeExpression when escape char isn't specified
Hi,
With the following table:
CREATE TABLE public."Values"
(
"Guid" bigint NOT NULL,
"RawValue" text COLLATE pg_catalog."default",
CONSTRAINT "Values_pkey" PRIMARY KEY ("Guid")
)
The following sample:
``` C#
public class EFCoreTestContext : DbContext
{
public DbSet
public static readonly ILoggerFactory DebugLoggerFactory = LoggerFactory.Create(builder => { builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole(); });
[DbFunction("unaccent")]
public static string Unaccent(string value) => throw new NotSupportedException("Esse m茅todo n茫o deve ser utilizado em memoria. Ele existe somente para ser traduzido para sql.");
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
.UseLoggerFactory(DebugLoggerFactory)
.UseNpgsql("Host=localhost;Database=EFCoreTest;Username=test;Password=test");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Value>(eb =>
{
eb.ToTable("Values");
eb.HasKey(e => e.Guid);
});
}
}
public class Value
{
public long Guid { get; set; }
public string RawValue { get; set; }
}
class Program
{
static void Main(string[] args)
{
using (var db = new EFCoreTestContext())
{
var result0 = (from e in db.Values
where EF.Functions.ILike(e.RawValue, "%a%")
select e).ToList();
/* Generates:
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE v."RawValue" ILIKE '%a%'
*/
var result1 = (from e in db.Values
where EFCoreTestContext.Unaccent(e.RawValue).Contains("a")
select e).ToList();
/* Generates:
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE STRPOS(unaccent(v."RawValue"), 'a') > 0
*/
var result2 = (from e in db.Values
where EF.Functions.ILike(EFCoreTestContext.Unaccent(e.RawValue), "%a%")
select e).ToList();
/* I hoped for... :
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE unaccent(v."RawValue") ILIKE '%a%'
*/
}
Console.ReadKey();
}
}
I'm getting this error:
> Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
I was expecting for this query translated:
``` SQL
SELECT v."Guid", v."RawValue"
FROM "Values" AS v
WHERE unaccent(v."RawValue") ILIKE '%a%'
Thanks in advance,
TanielianVB
All 8 comments
This looks like a general, non-Npgsql related error - can you please move this to https://github.com/dotnet/efcore/issues? A full stack trace would be useful too.
roji
on 25 Jun 2020
Actually before moving this... Your code doesn't trigger the exception for me. I copy-pasted it almost exactly as above, using version 3.1.3 of the provider. Can you please double-check?
Full code
```c#
public class EFCoreTestContext : DbContext
{
public DbSet
public static readonly ILoggerFactory DebugLoggerFactory = LoggerFactory.Create(builder => { builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole(); });
[DbFunction("unaccent")]
public static string Unaccent(string value) => throw new NotSupportedException("Esse m茅todo n茫o deve ser utilizado em memoria. Ele existe somente para ser traduzido para sql.");
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
.UseLoggerFactory(DebugLoggerFactory)
.UseNpgsql(@"Host=localhost;Username=test;Password=test");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Value>(eb =>
{
eb.ToTable("Values");
eb.HasKey(e => e.Guid);
});
}
}
public class Value
{
public long Guid { get; set; }
public string RawValue { get; set; }
}
public static class Program
{
static void Main(string[] args)
{
using (var db = new EFCoreTestContext())
{
var result0 = (from e in db.Values
where EF.Functions.ILike(e.RawValue, "%a%")
select e).ToList();
/* Generates:
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE v."RawValue" ILIKE '%a%'
*/
var result1 = (from e in db.Values
where EFCoreTestContext.Unaccent(e.RawValue).Contains("a")
select e).ToList();
/* Generates:
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE STRPOS(unaccent(v."RawValue"), 'a') > 0
*/
var result2 = (from e in db.Values
where EF.Functions.ILike(EFCoreTestContext.Unaccent(e.RawValue), "%a%")
select e).ToList();
/* I hoped for... :
* SELECT v."Guid", v."RawValue"
* FROM "Values" AS v
* WHERE unaccent(v."RawValue") ILIKE '%a%'
*/
}
Console.ReadKey();
}
}
```
roji
on 25 Jun 2020
Firstly, thank you for your response.
Secondly, sure! Double-checking right now.
TanielianVB
on 25 Jun 2020
Copy-pasted your code (changed just the database connection string) and still got the error:
Error's full stack trace
> System.NullReferenceException: Object reference not set to an instance of an object.
> at Microsoft.EntityFrameworkCore.Query.ExpressionExtensions.InferTypeMapping(SqlExpression[] expressions)
> at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlExpressionFactory.ApplyTypeMappingOnILike(ILikeExpression ilikeExpression)
> at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping)
> at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression)
> at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlExpressionFactory.ILike(SqlExpression match, SqlExpression pattern, SqlExpression escapeChar)
> at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlLikeTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList`1 arguments)
> at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>c__DisplayClass4_0.
My packages are:
<ItemGroup>
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.5" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" />
</ItemGroup>
This did turn out to be a bug in the provider, thanks!
roji
on 25 Jun 2020
No sr., thank YOU!
Such a quick fix. Thanks a lot. XD
TanielianVB
on 25 Jun 2020
My pleasure. You should be able to work around this for now by specifying an empty string as your escape character in EF.Functions.ILike.
roji
on 25 Jun 2020
Thanks a lot!
TanielianVB
on 25 Jun 2020
Related issues
newbiecse
路
4Comments
Drago95
路
3Comments
pgrm
路
4Comments
bikeladam
路
3Comments
Joseph-Anthony-King
路
3Comments