EF5, SQL Server, Longitude and Latitude
asp.net-mvc-4, c#, entity-framework-5, latitude-longitude
Solution
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.
Problem
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) ``` 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