SqlNullValueException: Data is Null on Json call

Question

I've seen a few threads regarding this but none that seems to help in my case. I am querying (with EF) into POCO objects then trying to return the results via Json in an MVC project.

My controller returns a JsonResult and the last 2 lines of my controller look like this:

var results = _context.Churches.Include(c => c.Address).Include(c => c.Address.Country).Where(predicate).ToList();
return Json(results, JsonRequestBehavior.AllowGet);

The error occurs in the second line on the call to Json(). However, I have no way of knowing what property is causing the issue. I've looked at the results list before the call and everything looks fine. There are definitely null properties but none that are Required.

I'll include the error output below (although its doesn't format well). It doesn't seem very apparent what exactly is causing the problem -- how can I figure out where the issue lies?

Error Text

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlTypes.SqlDouble.get_Value()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeEnumerable(IEnumerable enumerable, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat, MemberInfo currentMember)
at System.Web.Script.Serialization.JavaScriptSerializer.Serialize(Object obj, SerializationFormat serializationFormat)
at System.Web.Mvc.JsonResult.ExecuteResult(ControllerContext context)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass1c.<InvokeActionResultWithFilters>b__19()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList`1 filters, ActionResult actionResult)
at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)

and here is my POCO model:

    public class Church : IUserTrackingEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Display(Name="Denomination")]
    public int? DenominationId { get; set; }
    public Denomination Denomination { get; set; }
    [Display(Name="Web Address")]
    public string WebAddress { get; set; }
    public string Contact { get; set; }
    [Display(Name="Position")]
    public int? ContactPositionId { get; set; }
    public ContactPosition ContactPosition { get; set; }
    [Display(Name="Email")]
    public string EmailAddress { get; set; }
    [Display(Name="Phone Number")]
    public string PhoneNumber { get; set; }
    [Display(Name="Ext.")]
    public string PhoneExtension { get; set; }
    [Display(Name="Phone Type")]
    public int? PhoneNumberTypeId { get; set; }
    [Display(Name = "Country")]
    public int? CountryId { get; set; }
    public Country Country { get; set; }
    [Display(Name = "Partnership Requested")]
    public DateTime? PartnershipRequestDate { get; set; }
    [Display(Name = "Partnership Accepted")]
    public DateTime? PartnershipAcceptDate { get; set; }
    [Display(Name = "Last Contacted")]
    public DateTime? LastContactDate { get; set; }
    public string Notes { get; set; }
    public string LastChangedBy { get; set; }
    public int? AddressId { get; set; }
    public Address Address { get; set; }
}
    public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Continent { get; set; }
    public string PostalCodeFormat { get; set; }
    public string PostalCodeRegex { get; set; }
    public string Citizenship { get; set; }
    public string NTMCode { get; set; }
    public string ISOCode { get; set; }
    public string ISO3Code { get; set; }
    public string FIPSCode { get; set; }
}
    public class Address : IUserTrackingEntity
{
    public int Id { get; set; }
    public string Addressee { get; set; }
    [Required(ErrorMessage = "A value must be entered into street 1.")]
    public string Street1 { get; set; }
    public string Street2 { get; set; }
    public string Locality { get; set; }
    public string District { get; set; }
    [Required(ErrorMessage="A Country is required.")]
    public int CountryId { get; set; }
    public Country Country { get; set; }
    public string PostalCode { get; set; }
    public bool IsVerified { get; set; }
    public string NTMId { get; set; }
    public string LastChangedBy { get; set; }
    public DbGeography Location { get; set; }

    public double? Longitude
    {
        get
        {
            return Location == null ? null : Location.Longitude;
        }

        set
        {
            if (value != null)
                Location = DbGeography.FromText(String.Format("POINT({0} {1})", value, Latitude ?? 0d), 4326);
        }
    }
    public double? Latitude
    {
        get
        {
            return Location == null ? null : Location.Latitude;
        }

        set
        {
            if (value != null)
                Location = DbGeography.FromText(String.Format("POINT({0} {1})", Longitude ?? 0d, value), 4326);
        }
    }

}

Does it work if you put a `[ScriptIgnore]` attribute on the `Address.Location` property? — Steve Ruble, Aug 20 '14 at 19:26
That did it! Thanks so much. If you post that as an answer, I'll go ahead and mark it. — RHarris, Aug 21 '14 at 12:36

score 5 · Accepted Answer · answered Aug 21 '14 at 15:52

5

The exception is probably being thrown when the JSON serializer attempts to serialize the DbGeography instance in Address.Location. Some (double-valued and lazily resolved) property of that instance is not valid for the location the instance represents, but the serializer doesn't know that and goes ahead and tries to access that property which causes the exception.

Adding a [ScriptIgnore] attribute to the Address.Location property should fix it.

answered Aug 21 '14 at 15:52

Steve Ruble

3,875
21
27

Another option is to have Json.Net serialize your response. See http://stackoverflow.com/a/25197400/469777 – Stafford Williams Jul 26 '15 at 10:06

SqlNullValueException: Data is Null on Json call

1 Answers1