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.67k stars 3.16k forks source link

Created query for projection with ownedMany and join is wrong and leads to null exception #34749

Open KillerBoogie opened 3 days ago

KillerBoogie commented 3 days ago

Below is the relevant code. It is part of a bigger project. It is not easy and very time consuming to create a reduced working project. I hope the issue can be understood by the posted code.

This is the projection that causes the issue. It just tries to return location data with nested cover images. The image Uri, width and height is pulled from the Image collection. Therefore a join is created. 

List<LocationMLResponse> Locations;

var query = dbContext.Location.AsNoTracking().AsSplitQuery()
            .Select(location => new LocationMLResponse(
             location.Id.Value,
             location.Name.ToStringDictionary(),
             location.CoverImages
                 .Join(dbContext.Image.AsNoTracking(),
                 danceCoverImage => danceCoverImage.ImageId,
                 image => image.Id,
                 (danceCoverImage, image) => new ImageItemResponse(
                     danceCoverImage.ImageId.Value,
                     danceCoverImage.DisplayOrder,
                     danceCoverImage.Language == null ? null : danceCoverImage.Language.Tag,
                     danceCoverImage.ScreenSize == null ? null : danceCoverImage.ScreenSize.Value,
                     danceCoverImage.FocusPointX,
                     danceCoverImage.FocusPointY,
                     image.Uri.ToString(),
                     image.MetaData.Width,
                     image.MetaData.Height
                 )).ToList(),
             location.Address.MapToDto(),
             location.Created,
             location.LastModified,
             location.Version
             ));

 Locations = await query.ToListAsync();

 public record LocationMLResponse(
   object Id,
   Dictionary<string, string> Name,
   List<ImageItemResponse> CoverImage,
   AddressDto Address,
   DateTime Created,
   DateTime LastModified,
   ulong Version
   )

The projection creates 3 queries. The first 2 queries are sufficient and would return the correct result. The 3rd query is unnecessary. I don't understand why it is created. It leads to a wrong response.

 SELECT [l].[LocationId], [l].[Name], [l].[Created], [l].[CreatedBy], [l].[CreatedInNameOf], [l].[LastModified], [l].[LastModifiedBy], [l].[LastModifiedInNameOf], [l].[TId], [l].[Version], [l].[Address_City], [l].[Address_CountryId], [l].[Address_CountryName], [l].[Address_DeliveryInstruction], [l].[Address_State], [l].[Address_Street], [l].[Address_StreetAffix], [l].[Address_StreetNumber], [l].[Address_ZipCode]
 FROM [VC].[Location] AS [l]
 ORDER BY [l].[LocationId]

SELECT [t].[ImageId], [t].[DisplayOrder], [t].[Language], [t].[ScreenSize], [t].[FocusPointX], [t].[FocusPointY], [t].[Uri], [t].[c], [t].[c0], [l].[LocationId]
FROM [VC].[Location] AS [l]
INNER JOIN (
    SELECT [l0].[ImageId], [l0].[DisplayOrder], [l0].[Language], [l0].[ScreenSize], [l0].[FocusPointX], [l0].[FocusPointY], [i].[Uri], CAST([i].[MetaData_Width] AS bigint) AS [c], CAST([i].[MetaData_Height] AS bigint) AS [c0], [l0].[LocationId]
    FROM [VC].[LocationCoverImage] AS [l0]
    INNER JOIN [VC].[Image] AS [i] ON [l0].[ImageId] = [i].[ImageId]
) AS [t] ON [l].[LocationId] = [t].[LocationId]
ORDER BY [l].[LocationId]

SELECT [l0].[TId], [l0].[DisplayOrder], [l0].[FocusPointX], [l0].[FocusPointY], [l0].[ImageId], [l0].[Language], [l0].[LocationId], [l0].[ScreenSize], [l].[LocationId]
FROM [VC].[Location] AS [l]
INNER JOIN [VC].[LocationCoverImage] AS [l0] ON [l].[LocationId] = [l0].[LocationId]
ORDER BY [l].[LocationId]

Due to the wrong query the result causes an exception:

fail: 24.09.2024 01:52:03.554 CoreEventId.QueryIterationFailed[10100] (Microsoft.EntityFrameworkCore.Query) An exception occurred while iterating over the results of a query for context type 'VC.WebApi.Infrastructure.EFCore.Context.VCDbContext'. System.NullReferenceException: Object reference not set to an instance of an object. at VC.WebApi.Domain.ImageItems.ImageItem..ctor(ImageItem original) at VC.WebApi.Domain.ImageItems.ImageItem..ctor() in C:\Users\micro\Documents\repos\VC-Web-API\VC.WebApi\Domain\ImageItems\ImageItem.cs:line 17 at lambda_method97(Closure, QueryContext, DbDataReader, ResultContext, SplitQueryResultCoordinator) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateSplitIncludeCollectionAsync[TIncludingEntity,TIncludedEntity](Int32 collectionId, RelationalQueryContext queryContext, IExecutionStrategy executionStrategy, RelationalCommandCache relationalCommandCache, IReadOnlyList1 readerColumns, Boolean detailedErrorsEnabled, SplitQueryResultCoordinator resultCoordinator, Func3 childIdentifier, IReadOnlyList1 identifierValueComparers, Func5 innerShaper, Func4 relatedDataLoaders, INavigationBase inverseNavigation, Action2 fixup, Boolean trackingQuery) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.TaskAwaiter(Func1[] taskFactories) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() dbug: 24.09.2024 01:52:03.560 RelationalEventId.DataReaderClosing[20301] (Microsoft.EntityFrameworkCore.Database.Command) Closing data reader to 'VCServer' on server 'xxx'. dbug: 24.09.2024 01:52:03.566 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) A data reader for 'VCServer' on server 'xxx' is being disposed after spending 4011ms reading results. dbug: 24.09.2024 01:52:03.570 RelationalEventId.DataReaderClosing[20301] (Microsoft.EntityFrameworkCore.Database.Command) Closing data reader to 'VCServer' on server 'xxx'. dbug: 24.09.2024 01:52:03.572 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) A data reader for 'VCServer' on server 'xxx' is being disposed after spending 3931ms reading results. dbug: 24.09.2024 01:52:03.575 RelationalEventId.DataReaderClosing[20301] (Microsoft.EntityFrameworkCore.Database.Command) Closing data reader to 'VCServer' on server 'xxx'. dbug: 24.09.2024 01:52:03.578 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) A data reader for 'VCServer' on server 'xxx' is being disposed after spending 3890ms reading results. dbug: 24.09.2024 01:52:03.583 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) Closing connection to database 'VCServer' on server 'xxx'. dbug: 24.09.2024 01:52:03.592 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) Closed connection to database 'VCServer' on server 'xxx' (9ms). Exception thrown: 'System.NullReferenceException' in System.Private.CoreLib.dll Object reference not set to an instance of an object.

I also tried without SplitQuery. The result again has an additional LEFT JOIN that makes no sense.

SELECT [l].[LocationId], [l].[Name], [t].[ImageId], [t].[DisplayOrder], [t].[Language], [t].[ScreenSize], [t].[FocusPointX], [t].[FocusPointY], [t].[Uri], [t].[c], [t].[c0], [t].[TId], [t].[ImageId0], [l].[Created], [l].[CreatedBy], [l].[CreatedInNameOf], [l].[LastModified], [l].[LastModifiedBy], [l].[LastModifiedInNameOf], [l].[TId], [l].[Version], [l].[Address_City], [l].[Address_CountryId], [l].[Address_CountryName], [l].[Address_DeliveryInstruction], [l].[Address_State], [l].[Address_Street], [l].[Address_StreetAffix], [l].[Address_StreetNumber], [l].[Address_ZipCode], [l1].[TId], [l1].[DisplayOrder], [l1].[FocusPointX], [l1].[FocusPointY], [l1].[ImageId], [l1].[Language], [l1].[LocationId], [l1].[ScreenSize]
FROM [VC].[Location] AS [l]
LEFT JOIN (
    SELECT [l0].[ImageId], [l0].[DisplayOrder], [l0].[Language], [l0].[ScreenSize], [l0].[FocusPointX], [l0].[FocusPointY], [i].[Uri], CAST([i].[MetaData_Width] AS bigint) AS [c], CAST([i].[MetaData_Height] AS bigint) AS [c0], [l0].[TId], [i].[ImageId] AS [ImageId0], [l0].[LocationId]
    FROM [VC].[LocationCoverImage] AS [l0]
    INNER JOIN [VC].[Image] AS [i] ON [l0].[ImageId] = [i].[ImageId]
) AS [t] ON [l].[LocationId] = [t].[LocationId]
LEFT JOIN [VC].[LocationCoverImage] AS [l1] ON [l].[LocationId] = [l1].[LocationId]
ORDER BY [l].[LocationId], [t].[TId], [t].[ImageId0]

The Location object has a list of ImageItems. ImageItems link to images from the Image collection with additional properties. ML<> and MLRequired<> are value objects that allow storing text in different languages. LocationName and Address are also value objects.

public class Location : VersionedEntity<LocationId>
{
    public MLRequired<LocationName> Name { get; private set; }
    public IReadOnlyList<ImageItem> CoverImages => _coverImages.AsReadOnly();
    private List<ImageItem> _coverImages;
    public Address Address { get; private set; }

#pragma warning disable CS8618 
        private Location() { }
#pragma warning restore CS8618 
 }

 public record ImageItem (
      ImageId ImageId,
      int? DisplayOrder = null,
      Language? Language = null,
      ScreenSize? ScreenSize = null,
      decimal? FocusPointX = null, 
      decimal? FocusPointY = null
      )
  {
      private ImageItem() : this(default!) { }
  }

public class Image : VersionedEntity<ImageId>
{
    public FileName FileName { get; private set; }
    public ML<ImageDescription> Description { get; private set; }
    public Uri Uri { get; private set; }
    public ImageMetaData MetaData { get; private set; }

#pragma warning disable CS8618
        protected Image() { }
#pragma warning restore CS8618  
}

public record ImageMetaData
{
    public int? Width { get; private set; }
    public int? Height { get; private set; }
    public int? Size { get; private set; }

    private ImageMetaData(int? size, int? width, int? height)
    {
        Size = size;
        Width = width;
        Height = height;
    }
}

This is the EF Core configuration for Location. Since we use guids as ids we add a technical cluster key (TId).

public class LocationConfiguration : IEntityTypeConfiguration<Location>
{
    public void Configure(EntityTypeBuilder<Location> entity)
    {
        entity.ToTable("Location", 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(LocationId)).HasColumnOrder(1);

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

        entity.OwnsMany(e => e.CoverImages, ci =>
        {
            ci.ToTable("LocationCoverImage");
            ci.WithOwner().HasForeignKey("LocationId").HasConstraintName("FK_LocationCoverImage_Location_LocationId");
            ci.HasIndex("LocationId").HasDatabaseName("IX_LocationCoverImage_LocationId");
            ci.Property<long>("TId").HasColumnOrder(0);
            ci.HasKey("TId");
            ci.Property(i => i.ImageId).HasColumnName("ImageId").IsRequired();
            ci.HasOne<Image>()
                .WithMany()
                .HasForeignKey(i => i.ImageId)
                .HasConstraintName("FK_LocationCoverImage_ImageId")
                .OnDelete(DeleteBehavior.NoAction);
            ci.Property(i => i.DisplayOrder);
            ci.Property(i => i.FocusPointX).HasPrecision(precision:4, scale:2);
            ci.Property(i => i.FocusPointY).HasPrecision(precision:4, scale:2);
        });

        entity.ComplexProperty(e => e.Address).Property(e => e.DeliveryInstruction);
        entity.ComplexProperty(e => e.Address).Property(e => e.Street);
        entity.ComplexProperty(e => e.Address).Property(e => e.StreetNumber);
        entity.ComplexProperty(e => e.Address).Property(e => e.StreetAffix);
        entity.ComplexProperty(e => e.Address).Property(e => e.ZipCode);
        entity.ComplexProperty(e => e.Address).Property(e => e.City);
        entity.ComplexProperty(e => e.Address).Property(e => e.State);
        entity.ComplexProperty(e => e.Address).Property(e => e.CountryId);
        entity.ComplexProperty(e => e.Address).Property(e => e.CountryName);

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

Image Configuration:

 public class ImageConfiguration : IEntityTypeConfiguration<Image>
 {
     public void Configure(EntityTypeBuilder<Image> entity)
     {
         entity.ToTable("Image", 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(ImageId)).HasColumnOrder(1);

         entity.Property(e => e.FileName).HasMaxLength(FileName.MaxLength).HasColumnOrder(2);
         entity.HasIndex("FileName").IsUnique();
         entity.Property(e => e.Description).HasColumnOrder(3);

         entity.Property(e => e.Uri).HasColumnOrder(4);
         entity.HasIndex("Uri").IsUnique();
         entity.ComplexProperty(e => e.MetaData).Property(e => e.Width).HasColumnOrder(5);
         entity.ComplexProperty(e => e.MetaData).Property(e => e.Height).HasColumnOrder(6);
         entity.ComplexProperty(e => e.MetaData).Property(e => e.Size).HasColumnOrder(7);

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

EF Core version: 8.0.8 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET 8.0 Operating system: Win 11 Visual Studio 2022 17.11.4

KillerBoogie commented 2 days ago

For issue #34750 I installed v9.0 rc1 to test. I also tested this issue. The problem still exists with rc1.

maumar commented 1 day ago

@KillerBoogie we need a full repro to effectively investigate this problem. It's not immediately obvious what's going on here. I tried to reverse-engineer the repro based on code you provided, but I was unsuccessful.

KillerBoogie commented 1 day ago

@maumar : I stripped down the project. You can access it at https://github.com/KillerBoogie/ef-core-join-query-bug/tree/main