Results 1 to 6 of 6

Thread: [RESOLVED] Original DataTable columns after LINQ query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Resolved [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:
    1. Dim query = From r In dtLessons
    2.                     Order By r.Field(Of Integer)("id") Descending
    3.                     Group r By id = r.Field(Of Integer)("id")
    4.                     Into g = Group
    5.                     Select New With {
    6.                     Key id,
    7.                     .category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())}
    8.  
    9.         For Each row In query
    10.             Console.WriteLine(row.category)
    11.         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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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:
    1. Dim query2 = From r In dtLessons
    2.                      Order By r.Field(Of Integer)("id") Descending
    3.                      Group r By id = r.Field(Of Integer)("id"),
    4.                                     pm = r.Field(Of String)("Project Manager")
    5.                         Into g = Group
    6.                      Select New With {
    7.                         Key id,
    8.                          .pm = g.Select(Function(a) a.Field(Of String)("Project Manager")),
    9.                         .category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())}
    10.  
    11.         For Each row In query2
    12.             Console.WriteLine(row.pm)
    13.         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:
    1. Dim query2 = From r In dtLessons
    2.                      Group By id = r.Field(Of Integer)("id"),
    3.                          pm = r.Field(Of String)("Project Manager")
    4.                          Into g = Group
    5.                      Select New With {
    6.                          .id = id,
    7.                          .pm = pm,
    8.                          .category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())
    9.                         }
    10.  
    11.         For Each row In query2
    12.             Console.WriteLine(row.pm)
    13.         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.

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    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

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Original DataTable columns after LINQ query

    Quote Originally Posted by squatman View Post

    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
    Name:  squatmann.jpg
Views: 215
Size:  22.9 KB

    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
    Name:  squatmann2.jpg
Views: 197
Size:  27.2 KB
    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.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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:
    1. Dim lessonsAggregated = From r In dtLessons
    2.                                 Group By id = r.Field(Of Integer)("id"),
    3.                                         no = r.Field(Of Integer)("#"),
    4.                                         dateReported = r.Field(Of DateTime)("Date Reported"),
    5.                                         issue = r.Field(Of String)("Issue"),
    6.                                         cause = r.Field(Of String)("Cause"),
    7.                                         iar = r.Field(Of Boolean)("immediate_action_required"),
    8.                                         actionTaken = r.Field(Of String)("Action Taken"),
    9.                                         iadd = r.Field(Of DateTime)("immediate_action_deadline_date"),
    10.                                         dateComplete = r.Field(Of Nullable(Of DateTime))("Date Complete"),
    11.                                         accepted = r.Field(Of Boolean)("Accepted"),
    12.                                         furtherRequired = r.Field(Of Boolean)("Further Req."),
    13.                                         residualRisk = r.Field(Of Boolean)("Residual Risk"),
    14.                                         jobNumber = r.Field(Of String)("Job Number"),
    15.                                         jobTitle = r.Field(Of String)("Job Title"),
    16.                                         site = r.Field(Of String)("Site"),
    17.                                         client = r.Field(Of String)("Client"),
    18.                                         jobType = r.Field(Of String)("Job Type"),
    19.                                         projectManager = r.Field(Of String)("Project Manager"),
    20.                                         reportedBy = r.Field(Of String)("Reported By"),
    21.                                         completedBy = r.Field(Of String)("Completed By"),
    22.                                         actions = r.Field(Of Boolean)("Actions"),
    23.                                         access = r.Field(Of Boolean)("Access"),
    24.                                         myActions = r.Field(Of Boolean)("My Actions"),
    25.                                         furtherNotClosed = r.Field(Of Boolean)("Further Not Closed")
    26.                                 Into g = Group
    27.                                 Select New With {
    28.                                     .id = id,
    29.                                     .no = no,
    30.                                     .dateReported = dateReported,
    31.                                     .issue = issue,
    32.                                     .cause = cause,
    33.                                     .iar = iar,
    34.                                     .actionTaken = actionTaken,
    35.                                     .iadd = iadd,
    36.                                     .dateComplete = dateComplete,
    37.                                     .accepted = accepted,
    38.                                     .furtherRequired = furtherRequired,
    39.                                     .residualRisk = residualRisk,
    40.                                     .jobNumber = jobNumber,
    41.                                     .jobTitle = jobTitle,
    42.                                     .site = site,
    43.                                     .client = client,
    44.                                     .jobType = jobType,
    45.                                     .projectManager = projectManager,
    46.                                     .reportedBy = reportedBy,
    47.                                     .completedBy = completedBy,
    48.                                     .actions = actions,
    49.                                     .access = access,
    50.                                     .myActions = myActions,
    51.                                     .furtherNotClosed = furtherNotClosed,
    52.                                     .category = String.Join(",", g.Select(Function(a) a.Field(Of String)("Category")).ToArray())
    53.                                 }

    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
  •  



Click Here to Expand Forum to Full Width