How to call stored procedure using Entity Framework code-first?

Question

I want create delete user by userid using a stored procedure in Entity Framework code-first. But I am new to Entity Framework so I don't know how can do that this task. Can anyone please let me know how I can do that?

This is my code so far:

Stored procedure in SQL Server:

CREATE PROCEDURE [dbo].[DeleteUserById]
    @UserId bigint
AS
BEGIN   
    DELETE FROM [dbo].[Users] 
    WHERE id = @UserId
END

Now now can this stored procedure be used in code? I don't know any one please tell me.

This is my class model builder :

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext() : base("DefaultConnection")
    {
    }

    public virtual DbSet<UserInfo> UserInfo { get; set; } 

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    public virtual ObjectResult<List<Users>> GetUsers()
    { 
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<List<UserInfo>>("GetUsers");
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Users>()
            .MapToStoredProcedures(s => s.Delete(u => u.HasName("DeleteUserById", "dbo")
                                            .Parameter(b => b.id, "userid"))
            );
    }  
}

This is my delete method in the controller :

public string DeleteUsers(int id)
{
        //here how can call this sp and delete this user
}

If anyone knows, then please help me with this task.

[This question](http://stackoverflow.com/questions/643880/commandtype-text-vs-commandtype-storedprocedure) describes the two possible ways, and their benefits. — Tamás Szabó, Apr 07 '17 at 06:45
but in this code how can do that i am write this code is correct or wrong?? — coderwill, Apr 07 '17 at 06:46
you can delete the user with entity framework linq command. [Check This Answer](http://stackoverflow.com/questions/31672899/how-do-i-delete-single-record-from-table-using-ef-6-1-1). Thanks — Zaheer Ul Hassan, Apr 07 '17 at 06:50
yes this is wave also but i want to need sp call so you can please know for sp then let me know — coderwill, Apr 07 '17 at 06:51

satnhak · Accepted Answer · 2017-04-07T08:31:36.857

2

You can do it with the ExecuteSqlCommand method on the database.

this.Database.ExecuteSqlCommand(
   "[dbo].[DeleteUserById] @UserId", 
   new SqlParameter("@UserId", id));

UPDATE

(answer to comment)

[Table("Users")]
public class ApplicationUser
{
}

edited Apr 07 '17 at 08:31

answered Apr 07 '17 at 06:53

satnhak

9,407
5
63
81

i m write this code and add OnModelCreating method then go for the login getting error Invalid object name 'dbo.ApplicationUsers'. can you please how can fix it – coderwill Apr 07 '17 at 08:11
Well, your table is called `dbo.Users`, but it is looking for `dbo.ApplicationUsers` Try applying the `Table("Users") attribute to the `ApplicationUsers` class. – satnhak Apr 07 '17 at 08:24
mean idk please give me hint how can do – coderwill Apr 07 '17 at 08:28
this is my public class ApplicationUser : IdentityUser { public async Task GenerateUserIdentityAsync(UserManager manager, string authenticationType) { // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType var userIdentity = await manager.CreateIdentityAsync(this, authenticationType); // Add custom user claims here return userIdentity; }} – coderwill Apr 07 '17 at 08:29
still getting error The entity types 'Users' and 'ApplicationUser' cannot share table 'Users' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them. – coderwill Apr 07 '17 at 08:35
I think you might need to post another question. You seem to have two entities pointing at the same table. – satnhak Apr 07 '17 at 08:38
can you please let me know i can write this code it's correct or not in class?? – coderwill Apr 07 '17 at 08:45
You can call a stored procedure using the method I've described. The thing is you use an ORM like the EntityFramework to avoid writing stored procedures. The idea is to do all of your data access through LINQ-to-entities statements. It seems like you are fighting against the grain. – satnhak Apr 07 '17 at 09:27

Mantas Čekanauskas · Answer 2 · 2017-04-07T06:54:22.290

1

Using EF this can be achieved this way. Anf you won't need MapToStoredProcedures.

public void DeleteUsers(int id)
{
    SqlParameter idParam = new SqlParameter("@UserId", id);
    context.Database.ExecuteSqlCommand("DeleteUserById @UserId", idParam);
}

Edit: And you are not returning anything from stored procedure, so method signiture should be void.

edited Apr 07 '17 at 06:54

answered Apr 07 '17 at 06:52

Mantas Čekanauskas

2,218
6
23
43

sp_MyStoredProc here replce to my sp name??as i can defind in class? – coderwill Apr 07 '17 at 06:54
and i want to need for the one new class in store just userid get set?? – coderwill Apr 07 '17 at 06:58
didn't quite catch your last question – Mantas Čekanauskas Apr 07 '17 at 06:59
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity() .MapToStoredProcedures(s => s.Delete(u => u.HasName("DeleteUserById", "dbo") .Parameter(b => b.id, "userid")) ); } i m write this code aftre don't show my user list i m getting error so how can fix it? – coderwill Apr 07 '17 at 07:00
like this EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType. error is getting – coderwill Apr 07 '17 at 07:02
this error is well explained here http://stackoverflow.com/questions/28531201/entitytype-identityuserlogin-has-no-key-defined-define-the-key-for-this-entit – Mantas Čekanauskas Apr 07 '17 at 07:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/140148/discussion-between-coderwill-and-mantas-cekanauskas). – coderwill Apr 07 '17 at 07:51
hi my delete action is work perfect but then now i am logout and login again getting error like this 'Invalid object name 'dbo.ApplicationUsers'.' – coderwill Apr 07 '17 at 07:56

How to call stored procedure using Entity Framework code-first?

2 Answers2