Chapter 8 EF5 Support

Support for Entity Framework 5 (EF5) is based on the introduction of .NET Framework 4.5, which is the default target framework in Microsoft Visual Studio 2012, or higher. Using this framework version is a requirement for applying any of the features in EF5 to your code.

This chapter provides a detailed description of each one of the features.

EF5 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.

In MySQL Connector/Net, these different types can be managed with the new Geometry type. Before Connector/Net 6.7 the Geometry types did not have a MySql type inside the driver. Common practice was to use a binary type; however, this is no longer needed. Now, all the specific operations for any Geometry type can be performed 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 higher.

    //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 appropriate 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 a Spatial Reference System Identifier (SRID) 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"
	}

Other 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 by Connector/Net, because MySQL server does not have any equivalent type to map to this type. 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:

Enumeration 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: