Davide Davide - 3 months ago 18
C# Question

EF5, SQL Server, Longitude and Latitude

I found that the best type to store lat and long in SQL Server is decimal (9,6) (ref. What datatype to use when storing latitude and longitude data in SQL databases?) and so I did

AddColumn("dbo.Table", "Latitude", c => c.Decimal(nullable: false, precision: 9, scale: 6));
AddColumn("dbo.Table", "Longitude", c => c.Decimal(nullable: false, precision: 9, scale: 6));


SQL seems ok, everything is working, BUT when I insert / update a value, i.e.

lat = 44.5912853


it is saved like this:

44.590000


I checked the flow, and just before the update, my entity contains the correct value, so I don't think is related to my code, but to some round that EF / SQL does. Do you have any idea to avoid this?

UPDATE

update [dbo].[Breweries]
set [RankId] = @0,
[Name] = @1,
[Foundation] = null,
[DirectSale] = @2,
[OnlineSale] = @3,
[StreetAddress] = @4,
[StreetAddress1] = null,
[ZIP] = @5,
[City] = @6,
[Province] = @7,
[CountryCode] = @8,
[Latitude] = @9,
[Longitude] = @10,
[PIVA] = null,
[CodFiscale] = null
where ([BreweryId] = @11)


enter image description here

POCO entity

[Table("Breweries")]
public class Brewery : ABrewery
{
....
public decimal Latitude { get; set; }
public decimal Longitude { get; set; }
}


SQL Profiler

exec sp_executesql N'update [dbo].[Breweries]
set [RankId] = @0, [Name] = @1, [Foundation] = null, [DirectSale] = @2, [OnlineSale] = @3, [StreetAddress] = @4, [StreetAddress1] = null, [ZIP] = @5, [City] = @6, [Province] = @7, [CountryCode] = @8, [Latitude] = @9, [Longitude] = @10, [PIVA] = null, [CodFiscale] = null
where ([BreweryId] = @11)
',N'@0 int,@1 nvarchar(128),@2 bit,@3 bit,@4 nvarchar(256),@5 varchar(16),@6 nvarchar(64),@7 nvarchar(64),@8 nvarchar(128),@9 decimal(18,2),@10 decimal(18,2),@11 int',@0=2,@1=N'Davide',@2=0,@3=0,@4=N'Via Moscardini, 24',@5='zip',@6=N'city',@7=N'province',@8=N'ITA',

@9=44.59,@10=11.05,@11=2


Thanks

Answer

Apparently this guy had the exact same problem and solved it thus:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Activity>().Property(a => a.Latitude).HasPrecision(18, 9);
    modelBuilder.Entity<Activity>().Property(a => a.Longitude).HasPrecision(18, 9);
}

Although you may want to look at using the spatial data types (particularly geography) in SQL Server 2008 and later.