search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.65k stars 3.15k forks source link

LINQ creation fails when using join and user defined function #34266

Open KillerBoogie opened 1 month ago

KillerBoogie commented 1 month ago

I have multi-language text that is stored as JSON in SQL Server fields. A user defined function GetLanguage returns the data from the JSON field in the preferred language. When returning data from one table only it works fine. When I create a join I'm getting the error that the LinQ can't be created.

public class VCDbContext : DbContext
{
    public DbSet<EventVisibility> EventVisibility { get; set; }
    public DbSet<Event> Event { get; set; }
    public string? GetLanguage(object json, string preferredLanguages)
    => throw new NotSupportedException($"{nameof(GetLanguage)} cannot be called client side");
...
}

This works:

var queryTest1 = dbContext.Event.AsNoTracking()
      .Where(e => request.Title!.Equals(dbContext.GetLanguage(e.Title, @prefLangs)!))
      .Select(e => dbContext.GetLanguage(e.Title, prefLangs)!);

var test1 = await queryTest1.ToListAsync();

This causes runtime error as stated below:

record EventWithVisibility(Event Event, EventVisibility Visibility);

var queryTest2 = dbContext.Event.AsNoTracking()
.Join(
    dbContext.EventVisibility.AsNoTracking(),
    e => e.Visibility,
    v => v.Id,
    (e, v) => new EventWithVisibility(e, v)
).Where(e => request.Title!.Equals(dbContext.GetLanguage(e.Event.Title, @prefLangs)!))
.Select(e => dbContext.GetLanguage(e.Event.Title, prefLangs)!);

var test2 = await queryTest2.ToListAsync();

Stack Trace:

System.InvalidOperationException: The LINQ expression 'DbSet<Event>()
    .Join(
        inner: DbSet<EventVisibility>(),
        outerKeySelector: e => (object)e.Visibility,
        innerKeySelector: e0 => (object)e0.Id,
        resultSelector: (e, e0) => new TransparentIdentifier<Event, EventVisibility>(
            Outer = e,
            Inner = e0
        ))
    .Where(ti => __8__locals1_request_Title_0.Equals(__P_1.GetLanguage(
        json: new EventWithVisibility(
            ti.Outer,
            ti.Inner
        ).Event.Title,
        preferredLanguages: __prefLangs_2)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at VC.WebApi.Features.Events.GetEvents.GetEventsRepository.GetEvents(GetEventsRequest request) in C:\Users\micro\source\repos\VC-Web-API\VC.WebApi\Features\Events\GetEvents\GetEventsRepository.cs:line 62
   at VC.WebApi.Features.Events.GetEvents.GetEventsHandler.Handle(GetEventsQuery query, CancellationToken cancellationToken) in C:\Users\micro\source\repos\VC-Web-API\VC.WebApi\Features\Events\GetEvents\GetEventsHandler.cs:line 14
   at VC.WebApi.Infrastructure.MediatR.Piplines.LoggingBehavior`2.Handle(TRequest request, RequestHandlerDelegate`1 next, CancellationToken cancellationToken) in C:\Users\micro\source\repos\VC-Web-API\VC.WebApi\Infrastructure\MediatR\Pipelines\LoggingBehaviour.cs:line 23
   at VC.WebApi.Features.Events.GetEvents.GetEventsController.GetAllEvents(String eTags, IEnumerable`1 preferredLanguages, Nullable`1 LaterThan, Nullable`1 EarlierThan, List`1 eventTypes, List`1 eventTypeNames, List`1 locations, List`1 locationNames, List`1 dances, List`1 danceNames, List`1 danceLevels, List`1 danceLevelNames, List`1 danceLevelNrs, String title, String subTitle, EnvironmentDTO environmentDTO) in C:\Users\micro\source\repos\VC-Web-API\VC.WebApi\Features\Events\GetEvents\GetEventsController.cs:line 53
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at VC.WebApi.Infrastructure.Middleware.Exceptions.ExceptionMiddleware.InvokeAsync(HttpContext httpContext) in C:\Users\micro\source\repos\VC-Web-API\VC.WebApi\Infrastructure\Middleware\Exceptions\ExceptionMiddleware.cs:line 22

For testing I tried to remove the Where clause and then got the error that Get`Language can't be called client side. I don't understand why this is happening.

 var queryTest3 = dbContext.Event.AsNoTracking()
.Join(
    dbContext.EventVisibility.AsNoTracking(),
    e => e.Visibility,
    v => v.Id,
    (e, v) => new EventWithVisibility(e, v)
)
.Select(e => dbContext.GetLanguage(e.Event.Title, prefLangs)!);

var test3 = await queryTest3.ToListAsync();

I have another query that uses query syntax and works fine:

var query = from d in dbContext.DanceLevel.AsNoTracking()
      join l in dbContext.Level.AsNoTracking()
      on d.LevelId equals l.Id
      where d.Id.Value == danceLevelId
      select new DanceLevelSLResponse(
          d.Id.Value,
          d.Code.Value,
          d.DanceId.Value,
          l.Id.Value,
          dbContext.GetLanguage(l.Name, @prefLangs)!,
          dbContext.GetLanguage(l.ShortName, @prefLangs)!,
          dbContext.GetLanguage(d.Description, @prefLangs)!,
          dbContext.GetLanguage(d.ShortDescription, @prefLangs)!,
          d.Created,
          d.LastModified
          );

danceLevelSLResponse = await query.SingleOrDefaultAsync();

I needed to use fluent syntax for the problematic query, because the Where clauses should be added dynamically depending on provided query parameters in the web API. Is this a problem with fluent syntax?

Here is the EF Core configuration:

public class EventConfiguration : IEntityTypeConfiguration<Event>
{
    public void Configure(EntityTypeBuilder<Event> entity)
    {
        entity.ToTable("Event", VCDbContext.Schema);

        entity.Property<int>("TId").UseIdentityColumn().HasColumnOrder(0);
        entity.HasIndex("TId").IsUnique().IsClustered();

        entity.HasKey(e => e.Id).IsClustered(false);
        entity.Property(e => e.Id).HasColumnName(nameof(EventId)).HasColumnOrder(1);

        entity.Property(e => e.Title).HasColumnName("Title").HasMaxLength(4000);

        //TODO nullable SubTitle
        entity.Property(e => e.SubTitle).HasColumnName("SubTitle").IsRequired(false).HasMaxLength(4000);

        entity.Property(e => e.Description).HasColumnName("Description").IsRequired(false);

        entity.Property(e => e.EventTypeId);

        entity.Property(e => e.EventTypeName).HasColumnName("EventTypeName");

        entity.OwnsMany(e => e.Locations, loc =>
        {
            loc.ToTable("EventLocation");
            loc.WithOwner().HasForeignKey("EventId");
            loc.Property<long>("Id").HasColumnOrder(0);
            loc.HasKey("Id");
            loc.Property(l => l.LocationId).IsRequired();
            loc.Property(l => l.Name).IsRequired().HasMaxLength(4000);
            loc.OwnsOne(l => l.Address, addr =>
            {
                addr.Property(a => a.DeliveryInstruction)
                    .HasColumnName("DeliveryInstruction")
                    .HasMaxLength(Address.MaxLengthDeliveryInstruction);
                addr.Property(a => a.Street)
                    .HasColumnName("Street")
                    .IsRequired()
                    .HasMaxLength(Address.MaxLengthStreet);
                addr.Property(a => a.StreetNumber)
                    .HasColumnName("StreetNumber")
                    .IsRequired()
                    .HasMaxLength(Address.MaxLengthStreetNumber);
                addr.Property(a => a.StreetAffix)
                    .HasColumnName("StreetAffix")
                    .HasMaxLength(Address.MaxLengthStreetAffix);
                addr.Property(a => a.ZipCode)
                    .HasColumnName("ZipCode")
                    .IsRequired()
                    .HasMaxLength(Address.MaxLengthZip);
                addr.Property(a => a.City)
                    .HasColumnName("City")
                    .IsRequired()
                    .HasMaxLength(Address.MaxLengthCity);
                addr.Property(a => a.State)
                    .HasColumnName("State")
                    .HasMaxLength(Address.MaxLengthState);
                addr.Property(a => a.CountryId)
                    .HasColumnName("CountryId")
                    .HasMaxLength(CountryId.LengthCountryId).IsRequired()
                    .HasMaxLength(Address.MaxLengthCountryId);
                addr.Property(a => a.CountryName)
                    .HasColumnName("CountryName")
                    .IsRequired()
                    .HasMaxLength(CountryName.MaxLength);
            });
            loc.OwnsOne(e => e.CoverImage, img =>
            {
                img.Property(c => c.Title)
                    .HasColumnName("CoverImageTitle")
                    .IsRequired();

                img.Property(c => c.Uri)
                    .HasColumnName("CoverImageUri")
                    .IsRequired();
            });
        });

        entity.OwnsMany(e => e.Dances, a =>
        {
            a.ToTable("EventDance");
            a.WithOwner().HasForeignKey("EventId");
            a.Property<long>("Id").HasColumnOrder(0);
            a.HasKey("Id");
            a.Property(ed => ed.Name).IsRequired().HasMaxLength(4000);
            a.Property(ed => ed.Category).IsRequired().HasMaxLength(4000);
            a.Property(ed => ed.DanceLevelId);
            a.Property(ed => ed.DanceLevelCode);
            a.Property(ed => ed.LevelId);
            a.Property(ed => ed.LevelName).IsRequired().HasMaxLength(4000);
            a.Property(ed => ed.LevelShortName).IsRequired().HasMaxLength(4000);
            a.Property(ed => ed.IncludeHigherLevels);
        });

        entity.OwnsMany(e => e.Teachers, a =>
        {
            a.ToTable("EventTeacher");
            a.WithOwner().HasForeignKey("EventId");
            a.Property<long>("Id").HasColumnOrder(0);
            a.HasKey("Id");
            a.Property(t => t.TeacherId).IsRequired();
            a.Property(t => t.ArtistName).IsRequired();
            a.Property(t => t.ArtistShortName).IsRequired();
        });

        entity.Property(e => e.Visibility).HasConversion<string>();
        entity.HasOne<EventVisibility>()
            .WithMany()
            .HasForeignKey(e => e.Visibility)
            .HasConstraintName("FK_Event_EventVisibilityEnum")
            .OnDelete(DeleteBehavior.NoAction);

        entity.Property(e => e.Version).IsRowVersion().HasConversion<byte[]>();
    }
}

public class EventVisibilityConfiguration : IEntityTypeConfiguration<EventVisibility>
{
    public void Configure(EntityTypeBuilder<EventVisibility> entity)
    {
        entity.ToTable("EventVisibility", VCDbContext.Schema);

        entity.HasKey(e => e.Id).IsClustered();
        entity.Property(e => e.Id).HasColumnName("EventVisibilityId")
            .HasMaxLength(16).HasConversion<string>();

        entity.Property(e => e.Name).HasMaxLength(EventVisibilityName.MaxLength * 5); ;
    }
}

EF Core version: 8.0.7 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET 8.0 Operating system: Win 10 IDE: Visual Studio 2022 17.10.1

KillerBoogie commented 1 month ago

I found a way to get the desired result. Instead of creating a join I use a nested query. Below is just a fraction of the full query.

string prefLangs = string.Join(",", request.PreferedLanguages);

var eventQuery = dbContext.Event.AsNoTracking();
var eventVisibilityQuery = dbContext.EventVisibility.AsNoTracking();

var eventQuery = eventQuery.Where(e => request.Title.Equals(dbContext.GetLanguage(e.Title,  prefLangs)!))

var query = eventQuery.Select(e => new EventDTO(
   dbContext.GetLanguage(e.Title, @prefLangs)!,
   eventVisibilityQuery.Where(v => v.Id == e.Visibility).Select(v => dbContext.GetLanguage(v.Name, prefLangs)!).First(),
)
cincuranet commented 1 day ago

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.