How to deal with a flaw in System.Data.DataTableExtensions.CopyToDataTable()

Question

Hey guys, so I've come across something which is perhaps a flaw in the Extension method .CopyToDataTable.

This method is used by Importing (in VB.NET) System.Data.DataTableExtensions and then calling the method against an IEnumerable. You would do this if you want to filter a Datatable using LINQ, and then restore the DataTable at the end.

i.e:

Imports System.Data.DataRowExtensions
 Imports System.Data.DataTableExtensions

 Public Class SomeClass
      Private Shared Function GetData() As DataTable
          Dim Data As DataTable

          Data = LegacyADO.NETDBCall


          Data = Data.AsEnumerable.Where(Function(dr) dr.Field(Of Integer)("SomeField") = 5).CopyToDataTable()


          Return Data

      End Function
 End Class

In the example above, the "WHERE" filtering might return no results. If this happens CopyToDataTable throws an exception because there are no DataRows.

Why?

The correct behavior should be to return a DataTable with Rows.Count = 0.

Can anyone think of a clean workaround to this, in such a way that whoever calls CopyToDataTable doesn't have to be aware of this issue?

System.Data.DataTableExtensions is a Static Class so I can't override the behavior....any ideas? Have I missed something?

cheers

UPDATE:

I have submitted this as an issue to Connect. I would still like some suggestions, but if you agree with me, you could vote up the issue at Connect via the link above

cheers

Answer 1

If you feel it's a bug, then you should report it on Connect. Then post the URL of your bug as a comment or edit here, so those who like the idea can vote on it.

Answer 2

Until Microsoft fix this issue, here's a work around:

Create your own Extension method which uses the CopyToDataTable method if there are DataRows, if there aren't, then it returns an empty DataTable.

VB.NET

    Imports System.Data

Namespace CustomExtensions
    Public Module DataRowExtensionsOverride

        <System.Runtime.CompilerServices.Extension()> _
        Public Function CopyToDataTableOverride(Of T As DataRow)(ByVal Source As EnumerableRowCollection(Of T)) As DataTable

            If Source.Count = 0 Then
                Return New DataTable
            Else
                Return DataTableExtensions.CopyToDataTable(Of DataRow)(Source)
            End If

        End Function

    End Module
End Namespace

C#;

public static class DataRowExtensionsOverride
    {

        public static DataTable CopyToDataTableOverride<T>(this IEnumerable<T> Source) where T : DataRow {

            if (Source.Count() == 0) {
                return new DataTable();
            } else {
                return DataTableExtensions.CopyToDataTable<T>(Source);
            }
        }
    }

Answer 3

Do yo have have sample code in c# working, I have the same problem.

Answer 4

someDataTable.AsEnumerable().Where(r => r.Field<string>("SomeField") == "SomeValue").AsDataView().ToTable();

.AsDataView().ToTable() returns an empty table with the same structure as someDataTable if there are now rows returned from .Where()

Answer 5

I came across this problem today and worked out a workaround if it helps.

Sorry, but the blog is in C#, but I just used an IEnumerable on the LINQ variable and check .Current to see if it had returned any rows.