none
Issue with piece wise created Linq query and foreign keys RRS feed

  • Question

  • Hi!

    The database tables:

    Person [Id (PK), Name, etc...], Appointment [Id (PK), Company, personId (FK), startTime, etc...]

    So there is a one to many relationship between Person and Appointment

    The c# code
    var query = from p in database.Persons select p;
    if(company != null)
       query = from p in query
                   from a in p.appointments
                   where a.company == company
                   select p;

    if(startTime != null)
       query = from p in query
                   from q in p.appointments
                   where a.startTime == startTime
                   select p;

    Ok, so if one and only one of the above if statements are true it works like I intend to do.

    But if both are true it generates a query where it creates two aliases for the appointments table, and for each table reference in the query only one of the conditions are applied, so the result generates a crossproduct between the results of each appointment table alias (if i understand it right).

    But ofcourse what I want to do in the second if block (if both ifs are true) is to just add another condition on the appointment table. That is, the equivalent of:
       query = from p in query
                   from q in p.appointments
                   where a.startTime == startTime && a.company == company
                   select p;

    How do I write this in the above logic?

    BR



    Wednesday, April 1, 2009 1:39 PM

All replies

  • I believe this is what you want:
    var query = from p in query
                   from a in p.appointments
                   select p;
    
    
    
    if (startTime != null)
       query = query.Where(p => p.startTime == startTime);
    
    if (company != null)
       query = query.Where(p => p.company == company);
    
    

    The two "Where" clauses are anded together....

    Please mark this as a solution if it helps you - Thanks.
    • Proposed as answer by Narayan Sainaney Wednesday, April 1, 2009 8:38 PM
    • Unproposed as answer by Harry.Bz Thursday, April 2, 2009 7:11 AM
    Wednesday, April 1, 2009 8:38 PM
  • Thank you Narayan, but that doesn't quite do it.

    The problem I have arises from the fact that the query filter I want to conditially add to the where clause are applied to the related table Appointment, not the Person table like you assume in your reply.

    I.e.
    The code:
    p => p.startTime == startTime
    Isn't relevant because (Person) p doesn't contain startTime, (Appointment) a contains startTime.

    BR
    Thursday, April 2, 2009 7:34 AM
  • Ah...in that case:

    var query = from p in query
                   from a in p.appointments
                   select a;

    if (startTime != null)
       query = query.Where(a => a.startTime == startTime);

    if (company != null)
       query = query.Where(a => a.company == company);

    var result = query.Select(a => a.Person).ToList();

     

    You can use the relationship back from Appointments to get the Person. Sorry for the delay...I got a little side tracked :)


    Saturday, August 27, 2011 12:20 AM