dotnet / dotnet/efcore

Calling DbFunction does not work if the input parameter is EF.Function.

Open
#30,159 5 comments 0 reactions 0 assignees View on GitHub
area-query customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

If you try to call DbFunction and pass one of the EF.Function parameters, an exception will be thrown:
```
Unhandled exception. System.InvalidOperationException: The 'Like' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rew
rite the query to avoid client evaluation of arguments so that method can be translated to server.
at Microsoft.EntityFrameworkCore.DbFunctionsExtensions.Like(DbFunctions _, String matchExpression, String pattern)
at Program.$(String[] args) in C:\Users\a.petrenko\Documents\GitHub\Temp\EfCoreDbFunctionBug\EfCoreDbFunctionBug\Program.cs:line 29
```
Function configuration:
```C#
modelBuilder.HasDbFunction(typeof(BlogContext).GetMethod(nameof(SimpleFunc),
new[] { typeof(bool) })!)
.HasName("simple_func");
```
Function call:
```C#
context.SimpleFunc(EF.Functions.Like("abc", "abc"));
```

Repro

```C#
using Microsoft.EntityFrameworkCore;

using var context = new BlogContext();
context.Database.ExecuteSqlRaw(@"
create or replace function simple_func(in_flag boolean)
returns table(out_flag boolean)
language plpgsql
as $function$
begin
return query
select in_flag;
end;

$function$
;
");

foreach (var blog in context.SimpleFunc(true)) // It works
{
Console.WriteLine(blog);
}

foreach (var blog in context.SimpleFunc(true)
.Where(f => f.Flag == EF.Functions.Like("abc", "abc"))) // It works
{
Console.WriteLine(blog);
}

foreach (var blog in context.SimpleFunc(EF.Functions.Like("abc", "abc"))) // Does not work
{
Console.WriteLine(blog);
}

public record Blog(bool Flag);

public class BlogContext : DbContext
{
public IQueryable SimpleFunc(bool inFlag)
=> FromExpression(() => SimpleFunc(inFlag));

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql("Host=localhost;Port=5432;Database=postgres;Username=postgres;Password=postgres");

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(e =>
{
e.HasNoKey();
e.Property(o => o.Flag).HasColumnName("out_flag");
});

modelBuilder.HasDbFunction(typeof(BlogContext).GetMethod(nameof(SimpleFunc),
new[] { typeof(bool) })!)
.HasName("simple_func");
}
}
```

EF Core version: 7.0.2
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL (Npgsql.EntityFrameworkCore.PostgreSQL 7.0.1)
Target framework: .NET 6.0
Operating system: Microsoft Windows [Version 10.0.17763.316]
IDE: JetBrains Rider 2022.3.1

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.