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.ZuluDateTi me) 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.
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.ZuluDateTi
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.
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.ZuluDate Time ) 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.
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.ZuluDate
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
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
ASKER
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
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
ASKER
FernandoSoto,
I forgot to mention that I hope I am not missing something obvious. If so, my apologies.
I forgot to mention that I hope I am not missing something obvious. If so, my apologies.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All hail FernandoSoto !
Not a problem glad I was able to help. ;=)
Try your query like this.
Fernando
Open in new window