Linq query JOIN problem

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
databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
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
databarracksAuthor Commented:
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
databarracksAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

it_saigeDeveloperCommented:
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
it_saigeDeveloperCommented:
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
databarracksAuthor Commented:
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
louisfrCommented:
Here is an example of a left outer join using LINQ: LINQ - How to Perform Left Outer Joins
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
databarracksAuthor Commented:
Hi Louis

I have tried that method and it always returns no rows from my child table
0
databarracksAuthor Commented:
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
databarracksAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

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.