Dynamic query using LINQ to SQL

Question

I need to figure out if it is possible to dynamically build a query with LINQ, dynamically selecting the table in which to perform the query.

This is an example of what I would do:

//Not working,just for example

public List<dynamic> _getGenericList(String tableName)
    {
        var l = from a in db.//I need to use here tableName
                  select a;

        return l.ToList<dynamic>();
    }

Is there a way to make this possible?

Answer 1

If the query is this simple you can dynamically create a standard sql statement and execute it, this is the most simplest way without using processor heavy reflection and complex code?

var query = "SELECT * FROM " + tableName;
var res = context.ExecuteQuery<dynamic>(query).ToList();

Answer 2

I've found a way to do it, but I'm not sure if I'd use this code. If you have a DataContext that contains two tables:

PrimaryTable 
    ID,
    FirstValue,
    SecondValue

SecondaryTable
    ID,
    FirstSecondaryValue

You could use the following DataHelper class:

class DataHelper
{
    public MyDatabaseDataContext db = new MyDatabaseDataContext();

    List<dynamic> GetDynamicList<T>() where T : class
    {
        System.Data.Linq.Table<T> table = db.GetTable<T>();

        var result = from a in table select a;

        return result.ToList<dynamic>();
    }

    public List<dynamic> GetWhatIWant(string tableName)
    {
        Type myClass = Type.GetType("DynamicLinqToSql." + tableName);
        MethodInfo method = typeof(DataHelper).GetMethod("GetDynamicList", BindingFlags.NonPublic | BindingFlags.Instance);
        method = method.MakeGenericMethod(myClass);
        return (List<dynamic>)method.Invoke(this, null);
    }
}

Then you can create an instance of your DataHelper and call the GetWhatIWant method, passing in the table name.

var dataHelper = new DataHelper();

List<dynamic> myFirstList = dataHelper.GetWhatIWant("PrimaryTable");

for (int i = 0; i < 5 && i < myFirstList.Count; i++)
{
    System.Console.WriteLine(String.Format("{0} - {1}", myFirstList[i].FirstValue.ToString(),  myFirstList[i].SecondValue.ToString()));
}

List<dynamic> mySecondList = dataHelper.GetWhatIWant("SecondaryTable");

for (int i = 0; i < 5 && i < mySecondList.Count; i++)
{
    System.Console.WriteLine(mySecondList[i].FirstSecondaryValue.ToString());
}

System.Console.ReadKey();