c# - 如何使用 C# 从 SQL Server 检索点?
问题描述
我在我的 sql server 上使用空间数据类型,我想将此数据从我的 sql server 检索到我的实体类,它返回错误“.NET 数值,如正无穷大和负无穷大不能写为有效的 JSON。” 当我尝试从我的数据库中获取值时。
坐标是我想从我的数据库返回的值。我正在使用来自 NetTopologySuite 的 Geography 数据类型,并且正在从我的实体类中插入一个点。
我正在使用的类代码:
public class Address : EntityBase<int>
{
public Address () {}
public Address(string district, string street, int number, string complement, string zipCode,
string cityDescription, string stateDescription, Point coordinates, int countryId, byte stateId, int cityId)
{
District = district;
Street = street;
Number = number;
Complement = complement;
ZipCode = zipCode;
CityDescription = cityDescription;
StateDescription = stateDescription;
Coordinates = coordinates;
CountryId = countryId;
StateId = stateId;
CityId = cityId;
}
public string District { get; set; }
public string Street { get; set; }
public int Number { get; set; }
public string Complement { get; set; }
public string ZipCode { get; set; }
public string CityDescription { get; set; }
public string StateDescription { get; set; }
public virtual Point Coordinates { get; set; }
public int CountryId { get; set; }
public byte StateId { get; set; }
public int CityId { get; set; }
public int? CustomerId { get; set; }
public int? StoreId { get; set; }
public int? ProfessionalId { get; set; }
}
以及该表的 sql server 代码:
CREATE TABLE Address(
Id INT PRIMARY KEY IDENTITY(1, 1),
District VARCHAR(50) NOT NULL, -- Bairro
Street VARCHAR(100) NOT NULL, -- Rua
--Description VARCHAR(100) NOT NULL,
Number INT,
Complement VARCHAR(100),
ZipCode VARCHAR(20) NOT NULL,
CityDescription VARCHAR(100),
StateDescription VARCHAR(100),
Coordinates GEOGRAPHY,
CountryId INT FOREIGN KEY REFERENCES Country(Id) NOT NULL,
StateId TINYINT FOREIGN KEY REFERENCES State(Id),
CityId INT FOREIGN KEY REFERENCES City(Id),
CustomerId INT FOREIGN KEY REFERENCES Customer(Id),
StoreId INT FOREIGN KEY REFERENCES Store(Id),
ProfessionalId INT FOREIGN KEY REFERENCES Professional(Id),
INDEX IndexAddressCountryId NONCLUSTERED (CountryId),
INDEX IndexAddressStateId NONCLUSTERED (StateId),
INDEX IndexAddressCityId NONCLUSTERED (CityId),
INDEX IndexAddressCustomerId NONCLUSTERED (CustomerId),
INDEX IndexAddressStoreId NONCLUSTERED (StoreId),
INDEX IndexAddressProfessionalId NONCLUSTERED (ProfessionalId)
)
有什么方法可以从中检索点值吗?像函数OnModelCreating上的配置或其他什么?我只能从中检索经度和纬度吗?
我是空间数据的新手,所以我不太了解它。提前感谢您帮助我:)
编辑1:
这是我得到的异常错误:
实体库:
namespace CodenApp.Framework.Core.Entities
{
public abstract class EntityBase<T>
{
public T Id { get; set; }
}
}
还有我用来处理异常的两个函数:
public async Task InvokeAsync(HttpContext httpContext)
{
try
{
await _next(httpContext);
}
catch (Exception ex)
{
_logger.LogError(ex, ex.ToString());
await HandleExceptionAsync(httpContext, ex);
}
}
protected override Task HandleExceptionAsync(HttpContext context, Exception exception)
{
if(context == null)
throw new ArgumentNullException(nameof(context));
if(exception == null)
throw new ArgumentNullException(nameof(exception));
context.Response.ContentType = "application/json";
context.Response.StatusCode = (int)HttpStatusCode.BadRequest;
return context.Response.WriteAsync(ApiCatch.Log(exception).ToString());
}
我用来转换为 Point 的代码:
public static Point ConvertToGeometry(string coordinatesAux)
{
if(coordinatesAux == null)
throw new NullReferenceException();
NumberFormatInfo formatProvider = new NumberFormatInfo();
formatProvider.NumberGroupSeparator = ".";
var currentLocation = new Point(new Coordinate(
Convert.ToDouble(coordinatesAux.Substring(0, coordinatesAux.IndexOf(",")), formatProvider),
Convert.ToDouble(coordinatesAux.Substring(coordinatesAux.IndexOf(",") + 1), formatProvider))) { SRID = 4326 };
return currentLocation;
}
解决方案
正如Panagiotis Kanavos在评论中所说,为了解决这种情况,我需要添加 NuGet 包NetTopologySuite.IO.GeoJSON4STJ并更改文件 Startup.cs 以接收包给出的更改。
ConfigureServices 函数内 Startup.cs 的变化:
services.AddControllers(options =>
{
options.ModelMetadataDetailsProviders.Add(new SuppressChildValidationMetadataProvider(typeof(Point)));
options.ModelMetadataDetailsProviders.Add(new SuppressChildValidationMetadataProvider(typeof(Coordinate)));
options.ModelMetadataDetailsProviders.Add(new SuppressChildValidationMetadataProvider(typeof(LineString)));
options.ModelMetadataDetailsProviders.Add(new SuppressChildValidationMetadataProvider(typeof(MultiLineString)));
});
services.AddControllers().AddJsonOptions(options =>
{
var geoJsonConverterFactory = new GeoJsonConverterFactory();
options.JsonSerializerOptions.Converters.Add(geoJsonConverterFactory);
});
services.AddSingleton(NtsGeometryServices.Instance);
并将 IOptions 添加到我的控制器中:
private readonly IOptions<JsonOptions> _jsonOptions;
public StoreController(IAsyncRepository<Store, int> repository, StoreHandler handler, IOptions<JsonOptions> jsonOptions) : base(repository)
{
_handler = handler;
_orders = new Expression<Func<Store, object>>[] { x => x.Id };
_jsonOptions = jsonOptions;
}
所以在这个wiki中教授。
推荐阅读
- c# - WCF 服务未连接到测试客户端
- ruby-on-rails - 如何在本地测试地理编码器?
- java - 使用 Criteria Builder 运行带有 IN 子句的查询
- javascript - 用户使用 Jquery 连接元素
- javascript - 如果登录用户未单击,则突出显示网页中的按钮
- arduino - 为什么 switch 语句返回“重复大小写值”,我该如何解决这个问题?
- python - 没有找到任何带有机械化的表格
- powershell - PowerShell时间范围内的if语句条件
- c# - 简化 XAML 转换器中电话号码的正则表达式
- ffmpeg - ffmpeg 从 Windows 10 中的网络摄像头拍摄快照