Solved

Linq query JOIN problem

Posted on 2014-10-09
10
226 Views
Last Modified: 2016-02-15
Hi Guys,

Attached is a sample of a very basic schema. I have written the below code in VB and am looking to be able to create a single joined datatable using LINQ.

I am not too good or familiar with link but I do need it for this particular problem. My code is this at the moment

 
        Dim myListOfAnonymousTypes = (From request In ds.Tables("request") Join employee In ds.Tables("employee") On request("request_Id") Equals employee("request_Id") Join status In ds.Tables("status") On request("request_Id") Equals status("request_Id") Join type In ds.Tables("type") On request("request_Id") Equals type("request_Id") Join amount In ds.Tables("amount") On request("request_Id") Equals amount("request_Id") Join notes In ds.Tables("notes") On request("request_Id") Equals notes("request_Id") Join note In ds.Tables("note") On notes("notes_ID") Equals note("notes_ID") Select ID = request("id"), time_start_date = request("start"), time_end_date = request(2), time_created_date = request("end"), timeoff_id = request("request_Id"), employee_name = employee("employee_text"), EEID = employee("id"), time_status = status("status_text"), time_type = type("type_text"), time_days_off = amount("amount_text"), note = note("note_text")).ToList

        Me.GridControl1.DataSource = myListOfAnonymousTypes

Open in new window


This works fine however as you will note from the schema not everyone needs to have a comment and therefore due to my current join, only records for those who have comments show up in the results. AS you would expect I need to do a left outer join between notes and note datatables.

Could someone please show me how I can achieve this as I have looked at Group Join but can't get it to work.

Thanks for your help
schema.png
0
Comment
Question by:databarracks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40370914
Hi databarracks;

Which Linq technology are you using Linq to SQL, Linq to Entity Framework, Linq to DataTable, Linq to Object?

If using one of the following two Linq to SQL and Linq to Entity Framework any table that does not contain a Primary Key will not be able to participate in CRUD operations.

To your statement, "only records for those who have comments show up in the results", I can not find any field defined in the schema or in your query. Is something missing?

Reposting your query so that others can see it without scrolling .

Dim myListOfAnonymousTypes = _
    (From request In ds.Tables("request") _
      Join employee In ds.Tables("employee") _
        On request("request_Id") Equals employee("request_Id") _
      Join status In ds.Tables("status") _
        On request("request_Id") Equals status("request_Id") _
      Join type In ds.Tables("type") _
        On request("request_Id") Equals type("request_Id") _
      Join amount In ds.Tables("amount") _
        On request("request_Id") Equals amount("request_Id") _
      Join notes In ds.Tables("notes") _
        On request("request_Id") Equals notes("request_Id") _
      Join note In ds.Tables("note") _
        On notes("notes_ID") Equals note("notes_ID") _
      Select _
        ID = request("id"), _
        time_start_date = request("start"), _
        time_end_date = request(2), _
        time_created_date = request("end"), _
        timeoff_id = request("request_Id"), _
        employee_name = employee("employee_text"), _
        EEID = employee("id"), _
        time_status = status("status_text"), _
        time_type = type("type_text"), _
        time_days_off = amount("amount_text"), _
        note = note("note_text") _
    ).ToList

Open in new window

0
 

Author Comment

by:databarracks
ID: 40371044
Hi I am using Linq to Datatable?

I don't understand what you mean by "I can not find any field defined in the schema or in your query."?
0
 

Author Comment

by:databarracks
ID: 40371046
To make life easier please just show me how I can to a left outer join on my two notes and note datatables? Surely the image speaks for itself?????
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 34

Expert Comment

by:it_saige
ID: 40371281
I don't have your dataset so I made up some values.  Is this what you might be looking for:
Public Class Request
	Private fEmployeeID As Integer
	Public Property EmployeeID() As Integer
		Get
			Return fEmployeeID
		End Get
		Set(ByVal value As Integer)
			fEmployeeID = value
		End Set
	End Property

	Private fStart As DateTime
	Public Property Start() As DateTime
		Get
			Return fStart
		End Get
		Set(ByVal value As DateTime)
			fStart = value
		End Set
	End Property

	Private fEnd As DateTime
	Public Property [End]() As DateTime
		Get
			Return fEnd
		End Get
		Set(ByVal value As DateTime)
			fEnd = value
		End Set
	End Property

	Private fCreated As DateTime
	Public Property Created() As DateTime
		Get
			Return fCreated
		End Get
		Set(ByVal value As DateTime)
			fCreated = value
		End Set
	End Property

	Private fID As Integer
	Public Property ID() As Integer
		Get
			Return fID
		End Get
		Set(ByVal value As Integer)
			fID = value
		End Set
	End Property
End Class

Public Class Employee
	Private fID As Integer
	Public Property ID() As Integer
		Get
			Return fID
		End Get
		Set(ByVal value As Integer)
			fID = value
		End Set
	End Property

	Private fText As String
	Public Property Text() As String
		Get
			Return fText
		End Get
		Set(ByVal value As String)
			fText = value
		End Set
	End Property

	Private fRequestID As Integer
	Public Property RequestID() As Integer
		Get
			Return fRequestID
		End Get
		Set(ByVal value As Integer)
			fRequestID = value
		End Set
	End Property
End Class

Public Class Status
	Private fLastChanged As DateTime
	Public Property LastChanged() As DateTime
		Get
			Return fLastChanged
		End Get
		Set(ByVal value As DateTime)
			fLastChanged = value
		End Set
	End Property

	Private fChangedBy As Integer
	Public Property ChangedBy() As Integer
		Get
			Return fChangedBy
		End Get
		Set(ByVal value As Integer)
			fChangedBy = value
		End Set
	End Property

	Private fText As String
	Public Property Text() As String
		Get
			Return fText
		End Get
		Set(ByVal value As String)
			fText = value
		End Set
	End Property

	Private fRequestID As Integer
	Public Property RequestID() As Integer
		Get
			Return fRequestID
		End Get
		Set(ByVal value As Integer)
			fRequestID = value
		End Set
	End Property
End Class

Public Class Type
	Private fID As Integer
	Public Property ID() As Integer
		Get
			Return fID
		End Get
		Set(ByVal value As Integer)
			fID = value
		End Set
	End Property

	Private fText As String
	Public Property Text() As String
		Get
			Return fText
		End Get
		Set(ByVal value As String)
			fText = value
		End Set
	End Property

	Private fRequestID As Integer
	Public Property RequestID() As Integer
		Get
			Return fRequestID
		End Get
		Set(ByVal value As Integer)
			fRequestID = value
		End Set
	End Property
End Class

Public Class Amount
	Private fUnit As Integer
	Public Property Unit() As Integer
		Get
			Return fUnit
		End Get
		Set(ByVal value As Integer)
			fUnit = value
		End Set
	End Property

	Private fText As String
	Public Property Text() As String
		Get
			Return fText
		End Get
		Set(ByVal value As String)
			fText = value
		End Set
	End Property

	Private fRequestID As Integer
	Public Property RequestID() As Integer
		Get
			Return fRequestID
		End Get
		Set(ByVal value As Integer)
			fRequestID = value
		End Set
	End Property
End Class

Public Class NoteLink
	Private fID As Integer
	Public Property ID() As Integer
		Get
			Return fID
		End Get
		Set(ByVal value As Integer)
			fID = value
		End Set
	End Property

	Private fRequestID As Integer
	Public Property RequestID() As Integer
		Get
			Return fRequestID
		End Get
		Set(ByVal value As Integer)
			fRequestID = value
		End Set
	End Property
End Class

Public Class Note
	Private fFrom As String
	Public Property From() As String
		Get
			Return fFrom
		End Get
		Set(ByVal value As String)
			fFrom = value
		End Set
	End Property

	Private fText As String
	Public Property Text() As String
		Get
			Return fText
		End Get
		Set(ByVal value As String)
			fText = value
		End Set
	End Property

	Private fID As Integer
	Public Property ID() As Integer
		Get
			Return fID
		End Get
		Set(ByVal value As Integer)
			fID = value
		End Set
	End Property
End Class

Module Module1
	Sub Main()
		Dim requests As New List(Of Request)
		requests.Add(New Request() With {.EmployeeID = 1, .Start = DateTime.Now, .End = DateTime.Now, .Created = DateTime.Now, .ID = 1})
		requests.Add(New Request() With {.EmployeeID = 2, .Start = DateTime.Now, .End = DateTime.Now, .Created = DateTime.Now, .ID = 2})
		requests.Add(New Request() With {.EmployeeID = 3, .Start = DateTime.Now, .End = DateTime.Now, .Created = DateTime.Now, .ID = 3})
		requests.Add(New Request() With {.EmployeeID = 1, .Start = DateTime.Now, .End = DateTime.Now, .Created = DateTime.Now, .ID = 4})
		requests.Add(New Request() With {.EmployeeID = 2, .Start = DateTime.Now, .End = DateTime.Now, .Created = DateTime.Now, .ID = 5})

		Dim employees As New List(Of Employee)
		employees.Add(New Employee() With {.ID = 1, .Text = "Joe"})
		employees.Add(New Employee() With {.ID = 2, .Text = "Jane"})
		employees.Add(New Employee() With {.ID = 3, .Text = "Larry"})

		Dim statuses As New List(Of Status)
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 1, .RequestID = 1, .Text = "Status 1"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 2, .RequestID = 2, .Text = "Status 2"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 3, .RequestID = 3, .Text = "Status 3"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 3, .RequestID = 4, .Text = "Status 4"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 2, .RequestID = 5, .Text = "Status 5"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 1, .RequestID = 5, .Text = "Status 6"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 1, .RequestID = 4, .Text = "Status 7"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 2, .RequestID = 3, .Text = "Status 8"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 3, .RequestID = 2, .Text = "Status 9"})
		statuses.Add(New Status() With {.LastChanged = DateTime.Now, .ChangedBy = 3, .RequestID = 1, .Text = "Status 10"})

		Dim types As New List(Of Type)
		types.Add(New Type() With {.ID = 1, .Text = "Test 1", .RequestID = 1})
		types.Add(New Type() With {.ID = 2, .Text = "Test 2", .RequestID = 2})
		types.Add(New Type() With {.ID = 3, .Text = "Test 3", .RequestID = 3})
		types.Add(New Type() With {.ID = 4, .Text = "Test 4", .RequestID = 4})
		types.Add(New Type() With {.ID = 5, .Text = "Test 5", .RequestID = 5})
		types.Add(New Type() With {.ID = 6, .Text = "Test 6", .RequestID = 5})
		types.Add(New Type() With {.ID = 7, .Text = "Test 7", .RequestID = 4})
		types.Add(New Type() With {.ID = 8, .Text = "Test 8", .RequestID = 3})
		types.Add(New Type() With {.ID = 9, .Text = "Test 9", .RequestID = 2})
		types.Add(New Type() With {.ID = 10, .Text = "Test 10", .RequestID = 1})

		Dim amounts As New List(Of Amount)
		amounts.Add(New Amount() With {.Unit = 10, .Text = "10", .RequestID = 1})
		amounts.Add(New Amount() With {.Unit = 9, .Text = "9", .RequestID = 2})
		amounts.Add(New Amount() With {.Unit = 8, .Text = "8", .RequestID = 3})
		amounts.Add(New Amount() With {.Unit = 7, .Text = "7", .RequestID = 4})
		amounts.Add(New Amount() With {.Unit = 6, .Text = "6", .RequestID = 5})
		amounts.Add(New Amount() With {.Unit = 5, .Text = "5", .RequestID = 1})
		amounts.Add(New Amount() With {.Unit = 4, .Text = "4", .RequestID = 3})
		amounts.Add(New Amount() With {.Unit = 3, .Text = "3", .RequestID = 5})
		amounts.Add(New Amount() With {.Unit = 2, .Text = "2", .RequestID = 2})
		amounts.Add(New Amount() With {.Unit = 1, .Text = "1", .RequestID = 4})
		amounts.Add(New Amount() With {.Unit = 1, .Text = "1", .RequestID = 5})
		amounts.Add(New Amount() With {.Unit = 3, .Text = "3", .RequestID = 4})
		amounts.Add(New Amount() With {.Unit = 5, .Text = "5", .RequestID = 3})
		amounts.Add(New Amount() With {.Unit = 7, .Text = "7", .RequestID = 2})
		amounts.Add(New Amount() With {.Unit = 9, .Text = "9", .RequestID = 1})
		amounts.Add(New Amount() With {.Unit = 2, .Text = "2", .RequestID = 4})
		amounts.Add(New Amount() With {.Unit = 4, .Text = "4", .RequestID = 2})
		amounts.Add(New Amount() With {.Unit = 6, .Text = "6", .RequestID = 5})
		amounts.Add(New Amount() With {.Unit = 8, .Text = "8", .RequestID = 3})
		amounts.Add(New Amount() With {.Unit = 10, .Text = "10", .RequestID = 1})

		Dim notes As New List(Of Note)
		notes.Add(New Note() With {.ID = 1, .From = "Larry", .Text = "Blah Blah Joe"})
		notes.Add(New Note() With {.ID = 2, .From = "Joe", .Text = "Blah Blah Jane"})
		notes.Add(New Note() With {.ID = 3, .From = "Jane", .Text = "Blah Blah Larry"})
		notes.Add(New Note() With {.ID = 4, .From = "Jane", .Text = "Joe Blah Blah"})
		notes.Add(New Note() With {.ID = 5, .From = "Joe", .Text = "Larry Blah Blah"})
		notes.Add(New Note() With {.ID = 6, .From = "Larry", .Text = "Jane Blah Blah"})
		notes.Add(New Note() With {.ID = 7, .From = "Joe", .Text = "Jane Blah Blah Larry"})
		notes.Add(New Note() With {.ID = 8, .From = "Larry", .Text = "Joe Blah Blah Jane"})
		notes.Add(New Note() With {.ID = 9, .From = "Jane", .Text = "Larry Blah Blah Joe"})
		notes.Add(New Note() With {.ID = 10, .From = "Paul", .Text = "Larry Jane Joe Fired"})

		Dim noteLinks As New List(Of NoteLink)
		noteLinks.Add(New NoteLink() With {.ID = 1, .RequestID = 5})
		noteLinks.Add(New NoteLink() With {.ID = 2, .RequestID = 3})
		noteLinks.Add(New NoteLink() With {.ID = 3, .RequestID = 1})
		noteLinks.Add(New NoteLink() With {.ID = 4, .RequestID = 2})
		noteLinks.Add(New NoteLink() With {.ID = 5, .RequestID = 4})
		noteLinks.Add(New NoteLink() With {.ID = 6, .RequestID = 4})
		noteLinks.Add(New NoteLink() With {.ID = 7, .RequestID = 2})
		noteLinks.Add(New NoteLink() With {.ID = 8, .RequestID = 1})
		noteLinks.Add(New NoteLink() With {.ID = 9, .RequestID = 3})
		noteLinks.Add(New NoteLink() With {.ID = 10, .RequestID = 5})

		Dim myListOfAnonymousTypes = (From request In requests _
		  Join employee In employees On request.EmployeeID Equals employee.ID _
		  Join status In statuses On request.ID Equals status.RequestID _
		  Join type In types On request.ID Equals type.RequestID _
		  Join amount In amounts On request.ID Equals amount.RequestID _
		  Join link In noteLinks On request.ID Equals link.RequestID _
		  From noteLink In noteLinks _
		  Join note In notes On noteLink.ID Equals note.ID _
		  Select _
		  ID = request.EmployeeID, _
		  Start = request.Start, _
		  [End] = request.End, _
		  Created = request.Created, _
		  TimeOffID = request.ID, _
		  EmployeeID = employee.ID, _
		  StatusText = status.Text, _
		  TypeText = type.Text, _
		  AmountText = amount.Text, _
		  NoteText = note.Text).ToList()

		Console.WriteLine("Request ID | Start | End | Created | TimeOffID | EmployeeID | Status | Type | Amount | Notes")
		For Each item In myListOfAnonymousTypes
			Console.WriteLine(String.Format("{0} | {1} | {2} | {3} | {4} | {5} | {6} | {7} | {8} | {9}", item.ID, item.Start.TimeOfDay, item.End.TimeOfDay, item.Created.Date, item.TimeOffID, item.EmployeeID, item.StatusText, item.TypeText, item.AmountText, item.NoteText))
		Next

		Console.ReadLine()
	End Sub
End Module

Open in new window


This is my linq statement:
		Dim myListOfAnonymousTypes = (From request In requests _
		  Join employee In employees On request.EmployeeID Equals employee.ID _
		  Join status In statuses On request.ID Equals status.RequestID _
		  Join type In types On request.ID Equals type.RequestID _
		  Join amount In amounts On request.ID Equals amount.RequestID _
		  Join link In noteLinks On request.ID Equals link.RequestID _
		  From noteLink In noteLinks _
		  Join note In notes On noteLink.ID Equals note.ID _
		  Select _
		  ID = request.EmployeeID, _
		  Start = request.Start, _
		  [End] = request.End, _
		  Created = request.Created, _
		  TimeOffID = request.ID, _
		  EmployeeID = employee.ID, _
		  StatusText = status.Text, _
		  TypeText = type.Text, _
		  AmountText = amount.Text, _
		  NoteText = note.Text).ToList()

Open in new window


-saige-
0
 
LVL 34

Expert Comment

by:it_saige
ID: 40371396
Actually this is what you are looking for:
		Dim myListOfAnonymousTypes = (From request In requests _
		  Join employee In employees On request.RequestID Equals employee.RequestID _
		  Join status In statuses On request.RequestID Equals status.RequestID _
		  Join type In types On request.RequestID Equals type.RequestID _
		  Join amount In amounts On request.RequestID Equals amount.RequestID _
		  From link In noteLinks _
		  Join note In notes On link.ID Equals note.ID Where request.RequestID.Equals(link.RequestID) _
		  Select _
		  ID = request.RequestID, _
		  Start = request.Start, _
		  [End] = request.End, _
		  Created = request.Created, _
		  Employee = employee.Text, _
		  StatusText = status.Text, _
		  TypeText = type.Text, _
		  AmountText = amount.Text, _
		  NoteText = note.Text).ToList()

Open in new window


So your linq statement would look like such:
		Dim myListOfAnonymousTypes = (From request In ds.Tables("request") _
				Join employee In ds.Tables("employee") On request("request_Id") Equals employee("request_Id") _
				Join status In ds.Tables("status") On request("request_Id") Equals status("request_Id") _
				Join type In ds.Tables("type") On request("request_Id") Equals type("request_Id") _
				Join amount In ds.Tables("amount") On request("request_Id") Equals amount("request_Id") _
				From notes In ds.Tables("notes") _
				Join note In ds.Tables("note") On notes("notes_id") Equals note("notes_id") _
				Where request("request_Id").Equals(notes("request_Id")) _
				Select _
				ID = request("id"), _
				time_start_date = request("start"), _
				time_end_date = request(2), _
				time_created_date = request("end"), _
				timeoff_id = request("request_Id"), _
				employee_name = employee("employee_text"), _
				EEID = employee("id"), _
				time_status = status("status_text"), _
				time_type = type("type_text"), _
				time_days_off = amount("amount_text"), _
				note = note("note_text") _
				).ToList()

Open in new window


-saige-
0
 

Author Comment

by:databarracks
ID: 40372396
Hi Saige,

Thank you for sending your code, however your code won't work as there is no left outer join being used. If you look at my schema image that I originally attached you will see that the main table is the request table. All requests will have a transaction in the notes datatable, however not all notes will have transactions in the note table.
0
 
LVL 11

Accepted Solution

by:
louisfr earned 500 total points
ID: 40372471
Here is an example of a left outer join using LINQ: LINQ - How to Perform Left Outer Joins
0
 

Author Comment

by:databarracks
ID: 40372499
Hi Louis

I have tried that method and it always returns no rows from my child table
0
 

Author Comment

by:databarracks
ID: 40372505
This is the code below that I have and it always says that there are no rows in my note datatable therefore the  "note_text" column always returns "No Comment" as  per my query:

        Dim myListOfAnonymousTypes = (From notes In ds.Tables("notes")
                         Group Join note In ds.Tables("note") On notes Equals note("notes_Id")
                         Into NoteList = Group
                         From note In PetList.DefaultIfEmpty()
                         Select ID = notes("notes_Id"), request = notes("request_Id"),
                                note_text =
                                  If(note Is Nothing, "No Comment", note("note_text"))).ToList

Open in new window

0
 

Author Comment

by:databarracks
ID: 40372573
Figured out thanks to Louis guidance:

[embed=snippet 8266284]

Open in new window

        Dim myListOfAnonymousTypes = (From notes In ds.Tables("notes")
                         Group Join note In ds.Tables("note") On notes("notes_Id") Equals note("notes_Id")
                         Into NoteList = Group
                         From note In PetList.DefaultIfEmpty()
                         Select ID = notes("notes_Id"), request = notes("request_Id"),
                                note_text =
                                  If(note Is Nothing, "No Comment", note("note_text"))).ToList

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 52
Nested forach loop to linq 3 42
vb.net datagrid point 4 34
Broken .resx file generating errors 18 44
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question