Resolved Deleting a DataTable Record w/ LINQ

jsurpless

Well-known member
Joined
Jul 29, 2008
Messages
144
Programming Experience
Beginner
I've got a DataTable that I need to delete some rows from; I'm trying the following code but getting some unexpected results

VB.NET:
Dim DatabaseAuxiliary_Table_Query = From ADG_Auxiliary_Record In DatabaseAuxiliary_Table.AsEnumerable() Select ADG_Auxiliary_Record _
                    Where (ADG_Auxiliary_Record.Field(Of Object)(DrawingFileName_Auxiliary_Field) = strAutoCADFile)

'Delete ALL Auxiliary Blocks CURRENTLY in Auxiliary Table for THIS Drawing
For Each ADG_Auxiliary_Record In DatabaseAuxiliary_Table_Query

ADG_Auxiliary_Record.Delete()

Next

My expectation is that the DatabaseAuxiliary_Table_Query would return all of the results in DatabaseAuxiliary_Table with a given strAutoCADFile.

I would then iterate through these rows and delete them one by one.

What's happening is that when I delete the 1st row, I can see by clicking the 'magnifying glass' to view that DatabaseAuxiliary_Table and see that several rows (that previously had the strAutoCADFile value) no longer have it.

1st question is why would that be happening?

My 2nd issue is that when it runs again for the 2ND value of strAutoCADFile, it sometimes returns a 'DeletedRowInaccessibleException' exception. I believe that this is happening because it's trying to delete a row that's already been deleted. It's not clear to me why this would be the case since I'm only executing this code one time for each strAutoCADFile.

Thoughts?

Many thanks in advance!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
Rather than iterating over a live query, call ToArray to realise the query and then iterate over the results. Also, you should exclude already-deleted rows from the query based on RowState, i.e. use Where ADG_Auxiliary_Record.RowState = DataRowState.Unchanged AndAlso ... or Where ADG_Auxiliary_Record.RowState <> DataRowState.Deleted AndAlso ....
 

jsurpless

Well-known member
Joined
Jul 29, 2008
Messages
144
Programming Experience
Beginner
Rather than iterating over a live query, call ToArray to realise the query and then iterate over the results. Also, you should exclude already-deleted rows from the query based on RowState, i.e. use Where ADG_Auxiliary_Record.RowState = DataRowState.Unchanged AndAlso ... or Where ADG_Auxiliary_Record.RowState <> DataRowState.Deleted AndAlso ....

Could I ask you to specify where in my code I would use .ToArray?

Regarding restricting the LINQ query to non-deleted records, I modified my query as follows but I am still getting the 'DeletedRowInaccessibleException' exception.

VB.NET:
Dim DatabaseAuxiliary_Table_Query = From ADG_Auxiliary_Record In DatabaseAuxiliary_Table.AsEnumerable() Select ADG_Auxiliary_Record
                                                    Where (ADG_Auxiliary_Record.Field(Of Object)(c_Database.DrawingFileName_Auxiliary_Field) = strAutoCADFile _
                                                        And ADG_Auxiliary_Record.RowState <> DataRowState.Deleted)

thank you for your assistance
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
The loop executes the query, but you are also then changing the queried rows during the execution of the query.
VB.NET:
Dim executedQuery = DatabaseAuxiliary_Table_Query.ToArray
Here you will have a finished result set executedQuery that you can loop over.

This explains briefly deferred and immediate query execution in Linq:
 

jsurpless

Well-known member
Joined
Jul 29, 2008
Messages
144
Programming Experience
Beginner
The loop executes the query, but you are also then changing the queried rows during the execution of the query.
VB.NET:
Dim executedQuery = DatabaseAuxiliary_Table_Query.ToArray
Here you will have a finished result set executedQuery that you can loop over.

This explains briefly deferred and immediate query execution in Linq:

Here's what I'm trying at the moment but still getting the same error

VB.NET:
Dim DatabaseAuxiliary_Table_Query = From ADG_Auxiliary_Record In DatabaseAuxiliary_Table.AsEnumerable() Select ADG_Auxiliary_Record
                                                    Where (ADG_Auxiliary_Record.Field(Of Object)(c_Database.DrawingFileName_Auxiliary_Field) = strAutoCADFile _
                                                        And ADG_Auxiliary_Record.RowState <> DataRowState.Deleted)

Dim ExecutedQuery = DatabaseAuxiliary_Table_Query.ToArray

Dim MyrecordCount As Integer

MyrecordCount = ExecutedQuery.Count

'Delete ALL Auxiliary Blocks CURRENTLY in Auxiliary Table for THIS Drawing
For Each ADG_Auxiliary_Record In ExecutedQuery

ADG_Auxiliary_Record.Delete()

Next

I looked at the article you mentioned and had a question - when I make modifications to 'ExecutedQuery', am I making modifications to 'DatabaseAuxiliary_Table'?

It seems like I am but not sure why this would be the case
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
when I make modifications to 'ExecutedQuery', am I making modifications to 'DatabaseAuxiliary_Table'?
The query just asks for results, it is changes to the results that changes the table contents. Difference between looping ExecutedQuery and DatabaseAuxiliary_Table_Query is that the former is a completed query before you make changes, and the latter is an ongoing query where your changes could affect the outcome of that iteration.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
The reason for DeletedRowInaccessibleException is that the row is still in table and then your query is trying to read DrawingFileName_Auxiliary_Field of such row. Instead change the Where to first check RowState and use AndAlso short-circuiting operator for checking the field. That way when RowState<>Deleted expression evaluates to False the field part is not evaluated.

In general always use short-circuiting operators. AndAlso Operator - Visual Basic / OrElse Operator - Visual Basic
I also generally prefer for improved performance to check simple numeric expressions before more costly string expressions and other more time-consuming expressions, if that is possible, and the order of the expressions otherwise doesn't matter.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
LINQ uses iterators, which are special methods that return a list of items but returns them one at a time rather than in a complete list. One of the reasons that that can be much better is that, if you decide that you don;t need the whole list at some point, the iterator can be cancelled and the rest of the list not retrieved. The problem is that, if the items are retrieved based on a set of criteria, and changes you make while using items in the list can affect the results of applying those criteria to later items. By calling ToArray, you apply the criteria to every item first and get the full list, then iterate over that full list. No changes you make can change that list, even if they would affect the query if it were executed again, so you're safe.

Yes, you are making changes to the original DataTable because the items in the array are the DataRows from that DataTable. That's like asking if you made a list of the male members of a household and then gave them a haircut, are you affecting that household. Of course you are. You're not making clones of the people, just as you're not making clones of the DataRows in this case.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
In case it's still not clear, let me provide a practical example of the difference between iterators and conventional methods. Let's say that there are a number of people in a room and you want to ask all the people wearing a red shirt a question. You could handle that one of two ways.

If you handled it like a regular method, you would first make a list of all the people wearing red shirts and then you would work your way through that list, asking each person a question in turn. Even if some people were to change from a red shirt to a different colour or from a different colour shirt to red, your list would not change, so you would be questioning the people who had on red shirts when you made the list and only those people. That might mean that some people you questioned didn't have on red shirts when you questioned them and that some people might have on red shirts but remain unquestioned when you finished.

If you handled it like an iterator, you would look in the room until you found someone in a red shirt and you would pull them aside and ask them a question, then you would look again, repeating until there were no more people with red shirts. Some people might change their shirts after you started but before you finished, meaning that the list of people that you finally end up questioning would be different to the list of people who had red shirts to begin with.

Iterators are great where you don't want to wait for a large amount of work to be done before you start processing the results or you might not want to process all the results. In cases where the data is susceptible to changes made during processing though, you need to do the work of making a complete list up front and then iterating over that.
 
Top Bottom