Querying a DataTable using LINQ

Question

First apologies if I don't explain this properly, I've been at this for hours, and it's now morning.

I've tried so many methods, got so many errors that I can't remember the original version, nor can I solve the problem, here's my code, it's terrible as I should be using a join query my SP's are bugged on this server.

SqlConnection conn = new SqlConnection(connstring);
DataSet ds = new DataSet();
SqlDataAdapter ad;
SqlCommand cmd = new SqlCommand();
ad = new SqlDataAdapter("SELECT * FROM booking WHERE bookstartdate BETWEEN '" + ReturnDbDate(datefrom).ToString() + "' AND '" + ReturnDbDate(dateto).ToString() + "'", conn);
ad.Fill(ds, "CustomerIds");

ad = new SqlDataAdapter("SELECT customerid, firstname, lastname, telephone, email FROM customer", conn);
ad.Fill(ds, "Customers");

DataTable dt = new DataTable();
dt.Columns.Add("Customerid", typeof(String));
dt.Columns.Add("Firstname", typeof(String));
dt.Columns.Add("Lastname", typeof(String));
dt.Columns.Add("Telephone", typeof(String));
dt.Columns.Add("Email", typeof(String));

int lol = ds.Tables["CustomerIds"].Rows.Count;

foreach (DataRow row in ds.Tables["CustomerIds"].Rows)
{
    IEnumerable<DataRow> r = from dr in ds.Tables["Customers"].AsEnumerable()
                             where dr.Field<Guid>("customerid").ToString() == row[2].ToString()
                             select dr;
    dt.Rows.Add(r);
}

return dt;

When I try loop through the dataset using the following:

foreach (DataRow rows in dt.Rows)
{
    sb.Append("<tr><td>" + rows["Customerid"].ToString() + "</td><td>" + rows[1] + "</td><td>" + rows[2] +"</td><td>" + rows[3] + "</td></tr>");
}

I get:

System.Data.EnumerableRowCollection`1[System.Data.DataRow]

Anyone any ideas? Completely brain dead atm so it might be something simple.

Thanks

Edit:

DataRow r = from dr in ds.Tables["Customers"]
            where dr.Field<Guid>("customerid").ToString() == row[2].ToString()
            select dr;    
dt.ImportRow(r);

Error: Could not find an implementation of the query pattern for source type 'System.Data.DataTable'. 'Where' not found.

I'm assuming my LINQ syntax is not incorrect, although I think there's a IEnumberable<T>.Where() method? I remember it, just can't remember how to access it.

Edit2:

I fail, managed to re-create the problem again, sigh

 IEnumerable<DataRow> r = from dr in ds.Tables["Customers"].Select().Where(x => x.Field<Guid>("customerid").ToString() == row[2].ToString())
                            select dr;



                dt.ImportRow(r);

Answer 1

dt.Rows.Add() takes in a DataRow but your providing IEnumerable<DataRow> Also note that rows can only be added to a DataTable that has been created with dt.NewRow() try using dt.ImportRow() instead

EDIT:

Skip the temp DataTable and join the two datatables in the dataset instead. Or even better, skip using linq and join the tables in the database query instead.

return 
  from dr in ds.Tables["Customers"].AsEnumerable()
  join dr2 in ds.Tables["CustomerIds"].AsEnumerable()
    on dr.Field<Guid>("customerid") equals dr2.Field<Guid>(2)
  select dr;

Plain SQL

public DataTable GetCustomers(DataTime datefrom, DataTime dateto)
{
    var sql = @"
        SELECT customer.customerid, firstname, lastname, telephone, email
        FROM customer
        JOIN booking
            ON customer.customerid = booking.customerid
        WHERE bookstartdate BETWEEN '" + ReturnDbDate(datefrom).ToString() + "' AND '" + ReturnDbDate(dateto).ToString() + "'";

    using (SqlConnection conn = new SqlConnection(connstring))
    using (SqlDataAdapter ad = new SqlDataAdapter(sql, conn))
    {
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds.tables[0];
    }
}