There are several articles about how to do this, but none of them seem to reflect the kind of situations we encounter in the real world. They all seem to revolve around the same tables in the Adventure Works database, and they give you glimpses of "What" rather than "How."  Oh, and they all seem to be cases where the database schema actually matches your business objects.

I'm dealing with a fairly simple scenario, but at least it's more convoluted than the "typical" examples I've run across.

Two tables:

CorporateEntity

• CorporateEntityId (a guid...and the primary key)
• CorporateEntityTypeId (bigint)
• CreatedDate (a timestamp)

• CorporateEntityId (the guid link back to CorporateEntity)
• SecondaryEntityId (actually an enum to indicate its type)
• SecondaryEntityValue (whatever this "Secondary Entity"...does, for lack of a better term)

There are some more columns and tables involved, but there you have it.  The columns I mention for CorporateSecondaryEntity form the multiple-part primary key.

Clear, straightforward, and obvious, right? Don't blame me. I just inherited it and get to try to maintain the beast. Since its original design, we've come up with an alternative way to think about the data which (in my opinio) gets the point across much better.

I deciding to go with that naming convention when I started to write the maintenance apps. We also decided it was a good excuse to start actually using Linq to SQL.

Here's what I wound up with (more or less...it probably won't even compile. But this is the gist)

// It seems like everyone neglects to mention what namespaces things are in
using System.Data.Linq
using System.Data.Linq.Mapping;

[Table(Name="CorporateEntity")]
public class DictionaryItemModel{
        private EntitySet definitions;  // many definitions can be assigned to each item

	public DictionaryItemModel()
	{
		definitions = default(EntitySet);
	}

	[Column(Name="CorproateEntityId", IsPrimaryKey=true)]
	{
		public Guid DictionaryItemIdentifier { get; set; }
	}

	// (This is the column that gave me such fits in my last post)
	[Column(Name="CorporateEntityTypeId")]
	public long ItemTypeId
	{ get; set; }

	[Column(Name="CreatedDate")]
	public DateTime CreatedDate { get; set; }

	[Association(OtherKey="DefinitionItemIdentifier")]
	public EntitySet DefinitionModels
	{ 
		get
		{
			return definitions;
		}
		set
		{
			definitions = value;
		}
	}
}
	
[Table(Name="CorporateSecondaryEntity")]
public class ItemDefinitionModel
{
	private EntityRef item;

	// Figuring how to wire this up was what inspired this post
	[Association(OtherKey = "DictionaryItemIdentifier", IsForeignKey=true,
		ThisKey="DefinitionItemIdentifier")]
	public DictionaryItemModel Item
	{
		get
		{
			return item.Entity;
		}
		set
		{
			item.Entity = value;
		}
	}

	[Column(Name = "CorporateEntityId", IsPrimaryKey=true)]
	public Guid DefinitionItemIdentifier
	{ get; set; }

	[Column(Name="SecondaryEntityId", IsPrimaryKey=true)]
	public long EntityId
	{ get; set; }

	[Column(Name="SecondaryEntityValue", IsPrimaryKey=true)]
	public string Value
	{ get; set; }
}

For the sake of google (and anyone else who might run across this delightful error), I was getting InvalidOperationExceptions thrown when I tried to do a foreach over a table of the Item Definitions. The message associated with those exceptions was (for example) "Could not find key member 'CorporateEntityId' of key 'CorporateEntityId' on type 'ItemDefinitionModel'. The key may be wrong or the field or property on 'ItemDefinitionModel' has changed names."

That's still a bit over-simplified an example (I obviously left out lots of best practices stuff that would have just obscured the point), but at least it gives you an idea about what the attributes should look like and how they should be named.