Convert DataTable to LINQ: Unable to query multiple fields

Question

Importing a spreadsheet I have filled a DataTable object with that data and returns expected results.

Attempting to put this into a format I can easily query to search for problem records I have done the following

public void Something(DataTable dt)
{
     var data = from row in dt.AsEnumerable()
                select row["Order"].ToString();
}

Works as expected giving me a list of orders. However I cannot add other fields to this EnumerableRowCollection. Attempting to add other fields as follows gives me an error

public void Something(DataTable dt)
{
     // row["Version"] throws an error on me
     var data = from row in dt.AsEnumerable()
                select row["Order"].ToString(), row["Version"].ToString();
}

Error: "A local variable named 'row' cannot be declared in this scope because it would give a different meaning to 'row' which is already used in a 'child' scope to donate something else"

I'm thinking I need to alias the column name but I'm having no luck. What am I missing here?

Answer 1

I think you should use select new like this query for example:

var q = from o in db.Orders
        where o.Products.ProductName.StartsWith("Asset") && 
              o.PaymentApproved == true
        select new { name   = o.Contacts.FirstName + " " +
                              o.Contacts.LastName, 
                     product = o.Products.ProductName, 
                     version = o.Products.Version + 
                              (o.Products.SubVersion * 0.1)
                   };

Answer 2

It sounds like you're writing a bad select statement. Try the following:

public void Something(DataTable dt)
{
    var data = from row in dt.AsEnumerable()
               select new { 
                            Order = row["Order"].ToString(), 
                            Something = row["Something"].ToString(),
                            Customer = row["Customer"].ToString(),
                            Address = row["Address"].ToString()
                          };
}

That will create a new collection of Anonymously Typed objects that you can iterate over and use as needed. Keep in mind, though, that you want be able to return data from the function. If you need that functionality, you need to create a concrete type to use (in place of anonymous types).

Answer 3

You probably want the following.

var data = from row
           in dt.AsEnumerable()
           select new { Order = row["Order"].ToString(), Version = row["Version"].ToString() };