none
LINQ problem, joining 2 tables and an id RRS feed

  • Question

  • Hi all,

    I have a problem with a query.

    The scenario is this: I got a table (I'll call it t1) with exercises and an id for equipment. I also got a table (I'll call it t2) for linking an equipment to an exercise. I want a list with every exercise that isn't linked with my id in t2 (edited).

    Example: My id is 4

    In t1 I have 10 exercises => 1 to 10

    In t2 I have a column exerciseId and equipmentId

    Exercise Equipment

    --------- ------------

    1 4

    2 4

    3 4

    8 4

    So 4,5,6,7,9 and 10 isn't associated with equipment 4 and I want to have those in my var query.

    At the moment I got this:

     

    var query = from t1 in db.exercise

                            where (from t2 in db.exerciseequipment

                                    where t2.equipmentid == equipmentid

                                    select t2.exerciseid).Contains(t1.exerciseid)

                            select t1;

     

    This show 0 result, while t2 is empty.

    Thanks for helping in advance

    • Edited by KennyPeeters1986 Wednesday, May 11, 2011 3:47 PM incomplete information
    Wednesday, May 11, 2011 3:38 PM

Answers

  • I found a solution, but it's probably not a good solution:

    var oef = (from t1 in db.oefening

                             select t1).ToList();

                var oeftoe = (from t1 in db.oefeningtoestel

                           where t1.toestelId == toestelId

                           select t1).ToList();

                for (int teller = 0; teller < oef.Count(); teller++)

                {

                    oefening oefening = oef[teller];

                    for (int teller2 = 0; teller2 < oeftoe.Count(); teller2++)

                    {  

                        oefeningtoestel oefeningtoestel = oeftoe[teller2];

                        if (oefening.oefeningId == oefeningtoestel.oefeningId && oefeningtoestel.toestelId == toestelId)

                        {

                            oef.RemoveAt(teller);

                        }

                    }

                }

    Wednesday, May 11, 2011 4:11 PM

All replies

  • I found a solution, but it's probably not a good solution:

    var oef = (from t1 in db.oefening

                             select t1).ToList();

                var oeftoe = (from t1 in db.oefeningtoestel

                           where t1.toestelId == toestelId

                           select t1).ToList();

                for (int teller = 0; teller < oef.Count(); teller++)

                {

                    oefening oefening = oef[teller];

                    for (int teller2 = 0; teller2 < oeftoe.Count(); teller2++)

                    {  

                        oefeningtoestel oefeningtoestel = oeftoe[teller2];

                        if (oefening.oefeningId == oefeningtoestel.oefeningId && oefeningtoestel.toestelId == toestelId)

                        {

                            oef.RemoveAt(teller);

                        }

                    }

                }

    Wednesday, May 11, 2011 4:11 PM
  • Thank you Kenny for sharing your solution to us.

     

    Good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 17, 2011 7:17 AM
    Moderator