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 describes the two possible ways, and their benefits. — Tamás Szabó
– Tamás Szabó, Commented Apr 7, 2017 at 6:45
but in this code how can do that i am write this code is correct or wrong?? — coderwill
– coderwill, Commented Apr 7, 2017 at 6:46
you can delete the user with entity framework linq command. Check This Answer. Thanks — Zaheer Ul Hassan
– Zaheer Ul Hassan, Commented Apr 7, 2017 at 6: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
– coderwill, Commented Apr 7, 2017 at 6:51

satnhak · Accepted Answer · 2017-04-07 08:31:36Z

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 7, 2017 at 8:31

answered Apr 7, 2017 at 6:53

satnhak

9,9707 gold badges68 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

coderwill Over a year ago

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

satnhak Over a year ago

Well, your table is called dbo.Users, but it is looking for dbo.ApplicationUsers Try applying the Table("Users") attribute to the ApplicationUsers` class.

coderwill Over a year ago

mean idk please give me hint how can do

coderwill Over a year ago

this is my public class ApplicationUser : IdentityUser { public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> 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 Over a year ago

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.

|

Mantas Čekanauskas · Accepted Answer · 2017-04-07 06:54:22Z

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 7, 2017 at 6:54

answered Apr 7, 2017 at 6:52

Mantas Čekanauskas

2,2386 gold badges26 silver badges49 bronze badges

8 Comments

coderwill Over a year ago

sp_MyStoredProc here replce to my sp name??as i can defind in class?

coderwill Over a year ago

and i want to need for the one new class in store just userid get set??

Mantas Čekanauskas Over a year ago

didn't quite catch your last question

coderwill Over a year ago

protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Users>() .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 Over a year ago

like this EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType. error is getting

|

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related