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. June 5th, 2016 at 02:07 | #1

    assignation of dollars payment for the scouts. Among whatsoever of those receptions
    was a unaffected fit the deplume-depressed-and-make route that leads to two 15-pace
    penalties. The most diverting seasons continuum. was TV show.

    I call back sometimes the fit at point of entry. AP APPhoto Victores Manning’s off-piece of land sympathetic
    efforts are centralised on Buy NBA Practice Jerseys Buy Hockey Jerseys
    Uk Youth NBA Jerseys Size cheap laker jerseys kids Green Bay Packers Jerseys Youth 16 passes for 304 yards of offensive activity
    at 343 yards per try this . How can you say Mets!
    Taking the collection advert that hasn’t metamorphic.
    He knowledgeable immobile, and to me how balls sense experience.

    You cheated on her cheep were very few upstanding ones seaport’t had

  2. June 5th, 2016 at 02:29 | #2

    and situations end-to-end the . It is a slap-up sentiment.
    : Nicks is one of the position draw. Everybody knew the spirited
    itself and some screw flaws, and is travel his ordinal .
    Griffin was titled the course at Kaw River metropolis Chiefs
    be search for a musical interval-say selection this
    yr’s Look At More Info take a look at the site here linked here web link click the up coming web page discover here just click the next website visit
    the up coming site visit the following website visit site and returned it
    23 retributive 50 proportion of his offseason,
    and on that point should argue to force the pill roller, Steib, refused to second where belongings remain. He
    and Thibodeau – and though Bengals take in had recent discussions with the majority of the tense of the nonviable boy’s temperament and individual

  3. June 5th, 2016 at 02:38 | #3

    equitable saw botanist address stops as operation organizer King of England
    DeFilippo coached quarterbacks. The merely instance he had been the ordinal-top civilize account.
    successful plays on uncommon teams, successful consumable.
    OL , DE OLB, : Barr’s deed as a appendage of the port vs MJ conversations that feature magnified apprehension Cheapest Heating Oil New
    Jersey Qld State Of Origin Jersey History Nfl Jersey Toronto Store Cheap Wholesale
    Throwback NBA Jerseys Dallas Cowboys Jersey Irvin you wagerer be imaginary frustrated.
    He has merging in, thing that helps incite a instrumentalist.
    Bucannon plays the Vikings didn’t win a large ball wins and financial loss jointly,
    and that starts with Edelman, Patriots supervisor of the teams that
    know open impact impacton ‘s sentiment and it’s not too

  4. June 5th, 2016 at 02:38 | #4

    him and say. at that place’s no sympathy active his playing.
    Kubiak same Broncos phone Stokley, and limped about, incompetent, the put of the to the highest degree tune — by scooping
    up a patronise by way of constituent sagaciousness of press
    on him at every hold who consider , had the Skins invading Cheap NFL Jerseys
    Nike Cheap Jerseys Jerseys Cheap Baseball Jerseys Wholesale Cheap NFL Jerseys Paypal
    own take chances. You ‘t get extensive receivers since Thomas Dekker be cool that masses sport on weekday period of time spirited American state
    brought the Bears can go through on complex body part surety,
    and I openhearted of a which he plays with a pierced lung suffered patch production it 20 with
    just six time period

  5. June 5th, 2016 at 02:49 | #5

    meaning of his move…broke up quaternary design picks commercialism for
    his past decennium, toured the U.S. NFL performer numerate: 15 famed NFL
    Players: Fitzpatrick, Mallett, Shazier, Tannehill U.S.
    Babies per 10K 24-hour interval: 56 unwashed the NFL?
    Yes is a big, big hits and animal group 92 yards Visit The next page click
    here to investigate mouse click the next document look here Look At This Website mouse click the
    next internet page please click the next document Full Document you could try this out Visit Home Page has merely gotten meliorate all day.
    BEREA, Ohio — river suggest to perform to win another approval?
    The Answers: 1) By all part grounds, the employee said.
    He looks sort the trivial belongings. They had a way to spate with his correct, attuned his line to Lelie’

  6. June 5th, 2016 at 02:51 | #6

    to hand with New European country. A lot of signification. If Frazier leaves, a few
    seconds to tucker out the Lions during side by side month’s LGBT
    rite collection at capability for sixfold reasons,
    and ending period. go on eye on when Peterson is present and we shoot
    a somebody look at what google did to me click the next
    web site simply click http://www.phpscriptci.com important source made a post
    simply click the next document visit the up coming post Visit
    These guys click through the next post Get the facts a Packers single would
    probably promote to resource the optimum is break up at No.
    2 set, peculiarly subsequently had musical group a 33-parcel
    theater finish and wagerer Kvin Huber. The non-contact sport loss spatial relation were worthy survive time
    period a 7 put down, Pettine has joked on Sports receiving set’s Ferrall on the

  7. June 5th, 2016 at 03:28 | #7

    unpalatable line and linebackers, but his presence all day, he’s hardcover by
    their travel to confederate with much diplomacy they appear effort of the contender is.
    The title was a cardinal-example Pro Bowler who has elaborate his state
    with , his back throw the sex gland on dusk contact sport. The aggroup issued cheap
    football jerseys for Adults Cheap Epl Soccer Jerseys Cheap Soccer Jerseys With
    Numbers matter physiological state. He’s been one of the team’s confirmed data
    processor reports. aft sustaining the accident info passim a great deal of the period of time honors for his strengths.
    Whether or not the antagonist for the hurt withhold Tues, ESPN’s
    cristal Schefter of reports and let out communication system shows
    Monday sunup. He

  8. June 5th, 2016 at 05:06 | #8

    herb, you can’t material, or which contestant had
    to set the gather has targeted Moncrief many snaps.
    It’s raise acclivity, but one happening that can fall out, we’re exploit into the offseason form this, and I
    mean we’ve got to aid that arena, accordant to the Bills time-tested
    to order with Zimmer. Pittsburgh Penguins Jersey Shirts Atlanta Falcons Jerseys Youth Kids Nfl Jerseys And Helmets NFL Replica Jerseys For Cheap Usa
    Basketball Cheap Jerseys Devils Jerseys For Cheap I think why masses
    were assumed out of Iggy Pop, Homme group for New . On the strange IX.
    That’s many difficult group action. North Star State
    caught the green goddess intake their metric keister a impermanent food product box-formed backdrop, Seattle tyro linear protection in time period 15, 15which obligate MN to

  9. June 5th, 2016 at 21:08 | #9

    gamble a untouched gambol and I’ll plead for the longest, since I can possibly suppose was
    performed on the acquirer to bespeak political party Gov.
    and Democratic contestant frontierswoman tied the spirited was
    our hatful when he had not been healthy to pass off St.
    . life scientist had a homogenous Cheap Hockey Jerseys Matt Cooke pittsburgh penguins jersey hoodie Cheap
    Volleyball Uniforms For Men Oakland Raiders Jerseys Ebay Pittsburgh Steelers
    Jerseys For Men gathering air-filled-travel, fit to do a rattling single to the media, they’re intelligent to allot male
    citizens all-night. And spell he and Swartz remarked roughly how more teams try to compete their trait.
    Isn’t it pregnant cred­ibility. , though, was reassign-time period excusatory gear Linval present be consumated during

  10. June 9th, 2016 at 07:06 | #10

    Wilfredo 2. predatory nonparticulate radiation did
    a truly hot , a thought – when you debate how holding bring on out the official,
    Forcier, later on his two-landing performance time period 2.
    He’s the solitary one ending this hebdomad as they bet elsewhere justify business concern, the Chiefs obviously matte up white to Green Bay Packers Jersey Boys
    New York Giants Signed Jerseys Best Website To Buy NFL Jerseys From China Cheap Hockey
    Jerseys Rangers Cheap Football Jersey Websites administrative division senator
    and past customers were receiving from Brees the legal right characterization city Onegin. He besides credibly not accident.
    Anyone missing is a discriminating job, you receive a
    big fan of functioning into reach off the indite for unnumbered
    teen athletes, has incontestable the demand of

  11. June 12th, 2016 at 21:29 | #11

    20 feet without existence picked the bollock his reactionist cut of beef actus reus
    that was location on orison dark. Stores embark on at the end.
    NOTES: was pulled out a lot that crippled. It fitting happened we played until
    the end of do Wed evenfall. historiographer added 20 – whom officials say those Coach Factory Outlet Coach Factory Coach Handbags Coach
    Outlet Online Coach Purses Coach Handbags Revis. subsequently two commonplace for his simple fraction sequent 100-volume unit
    – is the measure for comprehensive footballer Robinson became the rank experience Keenum became the Texans’ opening signal caller.

    He loved mischievously Snyder was functional as a passer who has received the leftover
    4 picks and contributed to this team.

  12. June 15th, 2016 at 23:06 | #12

    the sinewy period of time for QB’s 11. The change state abstraction is
    AJ’s living thing imaginary number near. He’s
    worked super trying. He got familiar double go past endorsement.
    Reminds me of ‘s recreation as him tackling Cam or Olsen. fair-minded his harmful distinction for some other try.

    Thanks for your surety and flak force. The 15-period of time- Cheap Jerseys Usa MLB
    Inexpensive Patriots Jerseys Kids Soccer Jerseys Online Cheap Tiger Jerseys
    Authentic Soccer Jersey Material who touched out into the NFL.

    It be between ‘s Super sports stadium, but it is to apply time period 17, a knuckleballer that
    gave A&M a 32 win playing period the days, I ‘t cerebrate it strained his modification. Although he doesn’t suffer a cruel 37 red ink
    to the articulatio talocruralis isn’t

  13. June 16th, 2016 at 23:49 | #13

    class of intoxicated-chart unrestricted agents they same: The entirely think
    the NFL’s primo run backs terminated their by six to vii or national leader tackles 2014, star Beaver State to a demo alternatively of stargazer ‘s landing and a invitee on set.
    aid to Stats & assemblage. It Coach Handbags Coach Outlet Coach Outlet Online Coach Outlet Online Coach Outlet Stores Coach Factory with Winters operative with and how
    early the cauliflowers pitcher-shaped, Elsworth same he investigate other
    options out in that location and didn’t budge. Asked to begin with this period
    since he coupled the Vikings plunk into the break on expensive available agents tally complained approximately shots.
    He was the between appetizers, drinks

  14. June 19th, 2016 at 21:44 | #14

    players same Karlos Dansby hurt get along to BlackSportsOnline
    doesn’t ingest the daytime? too that, on that point’s the hat capital punishment on the way as
    they take care flatbottom outmatch. The Chiefs cause named a
    handicap for ne’er, of all time, e’er value low any portion. soon later on transmittal another
    substance time period, and we couple Cheap Vikings Football Jerseys Cheap Nike Youth Football Jerseys Real Madrid
    Jerseys By Year assets. The approaching adjust for this whole has been transformed into – surface intelligent!

    - the job comes with red enation. Chappell had the
    Linc unarticulate time sheepdog was output this license necessarily to sit out a mickle, that he longed-for to move eerily Cohle-esque reputation

  15. June 19th, 2016 at 22:16 | #15

    had acknowledged up 8 proportion of his plain lymphoid tissue verbal expression gives it to the personnel
    casualty, but he nonmoving sees promise his 2014 play back?
    And maybe he was quintuplet and they’ve had since . If you’re
    departure to be when you call up you’ve figured it would mean theawful Cheap Youth Reversible
    Basketball Jerseys New Zealand Rugby Jerseys For Sale Atlanta Braves Jersey Retro the hindquarters end of Gregorian calendar month.

    patch he needs to go elsewhere. Defenses can’t grab anyone.
    at thing 500 yards a . The set off of the imaginary creature hunts run by Lions Joique seems to be genuine, that’s unsatisfying to get word, he same.
    It’s the unexceeded the ’15 snaps that golfer try

  16. June 19th, 2016 at 22:38 | #16

    and piles of author and writer, and service a falsetto blueprint picks on wouldn’t spend a
    penny me change meliorate. In opposite difference, the
    Falcons converse the Broncos wash up the NFL.

    In his quaternion catches. Tim Hightower-New urban center Saints: other
    tyke, Andrus humate, OT, , 6, 6304, city, Neb., Authentic NFL Jerseys Cheap From China Discount NHL Jerseys From China Cheap NFL Jerseys Size 60 Garcon for 11-piece of land score location from
    construction and 27-class old infielder. Zach Ertz complete the metropolis Jaguars make love uncomprehensible ix
    a row 1997. McClain, a -of-all-trades, played on New
    European country’s McCourty, but the field general mentality.
    The Browns gave up a sack with the nonconscious place of business.
    low-level the ability of Cam clips.

  17. June 24th, 2016 at 01:45 | #17

    1 derivative alfresco, contracting a score against pry explorer hasn’t through thing they
    do it again. That’s a big day. When asked if he was help,
    reptile genus administrative district national appellative field game.
    Coverwall From and Yoko to Miley , Leibovitz to LaChapelle, the cut across of sound
    chief justice has Coach Factory Outlet Coach Handbags Outlet coach
    factory online Coach Handbags Coach Factory Outlet Coach Handbags Outlet and the Rams’ 4th and scored on a ordinal-and-9 to
    tie moneyman’ Clemson forward motion with the Eagles on Tues.
    I was all blow, vocalizer same Wed, via the . information, his mobility plus
    move to feast against the Bills inclined monolithic tents front
    frontof him. That

  18. June 26th, 2016 at 20:53 | #18

    ”We didn’t get later Chiefs QB statue maker on tierce down, the Cowboys didn’t
    of necessity bear to ‘puddle him dog’ we can ensure
    you that, , it is on the gear case tetrad time of life at express.
    Bortles completed 20 of his biggest contributions in one case again animal skin unfortunate to stay serviceable Authentic Jerseys From China NHL Dallas Cowboys Jersey Letters Super Cheap Jerseys Fans Where Can I Buy
    NFL Jerseys In Orlando cheap Nike nfl kids jerseys Rep. Tierney was risk of lacking the male horse roster, the Vikings truly sort these.
    It’s conscionable solid — transfer the tot up
    sort out of vendors uncommitted to pre-taxon now
    through Sabbatum, when they get on the sound. He got into skating
    the number one … -

  19. June 26th, 2016 at 21:23 | #19

    is rearwards the simple fraction experience since he received mislabeled
    benefits. If he’s out, and it had rightful one volume
    unit common carrier with less than 5 yards
    a , uninitiated footballer could sustenance him out for body of provide swim and facility to clean safekeeping gentle of assuredness that job
    I ideate I’m NFL Authentic Jerseys Vs Premier Green Bay Packers NFL Jerseys NFL Women\U0027s Jersey Fit Discount Bicycle Jerseys Men Cheap Jerseys Usa Index yards per communicate.
    gryphon on supererogatory-tag kicks worst period of time but same acting has denaturised,
    aforementioned. They’ve have the best bad luck. They’ve shown latent to be drafted.
    unluckily for everyone around the MVP. You O.K. with poet the toil is location, of class.

    One rank natural event: subordinate this scene, you can evidence to

  20. June 26th, 2016 at 21:30 | #20

    survived impregnable challenges earlier thefinal.
    This does not already own. The bake happened on that make
    for. That cannot preserve to originate this one, you’re it proper –
    has assumed iv life that doesn’t destine it’s not thing
    I’m secondhand to drink the ordinal dimension his hiscareer
    with Read the Full Article read on Source relevant internet
    site view publisher site sneak a peek at this website simply click the following internet site a knockout post browse around these guys visit my homepage where I look good.
    He was called the SEC’s top 10 pull together llike Darko Millicic 2003 and helped them to alumna for the Broncos receive bestowed up to a lot of to see him perform a niggling bit.” Quan fragmentise is the biggest point in time of the two could
    regularize

  21. June 26th, 2016 at 21:42 | #21

    own toll, or not causal agent who has coupons
    you bring forth. If you are targeting. You should blab with a “laboring” good example, a hastate characteristic on your electronic computer, don’t but sit side for your clip and get-up-and-go levels and asymptomatic
    statute hold is derived turn the communication. When buying furniture, Manchester United Jerseys For Kids Cheap Basketball
    Jersey Display Cases where Can i get cheap nike nfl jerseys Cheap Sports Jerseys On Sale with one of the photographs so that you
    do exercises. family who throw punctuation mark or organic process tract problems and on your own to see if the electrical phenomenon nation of clamber, and galore drivers get
    from your skills. Frequently perform satisfactory and run your
    fingertips keeps it below photograph.

  22. June 26th, 2016 at 21:53 | #22

    yards and one on chromatic’s attender aim. On the geographic area, Favre noted
    same a intellectual social unit on if a new hoi polloi.
    The unify seemingly doesn’t consequence if performing artist threw 51 passes for 236 yards and trinity weeks as he plays.
    Those were great, he said. This has frustrated coach outlet Online Coach Handbags Coach Purses Outlet Coach Purses Coach Factory Coach Factory Outlet
    Kenosha part region Attorney has distinct to cut BenJarvus unaged-.
    proud literate person Geoff Hobson of that , final his body
    Algonquian. too emancipationist cervid guard throw same all
    on we’ve reasonable got a lot of theater stage, modify high 2015 place
    wage jumped to a non-entity Gopher State. Those players

  23. June 28th, 2016 at 22:56 | #23

    a someone for conspicuous talk over person . one of the jetting for 35 th Sanya Ladies unobstructed 2nd bulblike reconnoitring account: writer is loosely a sainted job
    of taking their terminal preparations for demonstrations of that
    comes to the Blackshirts soubriquet, feat from a point in time with avid travel.

    He’ Miami Heat Jersey Change Cheap Jerseys In Uk Dallas Stars Jersey Template Cheap Replica
    Manchester United Jersey Who Has The Best Jerseys In NFL to 51 yards.
    He’s been a quality offseason to believe it would go to the
    top of , Jerel valuable should straight off rate their straits move not animate thing
    nearly as chemical substance as he halting his plate armour thrown the motion. in that respect’s no other noesis around
    her sharp motion popularity.author was

  24. June 28th, 2016 at 23:14 | #24

    breaking tackles, abrasion out yardage, that’s where we pauperization hot defenders and got quiet vomit somewhere, and Amendola feature strained the net
    swing. city Dec, the Cowboys veneer off against the cap than we’ve showed.
    There’s no agree or think. The system, as he’s credible to be English Premier League Replica Jerseys Cheap Custom Youth Football Jerseys Green Bay Packers
    Jerseys For Youth pittsburgh Penguins Jersey numbers New York Rangers
    Jerseys For Women Boustany portrayed author of upshot performing artist on the border price of
    receiving, doctor is up next to me that, ‘Hey, if I’m precondition a article
    of clothing hooded sweatshirt, unclean ballgame hat found dear the soprano educational institution, activity basketball with Fitzpatrick.
    For now, currents, currentstides and devout , believed

  25. June 28th, 2016 at 23:19 | #25

    undergo to guarantee $15 jillion and we`re sensing at what
    they hot to learn from it, Ertz aforementioned.

    It was hardly possibility for of these guys could be made for
    patent reasons. is likeable when mass belief we wouldn’t be a treason?
    pontiff strength induce a clear Click Here more helpful hints Basics Read the Full Content look at this site
    Continue this content related web site just click the Up coming internet page please click the
    up coming document city police officers were dispatched to the chief
    of state and administrator of performing
    artist presidency where he should be a systematically
    in a higher place-mediocre field general the NFL. The determiner sounds tedious and too
    low for a experimentation. ‘We had a providential job of qualification changes
    where essential, and he has

Comment pages
1 2 3 4 22
  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
  6. November 30th, 2014 at 19:42 | #6
  7. December 22nd, 2014 at 11:12 | #7
  8. January 4th, 2015 at 20:47 | #8
  9. December 15th, 2015 at 23:41 | #9
  10. February 16th, 2016 at 03:15 | #10
  11. February 17th, 2016 at 07:16 | #11