DeveloperNote.com

In general, getting EF work with MySQL is a fairly simple task, that could be accomplished by downloading and installing ADO.NET driver for MySQL. But what concerns to me, it taken me about four hours to clarify some MySQL-specific details that affect generation of associations in Model Designer. Also after doing an experimentation with the code I realized that ADO.NET driver for MySQL, as well as other third party ADO.NET drivers, do not support “MARS” and, as far as I see, this significant restriction makes EF unusable with MySQL in large real-life projects. Please read below if you interested in more information on this questions.

How to create an association in Model Designer

I did my experiments with VirtueMart 1.1.9 database and firstly, by executing the following query, I surprisingly noticed that there are no foreign keys in my database:

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME IS NOT NULL;

And furthermore, I discovered that all my VirtueMart tables use MyISAM storage engine that does not support foreign keys at all. So, my first step was to change the storage engine of related tables:

ALTER TABLE jos_vm_orders ENGINE = InnoDB;

then I created the foreign key:

ALTER TABLE jos_vm_order_item ADD FOREIGN KEY (order_id) 
REFERENCES jos_vm_orders(order_id) 
ON DELETE CASCADE ON UPDATE CASCADE;

and finally I updated my model from the database using “Update Model from Database” menu item in Visual Studio and got two entities “jos_vm_order” and “jos_vm_order_item” with related fields named “order_id”:

Problems with MARS

Suppose we have several tables (imported from VirtueMart database):

ADO.NET driver for MySQL does not support MARS, so the following code would not work:

using (VirtueMartContext db = new VirtueMartContext())
{
    jos_vm_product product = productListControl.SelectedProduct;

    if (product != null)
    {
        db.Attach(product);

        jos_vm_product_type_1 t1 = product.ProductType1;

        //everything is OK here
    }
    else
    {
        jos_vm_category category = productListControl.SelectedCategory;

        if (category != null)
        {
            var products = from p in db.jos_vm_products
                            join r in db.jos_vm_product_category_xrefs on p.product_id equals r.product_id
                            where r.category_id == category.category_id && p.product_publish == "Y"
                            select p;

            foreach (jos_vm_product p in products)
            {
                jos_vm_product_type_1 t1 = product.ProductType1;

                //at this point we get exception
                //There is already an open DataReader associated with this Command...
            }
        }
        else
        {
            MessageBox.Show("Select product or category.");
        }
    }

    db.SaveChanges();
}

If I change it to this everything is ok:

if (category != null)
{
    jos_vm_product[] products;

    {
        products = (from p in db.jos_vm_products
                    join r in db.jos_vm_product_category_xrefs on p.product_id equals r.product_id
                    where r.category_id == category.category_id && p.product_publish == "Y"
                    select p).ToArray();
    }

    foreach (jos_vm_product p in products)
    {
        jos_vm_product_type_1 t1 = product.ProductType1;

        //now everything is OK
    }
}