Breaking change in EFCore 5.0 #2731

ghost · 2020-09-28T10:09:16Z

After switching from EF3 to EF5 I get an System.InvalidOperationException on a method that previously worked:

System.InvalidOperationException: "Not enough information to uniquely identify outer element in correlated collection scenario. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns."

I'm afraid it is a bit complicated to reproduce since it is part of a very huge project, but I'll do my very best and can try to create a sample project, if the misbehavior is unknown and not reproducable:

I'm having a Queryable of Offers that gets authorized by a generic method:

public IQueryable<Guid> GetAuthorization(Guid paramUserId, IEnumerable<string> paramHierarchys = null, IEnumerable<string> paramPrivileges = null)
            => UserHierarchys
                .Where(h => h.Id == paramUserId && (paramHierarchys == null || paramHierarchys.Contains(h.Hierarchy)) && (paramPrivileges == null || paramPrivileges.Contains(h.Privilege)))
                .Select(h => h.CanAccessId)
                .Distinct()
            ;

where UserHierarchy is a simple table:

public class UserHierarchy : IUserHierarchy
    {
        public string Hierarchy { get; set; }
        public Guid Id { get; set; }
        public Guid CanAccessId { get; set; }
        public string Privilege { get; set; }
    }

Offer inherits from CreateableBase and its UserIdCreated is joined:

public abstract class CreateableBase : ModelBase, ICreateable
    {
        [KeepValue]
        [IgnoreHasChanged]
        public Guid? UserIdCreated { get; set; }

        [KeepValue]
        [IgnoreHasChanged]
        [ForeignKey("UserIdCreated")]
        public virtual User UserCreated { get; set; }

        [KeepValue]
        [IgnoreHasChanged]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime DateCreated { get; set; } = DateTime.UtcNow;
    }

public IQueryable<T> Authorize<T>(IQueryable<T> paramQueryable, Guid paramUserId, string paramHierarchy = null, string paramPrivilege = null) where T : ICreateable
            => Authorize(paramQueryable, paramUserId, p => p.UserIdCreated.Value, paramHierarchy == null ? null : new string[] { paramHierarchy }, paramPrivilege == null ? null : new string[] { paramPrivilege });

If an authorized Queryable now gets some includes, e.g.

offers = offers
                .Include(p => p.Etikettes)
                .ToArray();

the exception is thrown.

The query works without the Authorize / Include, but not if both are present. Not all includes break the program, but I couldn't figure out what includes do and what don't. Maybe some developer here has some suggestions what to check?

And there's another, maybe correlating issue I hoped to get rid of with EFCore 5.0 but don't:

I have a base class ChangeableBase inheriting from CreateableBase:

public abstract class ChangeableBase : CreateableBase, IChangeable
  {
      [KeepValue]
      [IgnoreHasChanged]
      public Guid? UserIdChanged { get; set; }

      [KeepValue]
      [IgnoreHasChanged]
      [ForeignKey("UserIdChanged")]
      [NotMapped]
      public virtual User UserChanged { get; set; }

      [KeepValue]
      [IgnoreHasChanged]
      public DateTime? DateChanged { get; set; }
  }

I need to set UserChanged to not mapped, since the AutoMapper seams to get confused with relations of the same type pointing to different fields (UserCreated from BaseClass and UserChanged).

Thank you very much for your support!

EF Core version: 5.0.0 rc.1.20451.13
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1

The text was updated successfully, but these errors were encountered:

smitpatel · 2020-09-28T15:25:51Z

The query works without the Authorize / Include, but not if both are present. Not all includes break the program, but I couldn't figure out what includes do and what don't. Maybe some developer here has some suggestions what to check?

You cannot apply include after applying Distinct. Exception message also makes it clear which scenarios would not work.

ghost · 2020-09-28T15:55:45Z

The query works without the Authorize / Include, but not if both are present. Not all includes break the program, but I couldn't figure out what includes do and what don't. Maybe some developer here has some suggestions what to check?

You cannot apply include after applying Distinct. Exception message also makes it clear which scenarios would not work.

Nope, same exception for

paramContext.Authorize(paramContext.Offers.Include(), LoggedInUserId.Value, paramHierarchy: null, paramPrivilege: null).Take(1).ToArray();

In this case the Includes are first added (extension method) and than filtered. Still the same behavior. Anyway, it worked prior to 3.0 and now works for most, but not all, scenarios. If this really is a new behavior it should get mentioned in breaking changes and how to avoid it.

In my scenario this wouldn't work anyway: my service generates a pre-filtered IQueryable the client can use / include the way be wants. This worked perfectly with 3.0.

smitpatel · 2020-09-28T16:15:33Z

In this case the Includes are first added (extension method) and than filtered.

It does not matter in order. You cannot do it either way. If you put collection Include before distinct then distinct cannot be done on server side so it will throw client eval error. Your issue is that you are only projecting one column and applying distinct. We don't have enough data to bring additional children from include. It is not a breaking change it is a bug fix. If your query worked earlier then either by co-incidence or just giving you incorrect results.

In my scenario this wouldn't work anyway: my service generates a pre-filtered IQueryable the client can use / include the way be wants.

Prefiltered IQueryable works fully. Selecting just 1 column and applying Distinct makes any collection include to fail. If you are certain of correct collection groupings, you can write a manual join to bring back all the data you want. Beyond that it is just a bad query and there is no common way to rewrite. How to write a proper query will depend on what is the intention of the query.

ajcvickers · 2020-09-28T17:20:54Z

@maumar to de-dupe.

ghost · 2020-09-28T21:54:07Z

In this case the Includes are first added (extension method) and than filtered.

It does not matter in order. You cannot do it either way. If you put collection Include before distinct then distinct cannot be done on server side so it will throw client eval error. Your issue is that you are only projecting one column and applying distinct. We don't have enough data to bring additional children from include. It is not a breaking change it is a bug fix. If your query worked earlier then either by co-incidence or just giving you incorrect results.

In my scenario this wouldn't work anyway: my service generates a pre-filtered IQueryable the client can use / include the way be wants.

Prefiltered IQueryable works fully. Selecting just 1 column and applying Distinct makes any collection include to fail. If you are certain of correct collection groupings, you can write a manual join to bring back all the data you want. Beyond that it is just a bad query and there is no common way to rewrite. How to write a proper query will depend on what is the intention of the query.

Sorry, but I don't think that this is right! EFCore 3.1 just built a prefectly correct SQL statement:

SELECT [t0].[Id], [t0].[DateChanged], [t0].[DateCreated], [t0].[EtiketteModeId], [t0].[StatusId], [t0].[UserIdChanged], [t0].[UserIdCreated], [t0].[VSSOfferId], [t0].[CanAccessId], [o0].[Id], [o0].[BeneficiarySalesPersonId], [o0].[BlueLightCustomer], [o0].[BlueLightKm], [o0].[BlueLightSUDienstleistung], [o0].[BlueLightSUImport], [o0].[BlueLightSURegion], [o0].[BlueLightUse], [o0].[FullDeposit], [o0].[IsConstruction], [o0].[IsDynafleet], [o0].[IsEstepeTerberg], [o0].[IsFEFL], [o0].[IsNewCustomer], [o0].[OfferId], [o0].[ServiceContract], [o0].[ServiceDuration], [o0].[ServiceLevel], [o0].[UseSURegion], [o0].[VehicleCount], [o0].[VehicleType]
FROM (
    SELECT TOP(@__p_1) [o].[Id], [o].[DateChanged], [o].[DateCreated], [o].[EtiketteModeId], [o].[StatusId], [o].[UserIdChanged], [o].[UserIdCreated], [o].[VSSOfferId], [t].[CanAccessId]
    FROM [Offer] AS [o]
    INNER JOIN (
        SELECT DISTINCT [v].[CanAccessId]
        FROM [viwUserHierarchy] AS [v]
        WHERE [v].[Id] = @__paramUserId_0
    ) AS [t] ON [o].[UserIdCreated] = [t].[CanAccessId]
) AS [t0]
LEFT JOIN [OfferEtikette] AS [o0] ON [t0].[Id] = [o0].[OfferId]
ORDER BY [t0].[Id], [t0].[CanAccessId], [o0].[Id]

Since the "CanAccessId" property is not unique I could multiply my resultset, if I do net use the Distinct-Operator. And that's the most performant way the SQL Server can execute this statement. A "Contains", interpreted to "WHERE UserIdCreated IN" would in the best case be not less performant, depinding on the Servers execution plan. The old behavior was perfectly right!

And: how about two ForeignKeys referencing the same Entity?

ghost · 2020-10-01T08:13:59Z

Just saw that there was some code missing that maybe helps understanding the issue:

public IQueryable<Guid> GetAuthorization(Guid paramUserId, IEnumerable<string> paramHierarchys = null, IEnumerable<string> paramPrivileges = null)
           => UserHierarchys
               .Where(h => h.Id == paramUserId && (paramHierarchys == null || paramHierarchys.Contains(h.Hierarchy)) && (paramPrivileges == null || paramPrivileges.Contains(h.Privilege)))
               .Select(h => h.CanAccessId)
               .Distinct()
           ;

public IQueryable<T> Authorize<T>(IQueryable<T> paramQueryable, Guid paramUserId, Expression<Func<T, Guid>> paramPropertySelector, IEnumerable<string> paramHierarchys = null, IEnumerable<string> paramPrivileges = null)
           => paramQueryable.Join(GetAuthorization(paramUserId, paramHierarchys, paramPrivileges), paramPropertySelector, k => k, (q, h) => q);

Gues there's no reason why this shouldn't work in all scenarios (like it did with 3.1).

ilmax · 2020-10-02T14:05:50Z

I'm also hitting this one with a query that was working on 3.1. I managed to reproduce it on very basic example, in my case projecting a child entity causes this exception while not projecting the child successfully run the query.
Here you can find the repo. The first query runs successfully and the second one throws an exception.

Expected output should be "Entities count: 0" for both queries. The second one differs from the first one just because it's projecting the children navigation property, that projection is what fails the second query.

ajcvickers · 2020-10-02T18:35:41Z

@smitpatel Below is the code from @ilmax with logs from 3.1 and 5.0 GA daily.

internal class Program
{
    private static void Main(string[] args)
    {
        using var context = new Context();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
        context.SaveChanges();

        var groupedQuery = context.EntityWithChildren.GroupBy(k => new {k.Id})
            .Select(x => new {x.Key.Id, Version = x.Max(v => v.Version)});

        var maxVersionQuery = context.EntityWithChildren.Join(groupedQuery,
            l => new {l.Id, l.Version}, r => new {r.Id, r.Version}, (l, r) => l);

        var entities = maxVersionQuery.ToList();
        Console.WriteLine($"Entities count: {entities.Count}");

        var maxVersionQuery2 = context.EntityWithChildren.Join(groupedQuery,
                l => new {l.Id, l.Version}, r => new {r.Id, r.Version}, (l, r) => l)
            .Select(x => new {x.Id, x.Version, Children = x.Children.Select(y => y.Id)});

        var entities2 = maxVersionQuery2.ToList();
        Console.WriteLine($"Entities count: {entities2.Count}");
    }
}

public class EntityWithChildren
{
    public Guid Id { get; set; }
    public int Version { get; set; }
    public ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }
}

public class Context : DbContext
{
    private static readonly ILoggerFactory
        Logger = LoggerFactory.Create(x => x.AddConsole()); //.SetMinimumLevel(LogLevel.Debug));

    public DbSet<EntityWithChildren> EntityWithChildren { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EntityWithChildren>(e =>
        {
            e.Property(x => x.Id).ValueGeneratedNever();
            e.HasKey(x => new {x.Id, x.Version});

            e.ToTable("EntitiesWithChildren");
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=TestX;ConnectRetryCount=0");
        //optionsBuilder.LogTo(Console.WriteLine);
    }
}

Log from 3.1.8:

C:\Users\Arthur\AppData\Local\JetBrains\Toolbox\apps\Rider\ch-0\202.6948.24\plugins\dpa\DotFiles\JetBrains.DPA.Runner.exe --handle=8016 --backend-pid=9036 --detach-event-name=dpa.detach.8016 "C:\Program Files\dotnet\dotnet.exe" C:/Stuff/AllTogetherNow/ThreeOne/bin/Debug/netcoreapp3.1/ThreeOne.dll
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.8 initialized 'Context' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [TestX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [TestX];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (154ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [TestX];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [TestX] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [EntitiesWithChildren] (
          [Id] uniqueidentifier NOT NULL,
          [Version] int NOT NULL,
          CONSTRAINT [PK_EntitiesWithChildren] PRIMARY KEY ([Id], [Version])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Child] (
          [Id] int NOT NULL IDENTITY,
          [EntityWithChildrenId] uniqueidentifier NULL,
          [EntityWithChildrenVersion] int NULL,
          CONSTRAINT [PK_Child] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Child_EntitiesWithChildren_EntityWithChildrenId_EntityWithChildrenVersion] FOREIGN KEY ([EntityWithChildrenId], [EntityWithChildrenVersion]) REFERENCES [EntitiesWithChildren] ([Id], [Version]) ON DELETE NO ACTION
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Child_EntityWithChildrenId_EntityWithChildrenVersion] ON [Child] ([EntityWithChildrenId], [EntityWithChildrenVersion]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [e].[Id], [e].[Version]
      FROM [EntitiesWithChildren] AS [e]
      INNER JOIN (
          SELECT [e0].[Id], MAX([e0].[Version]) AS [c]
          FROM [EntitiesWithChildren] AS [e0]
          GROUP BY [e0].[Id]
      ) AS [t] ON ([e].[Id] = [t].[Id]) AND ([e].[Version] = [t].[c])
Entities count: 0
Entities count: 0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [e].[Id], [e].[Version], [t].[Id], [c].[Id]
      FROM [EntitiesWithChildren] AS [e]
      INNER JOIN (
          SELECT [e0].[Id], MAX([e0].[Version]) AS [c]
          FROM [EntitiesWithChildren] AS [e0]
          GROUP BY [e0].[Id]
      ) AS [t] ON ([e].[Id] = [t].[Id]) AND ([e].[Version] = [t].[c])
      LEFT JOIN [Child] AS [c] ON ([e].[Id] = [c].[EntityWithChildrenId]) AND ([e].[Version] = [c].[EntityWithChildrenVersion])
      ORDER BY [e].[Id], [e].[Version], [t].[Id], [c].[Id]

Process finished with exit code 0.

From 5.0 GA:

al\JetBrains\Toolbox\apps\Rider\ch-0\202.6948.24\plugins\dpa\DotFiles\JetBrains.DPA.Runner.exe --handle=8940 --backend-pid=9036 --detach-event-name=dpa.detach.8940 "C:\Program Files\dotnet\dotnet.exe" C:/Stuff/AllTogetherNow/Daily/bin/Debug/netcoreapp3.1/Daily.dll
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 5.0.0-rtm.20479.3 initialized 'Context' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [TestX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [TestX];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (132ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [TestX];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (36ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [TestX] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [EntitiesWithChildren] (
          [Id] uniqueidentifier NOT NULL,
          [Version] int NOT NULL,
          CONSTRAINT [PK_EntitiesWithChildren] PRIMARY KEY ([Id], [Version])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Child] (
          [Id] int NOT NULL IDENTITY,
          [EntityWithChildrenId] uniqueidentifier NULL,
          [EntityWithChildrenVersion] int NULL,
          CONSTRAINT [PK_Child] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Child_EntitiesWithChildren_EntityWithChildrenId_EntityWithChildrenVersion] FOREIGN KEY ([EntityWithChildrenId], [EntityWithChildrenVersion]) REFERENCES [EntitiesWithChildren] ([Id], [Version]) ON DELETE NO ACTION
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Child_EntityWithChildrenId_EntityWithChildrenVersion] ON [Child] ([EntityWithChildrenId], [EntityWithChildrenVersion]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [e].[Id], [e].[Version]
      FROM [EntitiesWithChildren] AS [e]
      INNER JOIN (
          SELECT [e0].[Id], MAX([e0].[Version]) AS [c]
          FROM [EntitiesWithChildren] AS [e0]
          GROUP BY [e0].[Id]
      ) AS [t] ON ([e].[Id] = [t].[Id]) AND ([e].[Version] = [t].[c])
Entities count: 0
Unhandled exception. System.InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyCollectionJoin(Int32 collectionIndex, Int32 collectionId, Expression innerShaper, INavigationBase navigation, Type elementType, Boolean splitQuery)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitNew(NewExpression node)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main(String[] args) in C:\Stuff\AllTogetherNow\Daily\Daily.cs:line 58

Process finished with exit code -532,462,766.

smitpatel · 2020-10-02T18:58:55Z

We threw exception because we tried to find Version from grouping query (since it is part of PK), but we couldn't due to group by.

Unique identifier for GroupBy query can be grouping key. cc: @maumar
Non-trivial change, too risky for RTM.

smitpatel · 2020-10-02T19:02:44Z

Though @ilmax issue is separate issue and should be tracked in a different issue. The original issue remains the same, UserHierarchys is projecting CanAccessId only which is non-PK and applying distinct over that, we don't have a way to uniquely identify UserHierarchy to form collections on client side. At the least, we need runnable simplified repro code for it. There are multiple queries and without exact query, we cannot confirm if it can be translated or not.

ghost · 2020-10-05T09:45:10Z

Ok, finally took the time to build a full working example simulation my issue. Perfectly runs on 3.1.8, doesn't with 5.0 RC1:

Program.txt

If this is not possible anymore it's sad but ok. But in this case it should be mentioned as a breaking change.

Code:

internal class Program
{
    private static void Main(string[] args)
    {
        var context = new TestDbContext();
     
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var query = context.Offers.AsQueryable();

        query = query.Join(context.UserHierarchys.Select(h => h.CanAccessId).Distinct(), q => q.UserIdCreated, k => k,
            (q, h) => q);
        query = query.Include(p => p.Etikettes);

        var q = query.ToArray();
    }
}

public class TestDbContext : DbContext
{
   private static readonly ILoggerFactory Logger 
       = LoggerFactory.Create(x => x.AddConsole());//.SetMinimumLevel(LogLevel.Debug));

    public DbSet<UserHierarchy> UserHierarchys { get; set; }
    public DbSet<Offer> Offers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        => optionsBuilder
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(Logger)
            .UseSqlServer(Your.ConnectionString);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserHierarchy>(uh =>
        {
            uh.HasKey(p => new
            {
                p.Hierarchy,
                p.Id,
                p.CanAccessId
            });
        });

        modelBuilder.Entity<Offer>(o => { o.HasMany(p => p.Etikettes).WithOne(p => p.Offer); });
    }
}

public class UserHierarchy
{
    public string Hierarchy { get; set; }
    public Guid Id { get; set; }
    public Guid CanAccessId { get; set; }
    public string Privilege { get; set; }
}

public class Offer
{
    public Guid Id { get; set; }
    public Guid? UserIdCreated { get; set; }


    public virtual ICollection<OfferEtikette> Etikettes { get; set; }
}

public class OfferEtikette
{
    public Guid Id { get; set; }
    public Guid OfferId { get; set; }

    public virtual Offer Offer { get; set; }
}

3.1:

/home/ajcvickers/.dotnet/dotnet /home/ajcvickers/AllTogetherNow/ThreeOne/bin/Debug/netcoreapp3.1/ThreeOne.dll
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.8 initialized 'TestDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled 
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (48ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (304ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (131ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Offers] (
          [Id] uniqueidentifier NOT NULL,
          [UserIdCreated] uniqueidentifier NULL,
          CONSTRAINT [PK_Offers] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [UserHierarchys] (
          [Hierarchy] nvarchar(450) NOT NULL,
          [Id] uniqueidentifier NOT NULL,
          [CanAccessId] uniqueidentifier NOT NULL,
          [Privilege] nvarchar(max) NULL,
          CONSTRAINT [PK_UserHierarchys] PRIMARY KEY ([Hierarchy], [Id], [CanAccessId])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [OfferEtikette] (
          [Id] uniqueidentifier NOT NULL,
          [OfferId] uniqueidentifier NOT NULL,
          CONSTRAINT [PK_OfferEtikette] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_OfferEtikette_Offers_OfferId] FOREIGN KEY ([OfferId]) REFERENCES [Offers] ([Id]) ON DELETE CASCADE
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_OfferEtikette_OfferId] ON [OfferEtikette] ([OfferId]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [o].[Id], [o].[UserIdCreated], [t].[CanAccessId], [o0].[Id], [o0].[OfferId]
      FROM [Offers] AS [o]
      INNER JOIN (
          SELECT DISTINCT [u].[CanAccessId]
          FROM [UserHierarchys] AS [u]
      ) AS [t] ON [o].[UserIdCreated] = [t].[CanAccessId]
      LEFT JOIN [OfferEtikette] AS [o0] ON [o].[Id] = [o0].[OfferId]
      ORDER BY [o].[Id], [t].[CanAccessId], [o0].[Id]

5.0:

me/ajcvickers/AllTogetherNow/Daily/bin/Debug/netcoreapp3.1/Daily.dll
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 5.0.0-rtm.20502.6 initialized 'TestDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled 
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (47ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (289ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (136ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Offers] (
          [Id] uniqueidentifier NOT NULL,
          [UserIdCreated] uniqueidentifier NULL,
          CONSTRAINT [PK_Offers] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [UserHierarchys] (
          [Hierarchy] nvarchar(450) NOT NULL,
          [Id] uniqueidentifier NOT NULL,
          [CanAccessId] uniqueidentifier NOT NULL,
          [Privilege] nvarchar(max) NULL,
          CONSTRAINT [PK_UserHierarchys] PRIMARY KEY ([Hierarchy], [Id], [CanAccessId])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [OfferEtikette] (
          [Id] uniqueidentifier NOT NULL,
          [OfferId] uniqueidentifier NOT NULL,
          CONSTRAINT [PK_OfferEtikette] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_OfferEtikette_Offers_OfferId] FOREIGN KEY ([OfferId]) REFERENCES [Offers] ([Id]) ON DELETE CASCADE
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_OfferEtikette_OfferId] ON [OfferEtikette] ([OfferId]);
Unhandled exception. System.InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyCollectionJoin(Int32 collectionIndex, Int32 collectionId, Expression innerShaper, INavigationBase navigation, Type elementType, Boolean splitQuery)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.IncludeExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Program.Main(String[] args) in /home/ajcvickers/AllTogetherNow/Daily/Daily.cs:line 56

ghost · 2020-10-05T10:18:39Z

And here is the code for my 2nd issue (two relations to the same object). If this is not a simple one I'm going to create a new issue for it:
Program.txt

ajcvickers · 2020-10-05T16:02:59Z

@tsproesser Your second issue throws the same error and has the same model validation warning on both 3.1 and 5.0:

warn: Microsoft.EntityFrameworkCore.Model[10612]
      Navigations 'User.UserChanged' and 'User.UserCreated' were separated into two relationships as ForeignKeyAttribute was specified on navigations on both sides.
Unhandled exception. System.InvalidOperationException: Unable to determine the relationship represented by navigation property 'UserProperty.UserChanged' of type 'User'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
   at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted()
   at Program.Main(String[] args) in /home/ajcvickers/AllTogetherNow/ThreeOne/ThreeOneApp.cs:line 19

smitpatel · 2020-10-05T16:57:51Z

You can rewrite your query to do filtering via Where rather than Join.

        query = query.Where(q => context.UserHierarchys.Select(h => h.CanAccessId).Distinct().Any(k => q.UserIdCreated = k));

ajcvickers · 2020-10-05T21:17:21Z

Note from triage: we will document as a breaking change the differences in 5.0 here, since some queries generated by 3.1 did return correct results. We should try to add examples (like shown above) for ways to re-write the queries for 5.0.

smitpatel · 2020-10-05T21:18:42Z

@maumar - Can you "implement" this?

ghost · 2020-10-05T22:21:30Z

@tsproesser Your second issue throws the same error and has the same model validation warning on both 3.1 and 5.0:

warn: Microsoft.EntityFrameworkCore.Model[10612]
      Navigations 'User.UserChanged' and 'User.UserCreated' were separated into two relationships as ForeignKeyAttribute was specified on navigations on both sides.
Unhandled exception. System.InvalidOperationException: Unable to determine the relationship represented by navigation property 'UserProperty.UserChanged' of type 'User'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
   at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted()
   at Program.Main(String[] args) in /home/ajcvickers/AllTogetherNow/ThreeOne/ThreeOneApp.cs:line 19

Yeah, sorry! This probably was a bit unclear. It is absolutely off-topic and is worth a new issue I'll create one. Is this a bug? Should this work? Or did I just use it wrong?

ajcvickers · 2020-10-05T22:28:31Z

@tsproesser Please file a new issue and we'll look there. This issue is already way overloaded!

ghost · 2020-10-05T22:43:58Z

You can rewrite your query to do filtering via Where rather than Join.
        query = query.Where(q => context.UserHierarchys.Select(h => h.CanAccessId).Distinct().Any(k => q.UserIdCreated = k));

This sure is a suitable workaround, but generates a less performant execution plan! Even though it's still quite fast this is not a real solution for my issue but more a workaround.

Edit: did some more tests and there are cases where this is less performant. But it is more or less negligible. Anyway: shouldn't it be still possible?

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

ilmax · 2020-12-08T18:03:25Z

Hey @smitpatel do you know what's the issue that tracks fixing the breaking change I reported above? Thanks

smitpatel · 2020-12-08T18:06:06Z

@ilmax - dotnet/efcore#22892 should track that. Though the earlier release it was actually incorrect result since we ignored it altogether.

ilmax · 2020-12-08T18:07:26Z

Thank you!

ajcvickers transferred this issue from dotnet/efcore Oct 5, 2020

ajcvickers added area-query type-breaking-change labels Oct 5, 2020

ajcvickers assigned smitpatel Oct 5, 2020

ajcvickers added this to the 5.0.0 milestone Oct 5, 2020

ghost mentioned this issue Oct 15, 2020

System.Data.SqlTypes.SqlNullValueException on fielld allowing Null-Values with complex joins dotnet/efcore#22557

Closed

smitpatel assigned maumar and unassigned smitpatel Oct 15, 2020

maumar added a commit that referenced this issue Nov 6, 2020

Fixing several doc issues.

b1ec058

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

maumar mentioned this issue Nov 6, 2020

Fixing several doc issues. #2835

Merged

maumar added a commit that referenced this issue Nov 6, 2020

Fixing several doc issues.

2fe7ca2

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

maumar added a commit that referenced this issue Nov 6, 2020

Fixing several doc issues.

08e43a1

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

maumar added the closed-fixed label Nov 6, 2020

maumar added a commit that referenced this issue Nov 6, 2020

Fixing several doc issues.

259dc86

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

maumar added a commit that referenced this issue Nov 9, 2020

Fixing several doc issues.

286796f

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

maumar closed this as completed in #2835 Nov 9, 2020

maumar added a commit that referenced this issue Nov 9, 2020

Fixing several doc issues. (#2835)

6d9ea0b

Fixes #486 Fixes #756 Fixes #1311 Fixes #2307 Fixes #2511 Fixes #2731 Fixes #2823

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Breaking change in EFCore 5.0 #2731

Breaking change in EFCore 5.0 #2731

ghost commented Sep 28, 2020

smitpatel commented Sep 28, 2020

ghost commented Sep 28, 2020

smitpatel commented Sep 28, 2020

ajcvickers commented Sep 28, 2020

ghost commented Sep 28, 2020

ghost commented Oct 1, 2020

ilmax commented Oct 2, 2020 •

edited

Loading

ajcvickers commented Oct 2, 2020

smitpatel commented Oct 2, 2020

smitpatel commented Oct 2, 2020

ghost commented Oct 5, 2020 •

edited by ajcvickers

Loading

ghost commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

smitpatel commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

smitpatel commented Oct 5, 2020

ghost commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

ghost commented Oct 5, 2020 •

edited by ghost

Loading

ilmax commented Dec 8, 2020

smitpatel commented Dec 8, 2020 •

edited

Loading

ilmax commented Dec 8, 2020

Breaking change in EFCore 5.0 #2731

Breaking change in EFCore 5.0 #2731

Comments

ghost commented Sep 28, 2020

smitpatel commented Sep 28, 2020

ghost commented Sep 28, 2020

smitpatel commented Sep 28, 2020

ajcvickers commented Sep 28, 2020

ghost commented Sep 28, 2020

ghost commented Oct 1, 2020

ilmax commented Oct 2, 2020 • edited Loading

ajcvickers commented Oct 2, 2020

smitpatel commented Oct 2, 2020

smitpatel commented Oct 2, 2020

ghost commented Oct 5, 2020 • edited by ajcvickers Loading

ghost commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

smitpatel commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

smitpatel commented Oct 5, 2020

ghost commented Oct 5, 2020

ajcvickers commented Oct 5, 2020

ghost commented Oct 5, 2020 • edited by ghost Loading

ilmax commented Dec 8, 2020

smitpatel commented Dec 8, 2020 • edited Loading

ilmax commented Dec 8, 2020

ilmax commented Oct 2, 2020 •

edited

Loading

ghost commented Oct 5, 2020 •

edited by ajcvickers

Loading

ghost commented Oct 5, 2020 •

edited by ghost

Loading

smitpatel commented Dec 8, 2020 •

edited

Loading