Entity framework insert update problem!

Question

I have a problem with entity framework. Especially I can not do insert or update operation. Error message:

An error occurred while updating the entries. See the inner exception for details.System.Data.SqlClient.SqlException (0x80131904): The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
 at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
 at System.Data.SqlClient.SqlDataReader.get_MetaData()
 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
 at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
 at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
 at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
 at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
 at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

Can you help me?

Sample code:

ProductGroups group;
if (txtProductGroupName.Text.Trim() != null)
{
    group = new ProductGroups();
    group.ProductGroupName = txtProductGroupName.Text.Trim();
    context.ProductGroups.AddObject(group); 
    context.SaveChanges();
    context.AcceptAllChanges();
    lblState.ForeColor = Color.Red;
    lblState.Text = "Ürün grubu kaydedildi...";

}
else 
{
    lblState.ForeColor = Color.Red; 
    lblState.Text = "Ürün grubu ismini bos geçmeyiniz";
}

The ProductGroups table is defined with 5 columns:

[ProductGroupID] int
[ProductGroupName] varchar(100)
[ProductGroupComment] varchar(1000)
[CreatedOn] datetime
[DeletedOn] datetime

can you post sample code that fails? Also this should give you a hint: **The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.** — BrokenGlass, Mar 10 '11 at 07:38
possibly, you are giving a DateTime.MinValue() to somewhere and it is not in the range for the structure of your entity. — Beytan Kurt, Mar 10 '11 at 08:37

Ladislav Mrnka · Accepted Answer · 2012-05-02T15:31:05.643

8

You are not filling CreatedOn and DeletedOn datas in your added group instance. EF probably sent default .NET value which is 1/1/0001. It resulted in exception because SQL min allowed value for DATETIME column is 1/1/1753. Either fill value in your code or use DATETIME2 in database.

edited May 02 '12 at 15:31

answered Mar 10 '11 at 09:06

Ladislav Mrnka

360,892
59
660
670

The minimum value is 1/1/1753, at least in MS SQL Server. – Daniel Liuzzi May 02 '12 at 13:46
@DanielLiuzzi: Thanks for correction. You are right, I will update my answer. – Ladislav Mrnka May 02 '12 at 15:30

score 2 · Answer 2 · answered Oct 30 '13 at 10:38

Adding to BenSwayne's answer:

If you're updated entity is not coming from the database, you will have to tell entity framework to not update the CreatedOn property. To do this, update the code with the following:

if (entry.State == EntityState.Added)
{
     e.CreatedOn = currentTime;
}
else
{
    // handle updates of objects not originating from the database
    var objectStateManager = ((IObjectContextAdapter)this).ObjectContext.ObjectStateManager;
    ObjectStateEntry entryToUpdate = objectStateManager.GetObjectStateEntry(entry.Entity);
    entryToUpdate.RejectPropertyChanges("CreatedOn");
}

score 2 · Answer 3 · edited May 23 '17 at 12:14

2

Look this post please. It can be useful for you - Using sql date data type and EF4.

edited May 23 '17 at 12:14

Community

1
1

answered Mar 16 '11 at 09:00

JackD

597
4
7

score 0 · Answer 4 · answered Aug 14 '12 at 20:23

While the current answers explain why you encounter this error, no one has suggested a solution or best practice.

The most correct answer here is that you should either make your date fields nullable in the model, or provide a value during all inserts!

There is no need for you to use the DateTime2 datatype, that is just extra overhead and doesn't solve the problem. And you certainly don't want server generated default values for either of those fields.

I would suggest that CreatedOn should be set during inserts and DeletedOn should be nullable.

A neat thing to note is that the entity framework DbContext provides a SaveChanges() method that you can override in your context class. You can use this method to intercept EF saves and insert data such as the audit fields. I have a model base class which contains all of the common audit data like CreatedOn that all my models inherit from. Then I can update the audit dates for any record in my context like this (using EF 4.3 DbContext):

public override int SaveChanges()
{
    var currentTime = DateTime.UtcNow;

    foreach (var entry in ChangeTracker.Entries<Models.Abstract.ModelBase>())
    {
        // If this is a new entity add to the Db set the CreatedOn field to now
        if (entry.State == EntityState.Added)
        {
            entry.Entity.CreatedOn = currentTime;
        }

        // Always set the modified by/on
        entry.Entity.ModifiedOn = currentTime;
    }

    return base.SaveChanges();
}

Entity framework insert update problem!

4 Answers4