EF Core 7调用存储过程时FromSql抛出InvalidOperationException异常

问题概述 #

使用EF Core 7调用存储过程时，查询生成器抛出异常。已参照官方语法及相关案例调试，仍无法定位问题。

我的代码 #

SqlParameter casinoState = new SqlParameter("CasinoState", casinoStateFilter);
SqlParameter isActive = new SqlParameter("IsActive", isActiveFilter);
SqlParameter prohibitedCountry = new SqlParameter("ProhibitedCountry", prohibitedCountryFilter);
SqlParameter softwareProviderId = new SqlParameter("SoftwareProviderId", softwareProviderFilter);
SqlParameter casinoName = new SqlParameter("CasinoName", casinoNameFilter);

FormattableString sql = $"exec CasinoIdSerach @CasinoState={casinoStateFilter}, @IsActive={isActive}, @ProhibitedCountry={prohibitedCountryFilter}, @SoftwareProviderId={softwareProviderFilter}, @CasinoName={casinoNameFilter}"; 
var casinos = _context.Casinos.FromSql<Domain.Casino>(sql).ToList();

也尝试过_context.Casinos.FromSql(sql).ToList();，结果一致。

异常信息 #

System.InvalidOperationException: ''FromSql' or 'SqlQuery' 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 user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

尝试过的SQL变体 #

测试过几种SQL写法：

• FormattableString sql = $"exec CasinoSearch @CasinoName='%test%'";
• FormattableString sql = $"select * from Casino";

第二种写法无异常，但不符合需求。另外，调用无返回数据集的存储过程时，以下代码可正常运行：

int result = _context.Database.ExecuteSqlRaw(query, parameters.ToArray());

更新：Casino实体配置 #

附上OnModelCreating中Casino的配置代码：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.ApplyConfiguration(new Configurations.CasinoConfiguration());
...
}

public class CasinoConfiguration : IEntityTypeConfiguration<Casino>
{
    public void Configure(EntityTypeBuilder<Casino> modelBuilder)
    {
        modelBuilder.ToTable("Casino");
        modelBuilder.ToTable(tb => tb.HasTrigger("TR_upd_Casino"));
    
        modelBuilder.Ignore(c => c.EWalletWithdrawalTurnaround);
        modelBuilder.Ignore(c => c.CreditAndDebitCardWithdrawalTurnaround);
        modelBuilder.Ignore(c => c.BankTransferWithdrawalTurnaround);
        modelBuilder.Ignore(c => c.CheckWithdrawalTurnaround);
    
        modelBuilder.HasMany(c => c.Bonuses)
            .WithOne(b => b.Casino)
            .OnDelete(DeleteBehavior.Cascade);
    
        modelBuilder.HasMany(c => c.WebsiteCasinoReviewBonuses)
            .WithOne(w => w.Casino)
            .OnDelete(DeleteBehavior.Cascade);
    
        modelBuilder.HasMany(c => c.Promotions);
        modelBuilder.HasMany(c => c.CasinoGameTypes);
    
        modelBuilder.HasMany(c => c.AvailableCurrencies)
                .WithOne(cc => cc.Casino)
                .HasConstraintName("FK_CasinoCurrency_Casino");
    
        modelBuilder.HasMany(c => c.CompatibleOperatingSystems)
            .WithOne(cc => cc.Casino)
            .HasConstraintName("FK_CasinoOperatingSystem_Casino");
    
        modelBuilder.HasMany(c => c.CasinoSoftwareProviders)
            .WithOne(b => b.Casino)
            .HasConstraintName("FK_CasinoSoftwareProvider_Casino");
                
        modelBuilder.OwnsMany(c => c.Alerts)
            .ToTable("CasinoAlert");
    
        modelBuilder
            .HasMany(a => a.Languages)
            .WithOne(l => l.Casino)
            .HasConstraintName("FK_CasinoLanguage_Casino")
            .OnDelete(DeleteBehavior.Cascade);
    
        modelBuilder
            .HasMany(c => c.TrackerLinks)
            .WithOne(t => t.Casino)
            .HasConstraintName("FK_CasinoTrackerLink_Casino")
            .OnDelete(DeleteBehavior.Cascade);
    
        #region Intersect tables
    
        modelBuilder
            .HasMany(a => a.BankingProviders)
            .WithMany(b => b.Casinos)
            .UsingEntity<Dictionary<string, object>>(
                "Casino_BankingProvider",
                j => j
                    .HasOne<BankingProvider>()
                    .WithMany()
                    .HasForeignKey("BankingProviderId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.BettingMarkets)
            .WithMany(b => b.Casinos)
            .UsingEntity<Dictionary<string, object>>(
                "Sportsbook_BettingMarket",
                j => j
                    .HasOne<BettingMarket>()
                    .WithMany()
                    .HasForeignKey("BettingMarketId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
    
        modelBuilder
            .HasMany(a => a.BettingSports)
            .WithMany(b => b.Casinos)
            .UsingEntity<Dictionary<string, object>>(
                "Sportsbook_BettingSport",
                j => j
                    .HasOne<BettingSport>()
                    .WithMany()
                    .HasForeignKey("BettingSportId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.BettingTournamentLeagues)
            .WithMany(b => b.Casinos)
            .UsingEntity<Dictionary<string, object>>(
                "Sportsbook_BettingTournamentLeague",
                j => j
                    .HasOne<BettingTournamentLeague>()
                    .WithMany()
                    .HasForeignKey("BettingTournamentLeagueId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.Jurisdictions)
            .WithMany(b => b.Casinos)
            .UsingEntity<Dictionary<string, object>>(
                "Casino_Jurisdiction",
                j => j
                    .HasOne<Jurisdiction>()
                    .WithMany()
                    .HasForeignKey("JurisdictionId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.AllowedCountries)
            .WithMany(b => b.AllowedCasinos)
            .UsingEntity<Dictionary<string, object>>(
                "Casino_Allowed_Country",
                j => j
                    .HasOne<Country>()
                    .WithMany()
                    .HasForeignKey("CountryId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.ProhibitedCountries)
            .WithMany(b => b.ProhibitedCasinos)
            .UsingEntity<Dictionary<string, object>>(
                "Casino_Prohibited_Country",
                j => j
                    .HasOne<Country>()
                    .WithMany()
                    .HasForeignKey("CountryId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.SportsbookProhibitedCountries)
            .WithMany(b => b.ProhibitedSportsbooks)
            .UsingEntity<Dictionary<string, object>>(
                "Sportsbook_Prohibited_Country",
                j => j
                    .HasOne<Country>()
                    .WithMany()
                    .HasForeignKey("CountryId"),
                    j => j
                        .HasOne<Casino>()
                        .WithMany()
                        .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.CasinoAllowedUSStates)
            .WithMany(b => b.AllowedCasinos)
            .UsingEntity<Dictionary<string, object>>(
                "Casino_Allowed_USState",
                j => j
                    .HasOne<USState>()
                    .WithMany()
                    .HasForeignKey("USStateId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        modelBuilder
            .HasMany(a => a.SportsbookAllowedUSStates)
            .WithMany(b => b.AllowedSportsbooks)
            .UsingEntity<Dictionary<string, object>>(
                "Sportsbook_Allowed_USState",
                j => j
                    .HasOne<USState>()
                    .WithMany()
                    .HasForeignKey("USStateId"),
                j => j
                    .HasOne<Casino>()
                    .WithMany()
                    .HasForeignKey("CasinoId")
            );
    
        #endregion
    
    }
}

内容的提问来源于stack exchange，提问作者Adam Hey