none
LINQ to dataset, select all rows based on column, build a table from results. How to get working?

    Question

  • I have a table with duplicate entries under some columns, and I would like to use LINQ to query this DataSet for all rows under a specified column and return only distinct values.  Then I would like to build a table from the results.  I have seen pieces of this in different posts and examples but none that do it all.  This is where I am at now:

    IEnumerable<DataRow> query =
      (from rows in Dset.Tables[0].AsEnumerable()
       select rows.Field<String>("MANUFACTURER")).Ditinct;
    
    // Create a table from the query.
    DataTable boundTable = query.CopyToDataTable<DataRow>();
    
    
    The problem seems to be with the  Field<String>("MANUFACTURER") part of it all, and I get an error saying that a type cannot be converted from IEnumerable<char> to DataTable

    How would I get this to work?

    Thursday, May 20, 2010 1:06 AM

Answers

  • I think the problem here is that the LINQ query says to bring back the distinct manufacturers.  Realize that the distinct manufactures is just an IEnumerable<string>.  It is not an IEnumerable<DataRow> -- that wouldn't work: you haven't said which row to bring back in the case that two rows have the same MANUFACTURER.

    Assuming you don't have other columns in the DataTable that you need copied into the boundTable, there is a simple solution:

          DataSet Dset = new DataSet();
          DataTable dt = Dset.Tables.Add();
          dt.Columns.Add("MANUFACTURER");
          dt.Rows.Add("Microsoft");
          dt.Rows.Add("Microsoft");
          dt.Rows.Add("Contoso");
    
          IEnumerable<string> query =
           (from rows in Dset.Tables[0].AsEnumerable()
            select rows.Field<String>("MANUFACTURER")).Distinct();
    
          // Create a table from the query.
          DataTable boundTable = new DataTable();
          boundTable.Columns.Add("MANUFACTURER");
          foreach (string man in query)
            boundTable.Rows.Add(man);
    • Marked as answer by tonofsteel Thursday, May 20, 2010 1:29 AM
    Thursday, May 20, 2010 1:24 AM

All replies

  • I think the problem here is that the LINQ query says to bring back the distinct manufacturers.  Realize that the distinct manufactures is just an IEnumerable<string>.  It is not an IEnumerable<DataRow> -- that wouldn't work: you haven't said which row to bring back in the case that two rows have the same MANUFACTURER.

    Assuming you don't have other columns in the DataTable that you need copied into the boundTable, there is a simple solution:

          DataSet Dset = new DataSet();
          DataTable dt = Dset.Tables.Add();
          dt.Columns.Add("MANUFACTURER");
          dt.Rows.Add("Microsoft");
          dt.Rows.Add("Microsoft");
          dt.Rows.Add("Contoso");
    
          IEnumerable<string> query =
           (from rows in Dset.Tables[0].AsEnumerable()
            select rows.Field<String>("MANUFACTURER")).Distinct();
    
          // Create a table from the query.
          DataTable boundTable = new DataTable();
          boundTable.Columns.Add("MANUFACTURER");
          foreach (string man in query)
            boundTable.Rows.Add(man);
    • Marked as answer by tonofsteel Thursday, May 20, 2010 1:29 AM
    Thursday, May 20, 2010 1:24 AM
  • Thanks! That brings me to pretty much what I want, what would I have to do if I wanted to bring back rows, and just take the first row of every instance it runs into duplicates?

    And as a side question, if I did build a table out of returned rows, and then edited that table how would I get those changes all the way back to the original database?

    Thursday, May 20, 2010 1:29 AM
  • > just take the first row of every instance it runs into duplicates?

    Instead of using Distinct(), you could group by MANUFACTURER and take the first DataRow in each group.

          IEnumerable<DataRow> query =
           (from rows in Dset.Tables[0].AsEnumerable()
           group rows by rows.Field<String>("MANUFACTURER") into grp
           select grp.First());
           
          // Create a table from the query.
          DataTable boundTable = query.CopyToDataTable();

    > how would I get those changes all the way back to the original database?

    When all of the columns are present (as would be the case with CopyToDataTable), you likely would be able to pass that DataTable to an adapter Update method.  If you encounter problems, post as a new thread in this forum.

     

     

    Thursday, May 20, 2010 1:45 AM