Bug #55696 Problems converting from Sql Server to MySql using Connector/Net 6.3.3 beta 2
Submitted: 2 Aug 2010 18:47 Modified: 1 Jun 2011 16:09
Reporter: Gerald May Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.3 beta 2 OS:Windows (Vista)
Assigned to: Julio Casal CPU Architecture:Any

[2 Aug 2010 18:47] Gerald May
Description:
I am running Visual Studio Professional Released Version.  I am trying to convert an existing project from using Sql Server to My Sql.  I have the 5.5 Version of MySql and Connector/Net 6.3.3 beta 2.  My method is to use Update database from model to create a sql script using the MySql template.  I then ran this script to create a MySql database using MySql Workbench.  I should note that I am using Ado.Net Entity Framework, and that my project is an MVC2 application(a web project).  All of my code uses .Net Framework 4.0.  I had hoped that all I would have to do was to create the MySql database, and then create a new connection string for the MySql database.  So far this has not worked.  I had the following problems:

1) I was using nvarchar(max) in Sql Server to represent strings as recommended by microsoft.  The generated MySql Script has nvarchar(max) fields in it but could these were flagged as errors when I Executed the script in MySql Workbench.
In order to proceed I changed these manually by replacing mas with 200.
2) Following usual C# standards, by field are identifiers that have both upper and lower case letters in field names and table Names.  However, in the database,  Table names were all lower case.  
3) I tested the database in a new project.  I created a new model, by adding a new item, which was a Ado.Net Entity framework Model.  The resulting model had the following problems: table names were lower case.  Navigation properties were lower case.  The navigation properties for a table that had zero or 1 to many references to itself did not have the same navigation names as in the original model.  In all of the tables the field order was not preserved exactly. It appeared that some of the fields were in reverse order and some were not.  This is important because the factory methods have parameters in field order.

Note that may of these problems can be fixed by merely editing the resulting model.  However this should not be really necessary.  It really generates a lot of errors when you first compile the code.

NOW A MORE SERIOUS BUG. 

I did the operation in 3 above, in a version of the code I actually wanted to convert.  This included changing the coding style to avoid the factory methods, and renaming all of the tables and navigation properties to match the original code.  I finally got an error free compile, and ran the code.  Note that the code running Sql Server worked perfectly.  I am using JQuery, and an autocomplete plugin that uses AJAX to request the data for the Plug-in.  When the Plug-in uses AJAX to access a list of people in my People table, there is an exception that has the message {"There is already an open DataReader associated with this Connection which must be closed first."}

The corresponding controller code is as follows:

 public JsonResult GetPersonItems(string term)
        {
            if (term == null)
                term = string.Empty;
            SimpleAddressBookEntities Ctx = new SimpleAddressBookEntities();
            var query = from c in Ctx.People
                        orderby c.FirstName + ", " + c.LastName
                        where c.FirstName.StartsWith(term)
                        select c;

            List<personWithCompanies> L = new List<personWithCompanies>();
            foreach (Person c in query)
            {
//EXCEPTION OCCURS ON THE NEXT LINE. 
               company[] companies = new company[c.Companies.Count];
                int i = 0;

                if (c.Companies.Count > 0)
                {
                    foreach (Company p in c.Companies)
                        companies[i++] = new company { CompanyName = p.CompanyName, CompanyID = p.CompanyID.ToString() };
                }
                companies = companies.OrderBy(s => s.CompanyName).ToArray();
                L.Add(new personWithCompanies { value = c.FirstName + " " + c.LastName, id = c.PersonID.ToString(), comp = companies });
            }

            JsonResult r = Json(L.ToArray(), JsonRequestBehavior.AllowGet);
            return r;
        }

How to repeat:
Included in the above
[2 Aug 2010 19:06] Gerald May
Images of a couple of the tables from edmx files

Attachment: Model File Parts.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 104.91 KiB.

[2 Aug 2010 22:09] Gerald May
It looks as if the apparently unordered fields in the edmx file are in alphabetical order.  That of course is not the order they appear in the MySql Workbench or in the Visual Studio for the Sql Server version.  The field order in the MySql work bench matches what Visual Studio sees for the field order in the Sql Server database.
[1 Jun 2011 16:09] Julio Casal
There are too many issues described in this bug report. Please create one bug report for each issue you are facing in order to proceed with the appropriate next steps for each of them.

Also please try the latest released version of Connector/Net.