Nullable in entity causes issues in my linq expression

Question

Here is the InnerException from this method:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Below is the method where this exception is generated. I believe it has something to do with the Created_Date and Modified_Date columns being datetime?

I have tried doing this multiple ways as you can tell by the code below role.Is_Active is also a nullable column however non of the issue is experienced there.

public IEnumerable<Models.DTO.Role> GetRoles(bool active = true, int limit = 10)
        {
            using (var context = new SmartWarehouseEntities())
            {
                var roles = context.Security_Role
                                    .Where(role => role.Is_Active == active)
                                    .Select(role => new Models.DTO.Role
                                    {
                                        SecurityRoleId = role.Security_Role_Id,
                                        RoleName = role.Role_Name,
                                        RoleDetail = role.Role_Detail,
                                        IsActive = role.Is_Active ?? false,
                                        Created = role.Created_Date.HasValue ? role.Created_Date.Value :  DateTime.MinValue,
                                        Modified = role.Modified_Date.HasValue ? role.Created_Date.Value : DateTime.MinValue
                                    })
                                    .Take(limit);
                return roles.ToArray();
            }
        }

EDIT (SOLVED):

Thank you for input everyone, here are the steps I used to resolve the issue based on comments and answers.

ROLE MODEL (Updated to accept Nullable):

public class Role
    {
        public int SecurityRoleId { get; set; }

        public string RoleName { get; set; }

        public string RoleDetail { get; set; }

        public bool IsActive { get; set; }

        public DateTime? Created { get; set; }

        public string CreatedDate => !Created.HasValue
            ? string.Empty
            : Created.Value.ToString(CultureInfo.InvariantCulture);

        public DateTime? Modified { get; set; }

        public string ModifiedDate => !Modified.HasValue
            ? string.Empty
            : Modified.Value.ToString(CultureInfo.InvariantCulture);
    }

GetRoles Method:

public IEnumerable<Models.DTO.Role> GetRoles(bool active = true, int limit = 10)
        {
            using (var context = new SmartWarehouseEntities())
            {
                var roles = context.Security_Role
                                    .Where(role => role.Is_Active == active)
                                    .Select(role => new Models.DTO.Role
                                    {
                                        SecurityRoleId = role.Security_Role_Id,
                                        RoleName = role.Role_Name,
                                        RoleDetail = role.Role_Detail,
                                        IsActive = role.Is_Active ?? false,
                                        Created = role.Created_Date,
                                        Modified = role.Modified_Date
                                    })
                                    .Take(limit);
                return roles.ToArray();
            }
        }

i think the issue is in the nullable DateTime. DateTime is a struct, and therefore not nullable, they must have a value — ThisIsMe, Mar 05 '19 at 15:34
I suspect the problem is the use of `DateTime.MinValue`, which is outside the range of values SQL Server can handle. Have you tried using a different default value? Or just keep it nullable in your model? Note that your `Modified` property is using `Created_Date.Value`, which it probably shouldn't. — Jon Skeet, Mar 05 '19 at 15:34
Awesome thanks for the catch @JonSkeet, I was just thinking the same thing after reading deeper into the exception. Pretty sure you just solved it will update after change. — nulltron, Mar 05 '19 at 15:37

score 2 · Accepted Answer · answered Mar 05 '19 at 15:37

in sql server you have (at least) two DateTime type:

datetime : 1753/01/01 to then end of time
datetime2: 0001/01/01 to the end of time

Each of this type can be binded to the .Net DateTime, but DateTime.MinValue is always 0001/01/01. So with the sql column configured as a datetime, the sql server argues when tou try to write a DateTime.MinValue.

So you can :

alter your model at a database level to use datetime2, or
alter your code for not use DateTime.MinValue

there are also other considerations such as DateTime2 vs DateTime in SQL Server

score 1 · Answer 2 · answered Mar 05 '19 at 15:37

Don't pass DateTime.MinValue. It's January 1, 0001, which is less than the minimum value for SQL Server datetime (1/1/1753.) Unless you're working with historical data that goes back that far, you could just create your own constant for a value like 1/1/1753 and use that.

DateTime.MaxValue is okay because for both the .NET type and SQL server it's 12/31/9999.

More often we just use datetime2 as our type in SQL Server.

Nullable in entity causes issues in my linq expression

2 Answers2