System.Linq.Dynamic dynamic column name and parameter

Question

I am new to LINQ. I need to perform dynamic operations which seems to be done either with expression trees, or dynamic library. I chose to use System.Linq.Dynamic since it is faster(at this point). I looked up around but nothing seems to help address my issue. I saw several stack post on the same issue but even after changing my code to that it still not resolved.

I am passing two strings(column name, search parameter) to my controller which will execute query and return JSON object. Column name and search parameters have to be dynamic.

The SQL query i need to execute: 
SELECT top 50 c1, c2, c3 
FROM TableName
WHERE column_name LIKE '%search_parameter%';


var result = db.TableName
   .Where("@0.Contains(@1)", column_name, search_parameter)
   .Select("new(c1, c2, c3.)");

For now i just want to select select those specific columns ill worry about top 50 later.

I tried also reading through examples on System.Linq.Dynamic but i think i am lacking the basic knowledge i need to understand a lot of the content. I would appreciate some pointers. Thank you.

Answer 1

For LIKE you can use Contains

column_name.Contains(search_parameter)

To select the 3 columns in linq just use this

.Select(x=> new { x.c1,x.c3,x.c3 })

And to get the top 50 just use

.Take(50)

So basically

var result = db.TableName.Where(x => x.column_name.Contains(search_parameter)).Select(x=> new { x.c1,x.c3,x.c3 }).Take(50);

EDIT

If you need to execute dynamic SQL you can use SqlQuery something like this

string query = string.format("SELECT TOP 50 c1, c2, c3 
FROM TableName WHERE {0} LIKE '%{1}%'",column_name,search_parameter);
var result = db.Database.SqlQuery<YOURRESULTCLASS>(query);

EDIT 2

If you have security concerns you can use a stored procedure instead

db.Database.SqlQuery<YOURRESULTCLASS>("storedProcedureName",params);

EDIT 3

Since the sample code is using System.Linq.Dynamics this code should work

string column_name = "name";
            string column_value = "C";
            string where = string.Format("{0}.Contains(@0)", column_name); //first create the where clause with the column name
            var result = Courses.Where(where,column_value).Select("new(name,courseID)").Take(50);   //here apply the column value to the name

This is the entire console application so you can test code

class Program
{
    static void Main(string[] args)
    {
        List<course> Courses = new List<course>();
        Courses.Add(new course() { name = "CA", courseID = 1 });
        Courses.Add(new course() { name = "CB", courseID = 2 });
        Courses.Add(new course() { name = "CC", courseID = 3 });

        string column_name = "name";
        string column_value = "C";
        string where = string.Format("{0}.Contains(@0)", column_name); //first create the where clause with the column name
        var result = Courses.Where(where,column_value).Select("new(name,courseID)").Take(50);   //here apply the column value to the name
        foreach (var item in result)
        {
            Console.WriteLine(item);
        }
        Console.ReadLine();
    }
}

public class course
{
    public string name { get; set; }
    public int courseID { get; set; }
}

Answer 2

The documentation isn't very clear, but I believe parameter substitution is only supported for values, not column names. While risky, you can use interpolation to insert the column names if you trust the source:

var result = db.TableName
   .Where($"{column_name}.Contains(@0)", search_parameter)
   .Select("new(c1, c2, c3)");