How To: Using spatial data with Entity Framework and Connector/Net

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 EntityFramework

This 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 { getset; }

public string name { getset; }

public DbGeometry location { getset; }

}

public class JourneyDb : DbContext

{

public DbSet<MyPlace> MyPlaces { getset; }

}

 Also make sure to add the connection string to the App.Config file as in the example and the system.data section with the reference for the MySql provider:

<?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>

<system.data>

<DbProviderFactories>

<clear />

<remove invariant="MySql.Data.MySqlClient"/>

<add name="MySql.Data.MySqlClient" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,Version=6.7.4.0, Culture=neutral,PublicKeyToken=c5687fc88969c44d"/> 

</DbProviderFactories>

</system.data>

  <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 to

ease 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 Production

Environment.

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 have

to add them before running it.

Happly MySQL/.Net Coding.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today