The new ASP.NET (part 2 of 7): Entity Framework and the database

This is part of a series – see this post for explanation of the context: http://robseder.wordpress.com/2010/12/23/the-new-asp-net-part-1-of-7-getting-started/

—–

OK – so I have an MS Access database that is normal-form proper, and has foreign-key constraints. So, within MS Access, I use the “move data” wizard to get it into SQL (SQL Express, in this case):

image

I verify that everything came over OK and create a diagram – so here is the database schema we’re working with (click to enlarge):

image

Within Visual Studio, I have several projects set up in my solution:

  • SederSoftware.CrmWebSite – the MVC-based website for this project
  • SederSoftware.CrmWebSite.Support – class library project for code, for the website
  • SederSoftware.CrmWebSite.Tests – unit tests for the web site
  • SederSoftware.CrmWebSite.Support.Tests – unit tests for the class library

Entity Frameworks is going to let me interact with this schema in the same natural way that it’s shown above. Instead of dealing with “grids of data” that are in DataTables, EF let’s me interact with data structures – and it takes care of loading/saving/etc. all magically behind-the-scenes.

If you’re not clear on the EF stuff yet, read on – it should get clearer as we go through the example.

So, I want to create a new Entity Frameworks model for this database, so in the “Support” project, I add a new item:

image

As far as acronyms by the way: CRM = Customer Relationship Management and ATFS is an acronym for the company.

When I click Add, I get a wizard:

image

I do want to generate a model. I click Next and I choose the database that I want to connect to:

image

I click Next, and select the tables that I want to include:

image

Now, you have a file in the project called AtsfCrmModel.edmx. If you double-click, you’ll see that we now have an EF model that is modeled after our exact database schema:

image

Now I know – you’re saying “Yeah. That’s great, but what did that just buy me?”. Great question, but to be honest, I don’t appreciate the tone! But seriously, this :30 seconds of work just bought you so, so, so much. In a random class file, I create a method and look what I can do:

    public void EFExample()
    {
        AtsfCrmEntities entities = new AtsfCrmEntities();
 
        IQueryable<t_Customer> customers =
            from customer in entities.t_Customer
            where customer.LastName.Contains("e")
            select customer;
           
        foreach (t_Customer customer in customers)
        {
            Debug.WriteLine(customer.FirstName + " " + customer.LastName);
        }
    }

I create a new instance of my entity model (entities) – which automatically handles loading the data. Then, I construct a Linq query that says “get me all the customers with ‘e’ in their last name” – more on Linq syntax later.

From that query, I can foreach through the rows of data that match that query – and do stuff with all of the fields that are part of those rows! Sure enough, this code works.

I’m assuming you just said “Wow.” – even if you don’t understand the Linq part yet. Think about how you might do this in the olden days: you need to create a connection, create a command, execute the command, take the resultant DataTable and reference the columns by ordinal or name. That is way, way, way more code. This is very succinct, effective, and requires very little code! Very powerful stuff!

What’s even crazier, is the Entity Frameworks supports and fully understands related data. So, what if “for every customer that has an ‘e’ in their last name”, let me print out all the related Job Numbers (recall from the schema that a customer can have many jobs associated):

    public void EFExample()
    {
        AtsfCrmEntities entities = new AtsfCrmEntities();
 
        IQueryable<t_Customer> customers =
            from customer in entities.t_Customer
            where customer.LastName.Contains("e")
            select customer;
 
        foreach (t_Customer customer in customers)
        {
            Debug.WriteLine(customer.FirstName + " " + customer.LastName);
 
            foreach (t_Job job in customer.t_Job)
            {
                Debug.WriteLine("--" + job.JobNumber);
            }
        }
    }

You likely just said “Wow” again. Yes, it’s that easy. This generates something like this in my Output window, in Visual Studio (names changed to protect the innocent!):

Tina & Nathaniel MacDonald
–20060924-MAC1
–20061025-MAC1
–20061030-MAC1
–20061103-MAC1
EDWARD PACEK
–20060929-PAC1
MR. & MRS. STEPHEN LEFFUER
–20061006-LEF1
GARY DOUCELA
–20061012-DOU1
MR. & MRS. BURNETTE
–20061211-BUR1

So even forgetting the Linq syntax for now – hopefully you see that within just a few minutes of work, you now have a entity model that let’s you query and update a database very efficiently with very little work.

This is the database part of this project. Entity Framework handles the object-relational-mapping (ORM) to and from the database, and it also gives Linq and the .NET languages everything it needs to easily query this data too.

Now that we have our database in place, we’ve created an EF model for it, and showed some of the basic capabilities, we need to next dig in to how to use Linq to our advantage. See the next blog post in the series for that…

Posted in .NET 4.0, ASP.NET, ASP.NET MVC, Entity Framework, Linq, Uncategorized
2 comments on “The new ASP.NET (part 2 of 7): Entity Framework and the database
  1. […] Post a comment or leave a trackback: Trackback URL. « Starting back up with TFS The new ASP.NET (part 2 of 7): Entity Framework and the database » LikeBe the first to like this […]

    Like

  2. […] Post a comment or leave a trackback: Trackback URL. « Starting back up with TFS The new ASP.NET (part 2 of 7): Entity Framework and the database » LikeBe the first to like this […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 5 other followers

%d bloggers like this: