locked
Linq to SQL : Many-to-One Relationships?

    Question

  • I don't see anywhere in the linq-to-sql designer where I setup a many-to-one relationship (i.e., an entity reference... no collections). Surely it must be possible? This is a very common relationship type. Anyone have any advice here?

     

    Thanks...

    Wednesday, June 06, 2007 4:12 PM

Answers

  • Starting with Orcas Beta 2 you will be able to do what you're asking for. You can drag/drop your related tables onto the designer as normal and then set the "Child Property" property on the association to False. The generated class will then not contain a property that is a collection of the "Many" side of the relationship. In Beta 1 this was reversed -- you could choose not to create a property for the "One" side of the relationship.

    Wednesday, June 20, 2007 8:26 PM
  •  Michael.NET wrote:

    GROUP HUG!

     

    I'll be the first to admitt that I was bit too academic.

     

    And I now realise that you can have a EntitySet<T> without the EntityRef<T> at the other end.

     

    Cheers,

     

    Michael, its the other way around.  You can have an EntityRef without the EntitySet on the other end. Being able to do it the other way is not on the radar for the first version.

    Thursday, June 21, 2007 7:32 PM

All replies

  • Hi,

    when you drag two related tables (having a foreign key relationship defined in the database) from the Solution Explorer window and drop them on the designer, the tool generates a relationship between these tables, automatically. The code will contain an EntityRef object in the entity class related to the table that owns the foreign key and an EntitySet object in the entity class related to the table that owns the primary key.

     

    If you are creating manually the tables in the designer you have to select the Associate tool from the Toolbox and create a link between the two tables. Then you have to specify some property such as the Cardinality (ono-to-one or one-to-many relationship), the Foreign Key, and so on.

    Best,

    Fabio

    Thursday, June 07, 2007 8:56 AM
  • Thanks. I actually am aware of how the designer works. Following your advice it creates a one-to-many relationship, which is not what I want. I want a many-to-one.

     

    Is nobody familiar with many-to-one? Imagine I have a Person and an Address class. A person has an address. Other entities such as a Company also have an address. Different entities may even have the same address. But, an address does not have any child collections or references.

     

    So, if I do what you said, the designer will create a collection of List<Person> in the Address class. This is not right. There is no way to turn it off - I can turn off the "Reverse" property, but that's the one I want to keep!

     

    So far I have been able to find a way to make it work, sort-of. I create a one-to-one in the wrong direction and then disable the reverse property. This is a hack and probably doesn't really work.

     

    Anybody have any advice?

    Thursday, June 07, 2007 6:51 PM
  • Hi Grennis

     

    Not sure, but I think you're looking at it from the wrong perspective. The relationship you're looking for is created by the designer as explained by the previous poster.

     

    The many-to-one relationship between Person / Adress or customer/address is the same as having an address that contains a collection of Persons / Customers. The generated Person / Customer entities will have a property that exposes a single Address. Unless I'm mistaken, this is exactly the thing you're looking for, except that you don't need the Address.Persons / Address.Customers properties exposed by the Address entity.

     

    This behaviour is by design. Even though you don't actually want to expose the Address.Persons / Address.Customers from the Address entity, the designer generates the correct model according to your database design. It's easy for you to hide the properties by exposing your entities from business objects (embed the entity in a business object), which probably only applies to those writing APIs for 3rd party developers.

     

    For LINQ to SQL as an internal mechanism, it should be pretty easy to simply ignore those extra properties on the Address entity.

    Friday, June 08, 2007 7:33 AM
  • One-to-many and many-to-one are the same. In the designer you will find the Cardinality property showing only the One-To-Many value but, as I said before, if you create a foreign key in the database that goes from Address table to Person table, you will see the designer showing an arrow that goes from Address entity to Person entity. You will have many address for each person using the Person.Addresses property.

    Best,

    Fabio

    Friday, June 08, 2007 9:21 AM
  • "This behaviour is by design. Even though you don't actually want to expose the Address.Persons / Address.Customers from the Address entity, the designer generates the correct model according to your database design"

     

    Sorry guys, I'm not buying this at all. An address does not have a collection of companies, people, whatever. This is wrong. This means the OR mapper is going to go fetch a ton of data I don't want and don't ever use. This is bad for performance. Yeah sure, maybe I can turn it off by lazy loading. But this is silly. I don't want it in the first place. I want a many-to-one relationship!!!!

     

    Object mappers such as NHibernate, Wilson OR/M, LLBL, etc., etc., they ALL support an explicit many-to-one concept. There is a reason for this. Surely you will agree with me.

     

    Friday, June 08, 2007 2:04 PM
  • Thanks bltfast, but I do not agree at all. Please see my post above.

     

    Friday, June 08, 2007 2:05 PM
  • You don't have to agree at all to still be wrong. =)

     

    You can't have one-to-many without many-to-one. It is describing one type of relation from two perspectives.

     

    Cheers,

     

    Thursday, June 14, 2007 10:06 AM
  • Yes, I understand that in your simple and naive view you think in your little mind that many-to-one and one-to-many are the same.

     

    I see that you have failed to address any of arguments and blindly declared me to be "wrong".

     

    Please explain how it makes sense for a reference table row to have a collection of child fact table rows. Please explain why ALL other OR/M products support the independent concept of many-to-one. Do you actually have any experience using any other OR/M technology? It is very obvious to me that you have not.

     

    Linq to Sql seems to be a double-edge sword, it is bringing a bunch of cluess newbies that provide material for TheDailyWTF.com to the world of OR/M. That is a shame.

     

     

     

     

     

    Thursday, June 14, 2007 10:53 PM
  •  grennis_ wrote:

    "This behaviour is by design. Even though you don't actually want to expose the Address.Persons / Address.Customers from the Address entity, the designer generates the correct model according to your database design"

     

    Sorry guys, I'm not buying this at all. An address does not have a collection of companies, people, whatever. This is wrong. This means the OR mapper is going to go fetch a ton of data I don't want and don't ever use. This is bad for performance. Yeah sure, maybe I can turn it off by lazy loading. But this is silly. I don't want it in the first place. I want a many-to-one relationship!!!!

     

    Object mappers such as NHibernate, Wilson OR/M, LLBL, etc., etc., they ALL support an explicit many-to-one concept. There is a reason for this. Surely you will agree with me.

     

     

    Grennis, Lazy loading is the default. It will never preload the data except if you specify it explicitely in a shape. If you don't though the collection properties in the Address

    object it won't cause any overhead at all.

     

    It seems to me that there is no reason for this thread. One To Many is the same as Many To One. The only thing that might be handy for some is that maybe you should be able to hide

    the preoperty at the Many side, but being able to access the objects that point to your address can become handy before you know it, but that depends on your app.

     

    kind regards

     

    Patrick De Block

    Friday, June 15, 2007 8:08 AM
  • gman, if you can't discuss something without insulting a person that has another opinion than you have, please don't do it. The only person apparently having a little mind is you.

     

    kind regards,

     

    Patrick de Block

    Friday, June 15, 2007 8:11 AM
  • Gman, you make some very interesting observations and generalization about the people that try to help you. If I was you, I would keep a clean language and stay on topic. Mind you that the answer to your question has been given by several independent developers in this thread.

     

    Do you need Anders Hejlsberg to step in before seing the light?

    Friday, June 15, 2007 10:24 AM
  • Yes, I understand that in your simple and naive view you think in your little mind that many-to-one and one-to-many are the same.

     

    Well, I like simple truths. However, I am not naive.

     

    I see that you have failed to address any of arguments and blindly declared me to be "wrong".

     

    That is because you are wrong.  

     

    Please explain how it makes sense for a reference table row to have a collection of child fact table rows. Please explain why ALL other OR/M products support the independent concept of many-to-one. Do you actually have any experience using any other OR/M technology? It is very obvious to me that you have not.

     

    What I am saying is that you cannot have a many-to-one relation without the one-to-many relation. A relationship must be fully defined with cardinality and participation from both directions between two entities. Example:

     

    "A Customer may have several Orders. Each Order belongs to a Customer."

     

    Also, you could change this into a many-to-many relationship as:

     

    "Several Customers may share several Orders. Each Order belongs to at least one Customer"

     

    Linq to Sql seems to be a double-edge sword, it is bringing a bunch of cluess newbies that provide material for TheDailyWTF.com to the world of OR/M. That is a shame.

     

     Indeed.

     

    I used to teach datamodelling (EER) at university-level and I have flunked people for making statements like you do. Why not read a book on the subject. =)

     

    Cheers,

     

    Wednesday, June 20, 2007 9:22 AM
  • Starting with Orcas Beta 2 you will be able to do what you're asking for. You can drag/drop your related tables onto the designer as normal and then set the "Child Property" property on the association to False. The generated class will then not contain a property that is a collection of the "Many" side of the relationship. In Beta 1 this was reversed -- you could choose not to create a property for the "One" side of the relationship.

    Wednesday, June 20, 2007 8:26 PM
  •  

    Perfect! - Thank you!

     

    See guys? I was right

     

     

    Thursday, June 21, 2007 2:00 AM
  • You all are right. In the abstract a many-to-one relationship is the exact same thing as a one-to-many. However, you may choose to not want to model both directions in your objects. You can do the same for one-to-one relationships too.

     

    Now, everyone, group hug!

    Thursday, June 21, 2007 3:38 AM
  • GROUP HUG!

     

    I'll be the first to admitt that I was bit too academic.

     

    And I now realise that you can have a EntitySet<T> without the EntityRef<T> at the other end.

     

    Cheers,

    Thursday, June 21, 2007 12:15 PM
  • Sorry I was hostile, I was having a bad day and really lost it there.

     

    Thursday, June 21, 2007 12:42 PM
  •  Michael.NET wrote:

    GROUP HUG!

     

    I'll be the first to admitt that I was bit too academic.

     

    And I now realise that you can have a EntitySet<T> without the EntityRef<T> at the other end.

     

    Cheers,

     

    Michael, its the other way around.  You can have an EntityRef without the EntitySet on the other end. Being able to do it the other way is not on the radar for the first version.

    Thursday, June 21, 2007 7:32 PM
  • (Matt: I set that trap for grennis to step into, thanks for ruining that with simple truths *s*)

     

    Well, as long as grennis is happy, so am I.

     

    Guess I am still such a hardcore EER-fanatic that I lack the imagination to ever need the feature.

    Having half a relation is bad design in my world.

     

    When it comes to YAGNI - You Ain't Gonna Need It,  

    I would still express the other end of the relation by the idiom of MIDNI - Maybe I Do Need It.

     

    Guess that is what makes grennis happy. He really needs the feature.

     

    Cheers,

     

    Thursday, June 21, 2007 10:01 PM
  • Also, very much off-topic:

     

    What does 'Wayward LINQ Lacky' mean?

     

    I think you need a group hug! *hugs*

    Thursday, June 21, 2007 10:04 PM
  • Heheh, nope sorry - I just figured you were making ANOTHER mistake...

     

    If you want to go on believing that an OrderType should always have a collection of Orders, then good luck in your real world implementation when you come down from the ivory tower... 

     

     

    Friday, June 22, 2007 1:53 AM
  • grennis: - Be nice to nerds. Chances are you'll end up working for one. (Bill Gates)
    Saturday, June 23, 2007 1:52 PM
  • There is no difference between a many-to-one and a one-to-many relation. They both describe the same object, albeit from difference perspectives, and you cannot have one without the other. Having said that, you raise an important point that other ORM tools support (Hibernate and TopLink to name a few). What you are talking about is navigability of the relation. A bi-directional one-to-many relation means you have the (unwanted in your case) many collection in the one object. Remember that collections are lazy loaded in LINQ to SQL so it will not be populated. Just ignore it.
    Friday, November 28, 2008 10:51 PM