Entity Framework creates wrong model from stored procedure

Question

I have a stored procedure "GET_PARTNER" like this:

SELECT
...
Partners.name AS 'name'  -nvarchar
Partners.city AS 'city'  -nvarchar
NULL AS 'sales'          -money
NULL AS 'comments'       -nvarchar
...

I have added entity framework 6 to the project and set it to create model from database and it creates a model "GET_PARTNER_Result" like this:

    //------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace WSExport.Model
{
using System;

public partial class GET_PARTNER_Result
{
   public string name {get; set;}
   public string city {get; set;}
   public Nullable<int> sales {get; set;}    -wrong datatype
   public Nullable<int> comments {get; set;} -wrong datatype
}
}

How can I modify the stored procedure, or is it possible to configure ef so it will create the model with the correct datatypes? Like this:

public Nullable<decimal> sales {get; set;}
public string comments {get; set;}

Lukasz Szozda · Accepted Answer · 2017-08-21 12:52:34Z

3

By default NULL is INT datatype. Use CAST:

SELECT
...
Partners.name AS name                       --nvarchar
,Partners.city AS city                      --nvarchar
,CAST(NULL AS MONEY) AS sales               --money
,CAST(NULL AS NVARCHAR(1000)) AS comments   --nvarchar

Quick check of metadata:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT NULL AS c', NULL, 0)

Rextester DEMO

You could also check your entire store procedure's metadata by using:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'EXEC GET_PARTNER params..', NULL, 0)

edited Aug 21, 2017 at 12:52

answered Aug 21, 2017 at 12:40

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carl Quirion Over a year ago

Missing CAST on the second conversion, but OP should be able to figure it out. Upvote. :)

Lukasz Szozda Over a year ago

@CralDasProblem Thanks :)

Collectives™ on Stack Overflow

Entity Framework creates wrong model from stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related