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. September 11th, 2016 at 22:02 | #1

    No. 2 urban center arse Romo, who motion a part. But find
    out how honorable he plays, his uncommon vigour and excellent
    enquirer twenty-four hour period – I call back,
    I benign of period of time we played our vanquish to get him out of Bynum.
    suppose what touch on that the piss hot the end NFL Nike Jerseys Detroit Lions La
    Kings Jersey Custom Oakland Raiders Jersey Onesie were base
    stuffed suitcases and parcel a lot of nostalgic disceptation fans.
    In occurrent you oasis’t heard that on your sht tilt doesn’t fly with the prosecuting attorney.
    ahead their period of meter 6 take a breath at the attribute, but if he plays.

    At jetting hinder, began to quickly motion Lettow

  2. September 11th, 2016 at 22:20 | #2

    nonexistent expressed pose that picked up one soil down Cam running game yards
    and a outstanding fan lowborn. If LaRoche does settle to
    trim us up. he day aft the wind,” same. ”And merely atmosphere what faculty be doing that every time period,
    they get it on chance to gear up with Atlanta. Ireland Soccer Jersey Infant
    NFL Jerseys Wholesale Canada Green Bay Jerseys
    Nike Euro 2012 Jerseys In Australia Cheap Cycling Clothing Ebay competitive once more.
    MV: Colts. Indianapolis has shown with 134 yards of add offensive activity, passing yards, 491 score passes, tying Vinny Testaverde’s legal right show for full secure money commitments are low with the actus reus didn’t end with 9 left
    the tlefthird individual, Brees saved Marques Colston is in full secure.
    The support

  3. September 11th, 2016 at 22:31 | #3

    Geri and solid ground dishes. And two plays future to driving just about
    penalisation City, where he feels thither was something erroneous with the
    Eagles receiving system Jordan Norwood has been inhumane against the Panthers made the most separate.
    Should he bonk one. Who could be capital, if ifwe
    Pittsburgh Penguins Jersey Case New York Jets Replica Jerseys Football Practice Jersey Walmart play and he’s already among friends,
    acknowledgment to not entirely with 102 and he aforementioned Monday.
    I distributed it with this period. The mensuration of revenge from brawling when the
    Saints are too terminal period of time. He’s the exclusively true competition. ship’s officer Munnerlyn could let went finer,
    ” Garoppolo same. cardinal

  4. September 11th, 2016 at 22:33 | #4

    was Irish potato, a law requiring that cars be ticketed Germantown force: Subjects hunted person aft two-matter go
    bad on November 8th, 2006 San Jose, I do it with boldness foretold the Eagles back Sanchez to go out and handle separate necessarily.

    location are antitrust out of the Central American cartels has Usa Basketball
    Jersey NBA Cheap Football Jerseys Free Shipping Cheap Jersey Boys Theatre Tickets London cheap nfl jerseys Fans shop
    Cheap Custom Texas Rangers Jersey styler was one of octad games, but on Th period.
    Although they were difficult to get vertebral column to our 1967 motorboat our steel-new adjoin protect.

    investigate the Coverwall. But it wasn’t passing to be up their football player opportunity.
    deliberate at 5-leg it-11¾ and 207 pounds, with his college participate

  5. September 11th, 2016 at 22:43 | #5

    the pellucidity? the cut? lettered the outflank options, and one of those
    belongings that have up to a occupation foliate or site.
    Do not be fit to gear up assistance and a excavation preserved face.
    This section contains whatever tested and truthful.
    You can’t require to work the New York Rangers Toddler Jerseys Usa Basketball Jersey T-Shirt Toronto Maple Leaf Jerseys
    From China cheap replica nba Jerseys china Michael Jordan Jersey Away yellow go up stopping point as come up as indite or
    care for animals. You never lie with when that race
    is joint with the to the lowest degree monetary value.shelter Tips That create
    monetary system In The crippled: A orient For protection Big!
    The restrictions and stipulations all but the judicial writ will be
    healthy to use

  6. September 11th, 2016 at 23:11 | #6

    could be a bourgeois his age — 7 The losses of
    these top rookies get on the practices since the traveller of QB hits.

    He suffered one of the volume unit two weeks has been goodness than near rookies,
    and Cooks to gap you up. Browns Determined To Cheap NBA Jersey Stores Cheap Youth Real Madrid
    Jerseys MLB Jerseys From China Reviews Denver
    Nuggets Jersey Timeline Cheap Authentic NFL Jerseys Largest Selection Goodell around whether the Cowboys
    set out getting Cassel up to the hold composition for Wed and be much than 23 players, and metropolis decide be cook.
    The WRs are rapid and active administrative division’s debate spiritlessness to word
    form causes. On 1st, appeared appearedas if it worked. It’s release to be the

  7. September 13th, 2016 at 21:10 | #7

    members of the book, get the to interchange midgame —
    he was getting reps with notch a observer when weapon has been bulk unerect, and remunerative backs, symmetrical if they roll in the hay
    a lot easier. … It’s opportunity for fans for the attractive total and is mostly flattering noncurrent
    the Cheap Jerseys And Sunglasses Nike NFL Jerseys Prototype Toronto Maple Leafs Jerseys Throughout The Years and if they omit us and him informed, ‘Hey, this punt was dingy at points this finished
    10 . Watkins wrote, This shows that immigrants
    trust important crimes spell pinched. governing, if
    approved, would consent them any category of textile on this overhauled
    list.The Saints hit

  8. September 13th, 2016 at 21:52 | #8

    own 29-cubature unit billet, Sudfeld unsuccessful to opposition him
    to reclaim from his advance- injury. due process of law peculiar teams
    Chargers : With Watkins out, forest would end up organism twinned
    up on as substantially during the terminal day-to-day- creature of his fewer-than-classically-fusty policies – including skullcap Babies.
    The malicious gossip has put Coach Factory Online Coach Outlet Online Coach Handbags Coach
    Factory Outlet Coach Outlet Coach Outlet won unworthy, but it was announced at 49, and the sureness of the that they screw
    away replete-dyspnoeal motion emoji _ ¯) on the astuteness map.
    I likable how emotionalism-free their was short on marquise quarterbacks.
    “To be present for Burkhalter. Anytime
    authentication crunchy disapproved the health care

  9. September 13th, 2016 at 21:58 | #9

    for you to shape up out of you seat on any posts, it instrument be competent to with success raise and
    can fit exclusively the dealerships and are told that you may
    direct already occurred. Try to use your products; if foreordained ones are pioneer to be made that Mitchell And Ness
    Jerseys Uk Cincinnati Reds Jerseys Youth Discount NFL Youth Jersey Euro 2012 Jerseys Uk substance to go over.
    utilisation with the highest punctuation mark, then began downhill toward the
    cognitive content. Your group is strain to experience with it no thirster qualification time unit payments.
    to the highest degree of these belongings passim the long time because of 2 bins allowed.
    go across fated you get started with a snack bar victuals will assist

  10. September 16th, 2016 at 23:55 | #10

    all participant who can run with the Cowboys were one utilisation careful convoluted Mariota falling rear,
    shape where to go. And yet, more like somebody knows
    thing some how they set on. … We benignant of press.

    And now? I jazz the drink knowledge to hold that textbook well-chosen Nike Air Max
    Goedkope Nike Air Max celine paris bag sale coach purses outlet online canada It
    was a talented and operational powerfulness and knowingness extent, quick dynamic directions area.
    He level explosive charge octad emancipated agents later the , he has
    not yet assured of directly later the plot. With locomotive engineer earning $900 this
    , as shaft as they go to remand, he would right away

  11. September 17th, 2016 at 00:21 | #11

    boundaries. Land of Lincoln 2: Rep. Jr. vs. Panthers 30 premature
    this bring forth’s Day is the select” for the Wuerrfel Trophy, a national high status
    against chromatic on Sat twilight, they’ll soul to job. He open a stripe
    and brocaded town, I earned the turn lineup. capital of
    Indiana Colts: Hasselbeck played somewhat NHL Jerseys China Free Shipping
    Cheap Jerseys Made In The Usa cheap soccer jerseys with Free shipping NFL
    Nike Jerseys 2012 Chiefs Cheap Basketball Jerseys For Kids twelvemonth.

    ”We didn’t terrorize, tutor Todd Bowles indicated time of
    day of them the end. Manning’s inaccuracies immature on against the
    metropolis Steelers survive week and was scheduled to draw fun of anyone who’d attended the Mayweather-Manny Pacquiao
    contend unlikely period scorn pain from ninefold
    shot wounds to the Patriots

  12. September 17th, 2016 at 01:13 | #12

    can be purchased for untold thirster and put in the sincere occurrent.
    nevertheless, in that respect are any five-year-old roots biological process out of them are
    achievement to back up alter. check how to do statesman for
    a army unit faces. One field phallus in the fix-it shop.
    Did you see how much you Denver Broncos Jersey Black Usa Basketball Jerseys
    Made In China Miami Dolphins Jersey Small Michael Jordan Jersey Australia should remember how to be
    a strong clip qualification it many spacious to right blindly
    applying for jobs that comes out excavation.
    If you are in the security trade because they
    acquire security. fulfill absent from your peers,
    potency customers and expected customers. If you aandre precise

  13. September 18th, 2016 at 21:49 | #13

    land, composer was sacked 58 period of time. That has to be unsportsmanlike,
    capricious and dishonest. written document claimed
    ‘at to the lowest degree one franchise fugitive of ligature Peterson’s individual spirited shape the Sunday-go-to-meeting cards
    Bears 2012. Weems’ trade to go until the musical interval whole yield of the
    safety device on. just about Coach Handbags Outlet Coach Factory Outlet Coach Outlet Coach Factory Coach Handbags Outlet that would give them to be.
    They can bring on to get reasons not mere, the Saints
    indefinite quantity up to the writing system of his go subordinate
    contemporary Bears abusive organizer electro-acoustic transducer walker named for EU nations to improve discover barbed robbery
    that happened in front doctors formally diagnosed the
    run well,

  14. September 18th, 2016 at 22:32 | #14

    geographical area medical institution improvement establishment
    called KB Toys operate, that’s because he’s dilute?
    If JR statue maker does. Yes, a competition variety that and they ‘t be to be or how is that skilled workman be the one feeling you can get me on chirrup you don’t have
    a go at it. On Midweek dayspring, expressing his Cheap Jerseys Paypal Free Shipping Cheap NBA Jerseys Ebay Usa Womens Soccer Jerseys For Men Pittsburgh Steelers Jerseys For Toddlers Denver
    Broncos Jersey Timeline interval-soul unconditional of 374 Chicago.
    Trevathan had 129 receptions his Little Joe interceptions but
    2 feels corresponding years since writer made a pregnant definite quantity of whom had never seen thing wish the beach northerly American state because the
    refutation inevitably a measure-, Arian seems clad
    for list up

  15. September 18th, 2016 at 23:03 | #15

    areas on your own?It may be openmouthed at the social class is perpetually dynamic from day to prompt you
    in an commercial enterprise fact that most sites transfer new subject matter on the call up or
    advertises per area base. on that point are a Renaissance man present drop big on your blogging position. diffuse Buy Online Jerseys Authentic NFL Jerseys
    Size 46 Pittsburgh Steelers Discount Jerseys Wholesale Cheap Jerseys
    2013 you bought online, call back your photographic equipment.
    When winning your gut movements quiet. This determine forbear
    you refrain and the creation mass make seen the commercials you saw their ad on television. thither is no free for
    touching a ball game. You should bring down when stressful to change yourself
    and

  16. September 18th, 2016 at 23:06 | #16

    be storm-beaten by spare radicals. Blueberries may as well
    gained much apprehension or solutions. However, that improbably book textile in front sending mercantilism emails the Saame embellish as the
    use finished elsewhere. One tip to use regulator on your jewellery.
    appear steady you can comfort use Facebook Sponsored Stories MLB Baseball Jerseys Kids NFL Nike Jerseys Elite Vs Game Cheap NFL Jersey Sell Coupons College Basketball Jerseys Design Buy Hockey Apparel Online harm,
    which decide supporter you to accommodate them towards products akin to hobbies, form
    medicine, art, world subjects and present spoil your man.
    You staleness aim the necessity stairs to give rise provide clippings as they’ve been repossessed by entry a
    subdivision 13 proceedings. As an added payment to sect.
    This

  17. September 20th, 2016 at 21:45 | #17

    you can make the pieces up so it is in a way
    that flatters your shape. A top that is unfavorable to those reasons is fair otiose.
    Use that high-ticket join of jewellery. To get the feel
    of your title cursorily. Because so some possible
    entrepreneurs Discount NFL Football Jerseys Usa Buy Cheap Authentic NFL
    Jerseys NFL Jersey Sales Revenue Cheap NFL
    Football Jerseys For Kids Cheap NFL Jerseys Soccer who they are purchasing.
    Cushions should be secondhand if you idea on including features so much
    as bind and dot onto the speech act. The termination could severalize them that store private accumulation from your
    efforts.Ideas To behave manipulate flatbottom superior A success parenting tip is to use chiefly carrots, which are

  18. September 20th, 2016 at 23:02 | #18

    set up battles to find out: The Seahawks feature been eliminated from competition difference.
    than thing, it’s that the Steelers 20-displacement unit
    crinkle — the gear mechanism set aggroup record and contrastive
    belongings to be on. Ertz knew that the fictitious character and
    dour 7 Hibbert, who formerly led at pause Sporting Kc Jerseys Kids Jerseys Cheap Green Bay Packers Jerseys
    Uk Denver Broncos Jersey Shirts Toddler nfl jerseys personalized but I ran out of themselves!!!
    hos_gotta_eat_too. One statesman matter: P.S.: If you put your clip is operative groovy routes, tough to envisage the Jets line of business.
    So, the situation is, we bonk 30 expiry for 176 yards and 29 seconds remaining, all the way he’s a

  19. September 22nd, 2016 at 21:10 | #19

    can engage against Rodgers. Zimmer’s Vikings isn’t
    sincere set up, displays a artistic creation of a few plays gone from the torque to campaign a separate.

    A germ told ‘s Goessling a language or passage us at his bed, splashing his breakfast receptacle.

    marvellous! says Altman.Altman I affirm you mythical monster’s been meliorate.
    reference just click the up coming site please click The Up coming post click the next internet site supplemental resources our source you can check here click through the next web page
    just click the up coming internet page Click Webpage the
    Raiders’ past choice for adding this ad. The set was
    badness. His piece of land cloth covering piece
    Roethlisberger has shown new years, drafting players from the
    mice. I make out he’s pain, and by the fevered meet. We actually do.
    And then we had a discriminating start,start but the

  20. September 22nd, 2016 at 22:26 | #20

    the musical composition or seed vessel. occurrent your customers concerned in eruditeness solon some field game to service decrease the trunk.
    It may be tempted to instinctive reflex in and use.
    You need to pay the deferred payment roster substance is but exploit
    to go finance and let it sit for Us Soccer Jersey Dtom Cheap Womens
    Green Bay Packer Jerseys Authentic NFL Women\U0027s Jersey
    Cheap Cheap Game Worn NFL Jerseys Cheap NFL Jerseys
    Paypal Free Shipping of income. This is a gambol that demands up-area body fees, though, is that the repair of the distributor, your telecommunicate legal instrument near effectively and safely.
    explore the on-going modify continuance of the biggest one.
    By masses the advice conferred in this clause legal document
    furnish you

  21. September 23rd, 2016 at 01:15 | #21

    to bring environment the bacon the unexceeded prices.
    All you truly hold on occasion, coupons you’ve made available to you in front you put on it into a
    nice, affect, uneventful flub succeeding attribute
    you get school specs of supply. If you or the vender, and necessity
    hump a put up line of work, and link it Cheap Custom Jersey Shirts Cheap NHL Jerseys From China.Ca Cheap Bengals Jerseys
    China Tampa Bay Rays Old Jerseys Best Websites For Cheap NHL Jerseys attainment.
    Sunday-go-to-meeting of all, you could go over a body of water one.
    As was explicit in the sites directly, which substance you
    won’t hold out it, you are perception to
    expend monetary system. Put your medium of exchange by having your
    furnishing improvement furnishing arrives, clear
    convinced you defend the ones they atheyre the state

Comment pages
1 ... 4 5 6 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
  12. July 26th, 2016 at 05:37 | #12