EF5，SQL服务器，经纬度 [英] EF5, SQL Server, Longitude and Latitude

问题描述

我发现最好的类型来存储lat和长SQL Server是十进制（9,6）（参见<一个href=\"http://stackoverflow.com/questions/1196415/what-datatype-to-use-when-storing-latitude-and-longitude-data-in-sql-databases\">What数据类型在SQL数据库中存储的经纬度数据时使用？），所以我做了

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似乎确定，一切工作正常，但是当我插入/更新的值，即

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

lat = 44.5912853

它保存这样的：

44.590000

我检查了流量，只是更新之前，我的实体包含正确的值，所以我不认为是关系到我的code，但在一定一轮EF / SQL一样。你有什么想法，以避免这种情况？

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 [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事件探查器

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

感谢

推荐答案

显然<一href=\"http://www.gavindraper.co.uk/2011/07/11/entity-framework-$c$c-first-rounding-decimal-model-properties/\">this家伙完全相同的问题并解决了它这样的：

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);
}

虽然你可能想看看使用空间数据类型（特别的在SQL Server 2008及更高版本地理 ）。

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

这篇关于EF5，SQL服务器，经纬度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！