Dynamic Where in linq MVC

Question

I am trying to filter a model with two dropdown in an MVC project

var model = (from x in db.TABLE....
            join y in db.TABLE...).Where(where)...

my logic is

            String where = string.Empty;

            if (search.anno != null)
                where = " ANNO = " + search.anno ;

            if (search.Cliente != null)
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where += " And CODICE_CLIENTE = '" + search.Cliente + "'";                 }
                else
                {
                    where = " CODICE_CLIENTE = '" + search.Cliente + "'";
                }
            }

i get an error: System.Linq.Dynamic.ParseException: Character literal must contain exactly one character

i get that in where += " And CODICE_CLIENTE = '" + search.Cliente + "'";

i saw that the Apex at the end is '"

how can solve

yes ! I saw in debug ANNO = 2015 And CODICE_CLIENTE = '00106'" and i get that error — jon, Apr 07 '17 at 14:47
if only Anno is filtered the controller works ...but with the customer filter not works — jon, Apr 07 '17 at 14:48
I think you have to use double equals for the expressions: CODICE_CLIENTE == .... — Mark PM, Apr 07 '17 at 14:50
Also, you need to double quote the string, not single quote: CODICE_CLIENTE == \"" + search.Cliente + "\"" — Mark PM, Apr 07 '17 at 14:52
Last question: i want add few checkbox and how can i set the where clause ? Should I write like property IN ('A','B','C') ? Like in T-SQL right ? — jon, Apr 07 '17 at 14:56
if (search.linea != null) { if (!string.IsNullOrEmpty(where)) { List lineaSelezionate = new List(); foreach (var item in search.linea) { lineaSelezionate.Add(item); } where += " And lineaSelezionate.Contains(LINEA) "; } else { where = "lineaSelezionate.Contains(i.LINEA) "; } } — jon, Apr 07 '17 at 15:01
That i wrote is what i had if i use linq and not dynamic string ! — jon, Apr 07 '17 at 15:02
See: http://stackoverflow.com/questions/15633066/query-data-using-contains-keyword-in-dynamic-linq-in-c-sharp — Mark PM, Apr 07 '17 at 15:06

score 0 · Answer 1 · answered Apr 07 '17 at 15:17

0

This example translated into Linq without allowing Sql Injection Attacks

        String where = string.Empty;

        if (search.anno != null)
            where = " ANNO = " + search.anno ;

        if (search.Cliente != null)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where += " And CODICE_CLIENTE = '" + search.Cliente + "'";                 }
            else
            {
                where = " CODICE_CLIENTE = '" + search.Cliente + "'";
            }
        }

Would look like:

IQueryable<x> query = (from x in db.TABLE....
  join y in db.TABLE...);


if (search.anno != null)
{
  query = query.Where(x => x.ANNO == search.anno);
}   

if (search.Cliente != null)
{
  query = query.WHere(x => x.CODICE_CLIENTE == search.Cliente);
}

var model = query.ToList();  // or await query.ToListAsync();

answered Apr 07 '17 at 15:17

Erik Philips

53,428
11
128
150

Ok I Know this kind of logic! But Im tryng to use dynamic linq to improve that http://stackoverflow.com/questions/43201615/improve-pivot-linq-to-entities – jon Apr 07 '17 at 15:30
at this point select new { ANNO = ordine.Anno, LINEA = linea, MESE = ordine.Datord.Value.Month, CODICE_CLIENTE = ordine.Codcli, IMPORTO = rigaOrdine.Import }).Where(where) – jon Apr 07 '17 at 15:31
Nobody answer and so i tried with dynamic linq : filtering the Iqueryable object i improved about 60% time execution – jon Apr 07 '17 at 15:32

score 0 · Answer 2 · answered Apr 09 '17 at 15:14

I solved so ...
String where = string.Empty; object[] parameters = null;

    if (search.anno != null)
        where = " ANNO = @0 ";
      parameters = new object[] { search.anno };

    if (search.Cliente != null)
    {
        if (!string.IsNullOrEmpty(where))
        {
            where += " && CODICE_CLIENTE = @1";
            parameters = new object[] { search.anno, search.Cliente };
        }
        else
        {
            where = " CODICE_CLIENTE = @0";
            parameters = new object[] { search.Cliente };
        }
    }

    if (search.linea != null)
    {
        if (!string.IsNullOrEmpty(where))
        {
            where += " && LINEA.Contains(@2) ";
            parameters = new object[] { search.anno, search.Cliente, search.linea };
        }
        else
        {
            where = " LINEA.Contains(@0) ";
            parameters = new object[] { search.linea };
        }
    }

but the problem is with LINEA property (anonimous type): it is string and i cant use Contains(@p) tanks again for all the replay and help you provided

Mark PM · Accepted Answer · 2017-04-07T15:11:29.953

You need to use double equals for the expression and double quotes for the strings String where = string.Empty;

            if (search.anno != null)
                where = " ANNO == " + search.anno ;

            if (search.Cliente != null)
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where += " And CODICE_CLIENTE == \"" + search.Cliente + "\"";                 }
                else
                {
                    where = " CODICE_CLIENTE == \"" + search.Cliente + "\"";
                }
            }

Note that this is prone to SQL injection and should be avoided, you should use parameters, something like this:

var model = (from x in db.TABLE.... join y in db.TABLE...).Where(whereString, params)...

An answer that allows [SQL Injection Attacks](https://en.wikipedia.org/wiki/SQL_injection) I don't approve of. — Erik Philips, Apr 07 '17 at 15:07
Right ! Like SQL ! Could you show me how with the example above ? — jon, Apr 07 '17 at 15:26

Dynamic Where in linq MVC

3 Answers3

Linked