MySQL existing DB with EF6 Code First - Controller Creation With Scaffolding Results in Error

asp.net-mvc ef-code-first entity-framework-6 mysql

Question

Note: I can manually create controllers, get data and then dump then in a view that I manually make strongly typed but any attempt to scaffold results in this error.

Note 2: I'm using Visual Studio Professional 2013 Update 4

The error is:

There was an error running the selected code generator: 'Unable to retrieve metadata for 'WebApplication1.MySQLModels.***********. Sequence contains no matching element'

enter image description here

Where WebApplication1 is the name of my test project. MySQLModels is where the DbContext file with all the models generated by EF Code First are and *********** replace the name of the table.

Web.config Auto-generated Connection String (with sensitive values replaced):

<add name="stringname" connectionString="server=server;user id=userid;password=password;persistsecurityinfo=True;database=database" providerName="MySql.Data.MySqlClient" />

One of the Model Classes Resulting in the Error:

namespace WebApplication1.MySQLModels
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    [Table("database.SomeTable")]
    public partial class SomeTable
    {
        public int id { get; set; }

        [StringLength(255)]
        public string CM { get; set; }

        [StringLength(255)]
        public string Job { get; set; }

        [StringLength(255)]
        public string ClientJobNo { get; set; }

        [StringLength(255)]
        public string JobNo { get; set; }

        [StringLength(255)]
        public string Client { get; set; }

        [Column(TypeName = "date")]
        public DateTime? Start { get; set; }

        [Column(TypeName = "date")]
        public DateTime? Finish { get; set; }

        [StringLength(255)]
        public string Frequency { get; set; }

        [StringLength(255)]
        public string PONumber { get; set; }

        [Column(TypeName = "date")]
        public DateTime? AuditCompleted { get; set; }

        [Column(TypeName = "date")]
        public DateTime? Invoiced { get; set; }

        public decimal? InvoiceAmount { get; set; }

        [StringLength(255)]
        public string Query { get; set; }

        [StringLength(255)]
        public string Status { get; set; }

        [StringLength(255)]
        public string Type { get; set; }

        [StringLength(255)]
        public string Area { get; set; }

        [Column(TypeName = "date")]
        public DateTime? PlannedAudit { get; set; }

        [StringLength(255)]
        public string YellowJacketRequired { get; set; }

        [StringLength(255)]
        public string YellowJacketCompleted { get; set; }

        [StringLength(255)]
        public string SuperName { get; set; }

        [StringLength(255)]
        public string SuperPhone { get; set; }

        [StringLength(255)]
        public string ReasonsDateChange { get; set; }

        [Column(TypeName = "text")]
        [StringLength(65535)]
        public string Comments { get; set; }
    }
}

Corresponding Table Definition in the DB

CREATE TABLE IF NOT EXISTS `SomeTable` (
`id` int(11) NOT NULL,
  `CM` varchar(255) DEFAULT NULL,
  `Job` varchar(255) DEFAULT NULL,
  `ClientJobNo` varchar(255) DEFAULT NULL,
  `JobNo` varchar(255) DEFAULT NULL,
  `Client` varchar(255) DEFAULT NULL,
  `Start` date DEFAULT NULL,
  `Finish` date DEFAULT NULL,
  `Frequency` varchar(255) DEFAULT NULL,
  `PONumber` varchar(255) DEFAULT NULL,
  `AuditCompleted` date DEFAULT NULL,
  `Invoiced` date DEFAULT NULL,
  `InvoiceAmount` decimal(7,2) DEFAULT NULL,
  `Query` varchar(255) DEFAULT NULL,
  `Status` varchar(255) DEFAULT NULL,
  `Type` varchar(255) DEFAULT NULL,
  `Area` varchar(255) DEFAULT NULL,
  `PlannedAudit` date DEFAULT NULL,
  `YellowJacketRequired` varchar(255) DEFAULT NULL,
  `YellowJacketCompleted` varchar(255) DEFAULT NULL,
  `SuperName` varchar(255) DEFAULT NULL,
  `SuperPhone` varchar(255) DEFAULT NULL,
  `ReasonsDateChange` varchar(255) DEFAULT NULL,
  `Comments` text
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

ALTER TABLE `SomeTable`
 ADD PRIMARY KEY (`id`);

I don't have much control over table definitions since this is an already existing database that I'm working with.

Steps to Reproduce:

  1. Create a new ASP.NET MVC 5 project (called WebApplication1 in my case)

  2. Add MySQL DLLs: MySql.Data, MySql.Data.Entity.EF6, MySQL.Web.

  3. Replace the existing EntityFramework tag (in Web.Config) with the following:

    <entityFramework> <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" /> <providers> <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" /> </providers> </entityFramework>

  4. Rebuild the project.

  5. Create a folder to import all the models and create the DbContext, in my case the folder is MySQLModels

  6. Add an ADO.NET Entity Data Model to the project. Select the correct database connection. Select the tables and click finish.

  7. Rebuild the project.

  8. Right-click on Controllers folder, then Add->Controller. In the popup window select MVC 5 Controller with views, using Entity Framework and click Add.

  9. Select one of the models, select your Data context class, enter the appropriate controller name and click Add.

  10. Following this process you will see the error.

What I've Tried So Far:

There isn't much that I could find out there in relation with this error in general but specifically to do with MySQL.

There is an unanswered question, which does not have a lot of details, that can be found here: https://stackoverflow.com/questions/24471557/ef6-code-first-existing-mysql-database-scaffolding-errors

  1. I tried the suggestion here: Unable to Retrieve Metadata which suggested that I change providerName in my connection string to System.Data.SqlClient but this results in the same error.

  2. I uninstalled and reinstalled everything from MySQL but to no avail.

  3. UPDATE: Adding separate ADO.NET Entity Data Models for each table fixes this error. This isn't exactly the solution but absent any other solution that allows me to create scaffolding with one Db Context, this may work.

  4. UPDATE 2: Tried the migration tool from Microsoft: http://www.microsoft.com/en-us/download/details.aspx?id=42657. This works, however, a lot of the data is not ported over. Again, this is a last ditch effort. Still not the solution.

I'll keep updating this question to add more details as I try various thingamajigs.

1
2
5/23/2017 12:24:24 PM

Accepted Answer

This is old, but still if someone is trying to find a fix, here it is.

The Annotations which specify the datatype, are the culprits. Try removing the annotations from model. In this case below two

[Column(TypeName = "text")], [Column(TypeName = "date")]

And build, clean, build the project Now the Add Controller (Scaffolding) works like charm.

Later you can re-add the same.

4
10/9/2015 8:18:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow