Deleting datatable data using results from linq query

Question

I have done research on how to delete rows from a datatable.

Dataset.Datatable.Rows(0).Delete()

The problem I am having now is I need to delete specific data from my linq query.

 Dim qy = From rows In loadedData
 Where rows.Field(Of Double)("count") = elem
 Take elem
 Select ("count")

What I need to do is Delete the rows that are in the result of this linq query.

"Delete top elem from loaded data where count = elem"

I am just not sure how to write that in vb.net since my research shows you can't manipulate data using a linq query, you can only select it.

Oh and the reason I have the select top elem, is because the elem can be duplicated, so I could have a count of 500 rows and 500 rows. So I am using this query to get the first 500 rows and then delete that so when I run the query again I am not getting duplicate data.

Any help would be appreciated.

The duplicate part is not clear. Do you want to delete only one row if two rows have the same count=elem? Then you need to GroupBy count and use Skip(1) to leave only one row of each group. — Tim Schmelter
– Tim Schmelter, Commented Oct 15, 2013 at 14:25
I'm not sure if you use this Linq query somewhere else, but, if not, what I'm imagining you could do is make the Linq query the inverse of what you currently have - Where rows.Field(Of Double)("count") <> elem and then do something like Dataset.Datatable = MyQuery.CopyToDataTable - Rather than delete rows, use Linq to get the rows you want to keep instead. Hope this makes sense. — John Bustos
– John Bustos, Commented Oct 15, 2013 at 14:29
An example: I have an excel spreadsheet that has 1000 rows. I have a column that is a count to let me know how many items are in the collection. In this example I have all 1000 rows that have 500 as the number in the collection. This means I have to break this into two separate datatables. What I will do is add the results from the query to a datatable, delete the data I just used then redimension my array of datatables to add the next chunk to avoid getting duplicates. This is the solution I have come up with. Not sure if there is an easier way or not — John Janssen
– John Janssen, Commented Oct 15, 2013 at 14:31
@JohnBustos I understand what you are saying, but I need all of the data. I am taking the results of my linq query, putting them into separate datatables, and eventually exporting them to separate csv files which will be used to insert into peachtree. I am trying to delete the separate chunks after I add them to their own datatable. Thanks though for that. — John Janssen
– John Janssen, Commented Oct 15, 2013 at 14:33

Tim Schmelter · Accepted Answer · 2013-10-15 14:22:40Z

1

Dim toDelete = From row In table
               Let count = row.Field(Of Double)("count")
               Where count = elem
               Select row
               Take elem

For Each row As DataRow in toDelete 
    row.Delete()
Next

answered Oct 15, 2013 at 14:22

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Deleting datatable data using results from linq query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related