Entity Framework 7 Stored Procedure Result to Model

Question

I have a Model which is mapped to the database via EF7. All properties are identical to the mapped columns of the table.

Now I have a Stored Procedure, which returns some Joined Columns but the basis is still the mapped Model.

Here is my model(Entity) which is mapped to Table1 on my database.

public partial class Table1 { public int id { get; set; } public bool Column1{ get; set; } public int Column2 { get; set; } public int Column3 { get; set; } public int Column4 { get; set; } public int Column5 { get; set; } }

My StoredProcedure returns all of these columns and additionally a Column from another table, which contains additional information on this object.

So My StoredProcedure looks like this:

CREATE PROCEDURE Table1_Select
    AS
        BEGIN
            SELECT  Column1,
                    Column2,
                    Column3,
                    Column4,
                    Column5,
                    Table2.JoinedColumn1
            FROM    Table1
            JOIN    Table2 on Table1.ID = Table2.ID_Table1
        END

My Idea was to just add a Property to the Model

public string JoinedColumn1 {get; set;}

This is working for executing the StoredProcedure, but it's not working as soon as I want to get one Entity with

MyContext.Table1.FirstOrDefault(t=>t.id == 1)

this always returns a Server Error 500 in Fiddler whereas the Stored Procedure Execution with "Table1.FromSql("Table1Select")" works fine.

Or do I have to decide whether to use StoredProcedures for my Model OR accessing Entities directly?

Any Best-Practices or solutions for this?

Many thanks in advance. Nicn

Why do not the Store procedures included in the project which using Entity Framework Database First approach? — rootturk, Apr 27 '16 at 08:46
How to add StoredProcedures in Scaffold command? I only found [this link](https://github.com/aspnet/EntityFramework/wiki/Design-Meeting-Notes-(October-1,-2015)#table-selection-in-reverse-engineering) which says -t for tables or -s for schemas ... — nicn, Apr 27 '16 at 09:06

score 0 · Answer 1 · answered Apr 27 '16 at 08:46

0

try this way,

var results = db.Database.SqlQuery<YourModel>("exec YourSPName [Optional Parameters]").ToList();

answered Apr 27 '16 at 08:46

Sagar R

595
3
14

`return context.Table1.FromSql` is working fine when I add the joined Column as Property, however `context.Table1.ToList()` is not working anymore. – nicn Apr 27 '16 at 09:05
Model: `public partial class Table1 { public int id { get; set; } public bool Column1{ get; set; } public int Column2 { get; set; } public int Column3 { get; set; } public int Column4 { get; set; } public int Column5 { get; set; } public string JoinedColumn1 { get; set; } }` Working command: `StringBuilder sql = new StringBuilder(); sql.Append("EXEC Table1_Select"); return Table1.FromSql(sql.ToString()).ToList();` Not working command: `return context.Table1.ToList();` or do you need the files? – nicn Apr 27 '16 at 09:16
it will be much better if you provide files. – Sagar R Apr 27 '16 at 09:57
you can find my testproject here: http://www.filedropper.com/testproject_1. It's a tiny WebAPI, you may have to include your sql-server and create the Database if you need to test it with data. you can find the SP-Creation script in the root-folder of the zip file. – nicn Apr 27 '16 at 10:17
@nicn please check my latest answer. – Sagar R Apr 28 '16 at 07:17

score 0 · Answer 2 · edited May 23 '17 at 12:16

I read from a source as a solution use ADO.NET Connection Sample.

Parameters Sample

try
{
            SqlDataAdapter adapt = new SqlDataAdapter("Table1_Select", ConnectionString);
            adapt.SelectCommand.Parameters.AddWithValue("@yourparameters", param);

            adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            adapt.Fill(dt);
            adapt.Dispose();
            adapt = null;

            return dt;
}
catch(Exception ex)
{
throw ex
}

Don't Parameters Sample ;

try
{
    SqlDataAdapter adapt = new SqlDataAdapter("Table1_Select", ConnectionString);
    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataTable dt = new DataTable();
    adapt.Fill(dt);
    adapt.Dispose();
    adapt = null;

    return dt;
}
catch (Exception ex)
{
    throw ex;
}

You get table columns set datatable and read dataTable values and set your class datatable values.

Read DataTable Sample :

 DataTable dt = YourGetDataTableFunction(val);
  DataRow dr = dt.Rows[0]; //index is zero
string Column1 = dr["column1"].ToString();
string Column2 =dr["column2"].ToString();

I hope it helps.

Other Question : How to run stored procedures in Entity Framework Core?

score 0 · Answer 3 · answered Apr 28 '16 at 07:05

0

From your recent post,you said that you are using same model and getting error,so i would like to suggest that use [NOTMAPPED] before your pertcular declared entity lets say,

public int Column5 { get; set; } which is only used while you are calling your SP,so write like,

[NotMapped]
public int Column5 { get; set; }

answered Apr 28 '16 at 07:05

Sagar R

595
3
14

This is working for entities, but `.FromSql("exec SP_Name")` does not return this column now. Do I have to use Akin's way for storedProcedures then to get this column's value? – nicn Apr 29 '16 at 09:56
have you tried this? it might work for you. var results = db.Database.SqlQuery("exec YourSPName [Optional Parameters]").ToList(); – Sagar R Apr 29 '16 at 10:19
It seems that in 7.0.0-rc1-final FromSql has replaced SqlQuery and has to be against a DbSet. We will see in the team which way we will go and which cases may appear in our project. many thanks for your effort in this. – nicn Apr 29 '16 at 10:55
@nicn : it's my pleasure to work with you in this topic,as you said your team will decide that what will go and cases,so i would like to request you that if you find proper solution than also post in this page so other can solve from your updated answer.Thanks – Sagar R Apr 29 '16 at 11:02

Entity Framework 7 Stored Procedure Result to Model

3 Answers3