How to overcome "The cast to value type 'System.DateTime' failed because the materialized value is null" when enumerating a model in a view in MVC

Question

I'm following an MS tutorial online on how to use code first MVC on an existing DB. I'm trying to display the contents of an MVC model in a view and bombing out here:

Exception Details

The cast to value type 'System.DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Source Error

Line 27: <thead>
Line 28: <tbody>
Line 29: @foreach (var customer in Model)
Line 30: {
Line 31: <tr>

Stack Trace:

[InvalidOperationException: The cast to value type 'System.DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.]
System.Data.Entity.Core.Common.Internal.Materialization.ErrorHandlingValueReader1.GetValue(DbDataReader reader, Int32 ordinal) +107 System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetColumnValueWithErrorHandling(Int32 ordinal) +57 lambda_method(Closure , Shaper ) +2588 System.Data.Entity.Core.Common.Internal.Materialization.Coordinator1.ReadNextElement(Shaper shaper) +181
System.Data.Entity.Core.Common.Internal.Materialization.SimpleEnumerator.MoveNext() +76 System.Data.Entity.Internal.LazyEnumerator1.MoveNext() +112 ASP._Page_Views_Customers_Index_cshtml.Execute() in C:\OneDrive\FakeFolderName\Development\MasterDataWebApp\MDMWebApp\Views\Customers\Index.cshtml:29 System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197 System.Web.Mvc.WebViewPage.ExecutePageHierarchy() +105 System.Web.WebPages.StartPage.RunPage() +17 System.Web.WebPages.StartPage.ExecutePageHierarchy() +73 System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +78 System.Web.Mvc.RazorView.RenderView(ViewContext viewContext, TextWriter writer, Object instance) +235 System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext viewContext, TextWriter writer) +107 System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context) +291 System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult) +13 System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +56
System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +420 System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList1 filters, ActionResult actionResult) +52
System.Web.Mvc.Async.<>c__DisplayClass3_6.b__3() +198 System.Web.Mvc.Async.<>c__DisplayClass3_1.b__5(IAsyncResult asyncResult) +100
System.Web.Mvc.Async.WrappedAsyncResult1.CallEndDelegate(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
System.Web.Mvc.<>c.b__152_1(IAsyncResult asyncResult, ExecuteCoreState innerState) +11
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +45 System.Web.Mvc.<>c.b__151_2(IAsyncResult asyncResult, Controller controller) +13
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +22 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
System.Web.Mvc.<>c.b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) +28
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9874041 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159

MVC Version: 5
Authentication: Individual User Accounts
EF Mode: Code first with existing database

There is a CustomersController which has an action named Index. Here is the code of the controller:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MDMWebApp.Models;

namespace MDMWebApp.Controllers
{
    public class CustomersController : Controller
    {
        private ApplicationDbContext _context;

        public CustomersController()
        {
            _context = new ApplicationDbContext();
        }

        protected override void Dispose(bool disposing)
        {
            _context.Dispose();
        }

        // GET: Customers
        [Route("payroll/customers")] 
        public ActionResult Index()
        {
            var customer = _context.Customers;

            return View(customer);
        }
    }
}

Critically, there are only two datetime columns in the Customer model. And they are both nullable in the SQL Server Table.

What I've tried I've tried adding a '?' after the datatype in the model, i.e.

[Key]
[Column(Order = 31)]
public DateTime? FirstBuy { get; set; }

[Key]
[Column(Order = 32)]
public DateTime? LastSale { get; set; }

But this doesn’t work.

I've also explored the DefaultIfNull option but that seems to be more for when the model has no entries at all. There are loads of StackOverflow questions on this topic, but they all seem to be related to joining entities in c#, or aggregations etc. This is purely a direct query from one table.

This is probably due to you defining a composite key with nullable types. (see https://stackoverflow.com/questions/10888087/entity-framework-field-of-composite-key-cannot-be-nullable) What is the PK of the table in question? A customer should have something like a CustomerId defined as `[Key]` and nothing else. — Steve Py, May 08 '19 at 03:58
This is a very low quality question. Asking a good question doesn't mean to post everything in your project. For example it seems the error you get is irrelevant with identity part so you can delete this part from your question. Instead share more code from your view. — Masoud Keshavarz, May 08 '19 at 10:51
Hi Masoud, I added the other parts on purpose to add context. With regard to the identity part yes you are probably right. — Jonathan Garvey, May 08 '19 at 13:32
@StevePy You could be right. Admittedly I didn’t create a primary key column on the table and just looked at the customer model there and it inferred every column as a key. I’ll fix up the table schema and try again. Thanks! — Jonathan Garvey, May 08 '19 at 13:39
@StevePy Your suggestion did the trick. I'l post the answer now. — Jonathan Garvey, May 08 '19 at 14:08

score 0 · Answer 1 · answered May 08 '19 at 14:12

The reason the error was occurring was because I forgot to create a primary key on the database table. As a result, MVC inferred that every column was a key - i.e. it added [Key] to the top of every column in the model.

As soon as I added a primary key to the table and updated the model, the error went away.

How to overcome "The cast to value type 'System.DateTime' failed because the materialized value is null" when enumerating a model in a view in MVC

1 Answers1