locked
Need Many-to-Many? RRS feed

  • Question

  • I see examples all the time of Customers/Orders.  Of course, the Order detail child window is filled with items.  (One Order can have many Items).

    Naturally, likewise, an Item will be used in many Orders.  However, I never really see a discussion of creating a many-to-many relationship between Orders and Items.  It is always shown as a One-to-Many (One Order with Many Items)

    However, what if I want to find all Order that have a particular item?  (One Item has many Orders)?

    It seems this requires a many-to-many.  I know how to do the basic setup with a join table, but there are a lot more things that needs to be be done, such as searching for an Item from the Orders table and searching for an Order from the Items table.

    Additionally, I may want to add a new item when creating an Order, etc.  So far, I can't figure out how to do any of that with LS B1. 

    So, I'm wondering if I just missing something, and somehow I don't need a many-to-many, but just set up more than one, One-To-Many and do something that I'm just not getting.

    If I'm right, that these do require a Many-to-Many, is there going to be more robust support for M-t-M in LS Beta 2 and beyond or is there some way to do all that?

    Thanks,

     

    Friday, February 25, 2011 9:37 PM

Answers

  • many-many relationship is not directly in the LS application model.  You cannot create a M:M relationship directly in the LS designer.  However, you can create a middle entity and two *:1 relationship to represent this type of relationship.

    That is why we create OrderDetail, and add *:1 relationship between it to the order entity, and the Item entity in your scenario.

    You can write Linq query to get all Orders have a special item

    Orders.Where((order) => order.OrderDetails.Any((d) => d.Item == yourItem));

    This query is not supported directly inside the query designer.  But you can construct it by code.

    Thanks

     

     


    LLF
    Saturday, February 26, 2011 2:24 AM

All replies

  • many-many relationship is not directly in the LS application model.  You cannot create a M:M relationship directly in the LS designer.  However, you can create a middle entity and two *:1 relationship to represent this type of relationship.

    That is why we create OrderDetail, and add *:1 relationship between it to the order entity, and the Item entity in your scenario.

    You can write Linq query to get all Orders have a special item

    Orders.Where((order) => order.OrderDetails.Any((d) => d.Item == yourItem));

    This query is not supported directly inside the query designer.  But you can construct it by code.

    Thanks

     

     


    LLF
    Saturday, February 26, 2011 2:24 AM
  • Also check out this sample of many-to-many on our blog:

    http://blogs.msdn.com/b/lightswitch/archive/2010/12/16/how-to-create-a-many-to-many-relationship-andy-kung.aspx

    Cheers,
    -Beth

     


    Senior Program Manager, Visual Studio Community http://msdn.com/lightswitch http://msdn.com/vbasic http://msdn.com/vsto http://www.bethmassi.com
    Saturday, February 26, 2011 5:45 PM
  • Dear Beth:

     

    I've seen that, and think LS it is fantastic up to now.  I understand how to create a many-to-many.  However, the things I'm needing is:

    1) If I am in a screen with the join table in the child window, I can't make one of the fields not visible (I removed the check mark, but it still shows up

    2) If I try to add a new child from the join child it will only let me pick an existing child, I can't create a new one.

    3) I can not search for a child from the parent's search screen

     

    Sunday, February 27, 2011 2:20 AM
  • Hey Beth, thanks for all your great videos.

     

    Could you (or someone else out there) tell me if it's possible to create a Many-to-Many relationship in VB 2008 express?

    I think I'm doing it right...

     

    Using the authors example I have three tables...

     

    (Authors),  (AuthorsBooks),  (Books)

    Authors and Books both have auto increment primary keys called "RecordNo"

     

    The Junction table has auto increment primary key called "Record" and two more fields, Author_ID and Books_ID 

     

    I created a DataSet with these three tables and setup two, One to Many relations from Authors primary key "RecordNo" to Authors_ID

    and Books primary key "RecordNo" to Books_ID

    (making the junction table the Many side of both relations)

     

    I also right clicked on a field in the Junction table and selected "Edit key..." and added  both Author_ID and Books_ID

     

    Saved all that.  

     

    Then when I go to Data Sources and look at the DataSet I have the three tables but if I expand Authors the only relationship there is the junction table. And if I expand the Books table, the only relationship there is the junction table. So I have no way to tie them together. They both work as a one-to-many independently but not as a Many-to-Many

     

    Am I missing something?

     

     

    Thursday, March 10, 2011 1:31 AM
  • Hey Beth, thanks for all your great videos.

     

    Could you (or someone else out there) tell me if it's possible to create a Many-to-Many relationship in VB 2008 express?

     

    I think I'm doing it right...

     

    Using the authors example I have three tables...

     

    (Authors),  (AuthorsBooks),  (Books)

    Authors and Books both have auto increment primary keys called "RecordNo"

     

    The Junction table has auto increment primary key called "Record" and two more fields, Author_ID and Books_ID 

     

    I created a DataSet with these three tables and setup two, One to Many relations from Authors primary key "RecordNo" to Authors_ID

    and Books primary key "RecordNo" to Books_ID

    (making the junction table the Many side of both relations)

     

    I also right clicked on a field in the Junction table and selected "Edit key..." and added  both Author_ID and Books_ID

     

    Saved all that.  

     

    Then when I go to Data Sources and look at the DataSet I have the three tables but if I expand Authors the only relationship there is the junction table. And if I expand the Books table, the only relationship there is the junction table. So I have no way to tie them together. They both work as a one-to-many independently but not as a Many-to-Many

     

    Am I missing something?

     

     


    in SQL you should creat the middle / junction table with just the two columns, author_ID and Book_ID

    make both columns the PK so that a given author can appear many times but that author + Book is only able to be in the table one time.

    thats a classic M:M via a table that makes it via 2 1 to M relations

    Thursday, March 10, 2011 2:54 AM
  • Hey thanks for your response,

     

    I did make both Author_ID and Books_ID PK's

     

    " I also right clicked on a field in the Junction table and selected "Edit key..." and added  both Author_ID and Books_ID"

     

    But if I understand you correctly, you're saying delete the first PK and leave the  Author_ID and Books_ID PK's and it should work. But that does not work either, and if I'm not mistaken I can have additional fields in the junction table without causing any troubles.

     

    The problem as I see it is this...

     

    In the DataSet I would expect to see a table (Author or Books or even the junction table) that if expanded would have the other two relations in it. Like in Beth's one-to-many example of Orders and OrderDetails, you need to get the OrdersDetails from the relationship in the Orders table.

    Thursday, March 10, 2011 3:32 AM
  • KeySoft, does this article maybe help?

    http://blogs.msdn.com/b/lightswitch/archive/2010/12/16/how-to-create-a-many-to-many-relationship-andy-kung.aspx

     

    Regards

    Xander

    Thursday, March 10, 2011 3:39 AM
  • Hey thanks for your response,

     

    But no, I already read through that one.

    My problem I think relates to VB 2008 because of this...

     

    In the DataSet I would expect to see a table (Author or Books or even the junction table) that if expanded would have the other TWO relations in it.

     

    If I do not have the other two relations to drag from they are not all linked together.

     

      

     

    You can see what I mean here almost half way through. She gets the Orders from the Customers to get automatic filtering

     

    http://msdn.microsoft.com/en-us/vbasic/bb643827

     

    Thursday, March 10, 2011 3:55 AM
  •  

    Ok, here is a wild guess, but how about giving the PK columns and the FK columns the same names, eg.:

    Authors:

    - AuthorID

    - etc

    Books:

    - BookID

    - etc

    AuthorsBooks:

    - AuthorID

    - BookID

    Maybe, just maybe, LS somehow gets confused with the inconsistent column names? This is just a wild guess.
    Thursday, March 10, 2011 4:16 AM
  • Maybe, just maybe, LS somehow gets confused with the inconsistent column names? This is just a wild guess.


    I'm using VB 2008 not LS.

    I've done quite alot of reserch on that and the names should not matter because you just relate one to the other.

    A Many-to-Many relationship is, TWO One-to-Many relationships going to the same Junction table. I can do a One-to-Many relationships not problem so doing TWO One-to-Many relationships to the same Junction table should work if it's going to. I would think.   

    Thursday, March 10, 2011 4:21 AM
  • I'm using VB 2008 not LS.

    Sorry, I'm confused, this is the LS forum? You might be better off asking in the appropriate forum. Regards
    Thursday, March 10, 2011 4:29 AM
  • I'm using VB 2008 not LS.

    Sorry, I'm confused, this is the LS forum? You might be better off asking in the appropriate forum. Regards

    You're right I will do that.

     

    I just ended up here in my endless searching for Many-to-Many and saw that Beth Massi (the queen guru that would know) was here a couple weeks ago and I was actually asking in the form of... "Beth could you (or someone else out there) ... "

     

    I did not even realize I was on the "LS" forum, so I'll get out of here now but thanks for your help.  :)

      

    Thursday, March 10, 2011 4:48 AM
  • Hey KeySoft,

    You may want to check out this post:

    http://blogs.msdn.com/b/bethmassi/archive/2007/05/30/many-to-many-data-binding.aspx

    And try asking your question in this forum:
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/threads

    Cheers,

    -Beth


    Senior Program Manager, Visual Studio Community http://msdn.com/lightswitch http://msdn.com/vbasic http://msdn.com/vsto http://www.bethmassi.com
    Thursday, March 10, 2011 6:08 AM
  • Thanks Beth,

    That link looks great I'll go through it today. I knew you had the answer, but I did not realize I was plastering my VB question all over your LS fourm. My bad :)

    I'll check the forum I'm on next time. If I have any more questions I'll ask over there.

    But let me just say. You are by far the best speaker, and smartest teacher out there IMO and I'm sure you'll keep doing it, so thanks for "sharing the goodness"

    Thanks again KeySoft 

    Thursday, March 10, 2011 3:59 PM