How to get output result from stored procedure use Entity Framework in C#?

Question

I'm working on an ASP.NET MVC project; my goal is to prepare a report from a table so, the first time I've wrote Linq code but it was too slow.

And after that I've written a SQL query it was so fast and I want to use stored procedure for getting report data from my table. In fact my project is so easy: it gets two dates - start date and end date - and displays result in a table.

I want to write my stored procedure to get two parameters - start date and end date - from C# code and then return output in a variable in C#.

The first question: how to convert my SQL query to a stored procedure with two parameters, start Date and End date?

The second question: how to return output result in C#?

SELECT 
    CAST(date_rec_slash AS DATETIME), COUNT(code_marz) AS total,
    CASE
       WHEN code_marz = 1 THEN 'a'
       WHEN code_marz = 2 THEN 'b'
       WHEN code_marz = 3 THEN 'c'
       WHEN code_marz = 4 THEN 'd'
       WHEN code_marz = 5 THEN 'e'
    END
FROM 
    dbo.tbl_bar 
WHERE 
    CAST(date_rec_slash AS DATETIME) BETWEEN '2017/12/01' AND '2017/12/31'
GROUP BY 
    CAST(date_rec_slash AS DATETIME), code_marz
ORDER BY 
    CAST(date_rec_slash AS DATETIME) ASC;

C#:

var spResults = db.Database.SqlQuery<tbl_bar>("Report");

Solution 1

Declare your store procedure using this syntax:

    USE yourDataBaseNAme
    GO

    CREATE PROCEDURE [dbo].yourStoreProcedureName
    @startDate nvarchar(30), 
    @endDate   nvarchar(30)
    AS 
       SELECT Cast(date_rec_slash as datetime) AS 'date_rec_slash', count(code_marz) as total,
              CASE
                  WHEN code_marz = 1 THEN 'a'
                  WHEN code_marz = 2 THEN 'b'
                  WHEN code_marz = 3 THEN 'c'
                  WHEN code_marz = 4 THEN 'd'
                  WHEN code_marz = 5 THEN 'e'
              END AS 'code_marz'
      FROM dbo.tbl_bar 
     WHERE Cast(date_rec_slash as datetime) between @startDate 
                                                AND @endDate
     GROUP BY Cast(date_rec_slash as datetime), code_marz
     ORDER BY Cast(date_rec_slash as datetime) ASC;
    GO

Call this store procedure in EF:

db.Database.SqlQuery<yourObjectNameToCAST>("yourStoreProcedureName");

Call store procedure with parameter in EF:

SqlParameter startDate= new SqlParameter("@startDate", "Value");
SqlParameter endDate= new SqlParameter("@endDate", "Value");
db.Database.SqlQuery<yourObjectNameToCAST>("exec yourStoreProcedureName @startDate, @endDate", startDate, endDate).ToList();

your Object to Cast:

public class yourObjectNameToCAST
{
     public datetime date_rec_slash { get; set; }
     public int total { get; set; }
     public string code_marz { get; set; }
}

Solution 2

You can declare your stored procedures using

CREATE PROCEDURE [dbo].YourStoredProcedure
    @Start DATETIME, 
    @END   DATETIME
AS

Then you can get rid of the code needed to cast from string

In order to get the results mapped as a c# object, you need to use SqlQuery or FromSql depending on the version of Entity Framework that you are using

Entity Framework 6

var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure").ToList();

To pass a parameter, you woild do something like

var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure @SomeParameter", 
             new SqlParameter("@SomeParameter", TheParameterValue)).ToList();

And for Entity Framework Core 2

var result = dbContext.Bar
.FromSql("EXEC YourStoredProcedure")
.ToList();

Where Bar is your C# object declared as a property with type DbSet<Bar> in your dbContext class

Based on your output you should create a C# object similar to

public class Bar
{
     public datetime DateRecSlash { get; set; }
     public int CodeMarz { get; set; }
}

Comments

[behnam]

but didn't exist FromSql Method I'm using EF 6.

[Andre Lombaard]

Yes, sorry, I'm using the latest version of EF. Will update my answer for the solutions for both versions

[Hasan Fathi]

@behnam this is helpful for you?

[behnam]

How to pass two parameter from EF SqlQuery db.Database.SqlQuery<yourObjectNameToCAST>("yourStoreProcedu‌​reName",paramas);

[behnam]

but there is a little bit issue about it.it was return 246 records but all of them are null value.In fact I need just the same output of sql server.photobox.co.uk/my/photo/full?photo_id=500535291910

[Hasan Fathi]

i have not any idea from your data

[Hasan Fathi]

probably your issue about null values related your given data casting, please check your field name equal to names that receive from database

[behnam]

I just need three outputs datetime and total and name of them forexample a,b,c,d,e that's all.

[Hasan Fathi]

Let us continue this discussion in chat.

[Hasan Fathi]

@benam please check final change

[behnam]

Let us continue this discussion in chat.