How to call a SQL Server procedure using Entity Framework 6

Question

I have the following code in SQL Server:

ALTER PROCEDURE Checking
@name NVARCHAR(200),
@password NVARCHAR(200)
AS
Begin
    SET NOCOUNT ON
        DECLARE @res int
        SELECT @res = COUNT(*)
        FROM [User]
        WHERE [User].Username = @name AND [User].PasswordHashed = HASHBYTES('SHA2_512', @password)
        return @res
END

As you can see, the procedure is returning an Int32 value. In my C# code, I want to use this method using Entity Framework 6 and I have the following code:

SearchEngineEntities context = new SearchEngineEntities();
//Show the returned value by calling checking procedure in C#

How can I have the returning value of Checking procedure in C#? I have saw here and here but I was wondering if there is a direct way in Entity Framework.

have you read this? http://stackoverflow.com/questions/10339750/entity-framework-stored-procedure-return-value — Ricardo Pontual, Nov 11 '16 at 13:40
@Phill actually I care about that and that was the reason I did not use the methods that were used in the links that I brought. — Green Falcon, Nov 11 '16 at 13:44
Your stored proc hashes the password, and hashes without a salt, so if the database is stolen then it's pretty easy to attack the database to find passwords. — Phill, Nov 11 '16 at 14:31
@phill thanks, actually I am aware of that but at present I'm a stage behind security. I have not written a code which does my job yet. — Green Falcon, Nov 11 '16 at 14:33

Farhan Nasim · Accepted Answer · 2016-11-11T17:04:37.057

This link shows how to import and execute stored procedures with the Visual Studio Entity Data Wizard. You can figure out how to do that manually yourself from it.

Update: Mike has correctly put in comment that the link I shared earlier describes how to import stored procedures into Entity Framework data models.

On successful import of your stored procedure, EF will automatically generate a method in your DbContext extension class (SearchEngineEntities in your case) that corresponds to the stored procedure in your database. For your procedure, it should create a method that takes two string parameters and returns your int result encapsulated in ObjectResult type. Depending on your configuration it should be something like the following (not tested):

public virtual ObjectResult<Nullable<int>> Checking(string name, string password)
{
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<int>>("Checking", name, password);
}

You'll be able to call the stored procedure by calling context.Checking("name", "pass"). Iterate over the returned ObjectResult to find the result your procedure has returned.

@media In order to use a stored procedure from entity framework, you need to import the procedure into your model. EF will then create a function for you to call. That link is a pretty good step by step example of adding a stored proc, and it also has a nice little description of what EF does with it. — Mike, Nov 11 '16 at 16:09

How to call a SQL Server procedure using Entity Framework 6

1 Answers1