Somebody asked me to create a starter sample intended just to show the absolute basics of using LINQ, Entity Framework and a SQL Server database, so this is it. I’m using VS 2008 for this demo.
All I’m going to do is to connect to the Northwind database, create an Entity Data Model and then run a query against that model to fetch some data.
Setting up the Data Connection
If you already have this connection set up, you can of course skip this section. Otherwise, here are the steps:
Open the Server Explorer. (if you can’t see it in the IDE, use the Ctrl + Alt + S combination to view it).
Right click on the Data Connections folder.
Select ‘Add Connection’.
In the Add Connection dialog that appears, browse to the location of the Northwind database file and select it.
It’s probably a good idea to hit the Test Connection button too and, assuming you get a positive message, click OK.
The connection will then be visible in the Server Explorer:
Laying out the Windows Form
For this very simple demo, I’ve created the form shown below:
In this initial, not very realistic scenario, the application will load a specific Company Name and the Contact Name for that company from the Suppliers Table. You will then be able to change the Contact Name data on screen and save the change back to the database. I’m going this route in order to keep things as simple as possible. I’ll be heading towards more real world examples later, once we’ve covered the basics.
Setting Up the Entity Data Model (EDM)
In order for this to work, you need to have Visual Studio 2008 SP1 and the .NET Framework Version 3.5 SP1 installed:
There is an earlier alternative, which involved installing the EF Tools but I really wouldn't recommend you trying that. So if you don’t already have the SP installed, this would be a good time and a good reason to bring things up to date.
From the main Visual Studio menu, select Project > Add New Item. You will see ADO.NET Entity Data Model in the display of installed templates. Select that and rename it to ‘NWindModel.edmx’ and then click the Add button.
The EDM Wizard will then invite you to either Generate a model from an existing database or create an empty model. The easy option for us at this stage is to generate from the Northwind database, so click on ‘Generate from database’ and click Next.
In the next screen of the wizard, choose the Northwind data connection that you previously set up:
Once selected, an Entity connection string will be created for you and you will be encouraged to let the system save that connection in App.Config. Ensure that checkbox is checked and then click Next.
The next screen will allow you to select database objects to be included in the EDM. Expand the Tables list and select only the Suppliers table, then click Finish:
The Entity Data Model will then be created for you in the NWindModel.edmx file and, once created, the details will be displayed in the Model Designer window:
The diagram represents the schema of the Entity Data Model. The key thing to notice in that graphic is that the Columns from the Northwind database table are now clearly shown as Properties in the Model. The model is named NWindModel, as you can see from the tab at the top of the screenshot. The class that has been created behind the scenes for you is called NORTHWNDEntities and you can create an instance of this class in order to run run queries against the data model. There are several ways, but personally I prefer to use LINQ. I think it’s a readily understandable, logical and generally readable tool. If you are familiar and comfortable with SQL statements, you can alternatively use Entity SQL for the queries.
The LINQ Query
I’m going to take this one step at a time. If that’s too slow for you, feel free to jump to the end of this section where you will see the finished LINQ query.
1. Create a new instance of the NORTHWNDEntities class in the Load button click event:
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Using NWcontext As New NORTHWNDEntities
End Using
End Sub
If you’re not familiar with the Using statement, it’s designed to allow you to create a resource, but for it to be automatically disposed of for you at the end of its lifetime.
2. The first part of the LINQ query creates a variable named companyInfo which is able to query into the Suppliers property of the entity instance:
Using NWcontext As New NORTHWNDEntities
Dim companyInfo = From s In NWcontext.Suppliers
End Using
In this case, 'Suppliers' is an Entity Set which contains many Entities - in this case one for each Supplier currently stored in the Northwind database.
3. Because I want to select only a specific supplier from the available data, I will use a Where clause to clarify exactly what I want. (Note the addition of the underscore at the end of the first line of the LINQ query):
Dim companyInfo = From s In NWcontext.Suppliers _
Where s.SupplierID = 23
In the above example I only want to pull out the info on the Supplier with the SupplierID of 23.
4. I can then select all or part of the data in the Suppliers table* according to my needs. In the following example I choose only the CompanyName and ContactName: (Again I first added a continuation underscore to the preceding line of code.)
Dim companyInfo = From s In NWcontext.Suppliers _
Where s.SupplierID = 23 _
Select s.CompanyName, s.ContactName
Because I am only interested in the CompanyName and the ContactName, those are the two items I have pulled and stored in the companyInfo variable.
* Note: In fact, the query is being run against the Suppliers property in the entity instance, not directly against the database. This property is mapped back to the data in the underlying table, sparing you the task of accessing it directly. In more complex queries, this can make the query tasks much easier.
5. When the LINQ query runs, at this point the CompanyName and ContactName are stored in companyInfo. companyInfo is an IQueryable (of Anonymous Type). Essentially this means that there is no substantive type – such as String, List, etc – in use here. An anonymous temporary type is created just for this query. There are several other ways of creating and manipulating anonymous types in these kinds of queries and this is a relatively basic example.
companyInfo now holds the detail of the CompanyName and the ContactName of the Supplier whose ID is 23.
In order to extract that information and display it in textboxes, I will use the ‘First’ function. This looks into companyInfo and finds the first CompanyName and the first ContactName. Of course, in our simple example, there is only one of each, but we still need to use this function to get at them. In a more realistic data extraction sample, you would expect to have several Company Names, Contact Names or other items stored in companyInfo. There are other ways of enumerating through sets of data in that case.
Dim companyInfo = From s In NWcontext.Suppliers _
Where s.SupplierID = 23 _
Select s.CompanyName, s.ContactName
txtCompany.Text = companyInfo.First.CompanyName
txtContact.Text = companyInfo.First.ContactName
6. When the Load button is clicked, the display will change as shown below:
If you are interested in learning about the Entity Framework and using LINQ queries, you can tweak this first example to access other data. In my experience, reading and following examples helps a little, but the only real way to get to grips with this is to try experimenting and then work out ways of getting past those error messages that you are certain to hit before long.
As promised, the complete code for the LINQ query is:
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Using NWcontext As New NORTHWNDEntities
Dim companyInfo = From s In NWcontext.Suppliers _
Where s.SupplierID = 23 _
Select s.CompanyName, s.ContactName
txtCompany.Text = companyInfo.First.CompanyName
txtContact.Text = companyInfo.First.ContactName
End Using
End Sub
Saving Amended Data back to the Database
This is very easy to do in the Entity Framework, mainly because the creation of the code required (e.g. the Update Command in this case and any associated plumbing) is all done for you invisibly. To demonstrate this in action, I’ve put the following code in the click event of the Update button:
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Using nwUpdater As New NORTHWNDEntities
Dim getContact = From sup In nwUpdater.Suppliers _
Where sup.SupplierID = 23 _
Select sup
End Using
End Sub
This first section of code is very similar to that used to load and display the data initially. I’ve changed the name of the Entity instance and the IQueryable variable to indicate their roles.
I’ve also amended the Select statement so that it pulls all the information about the Supplier we are interested in. It isn’t strictly necessary in this example, although in a working app you may well want to amend more than one item of data – Supplier Address, City, Country, etc . As the query pulls in only a tiny amount of data, it isn’t going to affect performance in any visible way.
All you have to do is take the amended ContactName from the textbox and assign it to the first (and only) element of the getContact variable. The final step is invoke the SaveChanges method of nwUpdater:-
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Using nwUpdater As New NORTHWNDEntities
Dim getContact = From sup In nwUpdater.Suppliers _
Where sup.SupplierID = 23 _
Select sup
getContact.First.ContactName = txtContact.Text
nwUpdater.SaveChanges()
End Using
End Sub
At this level of query and data manipulation, it really is that easy. The SaveChanges method scans each entity to see if it has changed in any way and in this case will find the change to the ContactName:
It will then persist this change back to the underlying Northwind database. If you try this code, make an amendment, click the Update button and then close the application. Then restart the application, click the Load Data button and you will see that your amendment has been saved.
As a first example of using LINQ and Entity Framework, this is as good as any. Of course, your requirements can become a lot more complex than this and in many cases the syntax needed to achieve your aims also will then become more complicated. Overall though, I am finding the LINQ to Entities approach a much easier path than some of the approaches on offer.
