Tutorial: An Introduction to the ADO.NET Entity Framework

[mendhak]

This is the VB.NET version of the ADO.NET Entity Framework Tutorial. (C# version on Codeguru.com)

An Introduction to the ADO.NET Entity Framework

Database development with the .NET framework has not changed a lot since its first release. Many of us usually start by designing our database tables and their relationships and then creating classes in our application to emulate them as closely as possible in a set of Business Classes or (false) "Entity" Classes, and then working with them in out ADO.NET code. However, this process has always been an approximation and has involved a lot of groundwork.

This is where the ADO.NET Entity Framework comes in; it allows you to deal with the (true) entities represented in the database in your application code by abstracting the groundwork and maintenance code work away from you. A very crude description of the ADO.NET Entity Framework would be "It allows you to deal with database concepts in your code."

Of course, it's much more than that, but in this tutorial, instead of starting with a lengthy spiel about the Entity Framework and how it differs from LINQ to Sql, you will code first, talk later.


Contents

In this tutorial, you will go over the following:

1. Set up the environment and database, generating the Entity Data Model
2. Basic ADO.NET Entity Framework operations with a form for Payrolls
3. Adding a little efficiency using another form for Authors
4. The case for using stored procedures in the Entity Framework
5. Using stored procedures to perform SELECT operations against the database in the Articles form
6. Using stored procedures for the INSERT, UPDATE, and DELETE operations in the Articles form
7. More information and conclusion

Setting Up Your Environment

For this ADO.NET Entity Framework tutorial, you will need the following:

• SP1 for .NET Framework 3.5/Visual Studio 2008 (which you can download here.)
• Some VB.NET knowledge, because the code samples here are in VB.NET
• A little prior knowledge of ADO.NET and SQL
• Approximately 250ml to 350ml of trimethylxanthine, otherwise associatedly known as coffee

Setting Up the Database

The Sample Project File - SodiumHydroxideVB

You can either create your own project, or refer to the project files linked to above, but I would recommend starting your own and glancing at the attached project files if you need to. Before you start coding, though, you will need to create the database and its objects that will be used and referred to in this tutorial. The DatabaseScript.zip file contains a .sql script that you need to run against your SQL Express or SQL Server database; this script will generate the database for a theoretical publishing company, inventively named PublishingCompany, and the tables and stored procedures required.

Note: You don't need to use SQL Server. You can use any database you'd like, but then you will need to modify the script to work with the SQL implementation for your database. For the purposes of this tutorial, I will continue to refer to SQL Server as the database.

Generating an Entity Data Model in Your Visual Studio Project

Once you are satisfied that the database has been created and you have had a look through all of the tables and its fields, start by creating a new Windows Forms Application project. I suggest the name of the solution to be SodiumHydroxide. I chose this name because I'm hoping that this project will serve as a good base for your learning. (Chemistry pun alert!)

The very first step is to generate your Entity Data Model from the database that you created earlier; this will serve to be at the core of all your ADO.NET Entity Framework operations. To do this, right-click on the project and add a new item. Add an "ADO.NET Entity Data Model" and call it PublisherModel.edmx to correspond to your database.



The Entity Data Model Wizard shows up and you now can use this to query your database and generate the model diagram, as long as you supply it with the right credentials. In the Wizard, click "Generate from Database" and click Next.



Supply it with the right server name, authentication, credentials, and the database name PublishingCompany.



Yes, I do like to name various entities on my home network after arcane Mesoamerican civilizations. Finally, "Save entity connections settings in App.Config as" should be PublishingCompanyEntities.




In the next dialog box, choose all of the options—tables, views, and stored procedures—and the model will be generated for you. You should end up with this:



This is a graphical representation of the Entity Data Model (EDM) that's generated by the wizard. Note that it isn't exactly a table mapping in the database, but it looks close. You'll also see that the Author entity has an article reference and payroll reference, even though you haven't actually created fields in the Author table; this relationship was derived from the foreign key constraint by the EDM generator.

If you are like me, you probably want to know what's happening behind the scenes; you can right-click on the .edmx file in Solution Explorer and choose to view it with an XML Editor. Even if you aren't interested, I would encourage you to look at the XML anyways, because advanced Entity Framework operations will require you to directly edit the XML file, but not for this tutorial. As you can see, the EDM is essentially an XML file that's generated from the database schema, and which is understood by the Visual Studio designer to give you a graphical representation of your database entities.

Next, you will start working on the first form with basic Entity Framework operations.

[mendhak]

Basic ADO.NET Entity Framework Operations

Simplest stuff first; now that you're set up, you can start by creating a form to traverse through all the payrolls. To do this, you'll need a combobox that will enumerate the author names, a label, and a textbox to display the details of that author's payroll if it exists (keep in mind that some authors may not be on the payroll). Start by creating a form like this:



Name it PayrollView and give the controls appropriate names. For now, you'll just populate the combobox with the author names. In the form's code, at the top of the class, add this (instantiate the PublishingCompanyEntities object)

Dim publishContext As New PublishingCompanyEntities
Dim currentPayroll As New Payroll

In the form closing event, always dispose it.

publishContext.Dispose()

This PublishingCompanyEntities object — publishContext — is very important; it serves as the basis of all the ADO.NET Entity queries that you will be using. To watch it at work at its most basic level, populate the combobox with a list of authors. In the form's load event, add this:

'This is a simple ADO.NET Entity Framework query!
authorList.DataSource = publishContext.Author
authorList.DisplayMember = "FirstName"

In the code above, authorList is the name of the combobox. Press F5 and watch the form load. You should see the combobox with the author names in it! Loop through that list for a bit and marvel at your handiwork.



Behind the scenes, when you set the DataSource and DisplayMember of the combobox to the publishContext.Author property, the publishContext performed the query against the database and returned the results for the combobox to use. You didn't have to open a connection or create a command; the housework was taken care of by the publishContext object.

Now, you can populate the textboxes that represent the payroll properties for each author. Handle the combobox's SelectedIndexChanged event. Add this code to the event:

Dim selectedAuthor As Author = CType(authorList.SelectedItem, Author)
Dim selectedAuthorID As Integer = selectedAuthor.AuthorID
 
'Using Linq to Entities
Dim payrollQuery As IQueryable(Of Payroll) = From p In publishContext.Payroll _
                                             Where p.Author.AuthorID = selectedAuthorID _
                                             Select p
 
Dim selectedPayroll As List(Of Payroll) = payrollQuery.ToList()
 
If (Not selectedPayroll Is Nothing AndAlso selectedPayroll.Count > 0) Then
    currentPayroll = selectedPayroll.First()
Else
    currentPayroll = Nothing
End If
 
PopulateFields()

In the code above, you do the following:

1. Get the current Author object from the combobox by looking at the SelectedItem property.
2. Use a LINQ-to-Entities query against publishContext to filter the Payrolls on the AuthorID.
3. The return type for the LINQ-to-Entities query is IQueryable<>, which you convert to a List<>.
4. Check whether it has values and get the first row from the returned results because you only want one author's payroll.
5. Assign this value to the currentPayroll object which then is used in your common PopulateFields method.

The PopulateFields method, shown below, simply reads the properties of the Payroll object and places the value in corresponding labels/textboxes.

Private Sub PopulateFields()
 
    If Not currentPayroll Is Nothing Then
        payrollIDLabel.Text = currentPayroll.PayrollID.ToString()
        salaryUpDown.Value = Convert.ToDecimal(currentPayroll.Salary)
        addButton.Enabled = False
        deleteButton.Enabled = True
        updateButton.Enabled = True
    Else
        payrollIDLabel.Text = "Not on payroll"
        salaryUpDown.Value = 0
        addButton.Enabled = True
        deleteButton.Enabled = False
        updateButton.Enabled = False
    End If
End Sub

Run the application again, and when you select different authors from the combobox, you should get their corresponding salaries. You'll also see that the "Add" button is disabled for authors with payrolls, and enabled for authors without payrolls.

Coding the Update and Delete buttons

Because this is a simple example, the only property that the user can modify is the author's Salary. In the Update event, set the currentPayroll's Salary property to be the value of the numeric up-down control. Then, simply call the SaveChanges method on publishContext. Here is the Update button's click event:

currentPayroll.Salary = Convert.ToInt16(salaryUpDown.Value)
Dim rowsAffected As Integer = publishContext.SaveChanges()
MessageBox.Show(rowsAffected.ToString() + " changes made to the table")

The SaveChanges method is akin to the dataadapter's Update method in regular ADO.NET; it will go through the collection of objects for 'marked' entities, and then update them in the database.

In the Delete button's click event, use the publishContext's DeleteObject method before calling SaveChanges().

publishContext.DeleteObject(currentPayroll)
publishContext.SaveChanges(True)
currentPayroll = Nothing
PopulateFields()

You called the DeleteObject method, passing it the current Payroll object, which marks it for deletion. You then called the SaveChanges method that performs the deletion.

Run the form again. Update a few salaries and try deleting one or two payrolls. Note that this method will delete the payroll associated with an author; it will not delete the author itself.

Coding the Add button

The Add button's click event will require a little more code. You first must create a brand new Payroll object, assign it values, and then call the AddToPayroll() method of the publishContext. The AddTo<EntityName> methods are generated by the Entity Framework based on the entities it generated from the database—all entities have one. It will perform the INSERT against the database and return the PayrollID of the new row in the table.

Dim newPayroll As New Payroll()
Dim selectedAuthor As Author = CType(authorList.SelectedItem, Author)
newPayroll.Author = selectedAuthor
newPayroll.Salary = Convert.ToInt16(salaryUpDown.Value)
 
publishContext.AddToPayroll(newPayroll)
publishContext.SaveChanges()
currentPayroll = newPayroll  'newPayroll.PayrollID now matches the database table!
 
PopulateFields()

Because PopulateFields is called right at the end, you will see that after you add a new Payroll to the database, the PayrollID label has been filled with the new value. Again, the Entity Framework has taken care of the new ID and assigned it to newPayroll.PayrollID for you to use.

Run your form and, if you haven't deleted any authors yet, do so now. Once you delete the author, their ID label will say "Not on payroll" and their salary will be 0. Modify the salary and click "Add". Your form now displays authors and their payrolls, and allows you to add, update, and delete payrolls. Have a play with it and marvel at your handiwork again before you continue.



Next, you will use the same concepts learned here, but with a little more efficiency.

[mendhak]

Adding a Little Efficiency

In the Payroll form, every action performed meant one database call to get the data. For the Authors form, you will use almost all of the same logic as the Payroll form, but take a slightly different approach; we will minimize the number of database calls made by getting all the authors in one call and saving the data changes together. It will feel a little clumsy doing this, but it's only to show you how you can take another approach for basic operations in the Entity Framework.

Create a new form for Authors that looks like this. It has a previous, next, first, last, update, and add new button. The "Clear for new Author" button will simply empty out the controls and "Create New" button will work off it by creating the Author object.



Agreed, not the most intuitive interface, but this is just a tutorial. The "Send to Database" is the only button that will make a database call; the rest will manipulate the Author objects.

As before, create the PublishingCompanyEntities object and instantiate it in the form load event. This time, declare a List<Author> as well; this is what you will use to hold the authors. Also, add an indexing integer to hold your current 'position'.

At the top of the class:

Dim publishContext As New PublishingCompanyEntities
Dim authorList As New List(Of Author)
Dim currentAuthorIndex As Integer = 0

Back in the PayrollView form, you got all the authors using publishContext.Author. This is actually an ADO.NET Entity Query Builder method expression. Query builder methods allow you to use various methods to filter the data you get back. You will read more on these methods later, but for now you should know that the result of most query builder method expression is an ObjectQuery<>, ObjectResult<>, or IQueryable<>. These classes represent the returned entity collections for the queries you perform. You will read more about these later. For the sake of variety, you will use an ObjectQuery<> next. You will get all the authors from the database, except for anyone named Mark, because you don't really care about Mark. For business logic reasons, of course.

Dim authorQuery As ObjectQuery(Of Author) = publishContext.Author.Where("it.FirstName <> 'Mark'")
authorList = authorQuery.ToList()
PopulateFields()

Now you have a list of Author objects that you can manipulate in your form. The PopulateFields method will look slightly different.

Dim currentAuthor As Author = authorList(currentAuthorIndex)
firstName.Text = currentAuthor.FirstName
lastName.Text = currentAuthor.LastName
articleCountLabel.Text = currentAuthor.Article.Count.ToString()
authorIDLabel.Text = currentAuthor.AuthorID.ToString()

You are traversing the List<>, an in-memory object. The previous, next, first, and last buttons are now easy to implement.

Private Sub firstButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles firstButton.Click
    currentAuthorIndex = 0
    PopulateFields()
End Sub
 
Private Sub previousButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousButton.Click
    If currentAuthorIndex = 0 Then
        MessageBox.Show("No previous author")
    Else
        currentAuthorIndex -= 1
        PopulateFields()
    End If
End Sub
 
Private Sub nextButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles nextButton.Click
    If currentAuthorIndex = authorList.Count - 1 Then
        MessageBox.Show("No next author")
    Else
        currentAuthorIndex += 1
        PopulateFields()
    End If
End Sub
 
Private Sub lastButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastButton.Click
    currentAuthorIndex = authorList.Count - 1
    PopulateFields()
End Sub

Run the application (set AuthorView as the startup object) and ensure that the navigation buttons are working.

The Update button is easy too. Get the current author and set its values from the textboxes.

Private Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
    Dim currentAuthor As Author = authorList(currentAuthorIndex)
    currentAuthor.FirstName = firstName.Text
    currentAuthor.LastName = lastName.Text
End Sub

Note that this only modifies an existing author. Nothing has been sent to the database yet. You can do that now; in the click event for the "Send to database" button,

Private Sub sendToDB_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles sendToDB.Click
    Dim rowsAffected As Integer
    rowsAffected = publishContext.SaveChanges(True)
    MessageBox.Show(rowsAffected.ToString() + " changes made")
    PopulateFields()
End Sub

Try it out. Modify an author or authors and see whether your changes make it through to the database. A simple call to publishContext.SaveChanges() works because it still holds a reference to the same objects that were returned from the original ObjectQuery which is in your authorList as well.

Now, try adding a new author. The "Clear for new author" button should clear the fields to make it obvious (or not) that a new author is being created. The "Save New" button should actually create the Author object.

Private Sub clearForNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearForNew.Click
    firstName.Text = String.Empty
    lastName.Text = String.Empty
    authorIDLabel.Text = "Not yet saved"
End Sub
 
Private Sub createNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles createNew.Click
 
    Dim newAuthor As New Author
    newAuthor.FirstName = firstName.Text
    newAuthor.LastName = lastName.Text
    newAuthor.AuthorID = -1 'To make it obvious that it's new
    authorList.Add(newAuthor)
    publishContext.AddToAuthor(newAuthor)
    currentAuthorIndex = authorList.Count - 1
    PopulateFields()
 
End Sub

The key here is to use the AddToAuthor method to add the new Author object to your publishContext 'database'. You also are adding it to the list of authors that you are holding.

Because the list and the publishContext reference the same new Author object, when you add a new author and click "Send to database", you'll notice that the new Author object gets the proper primary key ID instead of the -1 that we placed there. Same principle as before—the new identity is returned and given to the new Author object.

Next, you will look at the efficiency of the SQL generated behind the scenes for your queries.

[mendhak]

The Case for Stored Procedures

The AuthorView form isn't that much different from the PayrollView form, but it does show you that you can deal with the data in different ways, depending on your preference. You did manage to cut down the database calls when navigating through the forms,, which is great. But, the profiler trace shows you queries that aren't exactly satisfactory. This is what I got in the profiler when I updated an author:

exec sp_executesql N'update [dbo].[Author]
set [FirstName] = @0, [LastName] = @1
where ([AuthorID] = @2)
',N'@0 nvarchar(6),@1 nvarchar(7),@2
int',@0=N'Lerroy',@1=N'Jenkins',@2=2

If you've done a lot of enterprise development, you will probably be tearing the last few strands of your remaining hair follicles out right now. Dynamic SQL, that's just not acceptable. In fact, if you were to perform a lot of 'joining' (in a LINQ-to-SQL style) with your objects, you will see some extremely large and convoluted SELECT statements being run against the database in SQL Profiler. If you use an ObjectQuery, you can use its .ToTraceString() method to see the SQL statement that will be used. Go back to the AuthorView form and use a MessageBox to display the ToTraceString of the ObjectQuery:

Dim authorQuery As ObjectQuery(Of Author) = publishContext.Author.Where("it.FirstName <> 'Mark'")
authorList = authorQuery.ToList()
MessageBox.Show(authorQuery.ToTraceString())
PopulateFields()

But the query in this specific example isn't that bad:

SELECT [Extent1].[AuthorID] AS [AuthorID],
[Extent1].[FirstName] AS [FirstName], [Extent1].
[LastName] AS [LastName]FROM [dbo].[Author] AS
[Extent1]WHERE [Extent1].[FirstName] <>
'Mark'

To illustrate this point further (and show you another query method), go back to the AuthorView form and add another label. You will use this label to display the number of articles that this author has written. For this purpose, you will use an ObjectQuery method called Include. What is the Include method? You have seen that query methods will only retrieve what you ask; for instance, in your current query

Dim authorQuery As ObjectQuery(Of Author) = publishContext.Author.Where("it.FirstName <> 'Mark'")

you won't have any information about the author's payroll or articles. You only have information about authors not named Mark. If you look at authorList in a quickwatch window after it has been populated, you will see that the Payroll and Article properties of each Author object have no values. The Include method will therefore load the entities associated with the author object, those that you ask for. Go back to the AuthorView form's load event, and modify the ObjectQuery like this to use the Include method.

Dim authorQuery As ObjectQuery(Of Author) = publishContext.Author.Where("it.FirstName <> 'Mark'").Include("Article")

This means get all the authors, except Mark, and for each author, include their associated Article entities. In the PopulateField() method, you now can read the article count.

articleCountLabel.Text = currentAuthor.Article.Count.ToString()

Run the form and you should see the articles count label change for each author. But, did you notice the SQL trace string?

SELECT
[Project1].[AuthorID] AS [AuthorID],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[C1] AS [C1],
[Project1].[C3] AS [C2],
[Project1].[C2] AS [C3],
[Project1].[ArticleID] AS [ArticleID],
[Project1].[Title] AS [Title],
[Project1].[Body] AS [Body],
[Project1].[AuthorID1] AS [AuthorID1]
FROM ( SELECT
   [Extent1].[AuthorID] AS [AuthorID],
   [Extent1].[FirstName] AS [FirstName],
   [Extent1].[LastName] AS [LastName],
   1 AS [C1],
   [Extent2].[ArticleID] AS [ArticleID],
   [Extent2].[Title] AS [Title],
   [Extent2].[Body] AS [Body],
   [Extent2].[AuthorID] AS [AuthorID1],
   CASE WHEN ([Extent2].[ArticleID] IS NULL)
      THEN CAST(NULL AS int) ELSE 1 END AS [C2],
   CASE WHEN ([Extent2].[ArticleID] IS NULL)
      THEN CAST(NULL AS int) ELSE 1 END AS [C3]
   FROM  [dbo].[Author] AS [Extent1]
   LEFT OUTER JOIN [dbo].[Article] AS [Extent2]
      ON [Extent1].[AuthorID] = [Extent2].[AuthorID]
   WHERE [Extent1].[FirstName] <> 'Mark'
)  AS [Project1]
ORDER BY [Project1].[AuthorID] ASC, [Project1].[C3] ASC

In case you're shaking your head and are about to give in to the temptation of criticizing large, faceless corporations for poor standards, you must keep in mind that in any given system, ease-of-use and the generic-ness is inversely proportional to the efficiency. So, to make it more convenient to query and load objects, there is a slight hit with your SQL statements.

However, this is where the ADO.NET Entity Framework shines through; you can tell it to use your own stored procedures instead of the generated SELECT, UPDATE, DELETE, and INSERT statements. Because stored procedures are generally more efficient than dynamic SQL and because you are given this flexibility with the Entity Framework, this is where the real 'power' of the framework is apparent.

Next, you will map stored procedures with the ADO.NET Entity Framework.

[mendhak]

Using Stored Procedures with the ADO.NET Entity Framework

Any proper enterprise environment will have a DBA or a set of DBAs who guard and watch over their databases in the same way that a mother bear watches over her cub. If you inappropriately step between the DBAs and the DBOs, you will get mauled. This means that even with the ADO.NET Entity Framework, they will still want to retain ownership of the database and the objects in it. The ADO.NET Entity Framework allows you to use most of the application code as you did before, but with stored procedures that you or they may have written in the most 'optimal' way.

It'll take a bit of work to get this set up and running, but the effort pays off in the end. You are going to work on using stored procedures with the Article entities on a new form, first starting with a few tasks that you're already familiar with, and then get on to the good stuff.

1. Creating the SELECT stored procedure(s)
2. Layout of the ArticleView form
3. Importing the stored procedure
4. Using the stored procedure to get entities in our code
5. Another stored procedure, and using it
6. Navigation and update code
7. Ta-daa!

Obviously, the first step is to create the SELECT stored procedure.

CREATE PROCEDURE GetArticle
   @ArticleID INT
AS
BEGIN
   SET NOCOUNT ON;
   SELECT ArticleID, Title, Body, AuthorID
   FROM Article
   WHERE ArticleID = @ArticleID
END
GO

Next, you need to import this newly created stored procedure into your EDM. There are two ways to do this: You can regenerate the EDM and import it via the designer, or you can go directly into the XML and edit it there. You will cover the visual method in this tutorial.

1. Right-click anywhere in the Entity Designer view and click "Update Model from Database".
2. Choose Tables and Stored procedures from the dialog box and click Finish.
3. Next, open the Model Browser tab and search for the newly created stored procedure, GetArticle.
4. Right-click on it and choose "Create Function Import".
5. Set the return type as the Article Entities.

Stored procedures within the EDM are imported as functions, and a function that you import always returns a collection of entities. In future releases, this may change to allow a stored procedure to return a single entity. The result of a stored procedure goes into an ObjectResult<>, similar to how publishContext.Author's return type was ObjectQuery<>.

To see it working, quickly create an ArticleView form. Add a new form to the solution, ArticleView, with these controls.



A little simple for now, but you'll expand it as you go along. In the code,

Top of the class:

Dim publishContext As New PublishingCompanyEntities
Dim articleLIst As List(Of Article)

In the form's load event:

Private Sub ArticleView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim articleQuery As ObjectResult(Of Article) = publishContext.GetArticle(1)
    currentArticle = articleQuery.ToList().First()
    PopulateFields()
End Sub

And your old friend:

Private Sub PopulateFields()
    If (currentArticle.ArticleID > 0) Then
        currentArticle.AuthorReference.Load()
    End If
 
    articleIDLabel.Text = currentArticle.ArticleID.ToString()
    titleText.Text = currentArticle.Title
    bodyText.Text = currentArticle.Body
End Sub

This time, you will notice that you make an explicit call to the GetArticle stored procedure, passing it the ArticleID 1. Run the form and you'll see the first article loaded up. And, it's done using your GetArticle stored procedure. This is good because it means that you can optimize complicated queries if you need to and use stored procedures to help you.

However, in this particular case, when you introduce navigation buttons to the ArticleView form, you'll have to make a new stored procedure call for each button click event (for each ID). Avoid that situation and get all of the Articles in one go instead. Create a GetArticles (plural) stored procedure now.

CREATE PROCEDURE GetArticles
AS
BEGIN
   SET NOCOUNT ON;
   SELECT ArticleID, Title, Body, AuthorID
   FROM Article
END
GO

Import the GetArticles function as shown earlier. You then can use an ObjectResult<Article>, convert it ToList(), and assign it to a List<> object.

Top of the class:

Dim publishContext As New PublishingCompanyEntities
Dim articleLIst As List(Of Article)
Dim currentArticleIndex As Integer

Modify the Form Load event now:

Dim articleQuery As IEnumerable(Of Article) = From ar In publishContext.GetArticles() _
                                              Select ar
articleLIst = articleQuery.ToList()
PopulateFields()

I used a LINQ-to-Entities query instead of a method expression, hoping you would notice the flexibility available to you. You can introduce your filters into the expression and it won't affect the SP call. To illustrate, just as a test:

Dim articleQuery As IEnumerable(Of Article) = From ar In publishContext.GetArticles() _
                                              Where ar.ArticleID > 5 _
                                              Select ar

This will perform a GetArticles SP call and then filter the values returned afterwards. However, you're not interested in filtering it right now, so remove the where clause from the LINQ expression.

Again, there is a PopulateFields method in this form that changes slightly.

Private Sub PopulateFields()
    Dim currentArticle As Article = articleLIst(currentArticleIndex)
    If (currentArticle.ArticleID > 0) Then
        currentArticle.AuthorReference.Load()
    End If
 
    articleIDLabel.Text = currentArticle.ArticleID.ToString()
    titleText.Text = currentArticle.Title
    bodyText.Text = currentArticle.Body
End Sub

Run the form and make sure that the first article still shows.

Now, go back to the form designer and add the navigation buttons. Also, add an "Update" button. a "Clear for new" button. an "Add as new article" button. and a "Delete" button. Same principles as before — you navigate through the List<> for the navigation buttons, update an object's properties in the List<> for the Update button, clear the fields for the "Clear for new" button, and add a new object to the publishContext for "Add as new article".



Based on work done in the past few pages, you must have an idea of what the various buttons will do now, so I'll simply list the code for the buttons here, and then you can get down to the main point of this task—using stored procedures for INSERT, UPDATE, and DELETE.

Private Sub firstButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles firstButton.Click
    currentArticleIndex = 0
    PopulateFields()
End Sub
 
Private Sub previousButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousButton.Click
    If currentArticleIndex > 0 Then
        currentArticleIndex -= 1
        PopulateFields()
    Else
        MessageBox.Show("No more articles to display")
    End If
End Sub
 
Private Sub nextButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles nextButton.Click
    If currentArticleIndex = articleLIst.Count - 1 Then
        MessageBox.Show("No more articles to display")
    Else
        currentArticleIndex += 1
        PopulateFields()
    End If
End Sub
 
Private Sub lastButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastButton.Click
    currentArticleIndex = articleLIst.Count - 1
    PopulateFields()
End Sub
 
Private Sub updateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles updateButton.Click
    
    Dim currentArticle As Article = articleLIst(currentArticleIndex)
    currentArticle.Title = titleText.Text
    currentArticle.Body = bodyText.Text
End Sub
 
Private Sub clearForNewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearForNewButton.Click
    articleIDLabel.Text = "-1"
    titleText.Text = String.Empty
    bodyText.Text = String.Empty
End Sub
 
Private Sub saveAsNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles saveAsNew.Click
 
    Dim newArticle As New Article
    newArticle.Title = titleText.Text
    newArticle.Body = bodyText.Text
    newArticle.ArticleID = -1
    publishContext.AddToArticle(newArticle)
    articleLIst.Add(newArticle)
    currentArticleIndex = articleLIst.Count - 1
    PopulateFields()
 
End Sub
 
Private Sub deleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles deleteButton.Click
 
    Dim currentArticle As Article = articleLIst(currentArticleIndex)
    publishContext.DeleteObject(currentArticle)
    articleLIst.Remove(currentArticle)
    currentArticleIndex = 0
    PopulateFields()
 
End Sub
 
Private Sub submitToDatabase_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitToDatabase.Click
    publishContext.SaveChanges()
    PopulateFields()
End Sub

Note that although the code looks just like it did in the AuthorView form, when you do map your stored procedures, you won't have to change any of the code.

Next, you can (finally!) map the INSERT, UPDATE, and DELETE stored procedures.

[mendhak]

Mapping INSERT, UPDATE, and DELETE to Stored Procedures

Now that the form and basic code has been set up, it would be a good idea to actually have the long-awaited stored procedures ready:

Insert:

CREATE PROCEDURE AddNewArticle
@Title NVARCHAR(50),
@Body NVARCHAR(400),
@AuthorID INT = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Article(Title, Body, AuthorID)
VALUES(@Title, @Body, @AuthorID)
 
SELECT SCOPE_IDENTITY() AS NewArticleID
END
GO

For the INSERT stored procedure, it is imperative that you also get the identity of the row inserted.

Update:

CREATE PROCEDURE UpdateArticle
@ArticleID INT,
@Title NVARCHAR(50),
@Body NVARCHAR(400),
@AuthorID INT
AS
BEGIN
   SET NOCOUNT ON;
UPDATE Article
SET
Title = @Title,
Body = @Body,
AuthorID = @AuthorID
WHERE
ArticleID = @ArticleID
END
GO

Delete:

CREATE PROCEDURE DeleteArticle
@ArticleID INT,
@AuthorID INT = NULL
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Article WHERE ArticleID = @ArticleID
END
GO

Finally, to replace the generated SQL with these stored procedures, open Solution Explorer and double-click on the .edmx file that you generated at the beginning of the tutorial.

Right-click on the Article entity and choose "Stored procedure mapping".



You will now see the Mapping Details window. It is here that you can choose the stored procedures to map.



Select "AddNewArticle" for the Insert function as shown, and it will populate the stored procedure parameters for you along with the corresponding Article class properties as it has derived them. Note the direction of the arrows as well, indicating value 'flow'.

Because you have kept your property names and parameter names the same, it was easy for the EDM designer to match them. However, the "Result Column Bindings" section is empty. This is what the Entity Framework uses to populate the ArticleID property in your new Article objects. You are returning a "NewArticleID" in our AddNewArticle stored procedure, so fill this in here. When you press Enter, it will automatically fill in 'ArticleID' and you'll also notice that the direction of the green arrow in the middle changes, indicating that the result of your SCOPE_IDENTITY() at the end of the stored procedure will be assigned to the ArticleID property in your code. Also, the AuthorID field is empty. Use the dropdown in the Property column for AuthorID to select Author.AuthorID. You should end up with something like this.



For the Update function, select the UpdateArticle stored procedure. It maps all the fields automatically for you; the only change you want to make is to check the "Use Original Value" checkbox for the ArticleID row. You'll also see that no field maps to the AuthorID field. You will need to use the dropdown in the property column to specifically choose Author.AuthorID.



Finally, set the DeleteArticle stored procedure as the function for Delete, also manually setting the AuthorID. You should have a stored procedure mapping section that now looks like this.



The Delete function may appear a little strange to you, because it doesn't make much sense to give it the AuthorID, but you need to pass any foreign key associations to the stored procedure in order to get it to work.

The good news is that you're all set! Build your solution and run it. Attempt adding a new article, updating one, and deleting one. Finally, click the "Submit changes to the database" button.

If you run a profiler trace as you do this, you should see nothing but stored procedure calls.

[mendhak]

The Wordy Bit

The diligent reader may have noticed that we skipped certain things:

1. The AuthorView form has no way of deleting an author. Because the author has associations with other tables, you'd need to ensure that a deleted author deletes everything else. This is now left as an exercise to you. It's simply a matter of creating a DeleteAuthor stored procedure and mapping it to the Author entity. This task will also show you a quirk of the framework—you cannot map the Delete function on its own. You must create stored procedures for Insert, Update, and Delete and map them all together.
2. The ArticleView form has no way of actually associating an author with an article. You may want to change the author of the article or set the author as nobody or just simply look at who the author is. This could be accomplished in three ways.
   
   1. As shown earlier, using a method expression: publishContext.Article.Include("Author");
   2. For each article entity, perform a currentArticle.AuthorReference.Load(). This will perform a SELECT against the database. This, too, is left as an exercise to you.
   3. Have a stored procedure return all the details for the article and the corresponding author and map it to an Article entity and Author entity. This is a topic in the ADO.NET Entity Framework known as Complex Types, which you shall explore in another tutorial.

Now that you've seen the code and gone through basic ADO.NET Entity Framework operations, I can get into the digressive harangue. I can already see your fingers hovering over the Ctrl and F4 buttons, but I would strongly recommend having a quick read through this to get a better understanding of the framework for future work and how you could use it in your application(s).
More Information about the Entity Framework

There are a few topics that you have already touched upon in this tutorial; you will go into more detail for each of these:

1. What is the ADO.NET Entity Framework?
2. How is it different from LINQ to SQL?
3. Querying data with LINQ to Entity expressions and Method Expressions
4. Complex Types and Association Sets

What is the ADO.NET Entity Framework?

The Entity Framework is Microsoft's first Object-Relational Mapping (ORM) framework for the .NET Framework. An ORM framework allows developers to work with a conceptual schema of a database. A conceptual schema is quite simply the logical representation of objects in a database. In other words, it is a representation of a set of concepts (Forum Members, Threads, Posts) and relationships (one to one, many to one).

For instance, in your database, if you have a 'link' table that allows for a many-to-many mapping between two other tables, the 'link' table is not an entity in the EDM. It is simply a relationship, but the two main tables that it is linking may be entities. Further, you may have a table that has been horizontally split for some reason; the two tables together map to a single entity.

When using the framework, you can refer to a Customer entity's Address property (Customer1.Address.PostCode), with the Address actually coming from some other table.

The Entity Framework abstracts the groundwork required to map the application classes to each other by generating and understanding the relationships between each of them (among other things) so that you do not need to perform database-plumbing in your code. You simply work with the objects in a relational model and remain 'unaware' of the underlying database implementation.

How Is It Different from LINQ to SQL?

A commonly asked question.

• LINQ to SQL was created with rapid application development (RAD) in mind. The Entity Framework was created with enterprise development in mind.
• LINQ to SQL works with the objects in a database. The Entity Framework works with the conceptual model of a database. As explained earlier, these are two different things. This further means that the Entity Framework allows you to perform queries against relationships between entities, mapping single entities to multiple tables, and so on.
• LINQ to SQL has the ability to call stored procedures, but not map them the results. The Entity Framework is all about mapping results to entities.
• Guess which one works with something besides SQL Server?

Querying Data with LINQ to Entity expressions and Method Expressions

Throughout the tutorial, you saw a spattering of LINQ to Entities expressions and Query Builder methods or Method Expressions. These are the basic expressions used to query the entities in the EDM. Internally, both of them evaluate to the same thing, so using one over the other is a matter of preference.

For example, this query method:

Dim articleQuery As ObjectQuery(Of Article) = publishContext.Article.Where("it.ArticleID > 5")

can be represented as

Dim articleQuery As IQueryable(Of Article) = From ar in publishContext.Article _
                                                            Where ar.articleId > 5 _
                                                            Select ar

Although LINQ to Entities may be more familiar to you, take a short look at query methods. Query methods allow you to parametrize the queries going through. Taking the above example, you therefore can do this:

Dim articleQuery As ObjectQuery(Of Article) = publishContext.Article.Where("it.ArticleID > @SomeArticleID", new ObjectParameter("SomeArticleID", 5))

Going back to one of the forms you worked on—the PayrollView—you could have modified a query slightly to load the author's first name and last name together using the Select method.

'Uses "ADO.NET Entity SQL Expressions" or "method based queries"
authorList.DataSource = publishContext.Author.Select("it.FirstName + ' ' + it.LastName As FullName, it.AuthorID")
authorList.DisplayMember = "FullName"
authorList.ValueMember = "AuthorID"

Other more commonly used methods are OrderBy, GroupBy, and Where. For more information on these methods, visit the Query Builder Methods page on MSDN.

Complex Types and Association Sets

One of the scenarios described earlier in this tutorial potentially called for the use of a ComplexType. You could have created a CAuthor type, and assigned it as a property of a CArticle type, which would have enabled you to work with them more efficiently for your purposes. Or, to look at it another way, think of a Person entity that has the properties PhoneCountryCode, PhoneAreaCode, PhoneNumber, PhoneExtension. Using a ComplexType, you could group those fields into a single Phone entity. Read more about Complex Type Objects.

Association Sets allow you to use and refer to entities as properties of other entities in the Entity Framework. Because the EDM generated will not always have the association that you want there, you will sometimes need to define your own association sets between entities and also use stored procedures to perform INSERTs or DELETEs across them.

Conclusion

This has been a quick look at the ADO.NET Entity Framework. So far, you have seen how to perform a few basic operations of the ADO.NET Entity Framework.

• LINQ to Entities to retrieve data
• Method expressions to retrieve data
• SaveChanges()
• Stored procedures to retrieve data
• Stored procedures to add, update, and delete data.

Even though there is a lot more to the Entity Framework, what you have learned here can serve as a basis for future application design. A lot of us are (subconsciously) familiar with ORM but have not used it to its full potential, so you may find yourself finding your steps initially as you wrap your head around a slightly different concept of creating data-driven applications.


Hopefully, this tutorial was useful to you in some way. Feel free to post in the VB.NET/Database Development/C# forum if you have any further questions about this.



Keywords
ADO.NET
Entity Framework
EF
mendhak
introduction
tutorial
VB.NET
C#

[maps]

Thanks for tut and the Links.

[estern]

Great article. Coming from vb6 I keep missing the imports that are required to make these things work.

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses

[FastEddie]

Does anybody have this in a printable version? I tried to print it out but I couldn't get the code sections to wrap.

[Nightwalker83]

Does anybody have this in a printable version? I tried to print it out but I couldn't get the code sections to wrap.

Try the archived version of the thread.

[Kenkob]

Hi Mendhak,
I'm new to the forum and new to Entity Framework, therefore hoping to learn from your tutorial. I am using Visial Basic Express 2010 with a standard installation so I only have the Sql Server Compact 3.5. Will there be any way to get round the use of Stored Procedures and or Views if I proceed with the tutorial?

Kenkob