Cross cutting composite keys

Hey Guys,

I can't get cross cutting keys to work (ie shared primary keys)

say I have entity StatementLine and Category

StatementLine has Id and PersonID as primary Key, Category as Id and PersonID

StatementLine has a column CategoryID

the foreign key reference between StatementLine and Category is on StatementLine.CategoryID = Category.ID and StatementLine.PersonID = Category.ID

Lightspeed wants a column called StatementLine.CategoryPersonID rathern than using the shared person id.

Is there any way I can make this work?

 

Cheers

Owen

setting the value field [Column("")] and having the properties called the full column names returns an error:

Unhandled Exception: Mindscape.LightSpeed.LightSpeedException: Unable to materia
lize field [PersonId] on type [testCompositeKeys.StatementLine]. Check you
r table has an Id column and that your mappings are correct. See inner exception
 

You would need to use ValueObjectColumnAttribute instead of ColumnAttribute, to map the CategoryId.Id field (in the FK field) to the CategoryID column and the CategoryId.PersonID field to the PersonID column:

// the CK type
public struct CategoryId {
  private readonly int _id;
  private readonly int _personId;
}

// the CKed entity
public class Category : Entity<CategoryId> { ... }

// the type with the FK to the CKed entity
public class StatementLine : Entity<int> {
  [ValueObject]
  [ValueObjectColumn("Id", "CategoryId")]
  [ValueObjectColumn("PersonId", "PersonId")]
  private CategoryId _categoryId;
}

Thanks, that makes sense.

 

One request: can the id struct be made to not generate from the .lsmodel ie can it use the generation:none property on the elements of the identity. Mainly because our Id columns are called the same name as the struct itself so I have to add a column attribute to the id field. it would be useful not to have to recode this every time using the partial method I can do this with other fields but not the fields within the id.

 

 

Have you tried setting the Column Name on the identity properties within the CK type?  That way you can give them property names that are different from the struct name, but still map them to column names that *are* the same as the struct name.  Or am I misunderstanding the problem?

Well I thought that might work, but the column name doesn't work, it doesn't seem to generate anything extra

 

O

Hmm, you're right.  Looks like we don't yet support Column Name (and a few other things too) on identity properties.  If I were to get that fixed, would that be okay rather than us adding a Generation option for the CK type?

most definitely. Thanks Ivan i'll keep an eye open for this fix :)

 

Cheers

Owen

This should now be in the nightly build if you want to give it a try -- let us know if it's still not working.  (Column Name should be okay but I've not had chance to test some of the other settings.)

that seems to work well. thank you.

One minor though, if I set the column to be the same name as the property (i.e. i have a property on the id called OrganisationId which is the cross cutting key, if I set the column name on that to be OrgansiationId) I get an exception 

Unhandled Exception: Mindscape.LightSpeed.LightSpeedException: Unable to materialize field [OrganisationId] on type [testCompositeKeys.StatementLine]. Check your table has an Id column and that your mappings are correct. See inner exception for details.  ---> System.InvalidCastException: Specified cast is not valid. 

if I remove the column name property this works fine.

Cheers

Owen

Weird.  Thanks, we'll investigate that.

Also, while I'm on the subject:

In the example I gave the composite key was made of OrganisationId, and the entity Id.

 

The organsiation id is a primary key on the organisation entity, and I'm wondering if there's a way to put in an entity holder for this organisation entity on some of these entities so that I can traverse the tree a little. The problem seems to be with the fact that the entity holder seems to expect the organisation id to be a field on the entity rather than a field on the entity's id. is there any way to tell lightspeed where to look for the id?

again putting in an _organisationId field leads to an invalid cast exception, as I believe it tries to read from the wrong column index in the result.

 

Cheers

Owen

the invalid cast exception seems to be a deeper issue than thought actually, it means I can't have more than one relationship that relies on the cross cutting key within an entity. Ie statement lines have a one to many relationship with category which works but also has a one to many association with statement which doesn't work (returns the invalid cast exception) removing one entityholder/foreign key means the other works.

also the traversal of the tree doesn't work: returns an error:

Unhandled Exception: System.ArgumentException: Field '_id' defined on type 'test

CompositeKeys.CategoryId' is not a field on the target object which is of type '

System.Guid'.

 

Finally, it would be nice to be able to make the foreign key null if the id itself isn't set (currently the id is populated with the cross cutting key and then guid.empty)

I'll email an example db/model so you can see what I'm working with

 

Cheers

Owen

 

Ok so sorry to pull this old post out, are there any plans to support cross cutting keys properly from lightspeed, the fact that the shared key isn't being pulled into joins is starting to become a bit of a query overhead and one we'd like to nail down... if not is there any way i can get in before the join call (ie Object.Children) and specify an extra where condition?

 

Thanks

Owen

I've kind of lost the thread of exactly what the outstanding issue is here, but I think you're asking for the ability to use standard LightSpeed associations on a foreign key that is also part of a composite key.  Is that right?

If so, you can now do this using ForeignKeyFieldAttribute (surfaced in the designer as Key Property Reference).  See http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=2990&PostID=9499 or http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=3147&PostID=10435 or http://www.mindscape.co.nz/blog/index.php/2010/06/17/many-to-many-associations-and-composite-keys-in-lightspeed/ (some of these are written in terms of many-to-many associations, but a MTM is just a pair of OTMs meeting in the middle, so it should be obviously how to apply it to your case).

If I've misunderstood what you're asking for, could you restate it with a concrete example of what you want to be able to do?  Thanks!

To a certain extent that's right, however I run into problems:

I have a core entity which provides my key for a multi-tenanted database:

CREATE TABLE [dbo].[Organisation](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Organisation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

then we have an entity with children (bill and bill lines)

CREATE TABLE [dbo].[Organisation](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Organisation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[BillLine](
	[OrganisationId] [uniqueidentifier] NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[BillId] [uniqueidentifier] NOT NULL,
	[Amount] [decimal](18, 0) NOT NULL,
 CONSTRAINT [PK_BillLine] PRIMARY KEY CLUSTERED 
(
	[OrganisationId] ASC,
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[BillLine]  WITH CHECK ADD  CONSTRAINT [FK_BillLine_Bill] FOREIGN KEY([OrganisationId], [BillId])
REFERENCES [dbo].[Bill] ([OrganisationId], [Id])
GO

ALTER TABLE [dbo].[BillLine] CHECK CONSTRAINT [FK_BillLine_Bill]
GO

ALTER TABLE [dbo].[BillLine]  WITH CHECK ADD  CONSTRAINT [FK_BillLine_Organisation] FOREIGN KEY([OrganisationId])
REFERENCES [dbo].[Organisation] ([Id])
GO

ALTER TABLE [dbo].[BillLine] CHECK CONSTRAINT [FK_BillLine_Organisation]
GO

as you can see each entity has a relation to the core organisation id

The key to bill from bill line is BillId and OrganisationId, we can make it work if we just map the single BillId but the query slows down significantly once your past a couple of thousand organisations so we always need the join to be made with organisation

I can't get this set up correctly in lightspeed.... was hoping there might be a solution or one on the horizon

the thing that doesn't seem to work is the foreign key that's part taken from the primary composite key and part taken from another column (billId)

 

 

So the Bill table has a composite key (the Bill table doesn't seem to be shown), is that right?  And you want to be able to define a BillLine.Bill association, but you are being foiled by the fact that one of the FK columns is part of the BillLine's Id and one of them isn't?

But if that's the problem, I'm a bit confused by where joins come into it.  That is a straight association from BillLine to Bill, of which OrganisationId is part of the FK, but doesn't seem to require a join with Organisation.  Am I missing something here?  Do you want the Organisation table itself to play some role in the query?  If so what?

Sorry if I'm being dumb here, I just want to make sure I'm solving the right problem before I start diving in and trying to come up with a solution...

Whoops sorry, bill looks like this

CREATE TABLE [dbo].[Bill](

[OrganisationId] [uniqueidentifier] NOT NULL,

[Id] [uniqueidentifier] NOT NULL,

[From] [nvarchar](50) NULL,

 CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED 

(

[OrganisationId] ASC,

[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[Bill]  WITH CHECK ADD  CONSTRAINT [FK_Bill_Organisation] FOREIGN KEY([OrganisationId])

REFERENCES [dbo].[Organisation] ([Id])

GO

 

ALTER TABLE [dbo].[Bill] CHECK CONSTRAINT [FK_Bill_Organisation]

GO

 

and you're right i'm talking about the association.

 

I want to be able to do bill.billlines and lightspeed to map this to all bill lines where billid = bill.billid and organisationid = bill.organisationid

I've sent you the backup of the simple database....

thanks

Owen

 

 

Well, this has been quite a three-pipe problem.  I have a candidate solution which should be in the next nightly build, but I've only been able to give it the relatively simple testing implied by your example, so please be patient if we have to bounce this back and forth a couple of times.

The solution is implemented as an attribute which you apply to association fields.  There is no designer support for this new attribute at the moment.

The new attribute is called AssociationResolverAttribute, and its semantics are basically, "Out of the way, default LightSpeed association lookup, I'm in charge here."  It takes a Type argument.  This type must implement the new IAssociationResolver interface.  IAssociationResolver has two methods:

* GetQueryExpression must return a QueryExpression which specifies how to select the associated entity (for an EntityHolder) or entities (for an EntityCollection).

* SetForeignKey is passed a parent entity's Id, and must update whatever fields are necessary to persist that.  It isn't used on EntityCollections, only on EntityHolders, but I didn't want to declare two separate interfaces (I realise this would send Uncle Bob Martin to the fainting couch, so your feedback is welcome on this).

This probably sounds a bit abstract so let's see how it plays out in your example.

First we have the composite identity type, call it OrganisedKey:

public struct OrganisedKey {
  private readonly Guid _organisationId;
  [Column("Id")]
  private readonly Guid _itemId;
  // constructor and accessors omitted
}

NOTE: There seems to be a bug in LightSpeed which causes errors if a composite key contains a field named "Id."  I've not had time to investigate this, so instead I worked around it by calling the field ItemId and mapping it to the Id column.

Then we have the entity types:

public class Organisation : Entity<Guid> { ... }

public class Bill : Entity<OrganisedKey> {
  // composite key plumbing
  [Transient] private OrganisedKey _pendingId;
  public static Bill New(Guid organisationId) {
    return new Bill { _pendingId = new OrganisedKey(organisationId, Guid.NewGuid()) };
  }
  protected override object GeneratedId() { return _pendingId; }
  // end composite key plumbing
  private readonly EntityCollection<BillLine> _lines = new EntityCollection<Bill>();
}

public class BillLine : Entity<OrganisedKey> {
  // composite key plumbing as per Bill
  private Guid _billId;
  private readonly EntityHolder<Bill> _bill = new EntityHolder<Bill>();
}

I have omitted the obvious wrapper properties for brevity.  Note that BillLine.BillId is a Guid as per the BillId column; it is NOT an OrganisedKey.  So if you run this as it stands, you'll get errors because a Guid can't be a FK to an Entity<OrganisedKey>.

Enter AssociationResolverAttribute.  We need to apply this to both ends of the Bill/BillLine association, but with different implementations at each end.  I'll start at the EntityHolder end:

public class BillLine : Entity<OrganisedKey> {
  [AssociationResolver(typeof(ParentBillResolver))]
  private readonly EntityHolder<Bill> _bill = new EntityHolder<Bill>();

  private class ParentBillResolver : IAssociationResolver {
    public QueryExpression GetQueryExpression(Entity sourceEntity)
    {
      BillLine line = (BillLine)sourceEntity;
      return Entity.Attribute("Id") == new OrganisedKey(line.Id.OrganisationId, line.BillId);
    }

    public void SetForeignKey(Entity childEntity, object parentId)
    {
      BillLine line = (BillLine)childEntity;
      OrganisedKey fk = (OrganisedKey)parentId;
      line._billId = fk.ItemId;
    }
  }
}

We'll look at GetQueryExpression first.  LightSpeed calls this when it needs to traverse the association to get a specification for the entity at the other end.  The sourceEntity argument is the entity from which we are traversing -- the entity containing the association field to which ARA was applied -- in this case our BillLine.  We want an entity whose Id is a composite consisting of our BillLine's organisation ID and its BillId, so we write that down as a QueryExpression.  (We could also have written Entity.Attribute("Id.OrganisationId") == line.Id.OrganisationId && Entity.Attribute("Id.ItemId") == line.BillId.  Pick whichever you find clearer: they boil down to the same thing.)

Now for SetForeignKey.  LightSpeed calls this when you add a BillLine to a Bill.Lines EntityCollection, or set a BillLine's Bill property.  In either case, it passes the BillLine as the childEntity, and the Bill's Id as the parentId.  We need to assign all the bits of the parentId to the relevant fields in the childEntity.  In this case, however, the _pendingId.OrganisationId will already have been set and cannot be changed.  So the only thing we need to set is the _billId.  In your real app, you may be expecting to set the _pendingId.OrganisationId at this point -- this would require you to construct a new OrganisedKey and set _pendingId to that, which is fine, but there may be a timing issue with when GeneratedId() gets called.  Let us know if this turns out to be an issue.

With that under our belts, the EntityCollection end is pretty simple:

[AssociationResolver(typeof(ChildBillLinesResolver))]
private readonly EntityCollection<BillLine> _lines = new EntityCollection<BillLine>();

private class ChildBillLinesResolver : IAssociationResolver
{
  public QueryExpression GetQueryExpression(Entity sourceEntity)
  {
    Bill bill = (Bill)sourceEntity;
    return Entity.Attribute("BillId") == bill.Id.ItemId
      && Entity.Attribute("Id.OrganisationId") == bill.Id.OrganisationId;
   }

  public void SetForeignKey(Entity childEntity, object parentId)
  {
    // Not required on parent side
  }
}

Again, the query expression specifies all the BillLines whose BillId and OrganisationId match this Bill.  In this case, however, there may be any number of such BillLines, because we are populating an EntityCollection rather than an EntityHolder.  And there's no foreign key at this end of the association, so SetForeignKey can be a no-op.

I hope all this makes sense.  I'm anticipating that some tweaking may be required to get this working in the greater complexity of your real application, so please let us know what issues you run into.

Thank you so much for helping us out with this, I'll get hold of the nightly now and have a play.

 

Thanks again!!

Thanks again for sorting this into the nightly builds so fast.

I've come up against an issue which i can replicate in the test DB file that I sent you.

Delete seems to throw an error:

 

doing this:

 

     var uow = context.CreateUnitOfWork();

 

            var org = new Organisation()

                          {

                              Name = "Test Org"

 

                          };

            uow.Add(org);

            uow.SaveChanges(true);

 

            var bill = new Bill(org.Id)

                           {

                               From = "Test"

                           };

 

            var billLine = new BillLine(org.Id)

                               {

                                   Amount = 12,

                               };

            bill.BillLines.Add(billLine);

 

           uow.Add(bill);

            uow.Add(billLine);

            uow.SaveChanges();

            uow.Remove(uow.FindById(typeof(Bill),bill.Id));

            uow.SaveChanges();

 

throws:

 

       Test 'M:LightspeedTest.TestMethod.Run' failed: No mapping exists from object type LightspeedTest.BillId to a known managed provider native type.

System.ArgumentException: No mapping exists from object type LightspeedTest.BillId to a known managed provider native type.

at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen)

at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()

at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)

at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)

at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at ...()

at ..(IUnitOfWork , IDbCommand ,
)

at ..(IUnitOfWork , IDbCommand )

at ..( )

at ..(IEnumerable`1 )

at ..()

at ..(ICollection`1 )

at ..(UnitOfWorkBase , IEnumerable`1 , IEnumerable`1 )

at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)

at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges()

TestMethod.cs(45,0): at LightspeedTest.TestMethod.Run()

Thanks for reporting this.  I've got a candidate fix going into the 31 July nightly.  Let us know how you get on.