How can I create a LINQ-to-SQL statement when I have table name as string?

Question

If I have the name of a database table like this:

string tableName = "Addresses";
string tableName = "Customers";

How can I construct a dynamic LINQ statement like this:

var items = from o in db.{tableName}
   select o;

foreach (var item in items)
{
    sb.Append(item.Id + Environment.NewLine);
}

I know I could do something like this:

IEnumerable<Customer> results = db.ExecuteQuery<Customer>
    ("SELECT contactname FROM customers WHERE city = {0}",
    "London");

But in this instance I don't want strongly typed objects as my result, I just want a recordset to pick apart.

Answer:

Thanks Shalkalpesh, I took your advice and solved this by just avoiding LINQ altogether:

SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["main"].ToString();
conn.Open();
string sql = "SELECT * FROM " + tableName;
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataTable dtResult = new DataTable();
da.Fill(dtResult);
foreach (DataRow drRow in dtResult.Rows)
{
    Console.WriteLine(drRow["Id"].ToString());
}
da.Dispose();
conn.Close();
conn.Dispose();

Answer 1

LINQ to SQL is meant to be strongly typed so I don't think you can use LINQ to SQL to use dynamic table names unless you use ExecuteQuery

Thanks

Answer 2

You can use the Dynamic Linq Query library (or D-Linq for short).

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Answer 3

Sorry - I'm away from a dev machine at the moment, but would this help?

It seems to suggest you should use DynamicQuery ...

Another way - as was mentioned by several commenters in my previous post - is to use DynamicQuery. DynamicQuery is one of the samples installed with the 101 LINQ samples and you can find it by clicking on Help | Samples in Visual Studio. If you drill into the sample folders there's a DynamicQuery sample project, which basically consists of a class that provides string based lambda expression parsing.

The class DynamicQuery class is self contained and you can simply add it to your project. It provides additional extension methods that let you use string expressions for various of the query methods including the .Where() method (but unfortunately for the above example not the .Single() method). So with Dynamic Query the above .Load() method can also be written as follows:

There's an actual code example on the post, too...

Answer 4

I don't think Dynamic Linq is the solution here.

As far as I know, there is no solution to your problem.

Even with dynamic linq, the compiler would need to somehow figure out what table the string refers to at compile time to allow strong typing of its members.

For instance, let's say you have two tables:

Product {Id, Name, Value}
Customer {Id, Firstname, Surname, Address, Email, ...}

And you use Linq-to-SQL as your ORM:

var items = from p in MagicTableResolver("Product")
            where p.Firstname // <-- How could intellisense allow this?
            select p;

var items = from c in MagicTableResolver("Customer")
            where c.Name // <-- It can't, it cannot be strongly typed
            select c;

Answer 5

If you want the recordset, you can access the Connection property of the DataContext class (db variable in your context) and use it to execute regular query and get the result in either of DataTable or DataReader.