LINQ Query Returning No Value

Question

Hi guys.

I'm currently trying to run a LINQ query over a MS SQL database. This is what I have done.

1. I can see the database, the table, and the data inside if i want to via the Server Explorer.
2. I've created a new DataSet automatically from "Add new item..". I've then dragged the table from the server explorer into the designer view of the DataSet. The correct table and column headers appear. Let's call it "MyTableDataSet".

3. In my code, when I need to query this is what my code looks like.

    MyTableDataSet data = new MyTableDataSet ();
    var queryResult =
    from c in data.MyTable
       select c;
   
   
    foreach (var date in queryResult)
    {
       // nothing!
    }
   

4. Not sure why, but looking at 'queryResult' through the debugger after the query is ran I get - Empty = "Enumeration yielded no results".

Am I doing something wrong with the LINQ expression? I'm guessing that is a 'SELECT *'. The table does have data in it there's about 50 rows. Maybe I need to 'Fill' the dataset? I'm using Windows Authentication through the Server Explorer to see the data. If I can see it from there, then the code should have access as well? Not sure. :|

Thanks for any help that you guys might give in resolving this issue.

Answer 1

According to your code, you are creating a new dataset and immediately querying it. It will of course be empty.

You have to fill your dataset using the table adapter that were created when you dragged the table in the dataset designer.

Answer 2

Here's a working example using a DataTable:

var queryResult = from c in data.MyTable select c;
DataTable dt = ToDataTable(yourDataContext, queryResult);

foreach (DataRow dr in dt.Rows)
{
  // ...
}
dt.Dispose();

The ToDataTable function is from here:

public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
     if (query == null)
     {
          throw new ArgumentNullException("query");
     }

     IDbCommand cmd = ctx.GetCommand(query as IQueryable);
     SqlDataAdapter adapter = new SqlDataAdapter();
     adapter.SelectCommand = (SqlCommand)cmd;
     DataTable dt = new DataTable("sd");

     try
     {
          cmd.Connection.Open();
          adapter.FillSchema(dt, SchemaType.Source); 
          adapter.Fill(dt);
     }
     finally
     {
          cmd.Connection.Close();
     }
     return dt;
}