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?

You can refere these articles and answers: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx http://social.msdn.microsoft.com/Forums/eu/linqprojectgeneral/thread/d9ffe9a1-59c3-4776-900a-4b45e0b4abcf http://stackoverflow.com/questions/7800770/how-can-you-dynamically-select-a-table-with-entity-framework-4-x http://www.west-wind.com/weblog/posts/2007/Aug/13/LINQ-to-SQL-and-Dynamic-Queries-and-Expressions — Saw, Jan 07 '13 at 17:22
I'll try to search at those links. I'll let you know if I find something useful. — benVG, Jan 07 '13 at 17:29

score 4 · Answer 1 · answered Jan 07 '13 at 17:19

4

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();

answered Jan 07 '13 at 17:19

CR41G14

5,464
5
43
64

1

This will give you an anonymous type, but not a dynamic result! – Saw Jan 07 '13 at 17:20
1

I can not, in the project we can only use LINQ to SQL, is one of the customer's requirements (it is a plug-in for an already existing application). – benVG Jan 07 '13 at 17:22
However, it is an useful alternative :) – benVG Jan 07 '13 at 17:24
@benVG - Then you will need to use reflection to get the Type of table from the tablename and create an instance of this object, you cannot do it in linq to my knowledge – CR41G14 Jan 07 '13 at 17:24

score 1 · Answer 2 · answered Jan 07 '13 at 18:42

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();

score 0 · Answer 3 · answered Mar 25 '20 at 15:38

I know this is old, but if you are here looking for answers like I was, then maybe this will help. I'm using a .NET ObjectContext directly instead of a DataContext data source. If you are using the DataContext version then you can simply (I hope) use queryResults = myGlobalContext.ExecuteQuery<dbGenericData>(query).ToList(); instead and I'm pretty sure it will work the same way.

Your tables will be a lot easier to work with if you have standards in naming and design like

the ID field for the table is always X type (INT, GUID, etc)
the ID field is always named tableNameID, the 'table name' with ID tagged on.
etc,

This will allow you to easily build the ID field by simply appending the 'ID' string onto the table name and will allow you to use a reliable CAST, if needed.

Speaking of CAST you will notice one in the query string. You will need to modify the use of the SQL string using CAST, changing field lengths like my nvarChar(50) example, etc, to overcome getting various TYPES of data from your database.

Final note: In the query string you will see I use the 'AS' key word to cast the DB field to a new name. I cast the 'tableIDField' into the name 'id' and I cast the 'requestedField' into the name 'dbData'. This allows the system to match up the renamed fields from the DB into the STRUCT object container we dump the data into. This allows you to construct generic containers to hold the data returned without having to worry about matching up with the DB field names.

I'm not a guru at this stuff, but I hope this helps somebody out.

private void testMethod(string requestedField, string tableName)
{
    var tableIDField = tableName + "ID";

    var query = "select " + tableIDField + " as id, CAST(" + requestedField + "as nvarchar(50)) as dbData from " + tableName;

    List<dbGenericData> queryResults = null;

    try
    {
        queryResults = myGlobalContext.ExecuteStoreQuery<dbGenericData>(query).ToList();
    }
    catch (Exception ex)
    {
        //Simply ignore any exceptions.  
        //These will need examined to determine best solution to unexpected results.
    }
}
private struct dbGenericData
{
    public dbGenericData(int id, string dbData)
    {
        this = new dbGenericData();
        ID = id;
        DBData = dbData;
    }

    public int ID { get; set; }

    public string DBData { get; set; }

}

score -1 · Answer 4 · answered Jun 10 '17 at 08:59

-1

you can Generic Method and use db.Set<T> that return a DbSet Based on T

answered Jun 10 '17 at 08:59

data

83
2
11

Season Mak · Answer 5 · 2013-03-09T18:47:54.437

-2


var esql = "select t from TypeName as t"
var q = db.CreateQuery(esql);

Use entity sql for linq to sql, http://esql.codeplex.com

edited Mar 09 '13 at 18:47

answered Mar 09 '13 at 18:41

Season Mak

9
2

Dynamic query using LINQ to SQL

5 Answers5

Linked