media media - 10 months ago 39
C# Question

How to call a sql-server procedure using Entity Framework 6

I have the following code in Sql Server:

@name NVARCHAR(200),
@password NVARCHAR(200)
DECLARE @res int
SELECT @res = COUNT(*)
FROM [User]
WHERE [User].Username = @name AND [User].PasswordHashed = HASHBYTES('SHA2_512', @password)
return @res

As you see the procedure is returning an
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 to mention that I have saw here and here but I was wondering if there is a direct way in Entity Framework.

Answer Source

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.