SubSonic Multiple column primary key

Question

Hi, we have certain tables where we have multiple columns which together make the primary key. When SubSonic generates all it's classes for our tables for the tables with multiple columns there are still methods, like FetchByID, that only use one value for the primary key.

Because of this the foreign key property will return the wrong items. For instance we have a product table that has a primary key of multiple columns. A class with a relation to the product table will have a product property with the get method like : Product.FetchByID(this.SalesOrganisationID). This should be Product.FetchByID(this.SalesOrganisationID, this.ProductID).

Does anyone have any advice for me about what i should do to make SubSonic work with these kind of primary keys?

Answer 1

You will have to get it using Query or SqlQuery (SubSonic.Select). You can add the method in the partial class of the Object you are adding this functionality, so as to encapsulate the details of performing the operation.

Answer 2

You could expand your Product class to have a method that uses a Select to find the Product by it's composite id, for example:

public partial class Product{
  public static Product FetchByCompositeId(int salesOrganisationId, int productId){
    return DB.Select().From<Product>()
      .Where(Product.Columns.SalesOrganisationId).IsEqualTo(salesOrganisationId)
      .And(Product.Columns.ProductId).IsEqualTo(productId)
      .ExecuteSingle<Product>();
  }
}