8.1 Entity Framework 5 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 description of the following features:

Spatial Data Type Support

Spatial data types support (Geometry type support) is backed up by the server capabilities, which are documented at: Extensions for Spatial Data There are different types for spatial data. MySQL supports the following types that you can instantiate and use:

  • Point

  • LineString

  • Polygon

  • GeometryCollection

  • MultiPoint

  • MultiLineString

  • MultiPolygon

In MySQL Connector/Net, the different types can be managed with the 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 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 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:

  • LineString

  • MultiLineString

  • Polygon

  • MultiPolygon

  • GeometryCollection

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

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 MySQL 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 database:

    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:

  • SpatialDimension

  • SpatialEnvelope

  • IsSimpleGeometry

  • SpatialTypeName

  • CoordinateSystemId

  • Point

  • XCoordinate

  • YCoordinate

  • GeometryFromText

  • SpatialContains

  • AsText

  • SpatialBuffer

  • SpatialDifference

  • SpatialIntersection

Enumeration 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 enumeration is int. But it can be specified as another numeric type by using a colon. For 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 example, Months.Jan has a default value of 0.

To use enumeration types from within a class defined by Entity Framework:

(In Visual Studio 2012 and using the Database First approach.)

  1. Create a new project (make sure .NET Framework is target in the New Project dialog.).

  2. Add a new Entity Model.

  3. Select the Existing Database option.

  4. Select the tables to be imported in the model.

  5. In order to create an enumeration type, an integer column should exists inside of a table.

  6. In the Model designer select the table to use.

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

    At this point, each LINQ to Entities query can use the enumeration type directly.

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:

  • 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 you to control how the Entity Framework synchronizes database column values and entity property values.