How do I use LINQ to update a datatable with a SqlDataReader?

Question

I am trying to merge data from two separate queries using C#. The data is located on separate servers or I would just combine the queries. I want to update the data in one of the columns of the first data set with the data in one of the columns of the second data set, joining on a different column.

Here is what I have so far:

ds.Tables[3].Columns[2].ReadOnly = false;
List<object> table = new List<object>();
table = ds.Tables[3].AsEnumerable().Select(r => r[2] = reader.AsEnumerable().Where(s => r[3] == s[0])).ToList();

The ToList() is just for debugging. To summarize, ds.Tables[3].Rows[2] is the column I want to update. ds.Tables[3].Rows[3] contains the key I want to join to.

In the reader, the first column contains the matching key to ds.Tables[3].Rows[3] and the second column contains the data with which I want to update ds.Tables[3].Rows[2].

The error I keep getting is

Unable to cast object of type 'WhereEnumerableIterator1[System.Data.IDataRecord]' to type 'System.IConvertible'.Couldn't store <System.Linq.Enumerable+WhereEnumerableIterator1[System.Data.IDataRecord]> in Quoting Dealers Column. Expected type is Int32.

Where am I going wrong with my LINQ?

EDIT:

I updated the line where the updating is happening

table = ds.Tables[3].AsEnumerable().Select(r => r[2] = reader.AsEnumerable().First(s => r[3] == s[0])[1]).ToList();

but now I keep getting

Sequence contains no matching element

For the record, the sequence does contain a matching element.

Might be worth looking into linked servers, if this is going to become a more frequent thing going forward, instead of trying to fix the issue in code. In the long term this will cause you way more performance issues and headaches than it's worth. — user2366842
– user2366842, Commented Aug 18, 2015 at 18:55
Linq is for querying, not updating. You are trying to create a Linq query that has side-effects in the Select which is not the proper use of Linq. You should use a traditional for or foreach loop instead. — D Stanley
– D Stanley, Commented Aug 18, 2015 at 20:58
@DStanley is it poor practice to use LINQ to update? I have done it in the past. I prefer it over foreach because it's more concise. I know how to do it with foreach statements, but I want to use LINQ, specifically dot notation. — Adam
– Adam, Commented Aug 18, 2015 at 21:33
@Adam yes it is poor practice for several reasons. 1. Select must return a value for each row. The assignment operator does return a value but a more complicated assignment may not. 2. Deferred execution - the assignment will not happen until you iterate the query with .ToList` or foreach` or a equivalent. 3. ambiguous syntax - you have to look vary carefully to see that the Select is updating objects versus projecting to a new collection, 4. it's not that much more code - one more line for the foreach. Just because you can do something doesn;t mean that you should. — D Stanley
– D Stanley, Commented Aug 19, 2015 at 4:25
Well I am a stickler for best practices. I'm going to rewrite it with foreach statements. I will post the rewritten code for reference once I'm done. Thanks for the advice. — Adam
– Adam, Commented Aug 19, 2015 at 13:01

vendettamit · Accepted Answer · 2015-08-18 23:03:25Z

1

You can use the following sample to achieve the join and update operation. Let's suppose there are two Datatables:

tbl1:

tbl2:

Joining two tables and updating the value of column "name1" of tbl1 from column "name2" of tbl2.

public DataTable JoinAndUpdate(DataTable tbl1, DataTable tbl2)
{
    // for demo purpose I have created a clone of tbl1.
    // you can define a custom schema, if needed.
    DataTable dtResult = tbl1.Clone();

    var result = from dataRows1 in tbl1.AsEnumerable()
                 join dataRows2 in tbl2.AsEnumerable()
                 on dataRows1.Field<int>("ID") equals dataRows2.Field<int>("ID") into lj
                 from reader in lj
                 select new object[]
                  {
                    dataRows1.Field<int>("ID"), // ID from table 1
                    reader.Field<string>("name2"), // Updated column value from table 2
                    dataRows1.Field<int>("age")
                    // .. here comes the rest of the fields from table 1.
                  };

    // Load the results in the table
    result.ToList().ForEach(row => dtResult.LoadDataRow(row, false));

    return dtResult;
}

Here's the result:

edited Aug 18, 2015 at 23:03

answered Aug 18, 2015 at 20:49

vendettamit

14.7k2 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gert Arnold Over a year ago

I would create the result list first (as list of object[]s) and then load the datatable. Linq methods are not supposed to have side effects and now the role of ToList is somewhat obscure.

Adam · Accepted Answer · 2015-08-19 13:15:17Z

0

After considering what @DStanley said about LINQ, I abandoned it and went with a foreach statement. See code below:

ds.Tables[3].Columns[2].ReadOnly = false;
while (reader.Read())
{
    foreach (DataRow item in ds.Tables[3].Rows)
    {
        if ((Guid)item[3] == reader.GetGuid(0))
        {
            item[2] = reader.GetInt32(1);
        }
    }
}

answered Aug 19, 2015 at 13:15

Adam

1,4834 gold badges22 silver badges52 bronze badges

Collectives™ on Stack Overflow

How do I use LINQ to update a datatable with a SqlDataReader?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related