Currently using:

• ASP.NET Core 3.1 / EF Core
• C#
• Code-first approach
• Postgres database

I'm building a method to support column searching on a table. I need to feed the column name to be searched by string value and build a query / lambda that can search the right column. I suspect I need to build some sort of expression and search on the expression but am having trouble with the syntax.

Here's the base code:

string search = "Search Value";
string givenColumn = "search_column";
IQueryable<MyModel> data = _dbContext.table;
data = data.Where(data => data.givenColumn.Contains(search));

I'd like to feed the column name in givenColumn and be able to build a query that searches the right column. At first I thought I wanted reflection but I'm looking to build a SQL query based off of a string, so I think I want to build an expression?

TIA!

CodePudding user response：

Here is some sample code for a runtime WhereContains that operates on string columns:

public static class IQueryableExt {
    // String.Contains(string)
    static MethodInfo containsMI = typeof(string).GetMethod("Contains", 0, new[] { typeof(string) });

    // generate r => r.{columnname}.Contains(value)
    static Expression<Func<T, bool>> WhereContainsExpr<T>(string columnname, string value) {
        // (T r)
        var rParm = Expression.Parameter(typeof(T), "r");
        // r.{columnname}
        var rColExpr = Expression.Property(rParm, columnname);
        // r.{columnname}.Contains(value)
        var bodyExpr = Expression.Call(rColExpr, containsMI, Expression.Constant(value));
        return Expression.Lambda<Func<T,bool>>(bodyExpr, rParm);
    }

    public static IQueryable<T> WhereContains<T>(this IQueryable<T> src, string columname, string value) => src.Where(WhereContainsExpr<T>(columname, value));   
}

CodePudding user response：

Just pass HTML Table id as a parameter onkeyup method of input field. HTML Code:

<input type="text" id="myInput"   onkeyup="searchData('myTable')" placeholder="Search...">

Javascript Code for exact match of any column:

function searchData(tableId) {
// Declare variables 
var input, filter, table, tr, i, j, column_length, count_td;
column_length = document.getElementById(tableId).rows[0].cells.length;
input = document.getElementById("myInput");
filter = input.value.toUpperCase();
table = document.getElementById(tableId);
tr = table.getElementsByTagName("tr");
if (filter != "") {
    for (i = 1; i < tr.length; i  ) { // except first(heading) row
        count_td = 0;
        for (j = 1; j < column_length - 1; j  ) { // except first column
            td = tr[i].getElementsByTagName("td")[j];
            /* ADD columns here that you want you to filter to be used on */
            if (td) {
                if (td.innerHTML.toUpperCase() === filter) {
                    count_td  ;
                }
            }
        }
        if (count_td > 0) {
            tr[i].style.display = "";
        } else {
            tr[i].style.display = "none";
        }
    }
}
else {
    for (i = 1; i < tr.length; i  ) {
        tr[i].style.display = "";
    }
}

}