Use Linq to Return a Row in a DataTable

Question

I am a newby to Linq.  Currently, I am using a loop to find the row I want to update.

    Public Sub pubsub_SetUserDGVColumnWidth(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs)

            ' update all user settings for the given column
            For Each rw As DataRow In Me.dsDGVUserSettings.Tables(DirectCast(sender, DataGridView).Name).Rows
                If rw(Me.intColumnName).Equals(e.Column.Name) Then
                    rw.BeginEdit()
                    rw(Me.intWidth) = e.Column.Width
                    rw.EndEdit()
                    Exit For
                End If
            Next rw

    End Sub

I would like to use Linq.  I think it would be more eligent, but I'm getting an error "Public member 'ColumnName' on type 'DataRow' not found.".  Why?

    Public Sub pubsub_SetUserDGVColumnWidth(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs)

            Dim dr As DataRow = (From rws In Me.dsDGVUserSettings.Tables(DirectCast(sender, DataGridView).Name).Rows Select rws Where rws.ColumnName = e.Column.Name).SingleOrDefault

            dr.BeginEdit()
            dr(Me.intWidth) = e.Column.Width
            dr.EndEdit()

    End Sub

---

Ryan

Answer 1

Well, I'm no master myself but in this case you will want to access the rows collection through the DataTable.AsEnumerable() function and then use the Field(Of T) property to specify the filtering column name.  The example below will return an enumerable collection as opposed to a single row simply because there could be multiples in any given query result.  Perhaps someone else could offer how to get this to a single DataRow result if that's what you need. 

        Dim results As EnumerableRowCollection(Of DataRow) = From myRow In MyTable.AsEnumerable() Where myRow.Field(Of Int32)("MyColumnName") = 1 Select myRow

Be aware that you do not need to set the results into a variable - you could just place the linq section into a ForEach loop.

Answer 2

Yes, I need to return a single row.  If you notice my For Each Loop has an Exit For line within the If Then statement.  My DataTable will never have multiple rows with the same ColumnName.  If it does the code should throw an error which I think the Single property will do.  So hopefully someone will be able to give me the correct syntax to return a single row in Linq.

Thanks,

---

Ryan

Answer 3

Okay.  This seemed to work for me, where the "default" option returns a null reference when the where clause conditions are not met.

Dim row As DataRow = MyTable.AsEnumerable().SingleOrDefault(Function(MyRow) (MyRow.Field(Of Int32)("MyColumnName") = 1))

Answer 4

Could also have been done by wrapping my original query in parentheses and calling SingleOrDefault on that...

Dim row As DataRow = (From myRow In MyTable.AsEnumerable() Where myRow.Field(Of Int32)("MyColumnName") = 3 Select myRow).SingleOrDefault

I think the trick here is to use the Field property while dealing with a row collection.

Answer 5

Ryan,

Elegant code means easy to maintain code. Not using something which looks for you advantage because you did not know it yet.

What you want can be done like this:

 dim row as datarow = (DataTable.Select(MyColumnName = 3))(0)

Linq was never meant as a way to obfuscate a program and try to prohibit it is maintainable.

:-)

If Linq is used with a DataBase than use it with a DataContext (Linq to SQL) or with an entity. In some cases where their are no good DataSet/DataTable methods Linq can also be used, but not to obfuscate code.

:-)

(I don't know if you have seen that sample I once made from creating a method to sum in this forum, this is from the same category you can simply use += but some think it is better to make a method like this but it makes their programs more complex they assume.

Public Function Sum(Base as integer, FieldToAdd as integer) as Integer
    Return base += FieldToAdd
End Function

---

Success
Cor

Answer 6

Ryan,

Elegant code means easy to maintain code. Not using something which looks for you advantage because you did not know it yet.

 

What you want can be done like this:

 dim row as datarow = (DataTable.Select(MyColumnName = 3))(0)

Linq was never meant as a way to obfuscate a program and try to prohibit it is maintainable.

:-)

If Linq is used with a DataBase than use it with a DataContext (Linq to SQL) or with an entity. In some cases where their are no good DataSet/DataTable methods Linq can also be used, but not to obfuscate code.

:-)

(I don't know if you have seen that sample I once made from creating a method to sum in this forum, this is from the same category you can simply use += but some think it is better to make a method like this but it makes their programs more complex they assume.

 

Public Function Sum(Base as integer, FieldToAdd as integer) as Integer
    Return base += FieldToAdd
End Function

 

 

---

Success
Cor

Yes, I know about the Select function.  The OPs question was specific to LINQ though, so I was attempting to answer the question how it was asked.

It's good that you have offered an alternate solution though ;)  It should be noted that you cannot blindly call (0) as the default indexer on the Select function's results because there may not be any results.  Really the Select function is no better than what the OP was trying to avoid because it returns a collection of rows when he just wanted a single row reliably.

If we are looking at options that do not involve LINQ then maybe we shoudl mention the Find function on the DataRowCollection class, though it has a prerequisite of having a defined Primary Key on the table...  http://msdn.microsoft.com/en-us/library/ydd48eyk.aspx

 

 

Answer 7

It's good that you have offered an alternate solution though ;)  It should be noted that you cannot blindly call (0) as the default indexer on the Select function's results because there may not be any results.  Really the Select function is no better than what the OP was trying to avoid because it returns a collection of rows when he just wanted a single row reliably.

 

DigBoy, I knew already before I clicked the sent button (I had seen you used singleordefault),  but it was just meant to show that Linq must not always be used. 

:-) 

The OP wrote about elegant code and sometimes I've the idea that many think that doing it in a way which is new for them is always the best way. 

:-)

That was my intention.

---

Success
Cor

Answer 8

The OP wrote about elegant code and sometimes I've the idea that many think that doing it in a way which is new for them is always the best way. 

I can't argue with that.  Words of wisdom.  :)