[RESOLVED] Entity Framework - adding records with null values

[HailMary]

When uploading data to my db, my old projects had me writing my own SQL statements, declaring every parameter one by one.

Today there's EF.

Here's a snag I've encountered:

I want to add a record to my db. My table has 6 columns. None of my columns accept null values.

3 of these columns have a default value setting.

The model in my solution reflects the structure of my db.

public class VerifyMobilePhone
{
    [Key]
    public string UserId { get; set; }
    [Required]
    public string PhoneVerifyCodeHash { get; set; }
    public int? DailyCodeCount { get; set; }
    [Required]
    public string Salt { get; set; }
    public DateTime? DateRequested { get; set; }
    public DateTime? CodeCountDateStamp { get; set; }
}

Before saving to the db, I'm assigning values to UserId, PhoneVerifyHash, and Salt.
I am not assigning anything to DailyCodeCount, DateRequested, and CodeCountDateStamp.

VerifyMobilePhone somebodypunchmeinthegrointhisinstant;

var verifynewphone = new VerifyMobilePhone
{
    UserId = userId,
    PhoneVerifyCodeHash = phoneVerifyCodeHash,
    Salt = salt
};

somebodypunchmeinthegrointhisinstant = verifynewphone;

_unitOfWork.VerifyMobilePhone.Add(somebodypunchmeinthegrointhisinstant);
_unitOfWork.Save();

SqlServer keeps throwing exceptions about null values. If I remove DailyCodeCount, DateRequested, and CodeCountDateStamp from my model, everything works.

What am I doing incorrectly?

- 4th down and long

[HailMary]

ADDENDUM:

The above code is doing this:
INSERT INTO VerifyMobilePhones(UserId, PhoneVerifyCodeHash,DailyCodeCount, Salt, DateRequested, CodeCountDateStamp)
VALUES ('6feec5f1-6b60-4586-bb6c-c9bc53e3bf5e', 'dfgsdfgsdfdgsd', null, 'fdgdfgsd', null, null)

Whereas I only want this:
INSERT INTO VerifyMobilePhones(UserId, PhoneVerifyCodeHash, Salt)
VALUES ('6feec5f1-6b60-4586-bb6c-c9bc53e3bf5e','dfgsdfgsdfdgsd','fdgdfgsd')

Error Message:
Cannot insert the value NULL into column 'DailyCodeCount', table 'FPP_Db.dbo.VerifyMobilePhones'; column does not allow nulls. INSERT fails.
The statement has been terminated.

[PlausiblyDamp]

Defining the column as

int?

is telling Entity Framework that the column does allow nulls, that is what the ? means in this context. What happens if you try just int as the datatype?

[HailMary]

Hi PD,

Thanks for your reply.
Initially I did not have the ?. Adding it was the only way that I could make the query succeed (but not work - nulls were introduced in my table).

Removing the ?, I get this:

Inner Exception 1:
SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

The error has nothing to do with datetime. I get a similar message for the int column.

I've decided to steer away from EF. It's great for devs who want to build simple CRUD websites, but doing things the old-fashioned way is far superior for anything but the most simplistic.
I'm going back to using SPROCs. So much more powerful, customizable, and secure.

I'll stay active in this thread and leave the code block in place in my app in case anyone wants to pursue the topic.

Old School still works

[PlausiblyDamp]

IIRC you can just mark those properties with the DatabaseGenerated attribute - https://www.learnentityframeworkcore...ated-attribute

something like

public class VerifyMobilePhone
{
    [Key]
    public string UserId { get; set; }
    [Required]
    public string PhoneVerifyCodeHash { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public int? DailyCodeCount { get; set; }

    [Required]
    public string Salt { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime? DateRequested { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)
    public DateTime? CodeCountDateStamp { get; set; }
}

would probably work.

[HailMary]

Your solution works perfectly!
Thanks for taking the time to respond.

public class VerifyMobilePhone
{
    [Key]
    public string UserId { get; set; }
    [Required]
    public string PhoneVerifyCodeHash { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public int DailyCodeCount { get; set; }
    [Required]
    public string Salt { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime DateRequested { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CodeCountDateStamp { get; set; }
}

Marking this as Resolved.

[PlausiblyDamp]

Hi PD,
...

I've decided to steer away from EF. It's great for devs who want to build simple CRUD websites, but doing things the old-fashioned way is far superior for anything but the most simplistic.
I'm going back to using SPROCs. So much more powerful, customizable, and secure.

I'll stay active in this thread and leave the code block in place in my app in case anyone wants to pursue the topic.

Old School still works

Just as a follow on...

I think both approaches have their place, possibly combining them to get the best of both worlds.

Any ORM (such as EF) can be a massive time saver when dealing with an OO language like C# and a database. In a more traditional approach you can end up with a lot of boilerplate code for accessing a DB, mapping results into an object model, figuring out efficient ways to push updates back etc. This kind of code can start to really add to the code size of a large project- an ORM can reduce a lot of this code and allow you to focus on the logic etc, rather than the SELECT, INSERT, etc.

However, complex DB logic can be a struggle to do in a 100% ORM approach, stored procs can really shine in this area. https://learn.microsoft.com/en-us/ef...red-procedures shows how you can call stored procs via EF and map the results into your objects. This approach means you can avoid writing lots of dull SQL code when the ORM can do a good enough job, but still drop into SQL when the benefits are there.