"Object must implement IConvertible" error with Time data type in SQL Server 2008 R2

[zzort]

Hi,

I have a database table in SQL Server R2 that has two columns of type Time. When I use the PetaPoco T4 templates to generate the data class off this table, I see that PetaPoco assigns the type DateTime to the properties corresponding to these Time columns. When I then try to load data from the table in my application, I get the exception below.

The problem lies in line 2095 of PetaPoco.cs (v4.0.3):

else if (!dstType.IsAssignableFrom(srcType))
{
    converter = delegate(object src) { return Convert.ChangeType(src, dstType, null); };
}

For the two columns in question, the delegate's src object is of type TimeSpan but dstType is DateTime, and the Convert.ChangeType method can't convert the one to the other.

To work around this problem I made a change to PetaPocoCore.ttinclude, under class SqlServerCeSchemaReader : SchemaReader, in the method string GetPropertyType(string sqlType), starting at line 594. What was originally this:

case "smalldatetime":
            case "datetime":
            case "date":
            case "time":
                sysType=  "DateTime";
                  break;

I changed to this:

case "smalldatetime":
            case "datetime":
            case "date":
                sysType=  "DateTime";
                  break;
            case "time":
                sysType = "TimeSpan";
                break;

This seems to have resolved the issue.

Is it an error that the SQL Server Time data type is mapped to DateTime instead of TimeSpan? Should this change be incorporated into the T4 template? Thanks.

Here's the exception text:

Server Error in '/' Application.

Object must implement IConvertible.

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.InvalidCastException: Object must implement IConvertible.

Source Error: 


Line 2093:                  else if (!dstType.IsAssignableFrom(srcType))
Line 2094:                  {
Line 2095:                      converter = delegate(object src) { return Convert.ChangeType(src, dstType, null); };
Line 2096:                  }
Line 2097:              }

Source File: D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Models\PetaPoco.cs    Line: 2095 

Stack Trace: 


[InvalidCastException: Object must implement IConvertible.]
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +9511173
   PetaPoco.c__DisplayClass3b.b__37(Object src) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Models\PetaPoco.cs:2095
   petapoco_factory_0(IDataReader ) +508
   PetaPoco.d__7`1.MoveNext() in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Models\PetaPoco.cs:780
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +327
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   PetaPoco.Database.Fetch(String sql, Object[] args) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Models\PetaPoco.cs:601
   MyApp.Business.PetaPocoRepository.Fetch(String sql, Object[] args) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Business\PetaPocoRepository.cs:50
   MyApp.ViewModels.Factories.EpisodeSongManager.GetSongsForEpisode(Int32 episodeId) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\ViewModels\Factories\EpisodeSongManager.cs:176
   MyApp.ViewModels.Factories.EpisodeSongManager.GetSongListForEpisode(Int32 episodeId) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\ViewModels\Factories\EpisodeSongManager.cs:154
   MyApp.Controllers.EpisodeSongController.ListSongsForEpisode(Int32 episodeId) in D:\Users\Username\Documents\Visual Studio 2010\Projects\MyApprepo\vs_project\MyApp\MyApp\Controllers\EpisodeSongController.cs:21
   lambda_method(Closure , ControllerBase , Object[] ) +150
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +208
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.c__DisplayClass15.b__12() +55
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +263
   System.Web.Mvc.c__DisplayClass17.b__14() +19
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +191
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +343
   System.Web.Mvc.Controller.ExecuteCore() +116
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +97
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
   System.Web.Mvc.c__DisplayClassb.b__5() +37
   System.Web.Mvc.Async.c__DisplayClass1.b__0() +21
   System.Web.Mvc.Async.c__DisplayClass8`1.b__7(IAsyncResult _) +12
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.c__DisplayClasse.b__d() +50
   System.Web.Mvc.SecurityUtil.b__0(Action f) +7
   System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +22
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +60
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8972317
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547

[bradbamford]

Thanks for the information, I have the same issue. What this never resolved?

[linhub15]

Also had this issue, I implemented your fix and works fine now. (PetaPoco v5.1.289) I changed Line 681 - 686 of PetaPoco.Core.ttinclude to:

case "date":
        sysType=  "DateTime";
        break;
case "time":
        sysType= "TimeSpan";
	break;