我发现在sql Server中存储lat和long的最佳类型是十进制(9,6)(参考
What datatype to use when storing latitude and longitude data in SQL databases?),所以我做了
AddColumn("dbo.Table","Latitude",c => c.Decimal(nullable: false,precision: 9,scale: 6));
AddColumn("dbo.Table","Longitude",scale: 6));
sql似乎可以,一切正常,但是当我插入/更新一个值,即
lat = 44.5912853
它像这样保存：
44.590000
我检查了流程,就在更新之前,我的实体包含正确的值,所以我不认为是与我的代码相关,而是与EF / sql有关.你有什么想法可以避免吗？
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实体
[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,[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,@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
谢谢
解决方法
显然
this guy had the exact same problem这样解决了：
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);
}
尽管您可能希望在sql Server 2008及更高版本中使用spatial data types(特别是geography).
