{"id":22,"date":"2009-04-01T20:29:19","date_gmt":"2009-04-02T00:29:19","guid":{"rendered":"http:\/\/pattersoncprod.wpengine.com\/?p=22"},"modified":"2009-04-01T20:29:19","modified_gmt":"2009-04-02T00:29:19","slug":"using-mysql-with-entity-framework-and-aspnet-mvc-part-i","status":"publish","type":"post","link":"https:\/\/pattersonc.com\/?p=22","title":{"rendered":"Using MySQL with Entity Framework and ASP.NET MVC &ndash; Part I"},"content":{"rendered":"<p>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.<\/p>\n<p>After some research on the subject I figured the path of least resistance would be to try out Microsoft\u2019s latest data access technology, Entity Framework. Now, I know people are not thrilled with some of the apparent short comings of EF \u2013 <a href=\"http:\/\/www.chadmoran.com\/blog\/tag\/adonet-ef\" target=\"_blank\" rel=\"noopener\">friend\u2019s don\u2019t let friend\u2019s use EF<\/a>, but still we march on.<\/p>\n<p><strong>Prerequisites<\/strong><\/p>\n<ul>\n<li><a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?FamilyID=AB99342F-5D1A-413D-8319-81DA479AB0D7&amp;displaylang=en\" target=\"_blank\" rel=\"noopener\">.NET 3.5 Service Pack 1<\/a> <\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/downloads\/mysql\/5.1.html#downloads\" target=\"_blank\" rel=\"noopener\">MySQL Database Engine<\/a> <\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/downloads\/gui-tools\/5.0.html\" target=\"_blank\" rel=\"noopener\">MySQL GUI Tools<\/a> (recommended and used in this example) <\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/downloads\/connector\/net\/6.0.html\" target=\"_blank\" rel=\"noopener\">MySQL\u2019s Latest ADO.Net Provider<\/a> (<strong>Update:<\/strong> requires Visual Studio Professional Edition. see comments) <\/li>\n<\/ul>\n<p>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 <a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?FamilyID=53289097-73ce-43bf-b6a6-35e00103cb4b&amp;displaylang=en\">here<\/a>. If you are unfamiliar with ASP.NET MVC, I suggest you get with the <a href=\"http:\/\/haacked.com\/archive\/2009\/03\/10\/chapter-one-pro-aspnetmvc.aspx\" target=\"_blank\" rel=\"noopener\">program<\/a>.<\/p>\n<p><strong>Getting Started<\/strong><\/p>\n<p>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 <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/p>\n<p>Once you have your MySQL database up and running open the <em>MySQL Administrator. <\/em>Fill in your connection information and credentials and select <em>Ok<\/em>.<\/p>\n<p>Next, we are going to select the Catalogs icon and create a new schema (or database for SQL Server people). Right-click in the <em>Schemata<\/em> list and select <em>Create New Schema<\/em>.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp1867.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmp1867\" border=\"0\" alt=\"tmp1867\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp1867-thumb.png\" width=\"165\" height=\"244\" \/><\/a><\/p>\n<p>Enter your new schema name and select <em>Ok<\/em>.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpb89e.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmpB89E\" border=\"0\" alt=\"tmpB89E\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpb89e-thumb.png\" width=\"244\" height=\"136\" \/><\/a><\/p>\n<p>Open the <em>MySQL Query Browser<\/em> from the <em>Tools<\/em> menu. Open and <em>New Script Tab<\/em> from the <em>File<\/em> menu and run the following scripts. This will create two tables. (note: we\u2019re using the default MyISAM database engine)<\/p>\n<pre class=\"brush: sql;\">CREATE TABLE  `demotest`.`category` (\n\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n\n  `name` varchar(45) NOT NULL,\n\n  PRIMARY KEY (`id`)\n\n) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;\n\nDROP TABLE IF EXISTS `demotest`.`product`;\n\nCREATE TABLE  `demotest`.`product` (\n\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n\n  `name` varchar(45) NOT NULL,\n\n  `categoryid` varchar(45) NOT NULL,\n\n  PRIMARY KEY (`id`)\n\n) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;<\/pre>\n<p>Now, in Visual Studio create a new <em>ASP.NET MVC Web Application. <\/em>Select, <em>Yes <\/em>to create a Unit Test project along side the web application. We may use this later.<\/p>\n<p><\/p>\n<pre><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpf6a8.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmpF6A8\" border=\"0\" alt=\"tmpF6A8\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpf6a8-thumb.png\" width=\"427\" height=\"154\" \/><\/a><\/pre>\n<p>Next, in your Visual Studio Server Explorer create a new data connection. Change your data source to MySQL and enter your database information.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp5a7a.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmp5A7A\" border=\"0\" alt=\"tmp5A7A\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp5a7a-thumb.png\" width=\"244\" height=\"233\" \/><\/a><\/p>\n<p>Notice you can now access your MySQL database from the Server explorer.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp6d96.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmp6D96\" border=\"0\" alt=\"tmp6D96\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp6d96-thumb.png\" width=\"228\" height=\"244\" \/><\/a><\/p>\n<p>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 <em>Add New Item. <\/em>Locate the ADO.NET Entity Data Model option and press <em>Ok<\/em>.<\/p>\n<p><em><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpaec3.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmpAEC3\" border=\"0\" alt=\"tmpAEC3\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpaec3-thumb.png\" width=\"430\" height=\"199\" \/><\/a><\/em><\/p>\n<p>Select the <em>Generate from database<\/em> option and click <em>Next<\/em>. Select, Yes to putting your connection string information in your web.config. This might not be the best option for your \u201creal world\u201d apps but it is just fine for us. Now select the option to generate objects for our tables and input your Model\u2019s namespace.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp4c6a.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmp4C6A\" border=\"0\" alt=\"tmp4C6A\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmp4c6a-thumb.png\" width=\"459\" height=\"431\" \/><\/a><\/p>\n<p>Select <em>Finish <\/em>and the EF magic happens.<\/p>\n<p><a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpe92c.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px\" title=\"tmpE92C\" border=\"0\" alt=\"tmpE92C\" src=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpe92c-thumb.png\" width=\"244\" height=\"133\" \/><\/a><\/p>\n<p>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.<br \/>\n  <\/p>\n<pre><pre class=\"brush: csharp;\">public class ProductRepository\n{\n\n    MySqlEntities mySqlEntities = new MySqlEntities();\n\n    public IQueryable&lt;Product&gt; Select()\n\n    {\n\n        var result = from p in mySqlEntities.ProductSet\n\n                     select p;\n\n        return result;\n\n    }\n\n    public IQueryable&lt;Product&gt; Select(int id)\n\n    {\n\n        var result = from p in Select()\n\n                     where p.id == id\n\n                     select p;\n\n        return result;\n\n    }\n\n    public void Add(Product product)\n\n    {\n\n        mySqlEntities.AddToProductSet(product);\n\n    }\n\n    public void Delete(Product product)\n\n    {\n\n        mySqlEntities.DeleteObject(product);\n\n    }\n\n    public void Save()\n\n    {\n\n        mySqlEntities.SaveChanges();\n\n    }\n}\n<\/pre>\n<p>We will do the same for Categories\u2026<\/p>\n<\/p>\n<pre class=\"brush: csharp;\">public class CategoryRepository\n{\n\n    MySqlEntities mySqlEntities = new MySqlEntities();\n\n    public IQueryable&lt;Category&gt; Select()\n\n    {\n\n        var result = from p in mySqlEntities.CategorySet1\n\n                     select p;\n\n        return result;\n\n    }\n\n    public IQueryable&lt;Category&gt; Select(int id)\n\n    {\n\n        var result = from p in Select()\n\n                     where p.id == id\n\n                     select p;\n\n        return result;\n\n    }\n\n    public void Add(Category category)\n\n    {\n\n        mySqlEntities.AddToCategorySet1(category);\n\n    }\n\n    public void Delete(Category category)\n\n    {\n\n        mySqlEntities.DeleteObject(category);\n\n    }\n\n    public void Save()\n\n    {\n\n        mySqlEntities.SaveChanges();\n\n    }\n\n}<\/pre>\n<p>\n  <br \/><strong>So far\u2026<\/strong><\/p>\n<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><strong>Source<\/strong><\/p>\n<div style=\"padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px\" id=\"scid:FF7EC618-8FBE-49a5-B908-2339AF2ABCDF:2a6ed7d4-832d-474e-a77a-5c71bde18639\" class=\"wlWriterSmartContent\">\n<div>Download File &#8211; <a href=\"https:\/\/pattersonc.com\/wp-content\/uploads\/2009\/04\/tmpddf9.zip\" target=\"_self\" rel=\"noopener\">Source<\/a><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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\u2019s latest data access technology, Entity Framework. Now, I know people &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_block_theme_hide_title":false,"footnotes":""},"categories":[3],"tags":[2,4,5],"class_list":{"0":"post-22","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-code","7":"tag-aspnet-mvc","8":"tag-entity-framework","9":"tag-mysql","11":"without-featured-image"},"featured_image_src":null,"featured_image_src_square":null,"author_info":{"display_name":"Christopher Patterson","author_link":"https:\/\/pattersonc.com\/?author=1"},"_links":{"self":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts\/22","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=22"}],"version-history":[{"count":0,"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts\/22\/revisions"}],"wp:attachment":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}