How to map and Entity, shared by 2 other Entities, on the same Key

Hi Guys,

I have the following Tables/Entities

Office
User
Image

All entities extend Entity<Guid>. What I would like to do, is allow Offices and Users to have multiple images associated with them, but just map them via a single column EntityId in the Image table which links to the ID of and office or user.

When I map this in the designer, it complains that there are 2 properties named Entity.

Any help appreciated.

Many thanks

Matt

Hi Matt,

I'm not quite sure I have understood your scenario so let me rephrase it to be sure.

You have three kinds of entity, Office, User and Image.  An Image may be associated with an Office or a User, and an Office or a User may have multiple Images associated with it.  But you have only one EntityId column in the Image table, which may contain the Id of an Office or a User.

If this is correct, this is not supported in LightSpeed, because it would be impossible for us to determine when loading the Image whether the EntityId referred to an Office or a User.  (Actually, you can do this by using single table inheritance, but that requires you to put your Offices and Users in the same database table, which is almost certainly a Bad Thing.)  We could search all tables in the model for an entry with the right Id, but this is costly (multiple searches) and risky (in general entities in different tables may have the same Id).

Instead, Image would need to have both OfficeId and UserId columns, which would be nullable.  These would then be modelled as separate Image.Office and Image.User properties in LightSpeed, and as separate associations in the designer (with different backreference names, Office and User, which makes the "two properties named Entity" issue go away).

What if you still want to keep the "each image is of only one thing" model in the object model?  I.e. you want an Image.Entity property rather than two separate properties?  The answer is that LightSpeed works with the private fields of your object.  You can create your own public API if you want.  So you could have something like this:

// Private fields that have to reflect the database model
private Guid? _officeId;
private Guid? _userId;
private readonly EntityHolder<Office> _office = new EntityHolder<Office>();
private readonly EntityHolder<User> _user = new EntityHolder<User>();

// Public API that can be whatever you like
public Entity Entity {
  get {
    Office office = Get(_office);
    if (office != null)
      return _office;
    return Get(_user);
  }
  set {
    Office office = value as Office;
    if (office != null)
      Set(_office, value);
    else
      Set(_user, (User)value);
  }
}

If you want to do this using the designer you will need to set both one-to-many associations' Generation option to FieldOnly, so that the designer does not create unwanted public Image.Office[Id] and Image.User[Id] properties, and implement the custom public API in a partial class.

Hope this helps -- if I've misunderstood the problem then let me know.

Hi Ivan,

Your understanding of the scenario was spot on, and your answer was exactly what I needed.

Thank you for spending the time to write such a detailed response.

Many thanks

 Matt