OrmLite LocalDate to DateTime Conversion Issue

DailyTransactionHistoryTableModel.cs

using NodaTime;
using ServiceStack.DataAnnotations;

namespace LoanVersionTwoDataService.ServiceModel {
    
    [Alias("cfo_daily_trans_hist")]
    public class DailyTransactionHistoryTableModel {

        [Alias("dh_daily_trans_hist_id")]    
        [PrimaryKey]
        [AutoIncrement]
        public int Id{ get; set;}

        [Alias("dh_tq_tran_quote_id")]
        [ForeignKey(typeof(TransactionQuote))]
        public int TransactionQuoteId{get; set;}

        [Alias("dh_sn_scenario_id")]
        public int ScenarioId{ get; set;}

        [Alias("dh_val_time_stamp")]
        public LocalDate ValuationTimeStamp{get; set;}
    }
}

DrawAndPaydownsDbFixture.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LoanVersionTwoDataService.OrmLiteConverters;
using LoanVersionTwoDataService.ServiceModel;
using NodaTime;
using NodaTime.Serialization.ServiceStackText;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;

namespace LoanVersionTwoDataService.Tests.DrawsAndPaydowns {
    public class DrawsAndPaydownsDbFixture: IDisposable
    {
        
        public DrawsAndPaydownsDbFixture()
        {
            var provider = new  SqliteOrmLiteDialectProvider();
            provider.RegisterConverter<LocalDate>(new NodaLocalDateOrmLiteConverter());

            DbFactory = new OrmLiteConnectionFactory( ":memory:", provider);
            using ( var db = DbFactory.OpenDbConnection())
            {
                db.CreateTable<Component>();
                db.CreateTable<TransactionQuote>();
                db.CreateTable<TransactionLinkTableModel>();
                db.CreateTable<DailyTransactionHistoryTableModel>();

                
                var transactionQuote = new TransactionQuote()
                {
                    Id = 1337,
                    TransactionId = 8675309,
                };

                var component = new Component()
                {
                    Id = 13,
                    TransactionQuoteId = 1337
                };

                var floorDerivativeTransactionQuote = new TransactionQuote()
                {
                    Id = 4,
                    TransactionId = 8
                };

                var capDerivativeTransactionQuote = new TransactionQuote()
                {
                    Id = 5,
                    TransactionId = 10
                };


                var floorTransactionLinkTableModel = new TransactionLinkTableModel()
                {
                    Id = 1,
                    LinkType = 17752,
                    TransactionId = 8,
                    TransactionLinkId = 8675309
                };

                var capTransactionLinkTableModel = new TransactionLinkTableModel()
                {
                    Id = 2,
                    LinkType = 17752,
                    TransactionId = 10,
                    TransactionLinkId = 8675309
                };

                var transactionDailyHistories = new []
                {
                    new DailyTransactionHistoryTableModel()
                    {
                        
                        ScenarioId = 1,
                        TransactionQuoteId = 1337,
                        ValuationTimeStamp = new LocalDate(1993, 10, 14)
                    },
                    new DailyTransactionHistoryTableModel()
                    {
                        ScenarioId = 1,
                        TransactionQuoteId = 1337,
                        ValuationTimeStamp = new LocalDate(1994, 10, 13)
                    }
                };

                var floorDailyHistories = new []
                {
                    new DailyTransactionHistoryTableModel()
                    {
                        ScenarioId = 1,
                        TransactionQuoteId = 4,
                        ValuationTimeStamp = new LocalDate(1993, 10, 15)
                    },
                    new DailyTransactionHistoryTableModel()
                    {
                        ScenarioId = 1,
                        TransactionQuoteId = 4,
                        ValuationTimeStamp = new LocalDate(1994, 10, 13)
                    }
                };

                var capDailyHistories = new []
                {
                    new DailyTransactionHistoryTableModel()
                    {
                        ScenarioId = 1,
                        TransactionQuoteId = 5,
                        ValuationTimeStamp = new LocalDate(1993, 10, 16)
                    },
                    new DailyTransactionHistoryTableModel()
                    {
                        ScenarioId = 1,
                        TransactionQuoteId = 5,
                        ValuationTimeStamp = new LocalDate(1994, 10, 13)
                    }
                };


                
                db.InsertAll(new []
                {
                   transactionQuote,
                   floorDerivativeTransactionQuote,
                   capDerivativeTransactionQuote
                });

                db.InsertAll(new []
                {
                    floorTransactionLinkTableModel,
                    capTransactionLinkTableModel
                });
                db.Insert(component);
                db.InsertAll(transactionDailyHistories);
                db.InsertAll(floorDailyHistories);
                db.InsertAll(capDailyHistories);  
            }
        }

        public OrmLiteConnectionFactory DbFactory { get; set;}

        public void Dispose()
        {
        }
    }
}

NodaLocalDateOrmLiteConverter.cs

using System;
using System.Data;
using NodaTime;
using ServiceStack.OrmLite;

namespace LoanVersionTwoDataService.OrmLiteConverters
{
    public class NodaLocalDateOrmLiteConverter : OrmLiteConverter
    {
        public override string ColumnDefinition => "DATETIME";

        public override DbType DbType => DbType.DateTime;

        public override object ToDbValue(Type fieldType, object value)
        {
            var localDate = value as LocalDate?;
            return localDate?.AtMidnight().ToDateTimeUnspecified();
        }

        public override object FromDbValue(Type fieldType, object value)
        {
            DateTime parsedDate;
            if (TryParseDate(value, out parsedDate))
            {
                return LocalDateTime.FromDateTime(parsedDate).Date;
            }
            return null;
        }

        private bool TryParseDate(object value, out DateTime outDate)
        {
            if (value is DateTime)
            {
                outDate = (DateTime)value;
                return true;
            }
            var s = value as string;
            if (s != null)
            {
                DateTime parsedDate;
                if (DateTime.TryParse(s, out parsedDate))
                {
                    outDate = parsedDate;
                    return true;
                }
            }

            outDate = default(DateTime);
            return false;
        }
    }
}

ValuationsCommander.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LoanVersionTwoDataService.ServiceModel;
using NodaTime;
using ServiceStack.Data;
using ServiceStack.OrmLite;

namespace LoanVersionTwoDataService.ServiceInterface.DrawsAndPaydowns {
    public class ValuationsCommander {

        private readonly IDbConnectionFactory _dbConnectionFactory;

        private readonly ITransactionQuoteProvider _transactionQuoteProvider;

        private static int _embeddedDerivativeLinkType = 17752;

        public ValuationsCommander(IDbConnectionFactory dbConnectionFactory, ITransactionQuoteProvider transactionQuoteProvider)
        {
            _dbConnectionFactory = dbConnectionFactory ?? throw new ArgumentNullException(nameof(dbConnectionFactory));
            _transactionQuoteProvider = transactionQuoteProvider ?? throw new ArgumentNullException(nameof(transactionQuoteProvider));
        }

        public async Task SetTransactionAndRelatedEmbeddedDerivativeValuationsToBad(int transactionId, LocalDate effectiveDate)
        {
            using( var db = _dbConnectionFactory.OpenDbConnection())
            {
                using( var unitOfWork = db.BeginTransaction())
                {
                    var transactionQuote = await _transactionQuoteProvider.GetAsyncWithTransactionId(transactionId);

                    await SetRelatedDailyTransactionHistoryRecordsToBad(db, transactionQuote.Id, effectiveDate);

                    var embeddedDerivativeQuery = db.From<TransactionLinkTableModel>().Where(
                        transactionLink => transactionLink.TransactionLinkId == transactionId && transactionLink.LinkType == _embeddedDerivativeLinkType
                    );
                    var embeddedDerivatives =  await db.SelectAsync<TransactionLinkTableModel>(embeddedDerivativeQuery);

                    if( embeddedDerivatives != null && 0 < embeddedDerivatives.Count)
                    {
                        foreach (var derivative in embeddedDerivatives)
                        {
                            var embeddedDerivativeTransactionQuote = await _transactionQuoteProvider.GetAsyncWithTransactionId(derivative.TransactionId);
                            if( embeddedDerivativeTransactionQuote != null)
                            {
                                await SetRelatedDailyTransactionHistoryRecordsToBad(db, embeddedDerivativeTransactionQuote.Id, effectiveDate);
                            }

                        }
                    }

                    unitOfWork.Commit();
                }
            }
        }

        private async Task<int> SetRelatedDailyTransactionHistoryRecordsToBad( IDbConnection db, int transactionQuoteId, LocalDate effectiveDate)
        {
            
            var transactionHistoryQuery = db.From<DailyTransactionHistoryTableModel>()
                .Update( history => history.ScenarioId)
                .Where( history => history.TransactionQuoteId == transactionQuoteId && effectiveDate <= history.ValuationTimeStamp);
            return await db.UpdateOnlyAsync(new DailyTransactionHistoryTableModel(){ ScenarioId = 3}, onlyFields: transactionHistoryQuery);
            
        }
    }
}

ValuationsCommanderTests.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LoanVersionTwoDataService.ServiceInterface.DrawsAndPaydowns;
using LoanVersionTwoDataService.ServiceModel;
using NodaTime;
using NSubstitute;
using ServiceStack.Data;
using ServiceStack.OrmLite;
using Xunit;

namespace LoanVersionTwoDataService.Tests.DrawsAndPaydowns {
    public class ValuationsCommanderTests: IClassFixture<DrawsAndPaydownsDbFixture> {

        private readonly DrawsAndPaydownsDbFixture _fixture;

        public ValuationsCommanderTests(DrawsAndPaydownsDbFixture fixture)
        {
            _fixture = fixture;
        }
        
        [Fact]
        public async Task SetTransactionAndRelatedEmbeddedDerivativeValuationsToBad_ValidInput_CorrectlyManipulatesDB()
        {
            var transactionQuoteProvider = Substitute.For<ITransactionQuoteProvider>();
            transactionQuoteProvider.GetAsyncWithTransactionId(8675309).Returns(new TransactionQuote()
            {
                Id = 1337,
                TransactionId = 8675309,
            });
            transactionQuoteProvider.GetAsyncWithTransactionId(8).Returns( new TransactionQuote()
            {
                Id = 4,
                TransactionId = 8
            });
            transactionQuoteProvider.GetAsyncWithTransactionId(10).Returns( new TransactionQuote()
            {
                Id = 5,
                TransactionId = 10
            });
            var commander = new ValuationsCommander(_fixture.DbFactory, transactionQuoteProvider);

            var effectiveDate = new LocalDate(1993, 11, 13);
            var transactionId = 8675309;
            await commander.SetTransactionAndRelatedEmbeddedDerivativeValuationsToBad(transactionId, effectiveDate);

            using(var db = _fixture.DbFactory.OpenDbConnection())
            {
                var transactionHistoriesQuery = db.From<DailyTransactionHistoryTableModel>();

                var transactionHistories = await db.SelectAsync<DailyTransactionHistoryTableModel>(transactionHistoriesQuery);

                Assert.NotNull(transactionHistories);
                Assert.Equal(transactionHistories.Count, 6);
                foreach( var history in transactionHistories)
                {
                    Assert.Equal(history.ScenarioId, history.ValuationTimeStamp < effectiveDate ? 1 : 3);
                }

            }
        }
    }
}