CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-entities
nulltron
nulltron

Reputation: 646

Nullable<DateTime> in entity causes issues in my linq expression

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();
            }
        }

Upvotes: 0

Views: 69

Answers (2)

tschmit007
tschmit007

Reputation: 7800

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

Upvotes: 2

Scott Hannen
Scott Hannen

Reputation: 29272

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.

Upvotes: 1

Related Questions