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.
