Linq Select * from Table ExecuteQuery

Question

First let me start by saying that I don't have a complete understanding of Linq. I am trying to dynamically query a database, The first query uses LINQ-SQL which works fine, but the second dynamic call is what fails in run time

public void getTables()
    {
        foreach (var c in dc.GetTable<TableListing>())
        {
            List<TableData> res = tableBrowse(c.TableName);
        }                  
    }

 public List<TableData> tableBrowse(string tablename)
    {
        string sql = "Select * from " + tablename;
        var results = dc.ExecuteQuery<TableData>(sql);
        return results.ToList();
    }

public class TableData
    {
        public int Time { get; set; }
        public string Value { get; set; }
    }

I query the "master table" and this retrieves a list of tables to query. They all have the same structure, as defined in the class TableData. I get a runtime error about Specified cast is not valid. I'm not really looking for code as much as I am looking for what I am doing wrong and how to fix it. Thanks.

Answer 1

You aren't explicitly converting the return value from dc.ExecuteQuery<TableData>(sql) to the TableData type that you've defined. I expect that the ExecuteQuery is complaining because it doesn't know what the TableData type is.

The ExecuteQuery helper needs to return a DBML (LINQ-to-SQL generated) type as defined in your database.

But I would suggest that you don't go down this route. If you want to get records from a table, say Customers, just use content.Customers - the point of LINQ-to-SQL is that it already contains all these accessors to save you time.

Answer 2

You might try decorating your class properties with ColumnAttributes, specifying the column name and type so that LINQ to SQL knows how to do the version of the column data to the properties. You may also need to set other attribute properties to make it work correctly. I would also specify the column names in the SQL instead of using *. Put the column names in the same order as your properties appear in the class as I believe that it processes the result values in the same order as the properties are defined. Not sure it this will work or not, but essentially you're recreating what the designer would do for you.

public List<TableData> tableBrowse(string tablename)
{
    string sql = "Select [time], [value] from " + tablename;
    var results = dc.ExecuteQuery<TableData>(sql);
    return results.ToList();
}

public class TableData
{
    [Column( Name="Time", DbType="DateTime NOT NULL", ... )]
    public int Time { get; set; }

    [Column( Name="Value", DbType="VARCHAR(255) NOT NULL", ... )]
    public string Value { get; set; }
}

Answer 3

Actually I found out what the problem was, I was missing a table definition. There was a third data type in one of the tables. Once I defined that table class and checked for the data type it worked fine. Sadly the compiler just didn't give that much information on just what was wrong.