EFCore5, using FromSqlRaw and [Owned] attribute

Question

I am trying to take advantage of the EF Core "[Owned]" attribute when returning data from a stored procedure. My model is as follows...

[Keyless]
public class TestResult
{

    public TestResultManifest Manifest { get; set; } = new TestResultManifest();

    public Int32 TransportJobID { get; set; }

}

[Owned]
public class TestResultManifest
{

    public Int32 ManifestID { get; set; }

}

and this is implemented within my DbContext as follows...

public DbSet<TestResult> TestItems { get; set; }

public async Task<List<TestResult>> SelectTestItemsAsync()
{
    return await TestItems.FromSqlRaw($"EXECUTE [dbo].[SelectTestItems]").ToListAsync();
}

It is my understanding from here that when this is mapped directly to a physical table it would result in columns named "TransportJobID" and "Manifest_ManifestID", therefore I am using the following stored procedure to pull back the necessary data...

CREATE PROCEDURE [dbo].[SelectTestItems]

as

    select Manifest_ManifestID = 3,
        TransportJobID = 22;

    return @@error;

Which as you would expect results in...

However, whenever I run this I get a NullReferenceException thrown deep inside Entity Framework...

System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasRelationship(EntityType targetEntityType, Nullable`1 navigationToTarget, Nullable`1 inverseNavigation, Nullable`1 setTargetAsPrincipal, ConfigurationSource configurationSource, Nullable`1 required)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasOwnership(TypeIdentity& targetEntityType, MemberIdentity navigation, Nullable`1 inverse, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasOwnership(Type targetEntityType, MemberInfo navigationMember, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Microsoft.EntityFrameworkCore.Metadata.Builders.IConventionEntityTypeBuilder.HasOwnership(Type targetEntityType, MemberInfo navigation, Boolean fromDataAnnotation)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.RelationshipDiscoveryConvention.CreateRelationships(IEnumerable`1 relationshipCandidates, IConventionEntityTypeBuilder entityTypeBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.RelationshipDiscoveryConvention.DiscoverRelationships(IConventionEntityTypeBuilder entityTypeBuilder, IConventionContext context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.RelationshipDiscoveryConvention.Process(IConventionEntityType entityType, String navigationName, MemberInfo memberInfo, IConventionContext context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.RelationshipDiscoveryConvention.ProcessNavigationRemoved(IConventionEntityTypeBuilder sourceEntityTypeBuilder, IConventionEntityTypeBuilder targetEntityTypeBuilder, String navigationName, MemberInfo memberInfo, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnNavigationRemoved(IConventionEntityTypeBuilder sourceEntityTypeBuilder, IConventionEntityTypeBuilder targetEntityTypeBuilder, String navigationName, MemberInfo memberInfo)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnNavigationRemovedNode.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.DelayedConventionScope.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Run()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Dispose()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model..ctor(ConventionSet conventions, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies, Boolean _)
   at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   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.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_Model()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityType()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.CheckState()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityQueryable()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlRaw[TEntity](DbSet`1 source, String sql, Object[] parameters)

I would expect this to work given that I am merely using (what I believe to be) standard functionality - at least I would have thought it would work if mapped to a real table and retrieved via LINQ and Entities are also supposed to be retrievable using FromRawSql(...); can anybody please explain what I am doing wrong and/or missing.

I don't expect this to make any difference, but the underlying database being used is SQL2014.

EDIT #1:

I Have just discovered that another FromSqlRaw method call within the same context also now fails when the DbSet<TestResult> TestItems {get; set;} is included within the DbContext.

This FromSqlRaw call worked previously and works again if I comment out the DbSet<TestResult> TestItems {get; set;} which make me think this is an issue with the configuration of the TestResult and or TestResultManifest classes?

EDIT #2:

I have tried flattening the result class...

public class TestResult2
{

    public Int32 Manifest_ManifestID { get; set; }

    public Int32 TransportJobID { get; set; }

}

If I now use the same FromSqlRaw to call the same stored procedure (though with the result type changed to TestResult2 of course), this works. It therefore has to be something to do with the configuration of the [Owned] entity.

EDIT #3

Following the advice from @ivan-stoev, I tried changing the model as follows since the TransportJobID property could be made unique...

public class TestResult
{

    public TestResultManifest Manifest { get; set; } = new TestResultManifest();

    [Key]
    public Int32 TransportJobID { get; set; }

}

[Owned]
public class TestResultManifest
{

    public Int32 ManifestID { get; set; }

}

This instead resulted in a new exception...

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Private.CoreLib.dll
'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

As a result, I then tried...

var e = TestItems.FromSqlRaw($"EXECUTE [dbo].[SelectTestItems]").AsEnumerable();

And this too resulted in the same exception.

Answer 1

I had the same problem with dotnet core 5.0 and 6.0 and I solved it like this:

Install the nuget package System.Linq.Async depending of you dotnet version https://www.nuget.org/packages/System.Linq.Async

and then in your query you would be able to add the .ToAsyncEnumerable()

This a example in case you use async method:

        public async Task<IEnumerable<ProductTableView>> GetProduct(Pagination pagination, string identifier)
    {

        var ListProductsTable = await _context.ModelView
                                       .FromSqlRaw("GetProductByIdentifier {0}", identifier)
                                       .ToAsyncEnumerable()
                                       .Select(a => new ProductTableView
                                       {
                                           ID = a.ID,
                                           Guid = a.Guid,
                                           Guid_Product_Category = a.Guid_Product_Category,
                                           Guid_Currency = a.Guid_Currency,
                                           NameProduct = a.NameProduct,
                                       }).ToListAsync();

        return ListProductsTable;
    }

In case you don't use the async method, you haven't need to add the above nuget package:

        public Task<IEnumerable<ProductTableView>> GetProduct(Pagination pagination, string identifier)
    {

        var ListProductsTable = _context.ModelView
                                       .FromSqlRaw("GetProductByIdentifier {0}", identifier)
                                       .AsEnumerable()
                                       .Select(a => new ProductTableView
                                       {
                                           ID = a.ID,
                                           Guid = a.Guid,
                                           Guid_Product_Category = a.Guid_Product_Category,
                                           Guid_Currency = a.Guid_Currency,
                                           NameProduct = a.NameProduct,
                                       }).ToList();

        return ListProductsTable;
    }