Home > Code > Using MySQL with Entity Framework and ASP.NET MVC – Part I

Using MySQL with Entity Framework and ASP.NET MVC – Part I

April 1st, 2009

I would like to take a look at creating an object model for a MySQL database schema in .NET and accessing that data with LINQ queries.

After some research on the subject I figured the path of least resistance would be to try out Microsoft’s latest data access technology, Entity Framework. Now, I know people are not thrilled with some of the apparent short comings of EF – friend’s don’t let friend’s use EF, but still we march on.

Prerequisites

Also, I am using ASP.NET MVC as our test harness so you will also need the ASP.NET MVC Framework installed along side Visual Studio. You can download that here. If you are unfamiliar with ASP.NET MVC, I suggest you get with the program.

Getting Started

I am going to assume that you are able to get the MySQL database engine along with the GUI tools installed. If not, read more here.

Once you have your MySQL database up and running open the MySQL Administrator. Fill in your connection information and credentials and select Ok.

Next, we are going to select the Catalogs icon and create a new schema (or database for SQL Server people). Right-click in the Schemata list and select Create New Schema.

tmp1867

Enter your new schema name and select Ok.

tmpB89E

Open the MySQL Query Browser from the Tools menu. Open and New Script Tab from the File menu and run the following scripts. This will create two tables. (note: we’re using the default MyISAM database engine)

CREATE TABLE  `demotest`.`category` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `demotest`.`product`;

CREATE TABLE  `demotest`.`product` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  `categoryid` varchar(45) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Now, in Visual Studio create a new ASP.NET MVC Web Application. Select, Yes to create a Unit Test project along side the web application. We may use this later.

tmpF6A8

Next, in your Visual Studio Server Explorer create a new data connection. Change your data source to MySQL and enter your database information.

tmp5A7A

Notice you can now access your MySQL database from the Server explorer.

tmp6D96

The next step is to add the Entity Framework model to our project. Right-Click on the Model folder that is created by the ASP.NET MVC project template and select Add New Item. Locate the ADO.NET Entity Data Model option and press Ok.

tmpAEC3

Select the Generate from database option and click Next. Select, Yes to putting your connection string information in your web.config. This might not be the best option for your “real world” apps but it is just fine for us. Now select the option to generate objects for our tables and input your Model’s namespace.

tmp4C6A

Select Finish and the EF magic happens.

tmpE92C

Now that our EF model has been generated we are going to wrap that in a simple repository pattern. Here is what the repository type will look like for Products.

public class ProductRepository
{

    MySqlEntities mySqlEntities = new MySqlEntities();

    public IQueryable<Product> Select()

    {

        var result = from p in mySqlEntities.ProductSet

                     select p;

        return result;

    }

    public IQueryable<Product> Select(int id)

    {

        var result = from p in Select()

                     where p.id == id

                     select p;

        return result;

    }

    public void Add(Product product)

    {

        mySqlEntities.AddToProductSet(product);

    }

    public void Delete(Product product)

    {

        mySqlEntities.DeleteObject(product);

    }

    public void Save()

    {

        mySqlEntities.SaveChanges();

    }
}

We will do the same for Categories…

public class CategoryRepository
{

    MySqlEntities mySqlEntities = new MySqlEntities();

    public IQueryable<Category> Select()

    {

        var result = from p in mySqlEntities.CategorySet1

                     select p;

        return result;

    }

    public IQueryable<Category> Select(int id)

    {

        var result = from p in Select()

                     where p.id == id

                     select p;

        return result;

    }

    public void Add(Category category)

    {

        mySqlEntities.AddToCategorySet1(category);

    }

    public void Delete(Category category)

    {

        mySqlEntities.DeleteObject(category);

    }

    public void Save()

    {

        mySqlEntities.SaveChanges();

    }

}


So far…

We have created our database schema in MySQL using the MySQL GUI Tools. In Visual Studio, we added a connection to our database in the Server Explorer. We generated a Entity Framework object model for or database schema, and wrapped that model with repositories.

Next time we will use those repositories to access and modify our data from ASP.NET MVC controllers. Also, we will add views to round out our demo with a simple user interface for Add, Edit, and Create scenarios.

Source

Download File – Source
  • Facebook
  • Twitter
  • Delicious
  • Reddit
  • StumbleUpon
  • Share/Save/Bookmark
  1. Pleb
    April 8th, 2009 at 10:15 | #1

    Thanks for the nice read. I look forward to your next article.

  2. jcavard
    April 17th, 2009 at 12:12 | #2

    Are you using Visual Studio 2008?
    I installed the MySQL ADO.NET Provider, but I don’t see it in the list of possible datasource in Visual Web Developer 2008. Is that normal?
    I thought I had found solution to my problem…

  3. April 17th, 2009 at 14:46 | #3

    @jcavard,

    Yes. I am using Visual Studio 2008. However, I am using the Professional version. I would not think that would make a difference. What options are you seeing when you select Add Connection->Change?

    Chris

  4. kay
    April 19th, 2009 at 17:20 | #4

    Hey Chris,

    Nice post. I am having the same issue as “jcavard”. I was using Visual web developer 2008 and i could not get MySql server to show in the list of datasource. So i got VS2008 professional version, and then i reinstalled MySql ADO.Net Provider “Connector/Net 6.0.2″… same issue i could not get MySql server to show up in the list of datasource. But when i tried in VS2005 i got MySql server to show up in the datasource list. Not sure where things are going wrong, any suggestions? thanks

  5. kay
    April 19th, 2009 at 17:39 | #5

    Follow up from my above comment. I tried something different this time, I completely uninstalled the MySql connector and then i installed it again and now its working.

    @jcavard :I can also confirm that MySql connector does not work with Visual Web Developer 2008. If you need more information on this issue i think you should contact Reggie @ http://www.reggieburnett.com, he is the developer of the connector. Also you can download a 90 day eval of VS2008 for free (3gb download)

  6. jcavard
    April 20th, 2009 at 10:44 | #6

    Thank you both.
    I have Visual Web Developer 2008 and Visual Studio 2005. I can see MySQL in 2005, but the thing is, I don’t have MVC template in 2005. I’m stuck in here, the sure bet would probably be Visual Studio 2008, since it has MVC AND MySQL, but the job does not have any license.

    I guess I will just have to start hand coding the whole model classes.

    Thanks, btw a great post like this deserve a better layout ;)

  7. April 20th, 2009 at 15:46 | #7

    @jcavard, The layout is on my list of things to do. Thanks for the positive feedback. Feel free to follow me on http://www.twitter.com/pattersonc

  8. jcavard
    April 21st, 2009 at 11:40 | #8

    wow, talk about some impressive change.
    I didn’t know my voice had such in impact ;)
    really nice layout man, great blog!

  9. April 21st, 2009 at 16:41 | #9

    Thanks for noticing but the credit goes to mg12 for designing and publishing his theme for other WordPress users. Eventually, I plan to have a custom theme designed by my partner Doug but things are a bit crazy right now preparing for the launch of our clothing company Infamous Couture ( http://www.vimeo.com/4047901 ).

  10. April 22nd, 2009 at 12:34 | #10

    Excellent article!
    Congratulations, when it will be able, also visit my Blog.

    Tanks!

  11. jcavard
    April 23rd, 2009 at 13:25 | #11

    I would have one question though.
    What should I use to create the model of a MySQL database? I mean, I can’t use ADO.NET Entity Data Model like you did, sine I can’t see MySQL tables, so am I doomed? Is there any other way to hand-code? What file should I choose when I click Add New Item on the Models directory?
    If only I could get a tip on where to start, I’d be grateful.
    thanks again

  12. April 23rd, 2009 at 15:41 | #12

    @jcavard – You have the option to code by hand – see the MySQL ADO.NET Connector documention for example of using the MySqlConnection object. And, you could also try to use another ORM like nHibernate – here is an example.

  13. Francis
    April 24th, 2009 at 11:54 | #13

    Anyone can confirm if this (data source from server explorer) works in the Standard edition of Visual Studio instead of the Professional edition? I could save almost 500$ right there…

  14. April 24th, 2009 at 22:09 | #14

    I have posted a comment on Reggie Burnett’s blog asking if he can confirm which Visual Studio versions are compatible with the integration feature of the connector.

  15. jcavard
    May 7th, 2009 at 13:41 | #15

    ok. I’m back again… I followed the NHibernate tut you posted, but it’s not MVC. I get stuck with that first line of code:
    Configuration config = new Configuration();

    duh! Where do I put that code… I’m on the verge of breakdown. I totally HATE asp.net and all that stuff, but I am stuck with it at work :S
    wouldn’t you have any idea abour the config?
    thanks!

  16. jcavard
    May 7th, 2009 at 15:15 | #16

    Heeeha! I had to let you know, I just figured out the whole thing and finally got something working!
    I was a mixed of
    - wrong assembly name
    - with outdated doc versus up to date source code of NHibernate…
    let me know if anyone be interested, in that code..
    Thanks Chris!

  17. captain
    May 29th, 2009 at 20:29 | #17

    Just wanted to thank you for an excellent article. It helped me get started with MySQL + EF.

    Also have a trivial question. Are relationships/references handled correctly in Visual Studio Designer/ADO.NET when using MySQL a database with this provider? I could go ahead and try and see for myself but am in a bit of a rush now and perhaps it’d be interesting for others that are new to this as well.

  18. June 1st, 2009 at 11:47 | #18

    @captain, I have not tried with any MySQL database engine besides the default, which does not support relationships.

  19. captain
    June 2nd, 2009 at 15:25 | #19

    yeah i was thinking of InnoDB. didn’t realize you were using MyIsam. anyway, i’m about to try it out now.

  20. July 16th, 2009 at 23:13 | #20

    I am trying to use EF with Mysql in one of my projects right now. I’ve got the data model set up correctly but when I try to do a LINQ a query I get a timeout. The query works well in the server view (using SQL).

    What could be up? I hope it’s just something I overlooked and I can find quick.

  21. July 24th, 2009 at 16:23 | #21

    @Cyril Can you verify that it is the query timing out or if there is a problem with your connection to the database?

  22. October 6th, 2009 at 09:08 | #22
  23. LogicalDev
    October 9th, 2009 at 06:41 | #23

    I fixed it.
    I am using Visual studio 2008 professional edition.
    MySQL version 5.1.30
    connector 6.0 installer:
    download :http://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-6.0.4.zip/from/http://gd.tuwien.ac.at/db/mysql/

    I used a version before 6.0:5.x,but it didn’t work.
    I am downloading entity framework for .NET 3.5 framework.

  24. LogicalDev
    October 9th, 2009 at 07:04 | #24

    where do i get ADO.NET Entity Data Model?Is is not in my templates.

  25. LogicalDev
    October 9th, 2009 at 10:55 | #25

    plz help,Iam using NET 3.5 framework.+VS 2008 Professional,
    cant install ADO.NET Entity Data Model.Cant find ADO.NET Entity Data Model
    3.0 beta.

  26. Ivan Shkuropadsky
    November 26th, 2009 at 04:20 | #26

    Hello, Christopher!

    We have encountered a problem of using Entity Framework with InnoDB tables with foreign keys.

    The same problem is well described by Christopher Hemple in his message “ADO.NET Entity Generation Wizard Taking Hours” (http://stackoverflow.com/questions/1277151/ado-net-entity-generation-wizard-taking-hours).

    In our database there are 130 tables with foreign keys, and model generation now takes about three hours…

    What do you think about this problem? Can you help us to understand and to solve it?

    Thank you in advance!
    Ivan Shkuropadsky (Russia)

  27. June 15th, 2010 at 16:41 | #27

    Hello,
    congratulations for your post.
    These Factories classes, do I creat at model directory?

  28. March 21st, 2011 at 03:11 | #28

    how to take database entry in drop down list in asp.net

  29. March 21st, 2011 at 03:12 | #29

    how to take mysql database entry in drop down list in asp.net

  30. May 7th, 2011 at 16:05 | #30
  31. Deepak
    June 21st, 2011 at 02:27 | #31

    While doing the edit & calling save is not updating the data. SaveChanges is returning 0(no exception). I tried calling AcceptChanges also before SaveChanges, but no effect. Any clue?

  32. September 7th, 2011 at 04:04 | #32

    Incorrect PIN bbs loli rompl dorki pthc
    hoq

  33. September 8th, 2011 at 03:59 | #33

    Is there ? young teen models
    gpq

  34. Chandran
    August 19th, 2013 at 07:05 | #34

    Hi,
    I am creating one model application,it has mvc3,entity framework and mysql also used,the create action result is very well to work but edit or update how can i do? please give your suggestion,send through my mail,mail id is chandrannew@gmail.com

  35. Chandran
    August 19th, 2013 at 07:07 | #35

    Hi,chandrannew@gmail.com
    I am creating one model application,it has mvc3,entity framework and mysql also used,the create action result is very well to work but edit or update how can i do? please give your suggestion,send through my mail,

  36. Chandran
    August 19th, 2013 at 07:08 | #36

    my mail id is chandrannew@gmail.com I am creating one model application,it has mvc3,entity framework and mysql also used,the create action result is very well to work but edit or update how can i do? please give your suggestion,send through my mail,

  37. May 6th, 2014 at 03:30 | #37
  38. August 20th, 2014 at 03:05 | #38

    I know this site provides quality based articles
    or reviews and other data, is there any
    other web site which provides such data in quality?

  39. August 22nd, 2014 at 23:46 | #39

    I think the admin of this web site is in fact working hard for his site, since here
    every data is quality based stuff.

  1. April 21st, 2009 at 18:39 | #1
  2. July 20th, 2009 at 11:27 | #2
  3. September 16th, 2010 at 21:57 | #3
  4. December 9th, 2013 at 10:21 | #4
  5. December 28th, 2013 at 20:21 | #5