Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

execute stored procedure in entity Framework Core without expecting map to dbset

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()
like image 443
K.Z Avatar asked Aug 24 '18 11:08

K.Z


People also ask

Can we use stored procedure in Entity Framework Core?

Stored procedures are one of the key advantages that the Microsoft SQL server provides. For boosting the query performance, the complex query should be written at the database level through stored procedures. Microsoft . NET Core supports calling of raw query and store procedure through entity framework.

What is the difference between DbContext and DbSet?

Intuitively, a DbContext corresponds to your database (or a collection of tables and views in your database) whereas a DbSet corresponds to a table or view in your database. So it makes perfect sense that you will get a combination of both!


2 Answers

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(...);

Update (EF Core 3.x+):

Starting with EF Core 3.0, query types have been consolidated with entity types and renamed to Keyless Entity Types, so the corresponding code is

modelBuilder.Entity<ClaimDataView>().HasNoKey().ToView(null);

and

var query = Context.Set<ClaimDataView>().FromSql(...);
like image 154
Ivan Stoev Avatar answered Oct 28 '22 07:10

Ivan Stoev


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
like image 27
rBalzer Avatar answered Oct 28 '22 07:10

rBalzer