How To: Using spatial data with Entity Framework and Connector/Net
- by GABMARTINEZ
One of the new features introduced in Entity Framework 5.0 is the incorporation of some new types of data within an Entity Data Model: the spatial data types. These types allow us to perform operations on coordinates values in an easier way. There's no need to add stored routines or functions for every operation among these geometry types, now the user can have the alternative to put this logic on his application or keep it in the database. In the new 6.7.4 version there's also this new feature incorporated to Connector/Net library so our users can start exploring it and could provide us some feedback or comments about this new functionality.
Through this tutorial on how to create a Code First Entity Model with a geometry column, we'll show an example on using Geometry types and some common operations when using geometry types inside an application.
Requirements:
- Connector/Net 6.7.4
- Entity Framework 5.0 version
- .NET Framework 4.5 version
- Basic understanding on Entity Framework and C# language.
- An installed and running instance of MySQL Server 5.5.x or 5.6.10 version- Visual Studio 2012.
Step One: Create a new Console Application
Inside Visual Studio select File->New Project menu option and select the Console Application template. Also make sure the .Net 4.5 version is selected so the new features for EF 5.0 will work with the application.
Step Two: Add the Entity Framework Package
For adding the Entity Framework Package there is more than one option: the package manager console or the Manage Nuget Packages option dialog. If you want to open the Package Manager Console, go to the Tools Menu -> Library Package Manager -> Package Manager Console.
On the Package Manager Console Type:Install-Package EntityFrameworkThis will add the reference to the project of the latest released No alpha version of Entity Framework.
Step Three: Adding Entity class and DBContext
We'll add a simple class that represents a table entity to save some places and its location using a DBGeometry column that will be mapped to a Geometry type in MySQL. After that some operations can be performed using this data.
public class MyPlace
{
[Key]
public int Id { get; set; }
public string name { get; set; }
public DbGeometry location { get; set; }
}
public class JourneyDb : DbContext
{
public DbSet<MyPlace> MyPlaces { get; set; }
}
Also make sure to add the connection string to the App.Config file as in the example:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="JourneyDb" connectionString="server=localhost;userid=root;pwd=;database=journeydb" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
<entityFramework>
</entityFramework>
</configuration>
Note also that the <entityFramework> section is empty.Step Four: Adding some new records.On the Program.cs file add the following code for the Main method so the Database gets created and also some new data can be added to the new table. This code adds some records containing some determinate locations. After being added a distance function will be used to know how much distance has each location in reference to the Queens Village Station in New York.
static void Main(string[] args) { using (JourneyDb cxt = new JourneyDb()) { cxt.Database.Delete(); cxt.Database.Create(); cxt.MyPlaces.Add(new MyPlace() { name = "JFK INTERNATIONAL AIRPORT OF NEW YORK", location = DbGeometry.FromText("POINT(40.644047 -73.782291)"), }); cxt.MyPlaces.Add(new MyPlace() { name = "ALLEY POND PARK", location = DbGeometry.FromText("POINT(40.745696 -73.742638)"), }); cxt.MyPlaces.Add(new MyPlace() { name = "CUNNINGHAM PARK", location = DbGeometry.FromText("POINT(40.735031 -73.768387)"), }); cxt.MyPlaces.Add(new MyPlace() { name = "QUEENS VILLAGE STATION", location = DbGeometry.FromText("POINT(40.717957 -73.736501)"), }); cxt.SaveChanges(); var points = (from p in cxt.MyPlaces select new { p.name, p.location }); foreach (var item in points) { Console.WriteLine("Location " + item.name + " has a distance in Km from Queens Village Station " + DbGeometry.FromText("POINT(40.717957 -73.736501)").Distance(item.location) * 100); } Console.ReadKey(); } }}Output :
Location JFK INTERNATIONAL AIRPORT OF NEW YORK has a distance from Queens Village Station
8.69448802402959 Km.
Location ALLEY POND PARK has a distance from Queens Village Station
2.84097675104912 Km.
Location CUNNINGHAM PARK has a distance from Queens Village Station
3.61695793727275 Km.
Location QUEENS VILLAGE STATION has a distance from Queens Village Station
0 Km. Conclusion:Adding spatial data to a table is easier than before when having Entity Framework 5.0. This new Entity Framework feature that handles spatial data columns within the Data layer has a lot of integrated functions and methods toease this type of tasks.Notes:This version of Connector/Net is just released as GA so is preatty much stable to be used on a ProductionEnvironment. Please send us your comments or questions using this blog or at the Forums where we keep answering any questions you have about Connector/Net and MySQL Server.A copy of this sample project can be downloaded here. This application does not include any library so you will haveto add them before running it.
Happly MySQL/.Net Coding.
