4

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.

Improve this question

2 Answers 2

Reset to default
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; }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

This is not what the question is about. The "column_name" is not known at compile time.
From the question: "I am passing two strings(column name, search parameter) to my controller". This indicates that the column name is not known at compile time.
@VictorHugoTerceros i am trying to use Dynamic LINQ library(System.Linq.Dynamic) as i stated. Column name and search_parameters are unknown they are passed from front end via ajax. I am trying to avoid using SQLQuery.
@Wazner yes that is what i am asking for, got any idea?
@VictorHugoTerceros i tried using EDIT 3, but it gave me : A first chance exception of type 'System.Linq.Dynamic.ParseException' occurred in System.Linq.Dynamic.dll Error: Expression of type 'Boolean' expected. I am looking currently at stored procedures as last resort.
|
1

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)");
Improve this answer

10 Comments

strange. If dynamic linq allows only manipulation of values at run time then there is no need for it since LINQ already allows for that. I thought it was possible to do something like: .Where("@0.Contain(@1)", columnname, search_parameter). Not sure why i can not reply to you by using @?
Well, the substitution for the column name in the string is manipulation at run time?
Yes if i understand your question correctly. I get column name based on the user's search option selection, which they select on front end. Controller does not know it till it is posted.
No, I meant that my shown code is manipulating setting the column name dynamically from the column_name variable by using string interpolation (C# 6), so I don't understand your issue with it.
Oh my bad misunderstood your there. The format you provide gives error expecting ';'. The Error comes the moment i add '$' sign before the quotes. The point i was making earlyer, it would be strange if Dynamic.LINQ does not support column substitution, since there is no point for it then. I can directly use LINQ to compare dynamic value to static(known) column name.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.