execute stored procedure in entity Framework Core

问题:

I am working on .NET CORE, Entity Framework core. I have stored procedure that I need to execute from .NET class. My stored procedure takes number of 'Context' and I don't know how to deal this, although I have dataView which is final exception.

I wounder if I can use my dataView instead of context.dataModel class, current implementation (Context.Claims.FromSql)

dataView

public class ClaimDataView
{
    public Guid ClaimId { get; set; }
    public int IdNum { get; set; }
    public string Value { get; set; }
    public DateTime ExpirationDate { get; set; }
    public ClaimAttributionActions Action { get; set; }
    public bool ActiveStateForRole { get; set; }

}

stored-Procedure call

public Guid UserId { get; set; }
public Guid ClientId { get; set; }
public Guid ConsultationId { get; set; }

  var userParam = new SqlParameter("@UserVal", UserId);
  var clientParam = new SqlParameter("@ClientVal", ConsultationId);
  var consultationParam = new SqlParameter("@ConsultationVal", ConsultationId);

 //**************need help in following line
  var query = Context.Claims.FromSql("EXECUTE dbo.ListUserClaims @userId=@UserVal, @clientId=@ClientVal, @consultationId=@ConsultationVal"
            , userParam, clientParam, consultationParam);

new update

moduleContext Class

  protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       //other models....
       modelBuilder.Query<ClaimDataView>();
    }

Stored Procedure executing from

 var query = Context.Query<UserDataView>().FromSql("EXECUTE dbo.ListUserClaims @userId=@UserVal, @clientId=@ClientVal, @consultationId=@ConsultationVal"
            , userParam, clientParam, consultationParam);

error

System.InvalidOperationException: Cannot create a DbSet for 'UserDataView' because this type is not included in the model for the context.
 at Microsoft.EntityFrameworkCore.Internal.InternalDbQuery`1.get_EntityType()

回答1:

You can utilize the Query Types introduced in EF Core 2.1.

First you need to register you class as query type:

modelBuilder.Query<ClaimDataView>();

Then you can use Context.Query<ClaimDataView>() in place of your current Context.Claims:

var query = Context.Query<ClaimDataView>().FromSql(…);

回答2:

If you are not on version 2.1, you will need to add:

public DbSet<ClaimDataView> ClaimDataView { get; set; }

to your moduleContext. And add NotMapped to your class:

[NotMapped]
public class ClaimDataView