3

so I have quite the odd predicament here. My SQL table is set to allow nulls for my ZipCode column, like so:

CREATE TABLE [dbo].[Companies]
(
    [CompanyId] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(100) NOT NULL, 
    [Address]     NVARCHAR (100) NULL,
    [City]        NVARCHAR (50)  NOT NULL,
    [State]       NVARCHAR (2)   NOT NULL,
    [ZipCode]     INT   NULL,
    [PhoneNum]    BIGINT  NULL,
    [CreatedDate] DATETIME2 NOT NULL DEFAULT GetDate()
)

This should let me have ZipCode's value as NULL, right? Well, apparently not....

I keep getting this error:

An exception of type 'System.Data.ConstraintException' occurred in EntityFramework.dll but was not handled in user code Additional information: The 'ZipCode' property on 'Company' could not be set to a 'null' value. You must set this property to a non-null value of type 'System.Int32'.

Can anyone think of any reason why this would be doing that?? I've checked and double checked my database project and my local database, and they both match. And all of my unit tests pass, so I'm pretty much at a loss here.

Any help would be appreciated!

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
mashedpotatoes
  • 33
  • 2
  • 7
  • Did you read the error message? It says that you're trying to assign `null` to an `Int32`. The corresponding field in your EF entity would have to be a nullable int for this to work, given the presence of the `NULL` designation in the database `ZipCode` field. – Robert Harvey Jun 12 '14 at 19:49
  • Did you check that both the created SQL table has a nullable field and the Entity Model knows that this is nullable? How did you generate yoru EF code? Database first or Code/Model first? – cost Jun 12 '14 at 19:52
  • 5
    Why is `ZipCode` an int? Are you going to be multiplying them or something? What about +4 codes and foreign codes? Use VarChar. – Dour High Arch Jun 12 '14 at 19:52
  • Yea, I agree with @DourHighArch. Also remember than an int will lose any leading zeros – cost Jun 12 '14 at 19:54
  • I did read it, but I assumed that it was saying that because the datatype needs to be an Int32. I didn't realize there was anything special I needed to do to make the value nullable. How would I make it a nullable int like you said? – mashedpotatoes Jun 12 '14 at 19:57
  • Additionally zip codes can start with zero. Not that there weren't enough arguments against storing them as `Int` already. – Hart CO Jun 12 '14 at 20:02

2 Answers2

6

Your property in C# Entity Framework is apparently:

public int ZipCode;

You'll have to change that to 

public Nullable<int> ZipCode;

You can also do that in the properties window in the visual editor of the entity framework's edmx file.

UPDATE:

It would also be advisable(if possible) to change the type of the ZipCode to string. It would not only take care of the current 'nullable' problem but would also scale very well with any change in requirements further down the line!!

Satwik Nadkarny
  • 5,086
  • 2
  • 23
  • 41
  • 2
    My preference would be a `string` for this. 10 digit zip codes have a dash in them, there's no math here, and international zip codes sometimes contain letters. – Robert Harvey Jun 12 '14 at 19:53
  • @RobertHarvey Absolutely, its always better to have zipcodes and phone numbers as strings. That way it allows you to fit in the dash(-) which works well when you storing them on an international level (international zipcodes, postal codes and Phone numbers) – Satwik Nadkarny Jun 12 '14 at 19:56
  • 1
    Good to know, thanks @SatwikNadkarny! I honestly wouldn't have thought to make the zip code (or phone number for that matter) a string, but it definitely would make sense after reading everyone's comments. Thanks everyone for your help! – mashedpotatoes Jun 12 '14 at 20:09
2

you can use as

public int? ZipCode;

it means Nullable

isanka thalagala
  • 456
  • 2
  • 10
  • 22