如何在关联Entity Framework的ASP.NET MVC中用Dapper插入检索Geography数据？

Hey there! I get it—combining DbGeography and Dapper can feel tricky when most resources are either outdated or not focused on spatial types. Let's walk through exactly how to get insert and retrieve working for your GEOGRAPHY table.

First, a quick note on your model: I noticed you have LAT and LONG as separate DbGeography properties. That's a bit unusual because DbGeography is meant to represent a full spatial object (like a point, line, or polygon). Typically, you'd store a single DbGeography point using both latitude and longitude. But I'll cover both scenarios—sticking with your current model, and the more standard approach—so you can choose what fits your project.

Step 1: Add a Dapper Type Handler for DbGeography #

Dapper doesn't know how to map DbGeography to SQL Server's geography type out of the box, so we need to create a custom type handler. This will handle converting between .NET's DbGeography and the SQL type seamlessly.

Here's the handler code:

using System.Data;
using Dapper;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Types;

public class DbGeographyTypeHandler : SqlMapper.TypeHandler<DbGeography>
{
    public override void SetValue(IDbDataParameter parameter, DbGeography value)
    {
        parameter.Value = value == null ? DBNull.Value : SqlGeography.Parse(value.AsText());
        parameter.DbType = DbType.Object;
    }

    public override DbGeography Parse(object value)
    {
        if (value == null || value is DBNull)
            return null;
        
        var sqlGeography = value as SqlGeography;
        return sqlGeography != null 
            ? DbGeography.FromText(sqlGeography.ToString()) 
            : DbGeography.FromText(value.ToString());
    }
}

Register this handler once when your app starts (like in your startup class or before making any database calls):

SqlMapper.AddTypeHandler(new DbGeographyTypeHandler());

Step 2: Inserting Data #

Option A: Using Your Current Model (Separate LAT/LONG as DbGeography) #

If you want to keep LAT and LONG as separate DbGeography properties, you'll need to create each as a point (since latitude/longitude are coordinates that define a point). Note that this is non-standard—most apps combine both into a single point—but here's how to make it work:

// Create DbGeography points for lat and lon (unusual, but per your model)
var latPoint = DbGeography.PointFromText($"POINT(0 {yourLatitudeValue})", 4326); // SRID 4326 = WGS84 standard
var lonPoint = DbGeography.PointFromText($"POINT({yourLongitudeValue} 0)", 4326);

var geographyRecord = new GEOGRAPHY
{
    LAT = latPoint,
    LONG = lonPoint,
    ISO = "US",
    COUNTRY = "United States",
    NICENAME = "United States",
    ISO3 = "USA",
    NUMCODE = 840,
    PHONECODE = 1
};

// Insert with Dapper
using (var connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    var sql = @"
        INSERT INTO GEOGRAPHY (LAT, LONG, ISO, COUNTRY, NICENAME, ISO3, NUMCODE, PHONECODE)
        VALUES (@LAT, @LONG, @ISO, @COUNTRY, @NICENAME, @ISO3, @NUMCODE, @PHONECODE)
        SELECT CAST(SCOPE_IDENTITY() AS INT)";
    
    var newRecordId = connection.QuerySingle<int>(sql, geographyRecord);
}

Option B: Standard Approach (Single DbGeography Point) #

This is the more common and practical way to store location data. Let's adjust your model to use a single DbGeography property for the full point:

[Table("GEOGRAPHY")]
public partial class GEOGRAPHY
{
    [Key]
    public int ID_ROUTE { get; set; }
    
    // Replace LAT and LONG with this single spatial point property
    public DbGeography Location { get; set; }
    
    [Required]
    [StringLength(2)]
    public string ISO { get; set; }
    
    // ... keep all your other existing properties and collections ...
}

Then insert using both latitude and longitude to create a single point:

var latitude = 40.7128; // Example: New York City latitude
var longitude = -74.0060; // Example: New York City longitude
// Note: POINT format is (longitude latitude) for SRID 4326
var locationPoint = DbGeography.PointFromText($"POINT({longitude} {latitude})", 4326);

var geographyRecord = new GEOGRAPHY
{
    Location = locationPoint,
    ISO = "US",
    COUNTRY = "United States",
    NICENAME = "United States",
    ISO3 = "USA",
    NUMCODE = 840,
    PHONECODE = 1
};

using (var connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    var sql = @"
        INSERT INTO GEOGRAPHY (Location, ISO, COUNTRY, NICENAME, ISO3, NUMCODE, PHONECODE)
        VALUES (@Location, @ISO, @COUNTRY, @NICENAME, @ISO3, @NUMCODE, @PHONECODE)
        SELECT CAST(SCOPE_IDENTITY() AS INT)";
    
    var newRecordId = connection.QuerySingle<int>(sql, geographyRecord);
}

Step 3: Retrieving Data #

Once the type handler is registered, retrieving data is straightforward. Dapper will automatically map SQL's geography column to your .NET DbGeography property.

For Your Current Model: #

using (var connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    var sql = "SELECT * FROM GEOGRAPHY WHERE ID_ROUTE = @Id";
    var geographyRecord = connection.QuerySingleOrDefault<GEOGRAPHY>(sql, new { Id = 1 });
    
    if (geographyRecord != null)
    {
        // Access the spatial data
        var latPointText = geographyRecord.LAT?.AsText();
        var lonPointText = geographyRecord.LONG?.AsText();
    }
}

For the Standard Single Point Model: #

using (var connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    var sql = "SELECT * FROM GEOGRAPHY WHERE ID_ROUTE = @Id";
    var geographyRecord = connection.QuerySingleOrDefault<GEOGRAPHY>(sql, new { Id = 1 });
    
    if (geographyRecord?.Location != null)
    {
        // Extract latitude and longitude from the point
        var latitude = geographyRecord.Location.Latitude.Value;
        var longitude = geographyRecord.Location.Longitude.Value;
        var pointText = geographyRecord.Location.AsText(); // Outputs something like "POINT(-74.0060 40.7128)"
    }
}

Important Notes #

• SRID: Always use SRID 4326 (WGS84) unless you have a specific reason not to—it's the global standard for GPS coordinates used by Google Maps and most location APIs.
• Null Handling: The type handler correctly maps SQL NULL values to .NET null for your nullable properties.
• SQL Table Schema: Ensure your SQL table's spatial columns are of type geography (not geometry) to match DbGeography.

Let me know if you run into any snags with this setup—I’m happy to help tweak things further!

内容的提问来源于stack exchange，提问作者frederick ramirez