时间戳

首页 > 解决方案 > How do i execute stored procedures that return single values from EFCore?

c# - How do i execute stored procedures that return single values from EFCore?

问题描述

In my application I have three insert stored procedures that I need to execute using EF Core (this is in ASP.NET Core Web API 3.1).

Here are the stored procedures

spInsertPerson

create proc spInsertPerson
    @FirstName VARCHAR(255), 
    @Lastname VARCHAR(255)
AS 
BEGIN 
        INSERT INTO Person( FirstName, LastName ) VALUES ( @FirstName, @LastName )
        
        SELECT @@SCOPE_Identity() as NewId
END

spPublishData

CREATE PROC spspPublishData
    @Data VARBINARY(MAX),
    @scriptId int, 
    @sequanceId int OUTPUT
as
begin 

    INSERT INTO PubslishRegistry(  data, scriptRegisterid ) values (  @data , scriptId   )
    
    --some triiggers will fire and fill some tables along

    SELECT @sequanceId = FN_RANDOMIZED( CRC ) FROM [dbo].[hexa32pgr66] where scriptId = @scriptId

end

spBulkSaveContent

CREATE spBulkSaveContent
    @publisherId int
    @dtPublishersCollection PublisherType READONLY,  -- SQL user defined data type
    @dtStoresCollection StoresType READONLY
as
    begin 
    
            -- code written to save data 
    end

In the Web API DBCOntext

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       //.....other entries here

        modelBuilder.Entity<Flag>().HasNoKey();

        // ....other entries here
    }

I have defined the entity Flag in the Entities folder

public class Flag
{   
    public int NewId { get; set; }
}

In the Service class I have create the methods to save data

      public async Task<Int> Save(PersonDTO obj)
      {     
           string sqlQuery = $"exec spInsertPerson @FirstName, @Lastname";        
          
           var  flag = await this._context.Set<Flag>().FromSqlRaw<Flag>(sqlQuery,
                 new SqlParameter("@FirstName", obj.FirstName),
                  new SqlParameter("@Lastname", obj.LastName)                     
               ).FirstOrDefaultAsync();  

           return flag.NewId;
        }

But I get this error:

FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it.

Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

How do I fix this issue, then how do i execute those three stored procedures mentioned above and retrieve their data from my service?

Am i doing this in the correct way?

标签： c#entity-framework-core

解决方案

此代码试图将IQueryable(from .FromSqlRaw) 转换为DBSet(from .Set)

  public async Task<Int> Save(PersonDTO obj)
  {     
       string sqlQuery = $"exec spInsertPerson @FirstName, @Lastname";        
      
       var  flag = await this._context.Set<Flag>().FromSqlRaw<Flag>(sqlQuery,
             new SqlParameter("@FirstName", obj.FirstName),
              new SqlParameter("@Lastname", obj.LastName)                     
           ).FirstOrDefaultAsync();  

        return flag.NewId;
    }

我相信将其更改为以下将强制将IQueryable其具体化为列表，但我还没有尝试过。

  public async Task<Int> Save(PersonDTO obj)
  {     
       string sqlQuery = $"exec spInsertPerson @FirstName, @Lastname";        
      
       var  flag = await this._context.Set<Flag>().FromSqlRaw<Flag>(sqlQuery,
             new SqlParameter("@FirstName", obj.FirstName),
              new SqlParameter("@Lastname", obj.LastName)                     
           ).ToList().FirstOrDefaultAsync();  

        return flag.NewId;
    }

推荐阅读