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

Question

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?

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

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 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);
            }
        }
    }