LINQ to SQL custom query builder

Question

It was easy to build a custom query like this with ADO.NET:

SqlCommand.CommandText = "SELECT Column" + variable1 + ", Column" + Variable2 + " FROM TABLE";

Is that able to do so in LINQ to SQL?

Thanks

You'll need to provide us with a better example of how you are going to use it before we can help you. — GrandMasterFlush, May 29 '12 at 14:24
Did you build your queries that way with ADO.NET? I mean by concatenating string with values? — Darin Dimitrov, May 29 '12 at 14:25
`string variable1 = "a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't";` — Oskar Kjellin, May 29 '12 at 14:26
@OskarKjellin I know people can do injection attach if variable is from UI, but my variable is not. — etlds, May 29 '12 at 14:44

score 0 · Answer 1 · edited May 23 '17 at 10:09

No there is no common way to build a dynamic query.

A method has to have a known, specific return type. That type can be System.Object but then you have to use a lot of ugly reflection code to actually get the members. And in this case you'd also have to use a lot of ugly reflection expression tree code to generate the return value.

If you're trying to dynamically generate the columns on the UI side - stop doing that. Define the columns at design time, then simply show/hide the columns you actually need/want the user to see. Have your query return all of the columns that might be visible.

Unless you're noticing a serious performance problem selecting all of the data columns (in which case, you probably have non-covering index issues at the database level) then you will be far better off with this approach. It's perfectly fine to generate predicates and sort orders dynamically but you really don't want to do this with the output list. More about this

score 0 · Answer 2 · answered Aug 01 '18 at 14:08

Yes You can do something like that with Dynamic query with Linq.
This is an example that you can build a custom query with Dynamic query with Linq:

string strWhere = string.Empty;
    string strOrderBy = string.Empty;

    if (!string.IsNullOrEmpty(txtAddress.Text))
        strWhere = "Address.StartsWith(\"" + txtAddress.Text + "\")";  
    if (!string.IsNullOrEmpty(txtEmpId.Text))
    {
        if(!string.IsNullOrEmpty(strWhere ))
            strWhere = " And ";
        strWhere = "Id = " + txtEmpId.Text;
    }
    if (!string.IsNullOrEmpty(txtDesc.Text))
    {
        if (!string.IsNullOrEmpty(strWhere))
            strWhere = " And ";
        strWhere = "Desc.StartsWith(\"" + txtDesc.Text + "\")";
    }
    if (!string.IsNullOrEmpty(txtName.Text))
    {
        if (!string.IsNullOrEmpty(strWhere))
            strWhere = " And ";
        strWhere = "Name.StartsWith(\"" + txtName.Text + "\")";
    }

    EmployeeDataContext edb = new EmployeeDataContext();
    var emp = edb.Employees.Where(strWhere);
    grdEmployee.DataSource = emp.ToList();
    grdEmployee.DataBind();

For more information you can check this page.

There's nothing dynamic here, it's nothing but string concatenation. The string happens to be used in Dynamic LINQ, but that doesn't make the string building itself dynamic. Moreover, don't use parameters in the query string *and* you overwrite `strWhere` all the time so you can't have multiple predicates. This is a very poor answer. — Gert Arnold, Aug 01 '18 at 14:27

LINQ to SQL custom query builder

2 Answers2