-
Apr 29th, 2021, 03:07 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Original DataTable columns after LINQ query
Hi there,
My original issue was wanting to concatenate values in a DataColumn across DataRows.
I have the following data:
Code:
id | col1 | col2 | Category
---------------------------------
1 | job1 | pm1 | Design
1 | job1 | pm1 | Commercial
2 | job5 | pm6 | Install
3 | job8 | pm5 | Design
3 | job8 | pm5 | Materials
How I want the data presented is:
Code:
id | col1 | col2 | Category
---------------------------------
1 | job1 | pm1 | Design,Commercial
2 | job5 | pm6 | Install
3 | job8 | pm5 | Design,Materials
From what I've found searching I need to use LINQ or Lamba expressions which I haven't really dealt with before.
I've had a little success with the following:
vb Code:
Dim query = From r In dtLessons
Order By r.Field(Of Integer)("id") Descending
Group r By id = r.Field(Of Integer)("id")
Into g = Group
Select New With {
Key id,
.category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())}
For Each row In query
Console.WriteLine(row.category)
Next
Were I've managed to get the concatenated categories. But now I'm unsure how to access all my other columns, do I need to add them to the query as well?
Thank you.
-
Apr 29th, 2021, 07:07 PM
#2
Re: Original DataTable columns after LINQ query
You have two choices. You can group by all of the first three columns instead of just the first, in which case the col1 and col2 values will be part of your key. Alternatively, you can get the col1 and col2 values from the group, much as you are the Category values. If you go with the second option, you would use g.First to get the first row in the group, then get the column values from that. Based on the data you've shown, you know that all col1 and col2 values in a group will be the same, so it doesn't matter which row you get them from. That said, if they are all the same then you really ought to be grouping by them in the first place.
-
Apr 30th, 2021, 06:10 AM
#3
Thread Starter
Hyperactive Member
Re: Original DataTable columns after LINQ query
Jmc,
Thank you for your response.
I've updated my code as follows, though do I really need to enter and select every column?
I've opted for your first solution, as every other column is going to just be duplicated values so should be Grouped as you say.
vb Code:
Dim query2 = From r In dtLessons
Order By r.Field(Of Integer)("id") Descending
Group r By id = r.Field(Of Integer)("id"),
pm = r.Field(Of String)("Project Manager")
Into g = Group
Select New With {
Key id,
.pm = g.Select(Function(a) a.Field(Of String)("Project Manager")),
.category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())}
For Each row In query2
Console.WriteLine(row.pm)
Next
With this the Output shows:
Code:
System.Linq.Enumerable+WhereSelectArrayIterator`2[System.Data.DataRow,System.String]
I guess I'm looking at this completely the wrong way and making more work for myself.
Any guidance would be appreciated.
Also, is using a LINQ query the best way to go to solve my original issue, I did originally try solving it in SQL, but it looked like the Function I needed wasn't available in my version of SQL.
Thank you.
Edit
Ok, I've just found another example and changed my code to this:
vb Code:
Dim query2 = From r In dtLessons
Group By id = r.Field(Of Integer)("id"),
pm = r.Field(Of String)("Project Manager")
Into g = Group
Select New With {
.id = id,
.pm = pm,
.category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())
}
For Each row In query2
Console.WriteLine(row.pm)
Next
This give me the correct output, but my question still stands if I need to write out every column in the Group By and Select New sections? And if LINQ is the best way to solve this issue?
Also, not to get back into a DataTable do I need to create a new DataTable, create the columns and iterate through my LINQ query to add the rows in?
Thank you.
Last edited by squatman; Apr 30th, 2021 at 06:22 AM.
-
Apr 30th, 2021, 04:42 PM
#4
Re: Original DataTable columns after LINQ query
Here's a custom extension to create a datatable from an anonymous query. I added sort order...
Code:
Dim query2 = From r As DataRow In dtLessons.AsEnumerable
Group By id = r.Field(Of Integer)("id"),
pm = r.Field(Of String)("Project Manager")
Into g = Group
Order By id
Select New With {
.id = id,
.pm = pm,
.category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())
}
Dim dt2 As DataTable = query2.CopyToDataTable
DataGridView1.DataSource = dt2
Code:
Module EnumerableExtensions
<System.Runtime.CompilerServices.Extension()>
Public Function CopyToDataTable(ByVal data As IEnumerable(Of Object)) As DataTable
Dim parseDT = New DataTable()
data.Select(Function(r, i) New With {
Key .Key = i,
Key .Value = r
}).ToList().ForEach(Sub(r)
If r.Key = 0 Then
r.Value.GetType().GetProperties().ToList().ForEach(Sub(p)
parseDT.Columns.Add(p.Name, p.PropertyType)
End Sub)
End If
Dim row = parseDT.NewRow()
r.Value.GetType().GetProperties().ToList().ForEach(Sub(p)
row(p.Name) = p.GetValue(r.Value, Nothing)
End Sub)
parseDT.Rows.Add(row)
End Sub)
Return parseDT
End Function
End Module
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Apr 30th, 2021, 10:36 PM
#5
Re: Original DataTable columns after LINQ query
Originally Posted by squatman
Also, is using a LINQ query the best way to go to solve my original issue, I did originally try solving it in SQL, but it looked like the Function I needed wasn't available in my version of SQL.
Thank you.
this is a Pivot Query with an Access Database, it is the closest to get what you want
I added a Table to Access and named that Table tblsquatmann
the Sql query
Code:
TRANSFORM Count(tblsquatmann.Category) AS CountCategory
SELECT tblsquatmann.id, tblsquatmann.col2, tblsquatmann.col1
FROM tblsquatmann
GROUP BY tblsquatmann.id, tblsquatmann.col2, tblsquatmann.col1
ORDER BY tblsquatmann.id
PIVOT tblsquatmann.Category;
here a Image of the result
EDIT:
changeing the Sql query a little with Max does give the result
Code:
TRANSFORM Max(tblsquatmann.Category) AS CountCategory
SELECT tblsquatmann.id, tblsquatmann.col2, tblsquatmann.col1
FROM tblsquatmann
GROUP BY tblsquatmann.id, tblsquatmann.col2, tblsquatmann.col1
ORDER BY tblsquatmann.id
PIVOT tblsquatmann.Category;
the result
Last edited by ChrisE; Apr 30th, 2021 at 11:16 PM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
May 10th, 2021, 04:06 AM
#6
Thread Starter
Hyperactive Member
Re: Original DataTable columns after LINQ query
Thank you both for your responses.
Paul, I'm still tot est but that looks like a really neat way to get the information back into a DataTable so thank you.
Chris, it doesn't look like this quite gives the desired result, allow something to consider I wasn't looking for resulting separate columns.
I thought I had this working, and now coming back to it I'm getting a cast from DbNull to DateTime error.
Code:
dateComplete = r.Field(DateTime)("Date Complete"),
I've tried If String.IsNullOrEmpty but still get the same error.
I've tried:
Code:
dateComplete = r.Field(Of Nullable(Of DateTime))("Date Complete"),
But still the same error. Does anyone have any ideas please?
Thank you.
vb Code:
Dim lessonsAggregated = From r In dtLessons
Group By id = r.Field(Of Integer)("id"),
no = r.Field(Of Integer)("#"),
dateReported = r.Field(Of DateTime)("Date Reported"),
issue = r.Field(Of String)("Issue"),
cause = r.Field(Of String)("Cause"),
iar = r.Field(Of Boolean)("immediate_action_required"),
actionTaken = r.Field(Of String)("Action Taken"),
iadd = r.Field(Of DateTime)("immediate_action_deadline_date"),
dateComplete = r.Field(Of Nullable(Of DateTime))("Date Complete"),
accepted = r.Field(Of Boolean)("Accepted"),
furtherRequired = r.Field(Of Boolean)("Further Req."),
residualRisk = r.Field(Of Boolean)("Residual Risk"),
jobNumber = r.Field(Of String)("Job Number"),
jobTitle = r.Field(Of String)("Job Title"),
site = r.Field(Of String)("Site"),
client = r.Field(Of String)("Client"),
jobType = r.Field(Of String)("Job Type"),
projectManager = r.Field(Of String)("Project Manager"),
reportedBy = r.Field(Of String)("Reported By"),
completedBy = r.Field(Of String)("Completed By"),
actions = r.Field(Of Boolean)("Actions"),
access = r.Field(Of Boolean)("Access"),
myActions = r.Field(Of Boolean)("My Actions"),
furtherNotClosed = r.Field(Of Boolean)("Further Not Closed")
Into g = Group
Select New With {
.id = id,
.no = no,
.dateReported = dateReported,
.issue = issue,
.cause = cause,
.iar = iar,
.actionTaken = actionTaken,
.iadd = iadd,
.dateComplete = dateComplete,
.accepted = accepted,
.furtherRequired = furtherRequired,
.residualRisk = residualRisk,
.jobNumber = jobNumber,
.jobTitle = jobTitle,
.site = site,
.client = client,
.jobType = jobType,
.projectManager = projectManager,
.reportedBy = reportedBy,
.completedBy = completedBy,
.actions = actions,
.access = access,
.myActions = myActions,
.furtherNotClosed = furtherNotClosed,
.category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())
}
Edit:
Solved this now I added a ? to all integer, boolean and datetime types as it seemed to give me the wrong error for the type it actually highlighted.
Last edited by squatman; May 10th, 2021 at 11:46 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|