There are ways LINQ can query completely dynamically from Database.
I had requirement to get a column value of a table in a generic and dynamic manner for a given key value.
Here I pass the table type (LINQ type) and key value to retrieve other column values of the table in a totally dynamic manner.
private string GetColumnValue(DataContext dc, Type tableType, Type keyColumnType, string keyColumnName, object objectValue, string getColumnName)
{
string getColumnValue = null;
IQueryable queryableData = dc.GetTable(tableType).AsQueryable();
//Create an expression that gets specific property
// tbl.Where (item => item.PropertyName == propertyValue)
ParameterExpression pe = Expression.Parameter(tableType, "item");
Expression left = Expression.Property(pe, tableType.GetProperty(keyColumnName));
//Convert the key value to the respective type
Expression right = Expression.Constant(Convert.ChangeType(objectValue, keyColumnType), keyColumnType);
Expression e1 = Expression.Equal(left, right);
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { queryableData.ElementType },
queryableData.Expression,
Expression.Lambda(e1, new ParameterExpression[] { pe }));
// Create an executable query from the expression tree.
IQueryable results = queryableData.Provider.CreateQuery<MediaRequest>(whereCallExpression);
//NOTE: Onlye IQueryable<T> has methods like FirstOrDefault<T>().
//So do a for..each to get the object in IQueryable.
foreach (object o in results)
{
//Use reflection to get the value property value
try
{
getColumnValue = o.GetType().GetProperty(getColumnName).GetValue(o, null).ToString();
break;
}
catch (Exception ex)
{
//Log the exception
}
}
return getColumnValue;
}
Example and Usage:
MyDataContext mediaDB = new MyDataContext("Server=Server;Database=DB;Trusted_Connection=true");
string tableTypeString = "Data.Request, Data"; //This is the full name of the LINQ class that corresponds to a data table
Type tblType = Type.GetType(tableTypeString);
string fileName = GetColumnValue(mediaDB, tblType, typeof(int), "RequestID", 1), "FileName");
string ipAddress = GetColumnValue(mediaDB, tblType, typeof(int), "RequestID", 2, "IPAddress");
Hope this helps..!