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.
thefridgeVFAAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi thefridgeVFA;

This will get you closer to what you want or what you need.

Fernando
Dim qSupportMessagesNew = From m In db.tblSupports _
                          Let open = (From o In m.tblSupportReplies _
                                      Order By o.ZuluDateTime Descending) _
                          Where (open.Count > 0 And open.FirstOrDefault.Status = "Open") Or (Not m.tblSupportReplies.Any())

Open in new window

0
 
Fernando SotoRetiredCommented:
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

0
 
thefridgeVFAAuthor Commented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Fernando SotoRetiredCommented:
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

0
 
thefridgeVFAAuthor Commented:
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
 
0
 
thefridgeVFAAuthor Commented:
FernandoSoto,
I forgot to mention that I hope I am not missing something obvious.  If so, my apologies.
0
 
thefridgeVFAAuthor Commented:
All hail FernandoSoto !
0
 
Fernando SotoRetiredCommented:
Not a problem glad I was able to help.  ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.