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

Question

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

Ivan Stoev · Accepted Answer · 2020-04-02T12:35:12.917

45

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

edited Apr 02 '20 at 12:35

answered Aug 24 '18 at 11:43

Ivan Stoev

179,274
12
254
292

many thanks to put me in right direction, however I am still getting no where... I have update my question ... refer code below newUpate heading... I have added in model class but have not create any configuration for ClaimDataView. I am not sure I need that but got error ... refer above – K.Z Aug 24 '18 at 12:25
1

It shouldn't be creating `DbSet` for `ClaimDataView`. Make sure you have no `DbSet()` calls. – Ivan Stoev Aug 24 '18 at 12:40
Noticed that your updated code uses `UserDataView` in some parts and `ClaimDataView` in other. Typo? Or the cause of the new exception? – Ivan Stoev Aug 24 '18 at 12:53
Is there a way to specify the *source* of the keyless types? I can call a sproc, and say that the result should go to some keyless entity type, but how do I add the sproc to my DB from OnModelCreating in the first place? I would like to say `modelBuilder.Entity().HasNoKey().FromSql("CREATE PROCEDURE Something...");` – Dave Cousineau Jun 08 '20 at 21:54
#@DaveCousineau Currently (v3.1.4) there is no such capability. – Ivan Stoev Jun 09 '20 at 02:11
@IvanStoev just bumped into this comment randomly. I think the answer to what I asked is that to create a sproc from your model, you would need to add it to a custom migration, not define it in OnModelCreating. But I think it's easier to maintain from the database side anyway, saving a CREATE script in your repo. – Dave Cousineau Oct 29 '20 at 19:17
3

also note that `FromSql` has been replaced with `FromSqlInterpolated` and `FromSqlRaw`. – Dave Cousineau Oct 29 '20 at 19:17
Question: "how do I call SP without expecting map to dbset". Answer: use DbSet. Why is this an accepted answer? – Egor Pavlikhin Dec 18 '20 at 01:50
4

@EgorPavlikhin I guess it depends what you're trying to do. The reason to avoid DbSet in this case is to not map your entity to a table. Keyless types register your type with the EF model without creating a table for it on the DB side. You can then use a sproc with your type. – Dave Cousineau Jan 08 '21 at 17:02
+1, thanks for the answer - I refer back to this now and again. I think an easy thing to miss is the .ToView(null) after the .HasNoKey() directive, otherwise the migration builder is still going to try to create a table from the snapshot without it. – Boyardee Jan 31 '22 at 18:43

score 6 · Answer 2 · answered Aug 24 '18 at 12:49

6

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

answered Aug 24 '18 at 12:49

rBalzer

297
1
5