How do I create a linq query where the select column name is a variable

Question

I have a configuration table that contains various configurations for permissions to show various data a e.g.

PermissionTable
key1    key2    showconfig1 showconfig2
1       A            Y             N
2       B            N             N
3       C            Y             Y

An application of various webservices make queries at different points in the code to the same table to get values from different showconfig columns for permissions.
How can I create a generic centralized query where the app can pass the key1 value , the key2value and a columnName variable to get the value of a particular column row without having to write a separate query to query each column row or without having to write a query to retrieve all the columns values for a particular row?

e.g. the following just returns the column name I want the value in that column:

public string GetPermission(int key1, int key2 ,string columnName)
{
   string show = "N";  
   var show = (from p in db.PermissionTable
               where p.key1== key1
               && p.key2 == key2
               select p.[columnName]).FirstOrDefault().ToString();
   return show;
}

There is a thread on this http://stackoverflow.com/questions/4203193/select-column-based-on-variable-in-linq-to-sql — bsayegh, Aug 28 '14 at 14:50
Possible duplicate: http://stackoverflow.com/questions/16516971/linq-dynamic-select — DanP, Aug 28 '14 at 14:51
Why not select all the object and then extract the property with reflection? — simoneL, Aug 28 '14 at 14:51
Is there a specific reason why you need to use Linq for this? Its not really the best scenario if you have to waste a bunch of time figuring it out. If you really want to use Linq, write a Stored Proc that takes in a column name as parameter, then select that column in sql. You can map the Stored Proc in Linq to Sql and it will just return the value you request. — bsayegh, Aug 28 '14 at 14:53

andleer · Answer 1 · 2014-08-28T15:01:29.487

You can dynamically apply filters to your query but it is not easy to dynamically generate or vary your result types based on some type of condition. Types (anonymous or otherwise) are defined at compile time, not runtime.

var result = from p in db.PermissionTable
          select new { p.ColumnName, p.Key1, p.Key2 };

if(condition1)
    result = result.Where(i => i.Key1 == filter1);

if(condition2)
    result = result.Where(i => i.Key2 > 0);

var result2 = from p in result
    select p.ColumnName;

return result2.FirstOrDefault().ToString();

If you really need to vary the query columns based on different criteria, I think it is reasonable to have multiple queries.

stepandohnal · Answer 2 · 2014-08-28T15:45:44.203

1

You can use dynamic linq extensions see http://dynamiclinq.codeplex.com/documentation

With this library you can select the data in format like this:

db.PermissionTable.Select("MyColumn");

edited Aug 28 '14 at 15:45

answered Aug 28 '14 at 14:54

stepandohnal

457
4
13

How do I create a linq query where the select column name is a variable

2 Answers2