Two new technologies, the Entity Framework (EF) and ADO.NET Data Services were released with .NET 3.5 SP1 in August 2008. These two major editions to the framework finally provide true data modeling and an easy way to send the data across a WCF service. Making programming the data tier much easier (kind of). Since these technologies are so new there is not a lot of good information out there and a lot of what you can find is a lot of "fluff"... not much "real world" solutions. Since I have been actively been using this where I work since it came out in a "real" project, I thought I would share what I have learned. This will be a living blog post, meaning that as I find things I will update it. So check back often.
One thing I should mention is that the project I am working on is converting VB6 code to C#. We use SQL Server 2005 with most all of the data access going through stored procedures. Most of the sp's I have seen so far are simple selects and inserts or selects based on a parameter. So these are ideal candidates for just letting the EF create the SQL dynamically.
Entity Framework
Design Considerations
When starting this project I chose to create an assembly just for the Entity Data Model's. This way, down the road it could easily be shared with another applications. I chose the naming scheme for the assembly of: MyCompay.Project.Data.Entities.dll (of course replace "MyCompany" and "Project" with your company name and project name). This works great since by default entity type access is public.
There is one caveat with this, when you configure your entities, connections are created in the app.config file that look like this:
<configuration>
<connectionStrings><add name="MyEntities" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MyServer;Initial Catalog=MyApplicationDB;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" /></connectionStrings>
</configuration>
This issue is when you reference this assembly in your WCF project (see more below), you will need to move the connection strings information to the web.config file under the <system.web> section.
Creating Your Entity Data Model
When I first started going though the VB6 project, I searched for sp calls or dynamic sql. As I saw these statements I added the appropriate tables to my data model. This worked great until I found the last table that was actually "root" table for the application. For example a Customer table that has a primary key that most if not all other tables rely on. While the designer correctly created the relationships, they never seemed to work correctly. I kept getting errors. So always add the "root" table first when creating your data model.
ADO.NET Data Services
Design Considerations
With the project I'm working on, there are multiple assemblies that will end up needing to connect to the Data Service to retrieve data. Because of this and the need to pass around the same "proxy" object, I decided to wrap the Data Service proxy in a separate assembly. The naming scheme I used for the assembly is: MyCompay.MyProduct.Data.ServiceProxy.dll (of course replace "MyCompany" and "Project" with your company name and project name). By default the proxy object created to the Data Service is public so it's easily used by any other assembly. Also, configuration is easy since the location of the service is defined in one place in this assemblies app.config file.
LINQ
With the Data Services, LINQ can be used to call the service. This is great for the programmer since there is no need to master REST. Your LINQ statement will be automagically turned into REST. But be forewarned that at this point LINQ does not support all of REST. So you can write your LINQ statement, it will seem correct, but nothing will come back from the service. Most of the time there will be no warning or Exception either. So, if you are not getting data back, look at your LINQ query.
Loading Data
Retrieving data from a data service using LINQ is really simple. In my class I declare:
MyEntities _serviceProxy = new MyEntities();
Client _client;
Then to load, simply do the following LINQ statement:
_client = (from cl in _serviceProxy.Clients where cl.ClientId == this.ClientId select cl).Single<Client>();
This performs a call to the Data Service and returns a single Client object using a REST query simular to this:
http://localhost:4437/Services/MyDataService.svc/Clients('1')
To load child objects simply code the following:
_serviceProxy.LoadProperty(_client, "Accounts");
_serviceProxy.LoadProperty(_client, "Categories");
Each of the LoadProperty methods above will create a call to the Data Service. These calls can be batched to reduce network traffic, but you will basically have to write the REST queries manually... something I have not tackled yet.
Data Joins
Joins are not supported in Data Services because they are not supported in REST. But if you need to join data to display it in a grid etc, you can do it like you would do it in EF. For it to work the data will have to be preloaded into memory from the Data Service. The join would look similar to this:
var data = (from a in _client.Accounts
join ac in _client.Categories on a.CatagoryId equals ac.Id into cat
from c in cat.DefaultIfEmpty()
orderby a.Type, a.Number
select new
{
a.Id,
a.Department,
a.Number,
a.Description,
a.Type,
a.IsTaxable,
a.IsActive,
Code = c == null ? string.Empty : c.Code
}).Distinct();
accountGridView.DataSource = data.ToArray();
In the code above, cat.DefaultIfEmpty() is very important because if there is no match between Account and Category, then no data at all will be returned! Then in the last line of the select, it's also important to check for null, since "c" could be null and will throw an exception if you try to access one of its properties.
Now that you have your data into the grid control (DataGridView) you are going to run into another problem... retrieving the data from the DataSource! Since the data has been loaded as an anonymous type it can't be converted back. Also, since I chose not to show the Id property in the grid, I simply can not look at the first column.
So in my case, I only want the Id value of the row the user clicked on, from there I can use LINQ to look up the Account and what ever else I need to display more detailed data etc. For this we are going to have to use reflection.
private void accountGridView_SelectionChanged(object sender, EventArgs e)
{
var data = accountGridView.Rows[accountGridView.SelectedRows[0].Index].DataBoundItem;
var propInfo = data.GetType().GetProperty("Id");
var propValue = (Int32)propInfo.GetValue(data, null);
LoadAccountDetail((from acc in _client.Accounts where acc.Id == propValue select acc).Single());
}
As you can see in the code above we are using reflection and GetProperty to retrieve the value for Id from the grid DataBoundItem which I then use in the LINQ statement to retrieve the Account object.
Updating Data
One of the great things about the Entity Framework is the built in state tracking. This make is really simple to update, insert and delete object (data) with a single method call. Unfortunately, as soon as you serialize an EF object across the wire using any service including ADO.NET Data Service, this tracking is gone for the most part. Though Microsoft has promised to make this better in upcoming releases, for now we have to do it a little more manually, but still is pretty easy. You just need to remember this when using objects coming from a Data Service.
To update an object, simply change it's properties and then do the following:
_serviceProxy.UpdateObject(currentAccount);
_serviceProxy.SaveChanges();
Calling SaveChanges will make a call to the DataService. You can make as many calls to UpdateObject, AddObject and DeleteObject as needed before the SaveChanges call.
Service Methods
What's Not Supported
- Enum parameters: I wanted to do this to send back different data based on an enum parameter (just like I do in normal programming). This is a perfect job for service methods. Unfortunately, I was told by Microsoft that it's not supported in "this release". They did not indicate if it will be supported in a future release.
"Good" Resources