Exception Using Copytodatatable with "New {..}" Linq Query

Exception using CopyToDataTable with new {..} LINQ query

Based on your use of Field<T>, the objects in dataTable (which I am assuming are of type Car) inherit DataRow. This is necessary to call the CopyToDataTable extension method. As written, however, you are returning an enumeration of an anonymous type which can not inherit DataRow.

So, probably your

select new

should be

select new Car

so that you're returning an IEnumerable<Car> instead of an IEnumerable<> of anonymous type.

Depending on the exact structure of your Car class, it might be necessary to make some minor syntatical changes. If Car has public properties, Make, Color, and PetName then it will work as I suggested. If, instead, Car has a constructor with method signature approximately equal to

public Car(string make, string color, string petName)

then you will have to alter the LINQ statement to be

var bmwCars = from car in dataTable.AsEnumerable()
              where car.Field<string>("Make").ToLower().Equals.("bmw")
              select new Car(
                  car.Field<string>("Make"),
                  car.Field<string>("Color"),
                  car.Field<string>("PetName")                          
              );

DataTable Error - Can't CopyToDataTable()

You need to add the Key identifier to the properties on Anonymous Types in VB. (In C# all properties are key properties) Only the values of key properties are compared in order to determine whether two instances are equal, and you need this for Distinct() to work.

Select New With {
                 Key .Col1 = dr1.Field(Of Double)("Col1"),
                 Key .Col2 = dr2.Field(Of String)("Col2")
                }

As for getting CopyToDataTable to work on anonymous types check out this answer.

Linq query to Datasource,InvalidOperationException

I assume[1](edit: now confirmed by OP) that you don't get the exception at tabelBindingSource.DataSource = resultTable but at result.CopyToDataTable<DataRow>() because the query doesn't contain any DataRows. The columns of the new DataTable will be derived from DataRow.Table.Columns, if there are no rows there are no informations.

The exception is documented:

InvalidOperationException:

• A DataRow in the source sequence has a state of Deleted.
• The source sequence does not contain any DataRow objects.
• A DataRow in the source sequence is null.

You can use Any to check it before:

if(result.Any())
{
    DataTable resultTable = result.CopyToDataTable<DataRow>();
    tabelBindingSource.DataSource = resultTable;
    dataGridView1.Update();
}

a more efficient approach:

DataTable resultTable = bDSpitalDataSet.Tabel.Clone();
foreach(DataRow row in result.Rows)
    resultTable.LoadDataRow(row.ItemArray, false);

more efficicient because result.Any() needs to execute the query to determine if there's at least one row, result.CopyToDataTable<DataRow>() executes it a second time.

---

[1] Why i assume that you've mentioned the wrong error-line? Because of your comment:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.DataSetExtensions.dll

The source contains no DataRows using Linq

The problem come here because you are trying to CopyToDataTable when you don't have any records. So solution will be

var firstDataTable = dt1.AsEnumerable()
  .Where(x => dt.AsEnumerable()
 .Any(z => z.Field<string>("Email").Trim() == x.Field<string>("Email").Trim()));

DataTable filteredEducation  = new DataTable();

if(firstDataTable.Any())
{
    filteredEducation  = firstDataTable.CopyToDataTable();
}

Avoid exception using CopyToDataTable() when no rows are found

You didn't specify the error, but I'm guessing the select statement is returning null. Can't you use a simple null check?

var table = dsDecEjID.Tables(0).Select(Cond);
if(table != null)
    ds.Tables.Add(table.CopyToDataTable());

error in binding datatable to database by linq

Try this code, place it in a static helper class and this will allow you to call ToDataTable(); on the items.

public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        DataTable dt = new DataTable();
        foreach (var prop in data.First().GetType().GetProperties())
        {
            dt.Columns.Add(prop.Name);
        }

        foreach (T entry in data)
        {
            List<object> newRow = new List<object>();
            foreach (DataColumn dc in dt.Columns)
            {
                var val = entry.GetType().GetProperty(dc.ColumnName).GetValue(entry, null);
                newRow.Add(val);
            }
            dt.Rows.Add(newRow.ToArray());
        }
        return dt;
    }

---

Related Topics