Link to home
Start Free TrialLog in
Avatar of thefridgeVFA
thefridgeVFA

asked on

LINQ to SQL using VB, parent child tables, results filtered by value of first sorted child

Greetings Experts,

How can I use Visual Basic and LINQ to query a Parent Table for just those rows which have Child Table rows with a certain value in a Child Table column OR no Child Table rows at all?

In my scenario, I am logging support messages to a SQL table "tblSupport" (they arrive via a web service from a thick client) at my web site.  There are five columns in this Parent table;
ID (BigInt, Primary Key)
ZuluDateTime (DateTime)
AccountNumber (VarChar(MAX))
Type (VarChar(MAX))
Message (VarChar(MAX))

I log my replies (there may be several) to these messages in a second table "tblSupportReply".  This Child table also has five columns;
ID (BigInt, Primary Key)
ZuluDateTime (DateTime)
Status (VarChar(MAX), contains either "open" or "closed")
IdSupport (BigInt, FK to tblSupport.ID)
ReplyMessage (VarChar(MAX))

I have used the O/R Designer and can query the Parent Table for the "new" (no children) messages as follows;
        'LINQ - get records from tblSupport with no replies (parents without children)
        Dim db As New MyDatabaseDataContext()
        Dim qSupportMessagesNew = From m In db.tblSupports _
                               Where Not m.tblSupportReplies.Any()

How can I also return the Parent rows which have their newest Child row (sort by tblSupportReply.ZuluDateTime) with tblSupportReply.Status = "open"?

My goal is to bind the result to a Gridview, which would list all messages which need my attention (either new or open).

Thank you for taking the time to review this question.
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Hi thefridgeVFA;

Try your query like this.

Fernando
Dim qSupportMessagesNew = From m In db.tblSupports _
                          Where (m.tblSupportReplies.Any(Function(s) s.Status = "Open") Or (Not m.tblSupportReplies.Any()))

Open in new window

Avatar of thefridgeVFA
thefridgeVFA

ASKER

Thank you for the quick reply.  I have one concern;

If there are multiple rows in the child table for a certain parent table row, and the newest of those rows (latest DateTime in tblSupportReplies.ZuluDateTime ) in the Child Table has tblSupportReply.Status = "closed", I would not want it returned in the query.

I presume the solution requires usage of "OrderByDescending" on the Status property and "First" on the sequence, but I'm not having any luck with it.  How do we further limit the first half of your OR statement above to just those parents whose newest child has Status="open"?

Thanks again.
Hi thefridgeVFA;

To the first part of your last post the Where clause will only let through the "Open" ones.

To the second part of your last post, Sorry I missed the ordering part of the question. I believe this will do what you need.

Fernando
Dim qSupportMessagesNew = From m In db.tblSupports _
                          Where (m.tblSupportReplies.Any(Function(s) s.Status = "Open") Or (Not m.tblSupportReplies.Any())) _
                          From r In m.tblSupportReplies _
                          Order By r.ZuluDateTime Descending

Open in new window

FernandoSoto,
Thanks again for your quick reply.  Unfortunately it did not work for me.  Here is the sample data I am using in my SQL tables;

"tblSupport"
ID  ZuluDateTime                   AccountNumber  Type                             Message
1   12/10/2009 10:18:59 PM  101               Problem                         It is broke
2   12/10/2009 10:49:19 PM  101                     Enhancement request   Make it better
3   12/10/2009 10:50:22 PM  102                     Question for Support    Do you like me?
4   12/11/2009 10:50:45 PM  101                     Report a problem          My cat is sick

"tblSupportReply"
ID  ZuluDateTime                    Status        IdSupport    ReplyMessage
1   12/12/2009 9:59:57 PM     Closed       2                  OK
2   12/12/2009 9:58:57 PM     Open         1                  We trying to fix it
3   12/13/2009 9:22:47 PM     Closed       1                  It fixed

The query should only return rows 3 & 4.
Row 1 has two children, the newest of which has Status="Closed".
Row 2 has one child and it has Status="Closed".

Your first code snippet returned Rows 1, 3 & 4.  Row 1 should be excluded, since its newest child is Status="Closed".

Your second snippet produced a sequence which did not work with this loop (it required "m.m.ID.ToString");
        For Each m In qSupportMessagesNew
            Diagnostics.Debug.Print("  ID = " & m.ID.ToString) 'm.ID.ToString) 'TODO: delete this line
        Next
 
FernandoSoto,
I forgot to mention that I hope I am not missing something obvious.  If so, my apologies.
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All hail FernandoSoto !
Not a problem glad I was able to help.  ;=)