none
Linq To Entities: how to order on column in "many to many" table RRS feed

  • Question

  • Hi;
    I have a Entity model which as many to many relational tables. In that table, I have a column for ordering number.

    From this model view, http://pascalc.nougen.com/stuffs/aspnet_linq_model3.png, I have relation between the testcase and testaction table, mapping is correct but my issue is to be able to order records based on a column which is not in the model.

    Here is the structure of the relational table:

    - testcase_id
    - testaction_id
    - ordernumber

    The 2 first columns are the primary key constrain to the 2 tables, how can I order my query based on the ordernumber column?
    At the moment, I get testactions of a testcase with:

    var testcase = (from tc in qasEntities.TestCases
       where tc.ID.Equals(testcaseIdValue) && tc.UserGroups_ID.Equals(groupIdValue)
       select tc).FirstOrDefault();
    
    testactionList = (from ta in testcase.TestActions
        where ta.IsDeleted.Equals(false) && ta.UserGroups_ID.Equals(groupIdValue) && ta.TestCases.Contains(testcase)
        select ta).ToList();
    I created a specific model to work on those relational tables so I thought; "Let's do a join on both entities"; but that's not allowed or I don't know how :)

    Thanks!



    Thursday, June 16, 2011 4:49 PM

Answers

  • Hi,

    When working with many-to-many relationships you actually doesn't have access to the table that connects your TestSuite to your TestCase, this connection table is treated as a association in entity framework, not an entity, and that's why you can't access it directly. That's the theory.

    Now to the practical. And here is what's I wonder what you have done. Normally, if you import a many-to-many relational table that contains more columns than the FK columns (which also are PKs) to the other tables, EF generates a seperate entity for this, it doesn't create the relational table as an association. So, how have you managed this? Did you add your ordernumber column to the relational table after you created your entity model first time? If so, you won't be able to get access to the OrderNumber column in your relational table.

    The simplest way to fix this, is to recreate your entities in your edmx:
    - delete the entities TestSuite and TestCase and the relation between them
    - on the question about deleting unmapped tables and view, answer yes.
    - update your datamodel and add the three tables again

    Now, you should have a entity model that corresponds to your store. The problem is however (as you will experience) is that querying will be a bit harder to work with.

    Hope this helps!


    --Rune
    • Marked as answer by Scal1050 Saturday, June 18, 2011 4:12 PM
    Thursday, June 16, 2011 8:58 PM

All replies

  • On 6/16/2011 12:49 PM, Scal1050 wrote:
    > Hi;
    > I have a Entity model which as many to many relational tables. In that
    > table, I have a column for ordering number.
    >
    >  From this model view,
    > between the testcase and testaction table, mapping is correct but my
    > issue is to be able to order records based on a column which is not in
    > the model.
    >
     
    What do you mean not in the model or is that you can't see the property
    in the object for the column? What EF version are you using?
     
    > Here is the structure of the relational table:
    >
    > - testcase_id
    > - testaction_id
    > - ordernumber
    >
    > The 2 first columns are the primary key constrain to the 2 tables, how
    > can I order my query based on the ordernumber column?
    > At the moment, I get testactions of a testcase with:
    >
    > var testcase = (from tc in qasEntities.TestCases
    >     where tc.ID.Equals(testcaseIdValue)&&  tc.UserGroups_ID.Equals(groupIdValue)
    >     select tc).FirstOrDefault();
    >
    > testactionList = (from ta in testcase.TestActions
    >      where ta.IsDeleted.Equals(false)&&  ta.UserGroups_ID.Equals(groupIdValue)&&  ta.TestCases.Contains(testcase)
    >      select ta).ToList();
    >
    > I created a specific model to work on those relational tables so I
    > thought; "Let's do a join on both entities"; but that's not allowed or I
    > don't know how :)
     
    You can use an 'include',  and after the ToList() to get the parent and
    the children  with the 'include', you can order the results with another
    query on the results.
     
     
    Thursday, June 16, 2011 5:13 PM
  • Hi;

    I mean it's not shown either in the model nor in Intelisence when I access a testcase or testaction through the model object reference

    I use .Net 4, but no clue which version of L2E it uses, how can I check?

    Hum, never heard of the "include" you're talking about, I'll go do some search on that already.

    Thursday, June 16, 2011 6:14 PM
  • On 6/16/2011 2:14 PM, Scal1050 wrote:
    > Hi;
    >
    > I mean it's not shown either in the model nor in Intelisence when I
    > access a testcase or testaction through the model object reference
    >
    > I use .Net 4, but no clue which version of L2E it uses, how can I check?
     
    Well, there should have been a setting when you first created the model
    to 'show all the properties of the entity', both primary and foreign
    keys of an entity that are normal hidden. The forenign key property of
    an entity will be hidden unless you tell EF to expose them during model
    generation.
    >
    > Hum, never heard of the "include" you're talking about, I'll go do some
    > search on that already.
    >
     
    var results = (from a in db.Table.Include("child").where(a => a.ID == 1)
    select a).Tolist();
     
    var resluts1 = (from a in parent.child.where(a => a.name ==
    "test").orderby(a => a.name);
     
    Thursday, June 16, 2011 6:56 PM
  • Hi,

    When working with many-to-many relationships you actually doesn't have access to the table that connects your TestSuite to your TestCase, this connection table is treated as a association in entity framework, not an entity, and that's why you can't access it directly. That's the theory.

    Now to the practical. And here is what's I wonder what you have done. Normally, if you import a many-to-many relational table that contains more columns than the FK columns (which also are PKs) to the other tables, EF generates a seperate entity for this, it doesn't create the relational table as an association. So, how have you managed this? Did you add your ordernumber column to the relational table after you created your entity model first time? If so, you won't be able to get access to the OrderNumber column in your relational table.

    The simplest way to fix this, is to recreate your entities in your edmx:
    - delete the entities TestSuite and TestCase and the relation between them
    - on the question about deleting unmapped tables and view, answer yes.
    - update your datamodel and add the three tables again

    Now, you should have a entity model that corresponds to your store. The problem is however (as you will experience) is that querying will be a bit harder to work with.

    Hope this helps!


    --Rune
    • Marked as answer by Scal1050 Saturday, June 18, 2011 4:12 PM
    Thursday, June 16, 2011 8:58 PM
  • Hi Rune;
    That was it, the relational table was updated after the edmx file was created.

    If I update it after deleting related tables, the relational table is indeed shown as a specific entity. Down side is that I loose the automatic relation, correct?

    I mean, before updating the model I used to be able to do some thing like: testactions = testcase.testactions.where(testcase.id.equal(1))

    This would give me back all testactions of a specific testcase; after the model update, I have to do joins on the tables myself like:

    testactions = (from ta in testactions
    join tc2ta in testcasesTotestactions on ta.ID equals tc2ta.testaction_ID
    where tc2ta.testcase_ID.equal(1)
    select ta)

    Correct? I guess that's the downside of having an additional column in a relational table?

    Thanks

    Friday, June 17, 2011 9:01 AM
  • Hi,

    Yes, it's a bit more complicated to work with, but you shouldn't need to manually join yourself. What you could do is something like the following:

    var testactions = (rom tc2ta in testcasesTOtestactions where tc2ta.testcase_ID == 1 select tc2ta.TestAction).ToList();

    As you see the difference is that your basetable is the relation table, not the testaction.

     


    --Rune
    Friday, June 17, 2011 1:32 PM
  • Thanks Rune;
    I will try to "re-factor" my project's and edmx files this weekend and see how it goes.
    Friday, June 17, 2011 3:24 PM
  • Rune;

    I've managed to update my project with the new Entity model, thanks again for the help and to others who contributed too.

    I'm now wondering how I can do an ordering on this query:

    (from tc2ta in qasEntities.TestCasesToTestActions where tc2ta.TestCase.ID.Equals(objectId) select tc2ta.TestAction)
    

    Where the order column is in "TestCasesToTestActions" and called "OrderNumber"

    Thanks!

    Saturday, June 18, 2011 7:05 PM
  • Hi,

    That should be done by simply add orderby to your query like this:

    (from tc2ta in qasEntities.TestCasesToTestActions where tc2ta.TestCase.ID.Equals(objectId) orderby tc2ta.OrderNumber select tc2ta.TestAction)
    

     

     


    --Rune
    Sunday, June 19, 2011 7:06 AM
  • Thanks Rune!
    Sunday, June 19, 2011 12:25 PM