Entity Framework with MySQL in Visual Studio 2015

Entity Framework with MySQL in Visual Studio 2015

.Visual Studio 2015 Entity Framework with MySQL

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

"Here are the steps to configure Entity Framework with MySQL in Visual Studio 2015."



Installation Procedure

Early users of Visual Studio 2015 found that Oracle did not have the drivers for MySQL needed to support Entity Framework. With the release of MySQL for Visual Studio 1.2.4 I was able to make Visual Studio recognize MySQL and configure Entity Framework in Visual Studio 2015. This is the procedure which worked for me.

  1. Configure Visual Studio 2015 to use MySQL. (Once VS 2015 is configured for MySQL, you can skip this step for subsequent projects)
    1. Install MySQL for Visual Studio 1.2.4 Then reboot.

    2. Install MySQL Connector/Net 6.9.7 Then reboot.

      Note: The connector installation may modify the machine.config file. My machine.config file was located at: "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config". The modification inserts MySql.Web in the MySqlSiteMapProvider, as shown below. This causes a runtime error when running existing Visual Studio 2010 projects. The solution I used was to remove the line from the MySqlSiteMapProvider section.

      1
      2
      3
      4
      5
          <siteMap>
      <providers>
      <add name="MySqlSiteMapProvider" type="MySql.Web.SiteMap.MySqlSiteMapProvider, MySql.Web, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
      </providers>
      </siteMap>
      
  1. Add a Data Access Layer Project using Entity Framework.
    1. Add an empty web application project(e.g. CathySite.DAL) in the Visual Studio 2015 solution for the Data Access Layer (Add, New Project).

    2. Inside the DAL project, create a "Models" folder.

    3. Inside the "Models" folder, create an ADO.NET Entity Data Model:
      1. Add, New Item, Data, ADO.NET Entity Data Model. Name the Model appropriately on this screen.
      2. EF Design from Database (Database First)
      3. New Connection
      4. Data source: MySQLDatabase. Populate Server name, User name, Password. Test Connection. Then select Database name.
      5. On the "Choose Your Data Connection screen", be sure to name the connection setting in Web.Config to an appropriate value (e.g. CathySiteEntities). This will also be the name of the class derived from DbContext in the "xxxx.Context.cs" file.
      6. Finish. The project generated should look similar to the one shown below. Note: To access the DAL from other projects, add a reference to the DAL project.
.DAL Project after Entity Framework Generation


.DAL Project after Entity Framework Generation

  1. Inside Visual Studio, use NuGet to install MySQL.Data.Entity 6.9.7.
    1. Install the package in the DAL project and any projects which will reference the DAL project. This package will install missing packages (i.e. DLL's) which it requires, such as MySql.Data (v6.9.7) and EntityFramework (v6.0.0) as well as MySql.Data.Entity (v6.9.7).
    2. At this point I used NuGet to upgrade Entity Framework to v6.1.3.
    3. After these NuGet installs and upgrades, the packages.config for my MVC 5.2.3 project is shown below:

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      <?xml version="1.0" encoding="utf-8"?>
      <packages>
        <package id="Antlr" version="3.4.1.9004" targetFramework="net45" />
        <package id="bootstrap" version="3.0.0" targetFramework="net45" />
        <package id="CKEditor" version="3.6.4" targetFramework="net45" />
        <package id="EntityFramework" version="6.1.3" targetFramework="net45" />
        <package id="jQuery" version="1.10.2" targetFramework="net45" />
        <package id="jQuery.Validation" version="1.11.1" targetFramework="net45" />
        <package id="Microsoft.AspNet.Identity.Core" version="2.2.1" targetFramework="net45" />
        <package id="Microsoft.AspNet.Identity.EntityFramework" version="2.2.1" targetFramework="net45" />
        <package id="Microsoft.AspNet.Identity.Owin" version="2.2.1" targetFramework="net45" />
        <package id="Microsoft.AspNet.Mvc" version="5.2.3" targetFramework="net45" />
        <package id="Microsoft.AspNet.Razor" version="3.2.3" targetFramework="net45" />
        <package id="Microsoft.AspNet.Web.Optimization" version="1.1.3" targetFramework="net45" />
        <package id="Microsoft.AspNet.WebPages" version="3.2.3" targetFramework="net45" />
        <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="1.0.0" targetFramework="net45" />
        <package id="Microsoft.jQuery.Unobtrusive.Validation" version="3.2.3" targetFramework="net45" />
        <package id="Microsoft.Net.Compilers" version="1.0.0" targetFramework="net45" developmentDependency="true" />
        <package id="Microsoft.Web.Infrastructure" version="1.0.0.0" targetFramework="net45" />
        <package id="Modernizr" version="2.6.2" targetFramework="net45" />
        <package id="MySql.Data" version="6.9.7" targetFramework="net45" />
        <package id="MySql.Data.Entity" version="6.9.7" targetFramework="net45" />
        <package id="Newtonsoft.Json" version="6.0.4" targetFramework="net45" />
        <package id="Respond" version="1.2.0" targetFramework="net45" />
        <package id="WebGrease" version="1.5.2" targetFramework="net45" />
      </packages>
      
    4. Packages.config for MVC5.2.3 Project

  1. When the Entity Framework generation is complete, you will have the following files in the Models folder:
    1. xxxx.edmx - the Entity Data Model XML file which defines:
      1. the conceptual model - written in the Conceptual Storage Definition Language (CSDL)
      2. the storage model - written in the Storage Schema Definition Language (SSDL)
      3. and the mapping between these models - written in the Mapping Specification Language (MSL).

      The .edmx file will show a graphical representation in the Model Designer. The Model Designer allows modification of entities, associations, mappings and inheritance relationships. Microsoft recommends using the Model Designer as a first choice for making changes to the .edmx. However some change may require the use of an XML editor.

. .edmx File in Model Designer


.edmx File Open in the Model Designer



Entity Framework Code Generation

When a model is created using the Entity Framework Designer, classes and context are automatically generated. The code that is generated depends upon the version of Visual Studio. Models created in VS 2012, 2013, and 2015 will generate POCO entity classes and a context derived from DbContext. Models in VS 2010 generate classes derived from EntityObject and a context derived from ObjectContext. In VS 2010 is is recommended to switch to the DBContext Generator template once the model has been created.

Microsoft provides a general-purpose templating engine called T4. T4 template files contain a .tt extension and can be used to generate any kind of text (C#, XML, HTML, etc.). Entity Framework uses T4 to generate code, which can be controlled by modifying the .tt template files

In the Visual Studio 2015, in the Solution Explorer under the xxxx.edmx file, exist the following files.

  1. xxxx.Context.tt - Is the T4 template for generating the context class (xxxx.Context.cs) every time the .edmx is regenerated. The context class functions to interact with the data as objects. Prior to EF 5.0, the context class was derived from the ObjectContext. With EF 5.0 an later the context class is derived from the DBContext. DBContext is a wrapper around ObjectContext which is easier to use for all development models. DBContext includes:
    1. EntitySet - contains entity set (DbSet) for the entities which are mapped to DB tables.
    2. Querying - LINQ-to-Entities queries to SQL query which are passed to the database.
    3. Object Materialization - converts table data into entity objects.
    4. Change Tracking - tracks changes to the entities.
    5. Saves Data - performs Insert, Update and Delete operations to the database.
    6. Caching -: performs first level caching by default

    Below is a simple example of a generated context class.

    Content.cs:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    namespace CathySite.DAL.Models
    {
        using System;
        using System.Data.Entity;
        using System.Data.Entity.Infrastructure;
        
        public partial class CathySiteEntities : DbContext
        {
            public CathySiteEntities()
                : base("name=CathySiteEntities")
            {
            }
        
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                throw new UnintentionalCodeFirstException();
            }
        
            public virtual DbSet<user> users { get; set; }
            public virtual DbSet<page> pages { get; set; }
        }
    }
    


  2. xxxxModel.tt - Is the T4 template for generating the POCO entity classes (e.g. user.cs). Typically one class is generated for each table from the database. Below is a simple example of the generated entity class for the user table.

    user.cs:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    namespace CathySite.DAL.Models
    {
        using System;
        using System.Collections.Generic;
        
        public partial class user
        {
            public int User_ID { get; set; }
            public string User_Name { get; set; }
            public string Password { get; set; }
            public string Email_Address { get; set; }
        }
    }
    




Model Browser

The Model Browser displays information about all the components in the entity data model. The Model Browser is accessed by right-clicking in the EDM designer.

.Entity Framework Model Browser


Entity Framework Model Browser

Error | ASP.NET Developer

Error

Error message

  • Warning: Cannot modify header information - headers already sent by (output started at /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/common.inc:2748) in drupal_send_headers() (line 1232 of /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/bootstrap.inc).
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user '1218369_b2cf'@'185.176.40.58' for table 'watchdog': INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 0 [:db_insert_placeholder_1] => cron [:db_insert_placeholder_2] => Attempting to re-run cron while it is already running. [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => 4 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.kcshadow.net/aspnet/?q=node/52 [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => 54.80.87.62 [:db_insert_placeholder_9] => 1534852036 ) in dblog_watchdog() (line 160 of /srv/disk9/1218369/www/kcshadow.net/aspnet/modules/dblog/dblog.module).
The website encountered an unexpected error. Please try again later.