CODEWITHSUNDEEP
c#mysqlentity-framework
Bryksin
Bryksin

Reputation: 2572

EF:6 - How to skip insertion of value if value has default value set in DB?

I'm fighting trough with Entity Framework 6 and MySQl Database

I got everything to work, however I'm confused with dates or not obligatory values.

In my database, in "Users" table I have column "RegistrationDate" which has default value of "CURRENT_TIMESTAMP" what is mean that if value not provided at insertion time it will insert default value = date time of the server

I got my schema reverse engineered into C# and all perfectly works, however when I insert "User" without setting a date to "RegistrationDate" property, it insert into Database new date as "0001-01-01 00:00:00" and ignore "CURRENT_TIMESTAMP".

So im wondering how to set it to ignore "RegistrationDate" and do not insert anything into db if it wasn't specifically set to some date?

Upvotes: 3

Views: 3467

Answers (2)

Ricardo Pieper
Ricardo Pieper

Reputation: 2702

I have a guess that the SQL EF generates is setting the field value. Even if you don't set in code, EF doesn't know that the database has a default value, and doesn't know that he should ignore it.

This article, from 2011, says that there is a DatabaseGenerated attribute, which you could use like this:

[DatabaseGenerated(DatabaseGenerationOption.Computed)]
public DateTime RegistrationDate { get; set; }

So, EF now knows that it should retrieve the data when you query the database, but should rely on the database to set the value.

However, I don't know what it would do if you explicitly set the value. Maybe it will ignore it, which may be not what you really want.

I didn't test it, it's just a guess, but it's a nice solution in my opinion.

[Edit1] Some months ago, I saw this video, and the guy does something like this in his DbContext class (i believe you have it) at 49:12 (the video is in portuguese)(i have modified the code, but didn't test it):

//This method will be called for every change you do - performance may be a concern
public override int SaveChanges()
{
    //Every entity that has a particular property
    foreach (var entry in ChangeTracker.Entries().Where(entry => entry.Entity.GetType().GetProperty("YourDateField") != null))
    {

        if (entry.State == EntityState.Added)
        {
            var date = entry.Property("YourDateField");

            //I guess that if it's 0001-01-01 00:00:00, you want it to be DateTime.Now, right?
            //Of course you may want to verify if the value really is a DateTime - but for the sake of brevity, I wont.
            if (date.CurrentValue == default(DateTime))
            {
                date.CurrentValue = DateTime.Now;
            }   
            else //else what? 
            {

                //Well, you don't really want to change this. It's the value you have set. But i'll leave it so you can see that the possibilities are infinite!

            }
        }


        if (entry.State == EntryState.Modified)
        {
            //If it's modified, maybe you want to do the same thing.

            //It's up to you, I would verify if the field has been set (with the default value cheking)
            //and if it hasn't been set, I would add this:

            date.IsModified = false;

            //So EF would ignore it on the update SQL statement.

        }

    }



}

Upvotes: 4

NDJ
NDJ

Reputation: 5194

I think many of us have been caught out by default database values when dealing with EF - it doesn't take them into account (there are many questions on this - e.g. Entity Framework - default values doesn't set in sql server table )

I'd say if you haven't explicitly set a datetime and want it to be null, you'll need to do it in code.

Upvotes: 1

Related Questions