Chapter 9 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;");
    conn.Open();
	MySqlCommand cmd = new MySqlCommand("CREATE TABLE Test (v Geometry NOT NULL)"); 
	cmd.Connection = conn;
    cmd.ExecuteNonQuery();

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

    cmd.CommandText = "SELECT AsText(v) FROM Test"; 
	using(MySqlDataReader reader = cmd.ExecuteReader()) 
	{ 
		reader.Read(); 
		varval = reader.GetValue(0); 
		Console.WriteLine(val); 
	} 
	conn.Close();
  

Output

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()) 
	{ 
		reader.Read(); 
		var val = reader.GetMySqlGeometry(0); 
		var valWithName = reader.GetMySqlGeometry("v");
		Console.WriteLine(val.ToString()); 
		// output : ("POINT(47.37 -122.21)" 
		Console.WriteLine(valWithName.ToString()); 
		// 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:

Example:

    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.Parameters.Add(par); 
	cmd.ExecuteNonQuery();

    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:

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
    u.point.CoordinateSystemId).ToList();

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

Output:

CoordinateSystemId 101

Spatial supported Functions

There are some useful functions that takes Geometry values:

Enum types Support

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

Example:

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

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

Output:

Regional Reseller Zone

Other important features and improvements included in Entity Framework are: