create table ProductCategory
(
ProductCategoryId int identity(1,1) primary key, -- ideally the entity is accessed through surrogate primary key
ProductId int not null references Product(ProductId),
CategoryId int not null references Category(CategoryId),
CustomizedProductCategoryDescription nvarchar(200) not null,
constraint uk_ProductCategory unique(ProductId, CategoryId) -- ideally on unique
);
create table Model
(
ModelId int identity(1,1) primary key,
-- ideally the entity referenced is accessed by the key, the whole key and nothing but the key.
-- hear it loud, not keys! not plural, singular key only. capiche? :-)
ProductCategoryId int not null references ProductCategory(ProductCategoryId),
ModelDescription nvarchar(200) not null
);
However not everyone are afforded of a perfect world, composite keys are pervasive on old database designs:
create table ProductCategory
(
ProductId int not null references Product(ProductId),
CategoryId int not null references Category(CategoryId),
CustomizedProductCategoryDescription nvarchar(200) not null,
constraint pk_ProductCategory primary key(ProductId, CategoryId) -- what an imperfect world
);
create table Model
(
ModelId int identity(1,1) primary key,
ProductId int not null,
CategoryId int not null,
ModelDescription nvarchar(200) not null,
constraint fk_Model__ProductCategory foreign key(ProductId, CategoryId) references ProductCategory(ProductId, CategoryId) -- why the world have to be imperfect?
);
Ideally, even when mapping that imperfect design, related entities should still be navigable through object reference:
public class ProductCategory
{
public virtual Product Product { get; set; }
public virtual Category Category { get; set; }
public virtual string CustomizedProductCategoryDescription { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as ProductCategory;
if (t == null)
return false;
if (Product == t.Product && Category == t.Category)
return true;
return false;
}
public override int GetHashCode()
{
return (Product.ProductId + "|" + Category.CategoryId).GetHashCode();
}
}
class ProductCategoryMapping : ClassMapping<ProductCategory>
{
public ProductCategoryMapping()
{
ComposedId(
c =>
{
c.ManyToOne(x => x.Product, x => x.Column("ProductId"));
c.ManyToOne(x => x.Category, x => x.Column("CategoryId"));
});
Property(x => x.CustomizedProductCategoryDescription);
}
}
However, the real problem with that kind of domain modeling is that lazy-loading will be defeated, think of Edit screen, you just need to get the IDs of both Product and Category via ProductCategory, there's no way we can avoid the unnecessary fetching of the whole ProductCategory object when we have the kind of domain model like the above. For a good detail why accessing the ProductId from Product of ProductCategory unnecessarily fetches the whole ProductCategory object, read this: http://devlicio.us/blogs/anne_epstein/archive/2009/11/20/nhibernate-and-composite-keys.aspx
Just a mere reading of ProductId from Product of ProductCategory, the app will unnecessarily fetch the whole ProductCategory object. This kind of problem doesn't happen on applications with no composite keys.
So this code..
public static Model LoadModel(int id)
{
using (var session = SessionMapper.Mapper.SessionFactory.OpenSession())
{
var x = session.Load<Model>(1);
Console.WriteLine("\nHey! {0} {1}", x.ProductCategory.Product.ProductId, x.ModelDescription);
return x;
}
}
..produces this SQL:
NHibernate:
SELECT
model0_.ModelId as ModelId3_0_,
model0_.ProductId as ProductId3_0_,
model0_.CategoryId as CategoryId3_0_,
model0_.ModelDescription as ModelDes4_3_0_
FROM
Model model0_
WHERE
model0_.ModelId=@p0;
@p0 = 1 [Type: Int32 (0)]
NHibernate:
SELECT
productcat0_.ProductId as ProductId2_0_,
productcat0_.CategoryId as CategoryId2_0_,
productcat0_.CustomizedProductCategoryDescription as Customiz3_2_0_
FROM
ProductCategory productcat0_
WHERE
productcat0_.ProductId=@p0
and productcat0_.CategoryId=@p1;
@p0 = 1 [Type: Int32 (0)], @p1 = 2 [Type: Int32 (0)]
Hey! 1 Viking shoe
Not optimized. As you can see, even we just read the ProductId the whole ProductCategory object is also fetched by our app. It looks amateurish when we are just accessing the ProductId and it's already available right there from the source table, yet our app still insist on loading the whole ProductCategory just to get the ProductId
Another problem with the kind of domain model above, when we persist the Model object, the persistence mechanism will become convoluted:
public static string AddModel()
{
using (var session = SessionMapper.Mapper.SessionFactory.OpenSession())
{
var m = new Model
{
ProductCategory = session.Load<ProductCategory>(
new ProductCategory { Product = session.Load<Product>(1), Category = session.Load<Category>(2) }),
ModelDescription = "Bad " + DateTime.Now.ToString()
};
session.Save(m);
session.Flush();
return m.ModelId + " " + m.ModelDescription;
}
}
To rectify that amateurish SQL and hideous persistence code, we must isolate the composite keys to their own class:
[Serializable]
public class ProductCategoryIdentifier
{
public virtual int ProductId { get; set; }
public virtual int CategoryId { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as ProductCategoryIdentifier;
if (t == null)
return false;
if (ProductId == t.ProductId && CategoryId == t.CategoryId)
return true;
return false;
}
public override int GetHashCode()
{
return (ProductId + "|" + CategoryId).GetHashCode();
}
}
That class will be the primitive type for the composite key of our ProductCategory:
public class ProductCategory
{
public virtual ProductCategoryIdentifier ProductCategoryIdentifier { get; set; }
//// To enforce single source-of-truth when creating product category, set both Product and Category properties setter as protected.
//// When assigning Product and Category, it must be done through the composite key class, i.e. through ProductCategoryIdentifier above
public virtual Product Product { get; protected set; }
public virtual Product Category { get; protected set; }
public virtual string CustomizedProductCategoryDescription { get; set; }
}
class ProductCategoryMapping : ClassMapping<ProductCategory>
{
public ProductCategoryMapping()
{
ComponentAsId(
i => i.ProductCategoryIdentifier,
c =>
{
c.Property(x => x.ProductId);
c.Property(x => x.CategoryId);
});
ManyToOne(x => x.Product, m =>
{
m.Column("ProductId");
m.Update(false);
m.Insert(false);
});
ManyToOne(x => x.Category, m =>
{
m.Column("CategoryId");
m.Update(false);
m.Insert(false);
});
Property(x => x.CustomizedProductCategoryDescription);
}
}
This will be how our app will fetch the ModelDescription property and ProductId..
public static Model LoadModel(int id)
{
using (var session = SessionMapper.Mapper.SessionFactory.OpenSession())
{
// Read the ID from Composite Key's separate class(ProductCategoryIdentifier)
// ,this way the whole object of ProductCategory won't be unnecessarily fetched.
var x = session.Load<Model>(1);
Console.WriteLine("{0} {1}", x.ProductCategory.ProductCategoryIdentifier.ProductId, x.ModelDescription);
return x;
}
}
..and the following is the SQL produced by that data access. As expected there's no unnecessary data that was fetched, ProductCategory is not fetched. Very optimized code
NHibernate:
SELECT
model0_.ModelId as ModelId3_0_,
model0_.ProductId as ProductId3_0_,
model0_.CategoryId as CategoryId3_0_,
model0_.ModelDescription as ModelDes4_3_0_
FROM
Model model0_
WHERE
model0_.ModelId=@p0;
@p0 = 1 [Type: Int32 (0)]
1 Viking shoe
This is how we persist the Model object when it has a composite foreign key..
public static string AddModel()
{
using (var session = SessionMapper.Mapper.SessionFactory.OpenSession())
{
var m = new Model
{
ProductCategory = session.Load<ProductCategory>(
new ProductCategoryIdentifier { ProductId = 1, CategoryId = 2 }),
ModelDescription = "Good " + DateTime.Now.ToString()
};
session.Save(m);
session.Flush();
return m.ModelId + " " + m.ModelDescription;
}
}
..the code looks more clean as compared to the model without a separate class for composite key.
It will be more clean if we don't have composite keys on the database in the first place. Ah legacy systems..!
Complete code: https://github.com/MichaelBuen/TestComposite
Happy Coding! ツ
