Storing double.NaN in a WP7 SQLCE database

Summary

double.NaN gives me update issues (ChangeConflictException) in SQL Compact, my fallback was to set the value to zero instead:

double.IsNaN(value.Altitude) ? 0 : value.Altitude

where ‘Alt’ is the property on my SQLCE entity object. Of course NaN is not the same as zero – but that does not matter to me.

Background

For a Windows Phone 7 demo app that I’m writing, I’ve been trying to store GPS locations in a SQL Compact database. The GeoCoordinate data type is not supported by SQLCE, so I just took the Latitude, Longitude and Altitude and stored those as doubles in the db. (here’s a list of SQL Compact supported data types)

And, this was not my first my first version of the app (hence db), so I had to make the properties nullable – otherwise the SQLCE engine cannot add columns for them, read more about wp7 databases here. I do not think the nullable thing was a part of my problem, but you never know…

The problem

So, I happily took data from the GPS in the emulator, got double.NaN as the Altitude and saved that to the DB, all fine so far… Then when updating the database record with other values and trying to save the entry again, I kept getting the error:

ChangeConflictException {"Row not found or changed."}

Since this problem appeared when I added the Altitude to the database, it was my prime suspect and when I removed it again all was fine.

I’m not sure of the inner workings of this problem, but I suspect it having to do with the fact that when trying to compare a double with double.NaN, VS hints with:

A NaN does not compare equal with any floating point number

I can perfectly understand that NaN is a special case so I’m fine with sidestepping it like this.

The fix

Alt = double.IsNaN(value.Altitude) ? 0 : value.Altitude;

The code in the [Table] class:

private double? _alt;
[Column(CanBeNull = true)]
public double? Alt
{
    get { return _alt; }
    set
    {
        OnPropertyChanging("Alt");
        _alt = value;
        OnPropertyChanged("Alt");
    }
}

public GeoCoordinate Location
{
    get { return new GeoCoordinate(
        Lat.GetValueOrDefault(),
        Lng.GetValueOrDefault(),
        Alt.GetValueOrDefault()); }
    set
    {
        Lat = value.Latitude;
        Lng = value.Longitude;

        //just setting the double.NaN value, this gave me trouble
        //Alt = value.Altitude;

        //my fix: replacing NaN with zero
        Alt = double.IsNaN(value.Altitude) ? 0 : value.Altitude;

        _location = value;
    }            

And the code where I update the Item entity from my view model:

private void SaveEntry()
{
    var item = (from i in db.Items
                where i.Id == ViewModel.Id
                select i).First();

    db.Refresh(RefreshMode.OverwriteCurrentValues, item);

    item.PhotoUrl = ViewModel.PhotoUrl;
    item.Text = ViewModel.LongText;
    item.Title = ViewModel.Title;
    item.Location = ViewModel.Location;
    db.SubmitChanges();
}

I have of course tried the different RefreshModes, with the same result.

I hope this helped someone out there with weird SQL errors and NaN values.

Bye!

This Post Has 2 Comments

  1. Andreas Hallberg

    Nice post. But wouldn’t null have been a more appropriate replacement instead of 0?

  2. Andreas Hammar

    It would absolutely, and it works fine to just replace NaN with null and save that instead.

    Also, to be very clear: the double.NaN gets saved fine to the database, and after roundtrip the value still is NaN. The problem appears when trying to save the loaded record.

Leave a Reply