您的位置: 首页  >  技术问答  >  使用多个LINQ语句的SQL日期时间溢出

使用多个LINQ语句的SQL日期时间溢出

分类: 技术问答 • 2022-04-05 21:54:24

使用多个LINQ语句的SQL日期时间溢出

问题描述：

我遇到以下问题。使用多个LINQ语句的SQL日期时间溢出

在下面的代码块中，第一次通过if语句触发并且一切正常。第二次通过formData.Where发生SQL DateTime溢出错误。被撤回的值是一个字符串，与日期无关。正在引用的记录确实有一个日期，但它是当天。我已经改变了这些if语句触发的顺序，并且哪一个是第一个或第二个都没有关系。第二个总是抛出异常。

foreach (string riderGroup in riderGroups) 
      { 
       if (riderGroup == "69") 
       { 
        var deathRiderValue = formData.Where(x => x.FieldName == "rider69Value").Select(x => x.FieldValue).Single().ToString(); 
        var rider = _riderDataService.GetRiderById(Convert.ToInt32(deathRiderValue)); 
        premiumAccount.AddRider(new PolicyRider { Rider = rider, PremiumAccount = premiumAccount, Policy = policy });       
       } 
       if (riderGroup == "211") 
       { 
        var gmabRiderValue = formData.Where(x => x.FieldName == "rider211Value").Select(x => x.FieldValue).Single().ToString(); 
        var rider = _riderDataService.GetRiderById(Convert.ToInt32(gmabRiderValue)); 
        premiumAccount.AddRider(new PolicyRider { Rider = rider, PremiumAccount = premiumAccount, Policy = policy }); 
       } 
       if (riderGroup == "96") 
       { 
        var giveawayPromoValue = formData.Where(x => x.FieldName == "rider96Value").Select(x => x.FieldValue).Single().ToString(); 
        if (giveawayPromoValue == "1") 
        { 
         var rider = _riderDataService.GetRiderById(5); 
         premiumAccount.AddRider(new PolicyRider { Rider = rider, PremiumAccount = premiumAccount, Policy = policy }); 
        } 
       } 
      }

任何想法？

异常详细信息：System.Data.SqlTypes.SqlTypeException：SqlDateTime溢出。必须介于1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM之间。

这里是调用堆栈：

[SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.] 
    System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam) +7688 
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026 
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375 
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 
    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +161 
    System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 
    System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +14 
    NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) +347 
    NHibernate.Id.InsertSelectDelegate.ExecuteAndExtract(IDbCommand insert, ISessionImplementor session) +77 
    NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) +112 
    NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session) +283 
    NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) +391 
    NHibernate.Action.EntityIdentityInsertAction.Execute() +283 
    NHibernate.Engine.ActionQueue.Execute(IExecutable executable) +48 
    NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) +839 
    NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) +579 
    NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) +278 
    NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event) +118 
    NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event) +219 
    NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) +232 
    NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event) +347 
    NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) +193 
    NHibernate.Impl.SessionImpl.SaveOrUpdate(String entityName, Object obj) +155 
    NHibernate.Engine.SaveUpdateCascadingAction.Cascade(IEventSource session, Object child, String entityName, Object anything, Boolean isCascadeDeleteEnabled) +106 
    NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) +238 
    NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) +101 
    NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) +135 
    NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) +667 
    NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) +292 
    NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) +188 
    NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) +135 
    NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) +588 
    NHibernate.Event.Default.AbstractFlushingEventListener.CascadeOnFlush(IEventSource session, IEntityPersister persister, Object key, Object anything) +130 
    NHibernate.Event.Default.AbstractFlushingEventListener.PrepareEntityFlushes(IEventSource session) +363 
    NHibernate.Event.Default.AbstractFlushingEventListener.FlushEverythingToExecutions(FlushEvent event) +203 
    NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event) +144 
    NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet`1 querySpaces) +289 
    NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) +206 
    NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) +227 
    NHibernate.Impl.ExpressionQueryImpl.List() +146 
    NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) +72 
    NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +93 
    NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +50 
    System.Linq.Queryable.Single(IQueryable`1 source) +211 
    FrontOffice.HelperExtensions.FormProcessHelper.AddOnPremiumProcess(Int32 id) in c:\Users\hprentiss\Documents\SI Project\T3 Core\Dev\FrontOffice\HelperExtensions\FormProcessHelper.cs:314 
    FrontOffice.Areas.NewBusiness.Controllers.ImportFormsController.ProcessFormAddOnPremium(Int32 id) in c:\Users\hprentiss\Documents\SI Project\T3 Core\Dev\FrontOffice\Areas\NewBusiness\Controllers\ImportFormsController.cs:135 
    lambda_method(Closure , ControllerBase , Object[]) +161 
    System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +19 
    System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +209 
    System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27 
    System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28 
    System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +15 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +49 
    System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57 
    System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223 
    System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223 
    System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223 
    System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223 
    System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +15 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +49 
    System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24 
    System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +44 
    System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +15 
    System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +16 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +54 
    System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +16 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +44 
    System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +12 
    System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25 
    System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +16 
    System.Web.Mvc.Async.WrappedAsyncResult`1.End() +58 
    System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +44 
    System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +11 
    System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9724953 
    System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

的FORMDATA类

public class FormData : Entity 
{ 
    public virtual int FormSubmissionID { get; set; } 
    public virtual string FieldName { get; set; } 
    public virtual string FieldValue { get; set; } 

    public FormData() 
    { 

    } 
}

实体类，它继承

[Serializable] 
public class Entity 
{ 
    public virtual int ID { get; set; } 
    public virtual bool IsActive { get; set; } 
    public virtual DateTime DateCreated { get; set; } 
    public virtual DateTime? DateModified { get; set; } 
    public virtual int? ModifiedBy { get; set; } 

    public Entity() 
    { 
     IsActive = true; 
     DateCreated = DateTime.Now; 
    } 
    public override bool Equals(object obj) 
    { 
     if (ID != 0) 
     { 
      Entity obj2 = obj as Entity; 
      return ((obj2 != null) && (ID == obj2.ID)); 
     } 
     return base.Equals(obj); 
    } 

    public override int GetHashCode() 
    { 
     return (ID != 0) ? ID.GetHashCode() : base.GetHashCode(); 
    } 

    public virtual void Deactivate() 
    { 
     IsActive = false; 
    } 

    public virtual void EncryptFields() 
    { 
    } 

    public virtual object AddHistoryRecord() 
    { 
     return null; 
    } 
}

FORMDATA地图

public class FormDataMap : BaseMap<FormData> 
{ 
    public FormDataMap() 
    { 
     Table("tblFormData"); 
     Id(x => x.ID, "FormDataID"); 
     Map(x => x.FormSubmissionID); 
     Map(x => x.FieldName); 
     Map(x => x.FieldValue); 
    }   
}

实体地图

public BaseMap() 
    { 
     Id(x => x.ID); 
     MapEntity(); 
     Map(x => x.IsActive).Not.Nullable().Default("1"); 
     Map(x => x.DateCreated).Not.Nullable().Default("getdate()"); 
     Map(x => x.DateModified); 
     Map(x => x.ModifiedBy); 
    } 
    protected virtual void MapEntity() 
    { 
    }
+1

你是什么意思'DateTime overflow'？所有这些代码中使用了“DateTime”的位置？什么是*实际*错误？ 'datetime'字段不允许存储无效值，那么如何溢出？你是否将日期存储为字符串，并试图将它们转换为'DateTime'？这本身就是一个严重的错误。 –

+0

发布* full *例外，包括其调用堆栈。你可以用'Exception.ToString（）'得到它。还张贴实际引发错误的代码。你在这里发布的内容似乎并没有在任何地方使用日期 –

+0

添加了异常和堆栈跟踪 – HarvP

答

你的例外规定发生了什么事在“没有fromdate”变量，它被设置为null。在那里使用断点，在“fromdate”内跟踪值，它不能小于1/1/1753 12:00:00 AM并且不能大于12/31/9999 11:59:59 PM。 如果您尝试将DateTime类型的变量设置为null，则会发生此错误。声明该变量为可空，即DateTime?。这将解决问题。

+0

有2列需要到期DateTime。 1是DateCreated，我们不允许为null，并且DateModified已经可以为null。 – HarvP

+0

fromdate是一个DateTime类型？ –

+0

没有从日期。我不确定你在哪里看到。有一个FormData类从具有DateCreated和DateModified的实体类继承。 – HarvP

答

这应该是注释，但它太长：

像@M阿迪尔·哈立德在他的回答表示，最常见条件造成这种异常试图把一个值在SQL Server datetime列即对于C＃有效但对于SQL Server无效。 可能发生的情况是关键，但很难从您提供的信息中分辨出来。

  1. 你的粘贴代码是哪一行出现异常？

  2. 堆栈跟踪（粘贴在下面）中的那些2行号码如何？

FrontOffice.HelperExtensions.FormProcessHelper.AddOnPremiumProcess（的Int32 ID）在C：\用户\ hprentiss \文件\ SI项目\ T3核心\开发\ FrontOffice \ HelperExtensions \ FormProcessHelper.cs：314

FrontOffice .Areas.NewBusiness.Controllers.ImportFormsController。ProcessFormAddOnPremium（Int32 id）in c：\ Users \ hprentiss \ Documents \ SI Project \ T3 Core \ Dev \ FrontOffice \ Areas \ NewBusiness \ Controllers \ ImportFormsController.cs：135

我想这些不是您自己的代码，但那就是你可能从中得到的线索。如果我们假设Datetime.Now是一个有效的值，那么将null的值更改为其他值，就像完整性检查一样？

+0

这里没有Datetimes。 NHibernate发布了一个更新并决定一个空日期应该是最小值。 – HarvP

+0

够公平的，但实际上这是“有DateTime的东西”。只是导致它在NHibernate内部发生并不会改变异常的性质。 – SlimsGhost

相关推荐