Composite keys in LightSpeed 3
Tagged as LightSpeedAs part of our programme to improve LightSpeed’s compatibility with legacy databases, we’ve added composite key support as part of version 3. However, our implementation of composite keys is a little different from the way that LINQ to SQL or Entity Framework do it, so I thought it was worth saying a little more about the way it works in LightSpeed.
Representing a composite key
In LightSpeed, every entity has an Id. That’s “an” Id, not “one or more.” So if you want to represent a composite key, you have to represent it as a single value, even though it may have several parts. The way to do this in .NET is to create a value type — think of the Point structure which includes X and Y properties — which you do in C# by declaring a struct and in Visual Basic by declaring a Structure. As the composite key is, well, a key, the value type has to be immutable.
Let’s look at an example. Suppose we’re modelling products that are built for international markets. Our database contains a Product table, but it has a composite key consisting of an integer ProductTypeId and a string CultureId. For example, if widgets are product type 1, then widgets destined for the French Canadian market would have a ProductTypeId of 1 and a CultureId of fr-CA. To model this in LightSpeed, we’ll need a value type representing these two elements:
public struct ProductId { private readonly int _productTypeId; private readonly string _cultureId; public ProductId(int productTypeId, string cultureId) { _productTypeId = productTypeId; _cultureId = cultureId; } public int ProductTypeId { get { return _productTypeId; } } public string CultureId { get { return _cultureId; } } }
Now we just use this value type as the Id type of our entity:
public class Product : Entity<ProductId> { // ... }
Rounding out the ID type
If you build the above, you’ll notice a few compiler warnings. C# wants you to implement the Equals and GetHashCode methods for the ProductId value type. This isn’t 100% necessary, but it’s good style and improves efficiency. The canonical implementations for a value type generally follow a pattern like the following:
public struct ProductId { public override bool Equals(object obj) { if (obj is ProductId) { ProductId other = (ProductId)obj; return other.ProductTypeId == ProductTypeId && StringUtils.EqualsIgnoreCase(other.CultureId, CultureId); } else { return false; } } public override int GetHashCode() { return 29 * ProductTypeId.GetHashCode() + (CultureId == null ? 0 : CultureId.GetHashCode()); } }
It’s also a good idea to override ToString(), so that when you’re debugging and you’re trying to identify the entity in a DataTip or the Watch window, you’ll see a readable ID value like {2/fr-CA} rather than just the type name.
Assigning composite IDs
One of the tricky bits to working with composite keys is that LightSpeed likes to be in charge of assigning IDs. But in a composite key scenario, that’s not possible. Composite keys typically have business meaning: in our case the ProductTypeId refers to a product type which presumably exists elsewhere, and the CultureId is also meaningful. Even if this weren’t the case, LightSpeed doesn’t have an IdentityMethod that could assign IDs of your custom type.
To get around this, you have to override the Entity.GeneratedId() method on your entity type, and have that return an instance of the composite key type. The typical pattern is to store a “pending ID” in a transient field and return that when LightSpeed decides it’s time to assign an ID. Here’s an example:
public class Product : Entity<ProductId> { [Transient] private ProductId _pendingId; protected override object GeneratedId() { return _pendingId; } }
It’s very important that the holding field be transient! Otherwise LightSpeed will try to store it in a PendingId, which will cause errors firstly because the ProductId value can’t be translated to a single database value and secondly because the Product table doesn’t have a PendingId column!
Finally, you’ll need to provide a way for the code that creates Products to specify the ID of a newly created Product. You can do this via a constructor overload, but if you do this, don’t forget that you must also provide a default constructor for LightSpeed to use when materialising Products from the database. Alternatively, you can use a factory method to distinguish the specific case of creating a new Product record from the construction of an in-memory Product instance:
public class Product : Entity<ProductId> { public static Product CreateNew(int productTypeId, string culture) { Product product = new Product(); product._pendingId = new ProductId(productTypeId, cultureId); return product; } }
Note you only need to use the ID providing method when creating a Product for insertion. At all other times, LightSpeed creates Product instances for you from the database records, and sets up their IDs automatically. You might want to deprecate the default constructor so that people get compiler warnings if they try to create Product instances themselves instead of using the overloaded constructor or the factory method.
We’re done
This is sufficient to define a composite keyed entity in LightSpeed. We’ll be putting out a couple more posts over the holiday period showing how to define queries and associations involving the composite key. Because, gosh darn it, if there’s anything more festive than a composite key, then I sure as heck don’t want to know about it.
Posted by Ivan Towlson on 22 December 2009 
9 Responses to “Composite keys in LightSpeed 3”
Leave a Reply
Categories
BrainDump (1)
Community Code (4)
Events (16)
F# (14)
General (53)
Lab Samples (2)
LightSpeed (268)
MegaPack (8)
News (71)
NHibernate Designer (26)
Nightly news (53)
Phone Elements (24)
Products (87)
Projects (5)
Screencast (6)
SharePoint (3)
Silverlight (14)
Silverlight Elements (66)
SimpleDB Management Tools (20)
Visual Studio (9)
VS File Explorer (7)
Web Workbench (39)
WPF (44)
WPF Diagrams (57)
WPF Elements (110)
WPF Property Grid (32)
And there are still a few more days to the holidays! :)
How such keys can be used in LINQ queries? I.e. is it possible to reference a part of such key in query?
Can I compose a key from other keys? Can subparts be composite keys? Can I declare e.g. a type having two references (properties of Entity type), when values of both these properties form a primary key (typical m-n intermediate entity)?
P.S. I’m key developer of competing framework tracking your blog :) The questions are asked because it’s not fully clear if it’s possible to implement mentioned features using such an approach.
[...] a previous episode, we saw how to define and create entities with composite keys in LightSpeed 3. It’s just [...]
Alex: I’ve posted some info about using CK “parts” in queries in a subsequent post. This should also work in LINQ.
I haven’t tested nesting composite key types and I doubt it would work: CKs should be simple structs. I don’t see much value to this functionality because the CK is modelling a set of database columns and database columns don’t nest.
Entity references can never form part of a composite key because keys are always scalar; you would use the entity IDs instead.
Concerning nesting: very simple example is intermediate table for many-to-many relationship. It references both left and right part of relationship (entities), but primary key there is combination of both these parts.
But last sentence in your reply explains that likely, there is no “duality” for references & keys in Lightspeed – i.e. it does not infer the type of column(s) to map entity reference to by the type of this reference. Since this is a design contract, likely, there is nothing to worry about. My original question has appeared just because I think in terms of different design…
[...] previous articles, we’ve seen how to create and query for entities with composite keys in LightSpeed [...]
So this is great for showing code… now how do I do this in the designer? I can’t just change code behind the designer, because it ignores what I have done…
Hello Matthew,
I know you’ve now raised this in the forums and have already seen the answer, but just to post the link here in case anybody else reading this post has the same question: http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=3197&PostID=10555
[...] have a composite key consisting of the EmployeeId and ProjectId. We’ve previously discussed how to define such a composite key in code. You can also create it in the designer by just dragging the table onto the design [...]