How to make Where ands dynamically with Linq and Entity Framework?

Asked

Viewed 155 times

3

How can I make the ands dynamically in the where using Linq and entityFramework?

Thanks in advance for any help. 

return _dbContext.TbParceiro                
//Preciso fazer algo do tipo mas isso não funciona, não sei como posso fazer aqui.
.Where(p =>                   

    if (!string.IsNullOrEmpty(mdlParceiroFiltro.FiltroId))
    {
         p.ParceiroId = mdlParceiroFiltro.FiltroId;
    }
)
.OrderBy(p => p.ParceiroId)
.Skip(mdlParceiroFiltro.Pula)
.Take(mdlParceiroFiltro.Pega)
.ToList();

Below is an example that works by using string command, I need to do the same thing only with String. 

        sql.Append("SELECT u.Id, u.Nome, u.Usuario ");
        sql.Append("FROM usuario u ");
        sql.Append("WHERE 1 = 1 ");

        if (model != null) 
        {
            if (!string.IsNullOrEmpty(model.Nome))
            {
                sql.Append("AND u.Nome like @Nome ");
                lstParametros.Add(new MySqlParameter() {
                    ParameterName = "@Nome",
                    MySqlDbType = MySqlDbType.String,
                    Value = "%" + model.Nome + "%"
                });
            }

            if (!string.IsNullOrEmpty(model.Usuario))
            {
                sql.Append("AND u.Usuario like @Usuario ");
                lstParametros.Add(new MySqlParameter()
                {
                    ParameterName = "@Usuario",
                    MySqlDbType = MySqlDbType.String,
                    Value = "%" + 
                    model.Usuario + "%"
                });
            }
        }
c# entity-framework linq

2 answers

3


What you want is to add the Where only if the mdlParceiroFiltro.FiltroId is not null or Empty.

IQueryable<ParceiroModel> entidades = _dbContext.TbParceiro;

// Verifica o FiltroId é válido
if (!string.IsNullOrEmpty(mdlParceiroFiltro.FiltroId))
{
    // Se for válido, você insere o Where
    entidades = entidades.Where(p => p.ParceiroId == mdlParceiroFiltro.FiltroId);
}

return entidades
    .OrderBy(p => p.ParceiroId)
    .Skip(mdlParceiroFiltro.Pula)
    .Take(mdlParceiroFiltro.Pega)
    .ToList();

You can add as many Where want with LINQ. It will automatically turn into and behind the scenes.

  • Cool, but in this line "var entities = _dbContext.Tbpartner;" it will not make a full select in the bank?

  • 2

    No, the dbContext only executes select when you call a finalizer method, type the ToList or ToArray, for example. While you only add filters, it will mount select without running.

  • Perfect. Thank you

  • He complained of a conversion ai instead of me using var put Iqueryable<Partneromodel> entities = _dbContext.Tbpartner;

  • I got it, I thought var would automatically pick up the type IQueryable.

  • I changed the answer to reflect what you commented.

Show 1 more comment

2

try with ternary operator:

Syntax: ((bool) ? (se true) : (se falso))

[...].Where(p => (!string.IsNullOrEmpty(model.Nome) ? p.Nome.Contains(model.Nome) : p.Usuario.Contains(model.Usuario))).[...]

Another example:

[...].Where(p => (!string.IsNullOrEmpty(mdlParceiroFiltro.FiltroId) ? p.ParceiroId = mdlParceiroFiltro.FiltroId : true)).[...]

Browser other questions tagged c# entity-framework linq

You are not signed in. Login or sign up in order to post.