Chapter 8 EF 5 Support

EF 5 Support is based on the new support for .Net Framework 4.5 which is the default target framework in the latest version of VS (2012). Using this framework version is a requirement in order to use any EF 5 feature.

Below is a detailed description on each one of the features.

EF 5 Features

Spatial Data Type (Geometry type support). Spatial data types support is backed up by the server capabilities which are documented at: Extensions for Spatial Data There are different types for spatial data, and the instantiable types that are supported at MySQL are:

Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString, and MultiPolygon.

At Connector/Net these different types can be managed with the new Geometry type. Before Connector/Net 6.7 the Geometry types didn't have a MySql Type inside the driver. So most of the users had to use it by using a binary type. This is not longer needed. Now all the specific operations for any Geometry type can be done by using this new class.

An example of how to write a point data is shown here using Connector/Net 6.7 and MySQL Server 5.6.7 or further.

    //Storing a geometry point
	MySqlConnection conn = new MySqlConnection("server=localhost;userid=root;database=testgeo;");
	MySqlCommand cmd = new MySqlCommand("CREATE TABLE Test (v Geometry NOT NULL)"); 
	cmd.Connection = conn;

    cmd = new MySqlCommand("INSERT INTO Test VALUES(GeomFromText(?v))", conn); 
	cmd.Parameters[0].Value = "POINT(47.37-122.21)"; 

    cmd.CommandText = "SELECT AsText(v) FROM Test"; 
	using(MySqlDataReader reader = cmd.ExecuteReader()) 
		varval = reader.GetValue(0); 


POINT(47.37, -122.21)

To read a set of rows that contains a Geometry column can be achieved by using an appropiate MySqlDataReader. Example:

    cmd.CommandText = "SELECT v FROM Test"; 
	using(MySqlDataReader reader = cmd.ExecuteReader()) 
		var val = reader.GetMySqlGeometry(0); 
		var valWithName = reader.GetMySqlGeometry("v");
		// output : ("POINT(47.37 -122.21)" 
		// output("POINT(47.37 -122.21)" 

A geometry point also can contain an SRID (Spatial Reference System Identifier)value. This value can also be stored using a geometry type:


    MySqlGeometry v = new MySqlGeometry(47.37, -122.21, 101); 
	var par =new MySqlParameter("?v", MySqlDbType.Geometry); 
	par.Value = v;

    MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(?v)", conn); 

    cmd.CommandText = "SELECT SRID(v) FROM Test";

    using (MySqlDataReader reader = cmd.ExecuteReader()) 
    reader.Read(); var val = reader.GetString(0);
    Console.WriteLine(val); // output "101" 

Another types of values besides point can be stored at the Geometry type:

  • LineString

  • MultiLineString

  • Polygon

  • MultiPolygon

  • GeometryCollection

Spatial data is supported in Model, Database and Code First.

Using spatial types with an Entity Framework Model is one of the new features of Connector/Net 6.7.

Spatial types can be used with any of the strategies used to create the data layer of any application: Database First, Code First or Model First. Entity Framework support two main types for spatial data: DbGeometry and DBGeography. The second one is not supported at Connector/Net since the MySQL server doesn't have any equivalent type to which map this type in. So all the examples will use the DbGeometry type.

Example of usage in Code First.

An entity that contains a Geometry column can be defined as follows:

    public class Distributor { public int DistributorId { get; set; }
    public string Name { get; set; } public DbGeometry point { get; set;
    } }

    public class DistributorsContext : DbContext { public
    DbSet<Distributor> Distributors { get; set; } }

Creating the db:

    using (DistributorsContext context = new DistributorsContext()) {
    context.Database.Delete(); context.Database.Create();
    context.Distributors.Add(new Distributor() { Name = "Graphic
    Design Institute", point =
    DbGeometry.FromText("POINT(-122.336106 47.605049)",101),
    }); context.SaveChanges();

    var result = (from u in context.Distributors select

    foreach (var item in result)
    Console.WriteLine("CoordinateSystemId " + item);


CoordinateSystemId 101

Spatial supported Functions

There are some useful functions that takes Geometry values:

  • SpatialDimension

  • SpatialEnvelope

  • IsSimpleGeometry

  • SpatialTypeName

  • CoordinateSystemId

  • Point

  • XCoordinate

  • YCoordinate

  • GeometryFromText

  • SpatialContains

  • AsText

  • SpatialBuffer

  • SpatialDifference

  • SpatialIntersection

Enum types Support

  • Enumeration types are used to define a set of named constants that may be assigned to a numeric value.

  • By default the underlying type of each element in the enum is int. But it can be specified another numeric type by using a colon. Example:enum Months : byte { Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec };

  • When a value is not specified in the defined list, the values are automatically incremented by 1. In the previous exampleMonths.Jan has a default value of 0.

In order to use enum types inside an Entity Framework defined class it should follow the next steps:

In Visual Studio 2012

Using Database First approach

  • Create a new project (make sure .NET framework is target in the New Project dialog.),

  • Add a new Entity Model

  • Select the Existing Database option

  • Select the tables to be imported in the model

  • In order to create an Enum Type an integer column should exists inside of a table.

  • In the Model designer select the table to use.

  • Right click on the column of integer type and select Create enum type.

  • At this point all linq to entities query can use the Enum type directly.


    using (var db = new DistributorsEntities()) {
		db.distributors.Add(new distributor { Type = DistributorType.Regional });
		db.distributors.Add(new distributor { Type = DistributorType.Reseller }); 
		db.distributors.Add(new distributor { Type = DistributorType.Zone }); 

		var testQ = (from d in db.distributors select d).FirstOrDefault();
		foreach (var item in testQ) 


Regional Reseller Zone

Other important features and improvements included in Entity Framework are:

  • All LINQ queries are now automatically compiled and cached to improve query performance.

  • Multiple Diagrams for a model is supported.

  • Enhancement Table per type in SQL Generation.

  • Performance Enhancements.

  • The StoreGeneratedPattern for key columns can now be set on an entity Properties window and this value will propagate from the entity model down to the stored definition. The stored Generated Pattern attribute allows to control how the Entity Framework synchronizes database column values and entity property values;

User Comments
Sign Up Login You must be logged in to post a comment.