Relationships with payloads

Relationships with payloads

• AK88

  Can you write that example in LINQ to Entities I still have trouble translating eSQL to LINQ to Entities.

  Thanks!

  
  
  

• A911Pro

  Kevin Hoffman wrote:

  I know that at the moment there is no support for relationships that carry their own payloads, but I was wondering if there was something I could do perhaps with views or with the entity model to give me the same functionality without resorting to a simple 1:1 mapping between my objects and my tables. Basically my schema works like this: table A : field, field, field, etc table B: field, field, field, etc. relationship_table: A_ID, B_ID, UserID, DateTime So, I basically have table A, table B, and a relationship table that contains a mapping between table A and table B, as well as the user that created the mapping and when that user created the mapping.

  
  You have 3 entities, not 2. The m:n relationship between A and B is called an 'objectified' relationship, i.e. it's an entity by itself (Object Role Modelling / NIAM, http://www.orm.net for more info on this)

  Therefore you need 3 entities in your classlibrary and map them onto the tables. You might think you don't want the intermediate entity, but you do :) Otherwise how would you insert a new A-B relationship Through 'obsure' code which magically inserts this entity If so, how would you set that DateTime field
  
  
  
  

• AJB123

  To close the loop, currently the only thing the entity framework provides that helps here is the general modelling and query support as was described above (which can simplify things somewhat even though you still have to have an explicit 3-entity model). We also do not yet provide eventing as was described which is an interesting approach. Both more pervasive/general eventing support and first-class support for relationships with payloads are something we are considering for a future release but at this point don't look like they will make it into the first release of the framework.

  - Danny

  
  
  

• imed-deborah

  I agree you need a third entity. However what I think LINQ for Entities should provide is some sort of Event on your relationship so you can modify what needs to happen. Base4 uses this approach:
  
  void OnAfterAddReference(object sender, ItemEventArgs<ISchema> e)
  {
  if (this.Name == e.Item.Name) throw new ArgumentException("Attempting to add a circular Schema Reference!");
  SchemaReferenceImpl sr = new SchemaReferenceImpl();
  sr.Referenced = e.Item.Name;
  sr.Referencer = this.Name;
  Internals.PendingTransaction.Updates.Add(sr);
  }
  
  This event handler is hooked up to a ManyToMany relationship.
  
  In this example it is builds a standard entry in the for a simple ManyToMany table with no payload. This approach is good because you can use this to embed business rules, and if SchemaReferenceImpl had a third field you could easily set that too.
  
  The challenge of course is how do you allow the programmer to interact with the context
  
  Base4 provides a PendingTransaction, but what would ADO.NET provide
  
  
  
  
  
  

• Jon Davis

  I didn't mean to imply that I needed to hide or obscure the middle table. I know that I need 3 entities. I was just looking to see if there was something that EF would provide me above and beyond a simple 1:1 mapping between my tables and the objects.
  
  
  

• Corey Bornmann

  The thread has moved on beyond this point, but when I take a look at the ESQL I wrote above I realize I just must have been very tired at that moment, because there should be no need for so many things in the FROM clause. Just this ought to be sufficient:

  SELECT VALUE c.PictureInstances.Picture
  FROM categories AS c
  WHERE c.name = "favorites" AND c.PictureInstances.UserID = "joe"

  In any case I've sent some email to some of the esql & linq gurus on the team hoping to get someone who can come up with the sample rewritten in linq, etc.

  
  
  

• Nightman28

  I hate to leave things hanging (even though the discussion moved on), so I went back to some other folks on the team to make sure that I had the ESQL exactly right and to get a LINQ equivalent, and here's what we came up with:

  ESQL:
  SELECT VALUE p
  FROM categories AS c, c.PictureInstances as p
  WHERE c.name = "favorites" AND p.UserID = "joe"

  LINQ:
  From c in categories
  From p in c.PictureInstances
  Where c.name = “favorites” && p.UserId = “joe”
  Return p

  - Danny

  
  
  

• Jones1

  Unfortunately, no, I don't think there's any way to avoid having three entity types if you need to model a relationship with a payload. This is something we would like to support in a future release, because the need is apparent. We have designed the EDM to handle this kind of model.

  Given the seemingly never-ending list of features we would like to implement and the desire to actually finish and ship a release, though, it seems unlikely that we'll be able to support a models with relationship entities in the first release of the entity framework. You can, as you mention above, create three entities with two relationships, and you still gain some benefit because queries are much simpler to write with relationship traversal than if you had to write all the joins yourself as you would in a relational version. To give a concrete example, if we were to take your three tables above and create a schema with pictures, categories, and picture_category_instances, we might have something like this:

  Entities:
  picture: filename, dateTaken, ..., navigation property - CategoryInstances
  category: name, ..., navigation property - PictureInstances
  picture_category_instance: UserID, DateTime, ..., navigation property - Picture, navigation property - Category

  Relationships:
  0..1:* between pictures and picture_category_instances
  0..1:* between categories and picture_category_instances
  (implicitly a *:* between pictures and categories)

  With this kind of schema you can, for instance, query for all pictures in a particular category where the category was assigned to the picture by a particular user. Such a query would look something like this (off the top of my head so I may not get the query syntax quite right, but you should be able to get the general idea -- plus if I screq up the query someone else on the team will probably jump in and tell us):

  SELECT VALUE c.PictureInstances.Picture
  FROM pictures AS p, categories AS c, picture_category_instances AS pc
  WHERE c.name = "favorites" AND c.PictureInstances.UserID = "joe"

  - Danny

  
  
  
• Relationships with payloads