I am using Entity Framework and Linq to EF on top of SQL Server for my data access and storage layers.
One issue that I have run into, which might be a common one, has to do with the generation of database keys.
As is typical, my business object layer contain several object relationships (one to many, many to many). From a performance perspective, the ideal PK would be an auto-incremented integer identify column. However, EF can only return an autogenerated key value when SaveChanges() is called on the context. This means that everytime I wanted to create a relationship in the business layer, I would need to commit the changes to get the generated key, if I want to use this key model. This is not desirable, since I want to work through list objects, for the most part. (Add, update, delete objects through the list and save the list to commit all changes at one time).
So, what I am left with is generating the keys in the business objects. The only reliably unique key value is a GUID and I am using this approach. However, using GUIDs as PKs brings a performance cost, especially if there is a lot of insert activity, because rows are essentially inserted in random locations in the tables and index maintenance brings a cost.
So, I am wondering whether anyone else has encountered this situation and if so, have found a way around this?
Frazer
I suggest that you are thinking about this incorrectly.
Business objects are not data entities. They don't have FK relationships, they just have object relationships. Relationships between objects exist through the references they hold to each other in memory.
In other words, your object model isn't held together by data relationships, it is held together by the references each object holds to other objects. So concrete FK values are simply meaningless in the object world.
When you are dealing with existing data you often need to carry FK values around so you can persist the data later. But even then the FK values in the objects are there as a plumbing detail to make the database happy - they have no value to the objects or object model.
When you are dealing with new data the FK values in the objects can usually be left as default values (often 0) because they have no meaning anyway. Your DAL code will know to do an insert because IsNew is true.
My biases are revealed. :)
After reading Rocky's reply, I went back through my business object code to see if, in fact, I could eliminate (or at least drastically reduce) the use of database keys in the business layer. In some cases, I can see where that is quite possible, but in some instances, it's a bit more difficult. One such area is where I have implemented many-to-many relationships. The approach that I took is not that much different from the Resources model from PTracker. The problem area is with the Assign method - if you do not have some kind of unique identifier for the business object you are Assign-ing then how else do you identify it? From PTracker::Resources:
public void Assign(int resourceId)
{
if (!(Contains(resourceId)))
{
ProjectResource resource = ProjectResource.NewProjectResource(resourceId);
this.Add(resource);
}
else
{
throw new InvalidOperationException("Resource already assigned to project");
}
}
What am I missing?
Frazer
I think that you may be trying to do work that EF can do for you. Maybe I'm missing something, but you can take Rocky's comments (think in object relationships) and apply it to EF too. EF maintains the object-graph relationships just like CSLA objects do, and they're assignable as well. When you do that, EF should take care of propagating the appropriate values for your PK/FK relationships automatically.
In the case of your example, you should already have an appropriate ID for your assignment, as (in this case) the resource has to exist before you assign it to a project. So while your BO doesn't necessarily care about the ID, you can still bring it along for the ride. If you can create projects and resources at the same time, then I think you have a muddled use case.
HTH
- Scott
If you create GUIDs as PKs for new objects you could use a so called COMB GUID to get around the issue of index fragmentation.
A COMB GUID relies on time to increase. http://www.informit.com/articles/article.aspx?p=25862 has detailed information.
Good points, Dan.
I found a little snippet of code to generate Combs in C# and I am going do some benchmarking, then switch them in for NewGuid() and see what kind of difference it makes.
thanks,
Frazer
Copyright (c) Marimer LLC