LINQ - want to give table name dynamically in linq

Asked By Reena Jain on 12-Dec-11 06:52 AM
HI,
 
I need to replace hard code mapping to dynamic mapping like
 
Instead of this
 
var results = db.tablename;
 
I need to do something like this.
 
string tableName = "tablename";
 
var results = db[tableName];
It could be any table name that is available in dataContext.
 
means with data context i want to use table name dynamically
I am using this but its not working
DataClassesDataContext objcontext= new DataClassesDataContext();
 var q =  (from a in objcontext.GetTableByName(objcontext, tablenm) select a);
Riley K replied to Reena Jain on 12-Dec-11 07:12 AM
Chintan Vaghela replied to Reena Jain on 12-Dec-11 07:13 AM

Hello,

You can use GetTable() to get the corresponding ITable of your data. Then coupled with using DLINQ , making it relatively easy.

This example uses the AdventureWorks database. My project has the context defined in the DatabaseTest assembly in the DatabaseTest.AdventureWorks namespace.

'' need my database and DLINQ extensions up top
Imports DatabaseTest.AdventureWorks
Imports System.Linq.Dynamic

'' sample inputs
Dim dc = New AdventureWorksDataContext()
Dim tableName = "Contact"
Dim whereClauses() = {"FirstName = ""John"" OR LastName = ""Smith"""}
Dim selectColumns() = {"FirstName", "LastName"}

'' get the table from a type (which corresponds to a table in your database)
Dim typeName = "DatabaseTest.AdventureWorks." & tableName & ", DatabaseTest"
Dim entityType = Type.GetType(typeName)
Dim table = dc.GetTable(entityType)
Dim query As IQueryable = table

'' add where clauses from a list of them
For Each whereClause As String In whereClauses
    query = query.Where(whereClause)
Next

'' generate the select clause from a list of columns
query = query.Select(String.Format("new({0})", String.Join(",", selectColumns)))

In retrospect, using reflection might have been the easier way to get the table since you have the name already. But then the names might not have a 1-to-1 correspondence so you'll have to compensate for it.

Dim table As ITable = dc.GetType().GetProperty(tableName & "s").GetValue(dc, Nothing)

Hope this is helpful !

Thanks

 

 

 

 

 

Reena Jain replied to Riley K on 12-Dec-11 07:25 AM
Hi,

No it won't work
the example you have provided is having table name (products) with  the db name, i don't want to put table name hard code i want to use it like a variable
Reena Jain replied to Chintan Vaghela on 12-Dec-11 07:27 AM
Hi,

I have already check the link from where you have copied it

http://stackoverflow.com/questions/5032228/how-can-i-dynamically-select-my-table-at-runtime-with-dynamic-linq

but its not working
Riley K replied to Reena Jain on 12-Dec-11 08:36 AM


Hi Reena,

         I don't think you got an option to pass the table name dynamically, this is because the Whole DataBase Model is already generated like Entity, when it is already generated then how could you pass a table name to context.

One option is to look at ExecuteStoreQuery, I have tested this and i could pass the table name dynamically

here is how i have done

EmployeeModel.EmployeeEntities ctx = new EmployeeModel.EmployeeEntities();
 
    string tableName="Employee";
    string st = string.Format(Employee.str, tableName);


In the highlighted code above Employee is a class in which i am writing query

public static class Employee
{
 
  public static string str = "Select * from {0} ";
}

And I could pass the table name

Try and let me know


Regards
Riley K replied to Riley K on 12-Dec-11 08:37 AM


Continue..

Now after this

string tableName="Employee";
  string st = string.Format(Employee.str, tableName);


you could call the ExecuteStoreQuery Method


Regards
Suchit shah replied to Reena Jain on 12-Dec-11 01:58 PM
I think you can try it with the use of Dynamic LINQ I know we can pass table type and column name we can pass it as a argument to the function.... I think you can try your existing query like below one

IQueryable queryableData = dc.GetTable(tableType).AsQueryable();

where tableType is a Argument as a Type
Please see one of the Dynamic LINQ example on http://geekswithblogs.net/SudheersBlog/archive/2008/11/25/127380.aspx

Hope it helps