Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Experts,

This is a repost with slight new changes.
The reasons for reposting is , I have been doing same thing over and over again. Keeping MAciej Los solution in mind, I am working on creating a LINQ Query on below datatable

Item	     Description	Size	         Bin	MoveQty	ToBin	Comment

AD1952234763	ADVANTA 	LT24575R16 	AN033	16	AR029	Reduced Bin AN033
AD1952234763	ADVANTA 	LT24575R16 	AN033	26	AL031	Reduced Bin AN033
AD1952234763	ADVANTA 	LT24575R16 	AN033	13	AL036	Cleared Bin AN033
AD1952234763	ADVANTA 	LT24575R16	AN049	23	AL036	Reduced Bin AN049
AD1952234763	ADVANTA 	LT24575R16	AN049	40	AZ031	Reduced Bin AN049
KU12354789     KUMHO           KT123          AN049   1      AX145   Cleared BIN AN049  


I want the LINQ query to give me results as below

Item	     Description	Size	 Bin	MoveQty	ToBin	Comment
AD1952234763	ADVANTA	        LT24575R16 	AN033	16	AR029	Reduced Bin AN033
AD1952234763	ADVANTA 	LT24575R16 	AN033	26	AL031	Reduced Bin AN033
AD1952234763	ADVANTA 	LT24575R16 	AN033	13	AL036	Cleared Bin AN033



I am supposed to get this, but with your results I get all 5 rows.


I want the rows that are Reduced with Bin and cleared Bin only.
I do not want the results that are only Reduced without clearing bins

I am not afraid of getting negative comments on this. Because I am new to LINQ . I am trying to Learn things here.

Please try to understand and help me if you can
Posted
Updated 21-Oct-15 6:49am
v4
Comments
sudevsu 19-Oct-15 14:50pm    
Basically I want a Right Outer Join for this

Dim JoinResult1 = _
(From o In dtcleared.AsEnumerable _
Join a In dtNew.AsEnumerable _
On a.Field(Of String)("Item") Equals o.Field(Of String)("Item") _
Select New With _
{ _
Key .Item = a.Field(Of String)("Item"), _
Key .Bin = a.Field(Of String)("Bin"), _
Key .Size = a.Field(Of String)("Size"), _
Key .Description = a.Field(Of String)("Description"), _
Key .MoveQty = a.Field(Of String)("Move Qty"), _
Key .ToBin = a.Field(Of String)("To Bin"), _
Key .Comment = a.Field(Of String)("Comment"), _
Key .Action = o.Field(Of String)("Action")
}).ToList()
Maciej Los 19-Oct-15 14:53pm    
Think of it! You're very close to find solution. Just filter first datatable the way i showed you then join data.
sudevsu 19-Oct-15 15:01pm    
Yeah I did try that. Only after trying I will ask here. I am not that person who will leave work on someone's shoulders and sit happily. I am very much interested to learn LINQ.

Dim JoinResult1 = _
(From o In dtcleared.AsEnumerable _
Join a In dtNew.AsEnumerable _
On a.Field(Of String)("Item") Equals o.Field(Of String)("Item") into JoinItem _
From a in JoinItem.DefaultIfEMpty() _
Select New With _
{ _
Key .Item = a.Field(Of String)("Item"), _
Key .Bin = a.Field(Of String)("Bin"), _
Key .Size = a.Field(Of String)("Size"), _
Key .Description = a.Field(Of String)("Description"), _
Key .MoveQty = a.Field(Of String)("Move Qty"), _
Key .ToBin = a.Field(Of String)("To Bin"), _
Key .Comment = a.Field(Of String)("Comment"), _
Key .Action = o.Field(Of String)("Action")
}).ToList()

I get error at into clause
sudevsu 19-Oct-15 15:10pm    
Nothing funny here but I have just been there to check syntax and all. :) but didn't help though

1 solution

Have a look at example:
VB
Dim dt As DataTable = New DataTable("Table1")
dt.Columns.Add(New DataColumn("Item", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Description", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Size", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Bin", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("MoveQty", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("ToBin", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Comment", Type.GetType("System.String")))

dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "38", "AF118", "Reduced Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "38", "AF118", "Reduced Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952203863", "ADVANTA", "120/116Q E", "AQ042", "2", "AX050", "Cleared Bin AQ042"})
dt.Rows.Add(New Object(){"AD1952204763", "ADVANTA", "120/116Q E", "AK088", "16", "AI110", "Reduced Bin AK088"})
dt.Rows.Add(New Object(){"AD1952204764", "ADVANTA", "120/116Q E", "AK088", "16", "AI110", "Reduced Bin AK088"})

Dim clearedBins  = dt.AsEnumerable() _
    .GroupBy(Function(b) b.Field(Of String)("Bin")) _
    .Select(Function(g) New With _
        { _
            Key .Bin = g.Key(), _
            Key .Comments = String.Join("|", g.Select(Function(c) c.Field(Of String)("Comment"))) _
        }) _
    .Where(Function(a) a.Comments.Contains("Cleared")) _
    .ToList()


Returns List(Of AnonymousType(Of String, Of String)):

BinComments
AQ042Reduced Bin AQ042|Reduced Bin AQ042|Cleared Bin AQ042


[EDIT]
If you want to get 2 or 3 rows with Bins which were cleared, try this:
SQL
Dim clearedBins  = (From d1 In dt.AsEnumerable() _
    .GroupBy(Function(b) b.Field(Of String)("Bin")) _
    .Select(Function(g) New With _
        { _
            Key .Bin = g.Key(), _
            Key .Comments = String.Join("|", g.Select(Function(c) c.Field(Of String)("Comment"))) _
        }) _
    .Where(Function(a) a.Comments.Contains("Cleared")) _
    Join d2 In dt.AsEnumerable() _
    .Select(Function(a) a) On d1.Bin Equals d2.Field(Of String)("Bin")) _
    .Select(Function(b) b.d2) _
    .Distinct() _
    .ToList()


Distinct() method eliminates duplicates.

Result:
AD1952203863 ADVANTA 120/116Q E AQ042 38 AF118 Reduced Bin AQ042 
AD1952203863 ADVANTA 120/116Q E AQ042 2 AX050 Cleared Bin AQ042 



[/EDIT]

BUT!!! i need to warn you: you should work on data, not on data stored in DataTable object!

I would strongly recommend to create class as follow:
VB
Public Class BinData
    '
	Private sItem As String = String.Empty
	Private sDescription As String = String.Empty
	Private sSize As String = String.Empty
	Private sBin As String = String.Empty
	Private sMoveQty As String = String.Empty
	Private sToBin As String = String.Empty
	Private sComment As String = String.Empty
	
	Public Property Item As String 
		Get
			Return sItem
		End Get
		Set (ByVal value As String)
			sItem = value
		End Set
	End Property
	
	Public Property Description As String 
		Get
			Return sDescription
		End Get
		Set (ByVal value As String)
			sDescription = value
		End Set
	End Property
	
	Public Property Size As String
		Get
			Return sSize
		End Get
		Set (ByVal value As String)
			sSize = value
		End Set
	End Property
	
	Public Property Bin As String
		Get
			Return sBin
		End Get
		Set (ByVal value As String)
			sBin = value
		End Set
	End Property
	
	Public Property MoveQty As String 
		Get
			Return sMoveQty
		End Get
		Set (ByVal value As String)
			sMoveQty = value
		End Set
	End Property
	
	Public Property ToBin As String
		Get
			Return sToBin
		End Get
		Set (ByVal value As String)
			sToBin = value
		End Set
	End Property
	
	Public Property Comment As String 
		Get
			Return sComment
		End Get
		Set (ByVal value As String)
			sComment = value
		End Set
	End Property
	
End Class


Now, we can "move" data from datatable object into List(Of BinData) this way:
VB
	Dim bins As List(Of BinData) = (From d In dt.AsEnumerable() _
		Select New BinData With _
			{ _
				.Item = d.Field(Of String)("Item"), _
				.Description = d.Field(Of String)("Description"), _
				.Size = d.Field(Of String)("Size"), _
				.Bin = d.Field(Of String)("Bin"), _
				.MoveQty = d.Field(Of String)("MoveQty"), _
				.ToBin = d.Field(Of String)("ToBin"), _
				.Comment = d.Field(Of String)("Comment") _
			}) _
		.ToList()

'or using Lambda expressions:
'	Dim bins As List(Of BinData) = dt.AsEnumerable() _
'		.Select(Function(d) New BinData With _
'			{ _
'				.Item = d.Field(Of String)("Item"), _
'				.Description = d.Field(Of String)("Description"), _
'				.Size = d.Field(Of String)("Size"), _
'				.Bin = d.Field(Of String)("Bin"), _
'				.MoveQty = d.Field(Of String)("MoveQty"), _
'				.ToBin = d.Field(Of String)("ToBin"), _
'				.Comment = d.Field(Of String)("Comment") _
'			}) _
'		.ToList()


Now, we can "filter" data:
VB
Dim result = bins.GroupBy(Function(i) i.Bin) _
    .Select(Function(g) New With _
        { _
            .BinName = g.Key(),  _
            .Comments = String.Join("|", g.Select(Function(c) c.Comment)) _
        }) _
    .Where(Function(e) e.Comments.Contains("Cleared")) _
    .ToList()


On the first look, there's no difference, but using List(Of strongly_typed_object) is much, much powerful.
Please, read this: Named and Anonymous Types Compared (Visual Basic)[^]

Final note: MoveQty should be Integer data type.
 
Share this answer
 
v2
Comments
sudevsu 21-Oct-15 9:05am    
Thank you Maciej.
I am trying to work on this LINQ queries as per my need. First to understand I have been trying your suggestion which you said "If you want to get 2 or 3 rows with Bins which were cleared, try this:" But I don't see right outer join is working with this LINQ . I cannot see null as results if it doesn't exists . instead I get a union of these two tables . Let me show you an example.

I have updted my Question so that formatting will be easy to understand.
Please see above
Maciej Los 21-Oct-15 9:26am    
As you can see, you don't need right outer join. Instead of it, you have to group data by "bin" name and check if "cleared" comment belongs to this group. Yes, you have to join data from the same table, because first query is a... kind of pre-selection, which returns the name of bins which have been cleared. So, second query should join others data. Got it?
sudevsu 21-Oct-15 9:46am    
I understood that we are grouping by BIN and checking if Cleared belongs to that Group. But In that case, the Query shouldn't return the last two rows which are not having "Cleared" in comments. But It returns them as well instead of First three. Please see the DataTable from Query
sudevsu 21-Oct-15 9:09am    
I totally agree on doing a class but I first need to get the LINQ query work first as expected. I have couple of other LINQ queries based on the results of the above LINQ results. If this gives me accurate results, I think everything will work perfect. I have been working on this since a week and I learned a bit from your Queries and explanation.
Maciej Los 21-Oct-15 9:29am    
OK.
In a mean while, can you accept my answer as a solution? Formally - to remove your question from unanswered list.
Have a nice day. Call again - in case of trouble.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900