c# - EFCore5，使用 FromSqlRaw 和 [Owned] 属性

Question

我试图在从存储过程返回数据时利用 EF Core“[Owned]”属性。我的模型如下...

[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; }

}

这是在我的 DbContext 中实现的，如下所示...

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

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

这是我的理解from here当它直接映射到物理表时，会产生名为“TransportJobID”和“Manifest_ManifestID”的列，因此我使用以下存储过程来提取必要的数据...

CREATE PROCEDURE [dbo].[SelectTestItems]

as

    select Manifest_ManifestID = 3,
        TransportJobID = 22;

    return @@error;

如您所料，结果...

但是，每当我运行它时，我都会在 Entity Framework 深处抛出一个 NullReferenceException...

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)

鉴于我只是在使用(我认为是)标准功能，我希望它能工作——至少我认为如果映射到一个真实的表并通过 LINQ 检索它会工作，并且实体也应该可以使用 FromRawSql(...) 检索；谁能解释一下我做错了什么和/或遗漏了什么。

我不希望这有什么不同，但所使用的底层数据库是 SQL2014。

编辑#1:

我刚刚发现另一个 FromSqlRaw当 DbSet<TestResult> TestItems {get; set;} 时，同一上下文中的方法调用现在也会失败包含在 DbContext 中。

这FromSqlRaw如果我注释掉 DbSet<TestResult> TestItems {get; set;}，调用之前有效并再次有效这让我觉得这是 TestResult 和/或 TestResultManifest 类的配置问题？

编辑#2:

我试过扁平化结果类...

public class TestResult2
{

    public Int32 Manifest_ManifestID { get; set; }

    public Int32 TransportJobID { get; set; }

}

如果我现在使用相同的 FromSqlRaw调用相同的存储过程(尽管结果类型当然更改为 TestResult2)，这是可行的。因此，它必须与 [Owned] 实体的配置有关。

编辑#3

根据@ivan-stoev 的建议，我尝试如下更改模型，因为 TransportJobID 属性可以是唯一的...

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; }

}

这反而导致了一个新的异常...

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.

结果，我接着试了...

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

这也导致了同样的异常。

Answer 1

异常(与任何未处理的 NRE 一样)当然具有误导性并隐藏了实际问题，即您遇到了一些 EF Core Keyless entity limitations .主要原因是他们

Cannot have a key defined.

因此不能作为关系的主体(这是所有者拥有的)，因此(注意第二个项目符号):

Only support a subset of navigation mapping capabilities, specifically:

• They may never act as the principal end of a relationship.
• They may not have navigations to owned entities
• They can only contain reference navigation properties pointing to regular entities.
• Entities cannot contain navigation properties to keyless entity types.

您必须在模型级别解决该问题，否则每当您尝试对上下文执行某事时(由于查询或 CUD 操作需要模型而延迟初始化的那一刻)，都会出现上述异常).