• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

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.
0
thefridgeVFA
Asked:
thefridgeVFA
  • 4
  • 4
1 Solution
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
Fernando SotoCommented:
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
 
thefridgeVFAAuthor Commented:
All hail FernandoSoto !
0
 
Fernando SotoCommented:
Not a problem glad I was able to help.  ;=)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now