Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
SolvedPrivate

VB.Net: Exporting Aggregate LINQ Query Results to Data Table (or Excel file)

Posted on 2014-11-20
5
32 Views
Last Modified: 2016-02-15
I am programming in VB.Net in Visual Studio 2010 (4.5 Framework).  I have a data table for which I have to create an Aggregate Query (grouping on multiple fields and summing multiple fields), and ultimately exporting my aggregated results to a new Excel file.

I was able to use LINQ to aggregate the records in my table, but am having problems writing those results to another blank, existing data table (I ultimately need to export the results to Excel, but didn't know if I can do that directly from my LINQ results so am trying to export the results to a table first).

I looped through the result set and returned my results back to the console, to confirm that part is working properly (and it is).  Here is my code:
    Private Sub PopulateRolledClaims()

        '   Aggregate records and sum amount fields
        Dim results = From c In EnhancedClaimsTable.AsEnumerable _
                    Group c By SSN = c.Field(Of String)("SSN"), _
                    FirstName = c.Field(Of String)("FirstName") _
                    Into g = Group _
                    Select New With _
                           { _
                               .SSN = SSN, _
                               .FirstName = FirstName, _
                               .TotalCopayAmt = g.Sum(Function(c) _
                                    c.Field(Of Decimal)("CopayAmount")), _
                                .TotalDeductibleAmt = g.Sum(Function(c) _
                                     c.Field(Of Decimal)("DeductibleAmount")) _
                            }

        '   Display results in console
        For Each c In results
            Console.WriteLine("SSN = {0} " & vbTab & _
                              "FirstName = {1} " & vbTab & _
                              "TotalCopayAmt = {2} " & vbTab & _
                              "TotalDeductibleAmt = {3} ", _
                              c.SSN, c.FirstName, c.TotalCopayAmt, c.TotalDeductibleAmt)
        Next

        ''   Write results of all rows to table table
        For Each row In results
            RolledClaimsTable.ImportRow(row)    'THIS IS WHERE MY ERROR IS
        Next

    End Sub

Open in new window

On the line of code where I have commented "THIS IS WHERE MY ERROR IS", it is underlining the word "row" and when I hover over it, it says:
"Value of type ' <anonymous type> (line 251) cannot be converted to 'System.Data.DataRow'."

Note that the fields in my "RolledClaimsTable" table have the same name as the fields in the Aggregate Query.

I have used this ".ImportRow(row)" methodology to successfully populate other Data Tables from LINQ queries in the past, but they were never Aggregate Queries.  I am guessing that changes things and I have to use a different method to populate the table.

Can anyone help?

Thanks
0
Comment
Question by:JoeMiskey
  • 2
  • 2
5 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40456121
Hi Joe;

The ImportRow of the DataTable object takes a DataRow object. The query is returning a Anonymous data type and therefore you can't you that method. It looks like the RolledClaimsTable DataTable is already created with all its columns. The way you can do it in that For Each loop is to get a new row from RolledClaimsTable and fill in the corresponding values from the results of the query. Something like the following.

For Each row In results
    Dim newRow As DataRow = RolledClaimsTable.NewRow()
    newRow[0] = row.SSN
    newRow[1] = ...
    ...
    RolledClaimsTable.Rows.Add(newRow)
Next

Open in new window

0
 

Author Comment

by:JoeMiskey
ID: 40456158
Fernando,

When I tried that code, it underlines "newRow[0]" and says "Expression is not a method".

Note, if it is easier, I don't need to create a empty DataTable object first and then populate it in this manner.  All I am trying to do is take the results of this query, and export it to an Excel file, one way or another.  So if there are different/easier ways of getting from my LINQ query to the final Excel file, I am open to them.

Thanks
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40456361
Sorry Visual Basic, it should be newRow(0)
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40456491
0
 

Author Closing Comment

by:JoeMiskey
ID: 40457182
Thanks Fernando, that works.

Eric, that looks like it works in that it creates a data table on the fly, which is nice.  A bit more work, but powerful.  Since I already have the table shell, I didn't need to go that route, but I will definitely file this away, as I can envision having to use this for other projects.  Thanks!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

809 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