Solved

Linq query JOIN problem

Posted on 2014-10-09
10
212 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
10 Comments
 
LVL 62

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
 
LVL 32

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 32

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now