What OR-mappers allow one-to-many AND One-to-one relationship between the same tables?

Question

Hi,

I'm currently looking for an OR-mapper to use for a side project I'm about to start. Since we're using an inhouse developed OR-mapper at work I don't really know much about how other mappers work (and have a pretty incomplete picture of which exist). After some research it seems that NHibernate, Linq-to-SQL and Entity Framework are the most commonly used ones.

So here's my question: is it possible in [insert your favorite ORM here] to map a one-to-many AND a one-to-one relation between the same tables and how is it done (elegantly)? A simple example for this is to store a list of items (one-to-many) and the selected item (one-to-one).

The reason I chose this particular question is that it's a painpoint with our inhouse OR-mapper: it just doesn't allow more than one relation between two tables. What I want to gain from asking this is some insight from people who are working with those mappers to aid me in deciding which (existing!) OR-mapper to use for my side project.

Thanks for your help!

Answer 1

If you have two tables, one for Lists and one for ListItems. ListItems would have a foreign key to Lists and Lists would have a foreign key to ListItems for the selected item.

This is a very simple relationship and should be supported in all OR-mapping tools.

Answer 2

The purpose of an OR mapper is to make the database correspond to an object graph. So the association you describe might look like: (and here I'm using Hibernate, which is more or less the same as NHibernate, but in Java instead of C#/.NET)

//table schema: 
// item_store(item_store_id, selected_item_id)

@Entity
@Table(name="item_store")
class ItemStore {
 @Id
 @Column(name="item_store_id")
 public String getId() {...}

 @OneToMany(mappedBy="itemStore")
 public Collection<Item> getItems() {...}

 @ManyToOne
 @JoinColumn(name="selected_item_id")
 public Item getSelectedItem() {...}
}

//table schema:
// item(item_id, item_store_id)

@Entity
@Table(name="item")
class Item {
 @Id
 @Column(name="item_id")
 public String getId() {...}

 @ManyToOne
 @JoinColumn(name="item_store_id")
 public ItemStore getItemStore() {...}
}

I'm sure I've made a mistake or two in here somewhere, but hopefully you get the idea how it works.

Answer 3

I think most of leading ORM support such mappings (the only exception i heard is Subsonic). For example in DataObjects.Net it will look like this:

public class ItemStore : Entity
{
  [Field, Key]
  public int Id { get; set; }

  [Field]
  public Item SelectedItem { get; set; }

  [Field, Association(PairTo = "Store")]
  public EntitySet<Item> Items { get; private set; }
}

public class Item : Entity
{
  [Field, Key]
  public int Id { get; set; }

  [Field]
  public ItemStore Store { get; set; }
}