CODEWITHSUNDEEP
c#sql-serverentity-framework
Max Favilli
Max Favilli

Reputation: 6449

in entity framework how to have a stored procedure return nullable when column is defined as not nullable?

In SQL server I have a table with a column defined as not nullable.

And I have a stored procedure doing some joins so that the resulting output column for that field is actually never null.

In EF5 I have a complex type for that output which was automatically generated by EF (when I added the stored procedure to the model) and the field was generated as "nullable = false" I guess because the definition for that column in all the joined tables is "NOT NULL".

Now, in my solution I query the db with the store procedure and do a union (just for display, no "Savechanges") where the table I union with has NULL for that column/field in some rows; and there is where I am in trouble because I get an exception saying null is not allowed by my complex type.

My solution so far has been to manually change from not nullable to nullable the type of the complex type field, but every time I update from the model I get my change reverted (of course).

I guess there's a smarter solution but I can't see it...

Any idea?

Upvotes: 4

Views: 4039

Answers (1)

JamieA
JamieA

Reputation: 2013

If you have a proc that returns a non-nullable column called mycol like below 

     create PROCEDURE [dbo].[test1] 
         AS
         BEGIN
        SET NOCOUNT ON;
      select col1,col2,col3,mycol from mytable
         END

You could simply union your select statement with an empty result set which includes a null for the column that needs to be nullable

create PROCEDURE [dbo].[test2] 
AS
BEGIN
SET NOCOUNT ON;
select col1,col2,col3,mycol from mytable
union
select col1,col2,col3,null from mytable where 1=2

END

EF imports test1 with mycol as nullable = false, and imports test2 with mycol as nullable = true

Upvotes: 3

Related Questions