Results 1 to 7 of 7

Thread: [RESOLVED] Entity Framework - adding records with null values

  1. #1

    Thread Starter
    New Member HailMary's Avatar
    Join Date
    Dec 2023
    Posts
    9

    Resolved [RESOLVED] Entity Framework - adding records with null values

    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.
    Name:  01.jpg
Views: 1049
Size:  3.5 KB
    3 of these columns have a default value setting.

    The model in my solution reflects the structure of my db.
    Code:
    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.

    Code:
    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

  2. #2

    Thread Starter
    New Member HailMary's Avatar
    Join Date
    Dec 2023
    Posts
    9

    Re: Entity Framework - adding records with null values

    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.

  3. #3
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,549

    Re: Entity Framework - adding records with null values

    Defining the column as
    Code:
    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?

  4. #4

    Thread Starter
    New Member HailMary's Avatar
    Join Date
    Dec 2023
    Posts
    9

    Re: Entity Framework - adding records with null values

    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

  5. #5
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,549

    Re: Entity Framework - adding records with null values

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

    something like

    Code:
    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.

  6. #6

    Thread Starter
    New Member HailMary's Avatar
    Join Date
    Dec 2023
    Posts
    9

    Re: Entity Framework - adding records with null values

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

    Code:
    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.

  7. #7
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,549

    Re: Entity Framework - adding records with null values

    Quote Originally Posted by HailMary View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width