Entity Framework 7 Stored Procedure Result to Model

c# entities entity-framework-core sql-server stored-procedures

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

1
0
4/27/2016 8:40:07 AM

Popular Answer

the issue is NOT that I cannot get data from storedProcedure. The issue is that due to the storedProcedure, my Model has more properties than columns in database-table. However I'd still like to access model-entities sometimes directly without using stored procedure which is not working as the debugger says "invalid column" for the column that is not in the table but in the stored procedure.

Hope it makes it more clear.

Thanks again in advance.

Nicn

1
4/28/2016 6:58:27 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow