What would cause a SqlDateTime overflow when using LINQ to SQL SubmitChanges()?

Question

In my code I have multiple objects being added to the repository, I have tried running the repository Save() function one time at the end of all the loops, and also calling it after every object that is added. But either way I still get a SqlDateTime overflow when the db.SubmitChanges() in the repository .Save()... any idea?

 SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Source Error:

Line 155:        public void Save()
Line 156:        {
Line 157:            db.SubmitChanges();
Line 158:        }
Line 159:


Source File: C:\inetpub\wwwroot\Models\OrderRepository.cs    Line: 157


        foreach (string vol in volumes)
        {
            if (vol != "-1" && vol != "")
            {
                Product prod = orderRepository.getProductByVolumeID(System.Convert.ToInt32(vol));
                ProductVolume pvol = orderRepository.getProductVolumeByID(System.Convert.ToInt32(vol));

                OrderProduct oProd = new OrderProduct();
                oProd.OrderID = getOrder.OrderID;
                oProd.VolumeID = pvol.VolumeID;
                orderRepository.AddOrderProduct(oProd);


            }
        }

        foreach (string feat in features)
        {
            if (feat != "")
            {
                Product prod = orderRepository.getProductByID(System.Convert.ToInt32(feat));

                OrderFeature oFeat = new OrderFeature();
                oFeat.OrderID = getOrder.OrderID;
                oFeat.ProductID = prod.ProductID;
                orderRepository.AddOrderFeature(oFeat);


                featuresTotal += prod.UserIntervalPrice;
            }

        }

        totalTotal = volumeTotal + featuresTotal;
        orderRepository.Save();

Answer 1

Basically, the issue is that the Sql DATETIME datatype starts at 1/1/1753, wheras DateTime.MinValue is 1/1/0000 (or something like that). So, if you don't initialize the date properties, you get Sql overflows.

Options to fix:

a) Initialize the DateTime somewheres.

b) Switch sql to use the DATETIME2 datatype which does cover the whole range of .NET DateTime values. [SQL 2008 only]

Answer 2

You can validate your dates in the OnValidate() partial method using SqlDateTime.MinValue and SqlDateTime.MaxValue.