Where Dinâmico no Linq to Entities

Asked

Viewed 108 times

1

Expensive,

I need to perform a dynamic Where, in a Linq to Entities. I researched some solutions but none could accomplish what I need. Until then, I have the following switch:

    public TransportadorasGrid ObterTransportadorasBusca(Int32 PageNumber, Int32 PageSize, String NomeTransportadora, Boolean Status, Guid cdUsuario, String Idioma)
    {
        TransportadorasGrid Grid = new TransportadorasGrid();
        List<Transportadora> Transp = new List<Transportadora>();
        Int32 idLoja = Identidade.ObterIdLoja(cdUsuario);

        try
        {
            using (IFEcommerce ctx = new IFEcommerce())
            {
                BaseEcommerce.Definir(ctx);
                ctx.Configuration.ProxyCreationEnabled = false;

                Int32 StartLine = PageNumber * PageSize;

                Expression<Func<vw_Painel_FretesLoja_GridTransportadoras, object>> ExpressionOrdenacao;
                String NomeColunaBusca = String.Empty;

                switch (Idioma)
                {
                    case "en-US":
                        ExpressionOrdenacao = p => p.Label_Nome_EN;
                        NomeColunaBusca = "Label_Nome_EN";
                        break;
                    case "es-ES":
                        ExpressionOrdenacao = p => p.Label_Nome_ES;
                        NomeColunaBusca = "Label_Nome_ES";
                        break;
                    case "fr-FR":
                        ExpressionOrdenacao = p => p.Label_Nome_FR;
                        NomeColunaBusca = "Label_Nome_FR";
                        break;
                    case "de-DE":
                        ExpressionOrdenacao = p => p.Label_Nome_DE;
                        NomeColunaBusca = "Label_Nome_DE";
                        break;
                    case "it-IT":
                        ExpressionOrdenacao = p => p.Label_Nome_IT;
                        NomeColunaBusca = "Label_Nome_IT";
                        break;
                    default:
                        ExpressionOrdenacao = p => p.Label_Nome_PT;
                        NomeColunaBusca = "Label_Nome_PT";
                        break;
                }


                //Eu coloquei o NomeProp no where Somente para exemplificar...
                List<vw_Painel_FretesLoja_GridTransportadoras> tr = ctx.vw_Painel_FretesLoja_GridTransportadoras
                    .Where(u => u.idLoja == idLoja && u.flExcluido == false && NomeProp == NomeTransportadora)
                    .OrderBy(ExpressionOrdenacao)
                    .Skip(StartLine)
                    .Take(PageSize)
                    .ToList();

                List<tb_Param_TipoAcrescimoDesconto> AcoesAcDesc = ctx
                    .tb_Param_TipoAcrescimoDesconto
                    .ToList();

                List<ParametroAcrescimoDesconto> Acoes = ObterParametrosAcrescimoDesconto(cdUsuario);

                foreach (vw_Painel_FretesLoja_GridTransportadoras trnsp in tr)
                {
                    Transportadora t = new Transportadora();

                    tb_Param_TipoAcrescimoDesconto Frete = AcoesAcDesc.FirstOrDefault(a => a.idTipoDescontoAcrescimo == trnsp.idTipoDescontoAcrescimoFrete);
                    tb_Param_TipoAcrescimoDesconto Pedido = AcoesAcDesc.FirstOrDefault(a => a.idTipoDescontoAcrescimo == trnsp.idTipoDescontoAcrescimoPedido);

                    t.AcaoFrete = Acoes.FirstOrDefault(a => a.CodigoParametro == Frete.cdTipoDescontoAcrescimo);
                    t.AcaoPedido = Acoes.FirstOrDefault(a => a.CodigoParametro == Pedido.cdTipoDescontoAcrescimo); 
                    t.CodigoTransportadora = trnsp.cdTransportadora;
                    t.LabelInformacoes = trnsp.lbInformacoes;
                    t.LabelNomeExibicao = trnsp.lbNomeExibicao;
                    t.Traducoes = new List<TraducoesLabel>();
                    t.ValorFrete = trnsp.vlDescontoAcrescimoFrete;
                    t.ValorPedido = trnsp.vlDescontoAcrescimoPedido;
                    t.Status = trnsp.flStatus;

                    Transp.Add(t);
                }

                Grid.Transportadoras = Transp;

                Int32 Count = ctx.vw_Painel_FretesLoja_GridTransportadoras.Count(u => u.idLoja == idLoja && u.flExcluido == false);

                Grid.Count = Count;
            }
        }
        catch (Exception Exc)
        {
            ExceptionDispatchInfo.Capture(Exc).Throw();
        }

        return Grid;
    }

So far Ok. But I need to filter the column selected by the switch. (I put Propname in Where only to illustrate)

List<vw_Painel_FretesLoja_GridTransportadoras> tr = ctx.vw_Painel_FretesLoja_GridTransportadoras
    .Where(u => u.idLoja == idLoja && u.flExcluido == false && NomeProp == NomeTransportadora)
    .OrderBy(ExpressionOrdenacao)
    .Skip(StartLine)
    .Take(PageSize)
    .ToList();

I tried using the code below but apparently works only with LINQ to Objects.

List<vw_Painel_FretesLoja_GridTransportadoras> tr = ctx.vw_Painel_FretesLoja_GridTransportadoras
    .Where(u => u.idLoja == idLoja && u.flExcluido == false && u.GetType().GetProperty(NomeProp).GetValue(u, null) == NomeTransportadora)
    .OrderBy(ExpressionOrdenacao)
    .Skip(StartLine)
    .Take(PageSize)
    .ToList();

Some light ?

c#

  • Language comes from where?

  • Language is a String that comes in the Method signature.

  • Example: ExpressionOrdenacao("it-IT") ?

  • No... Expressionsorting I use for Orderby... (defined on line 1, before switch: Expression<Func<vw_Painel_FretesLoja_GridTransporters, Object>> Expressionsorting;).. He’s just a System.Linq.Expression

  • Well let’s see if I understand now, where has to depend on Swith to work?

  • Yes. according to the language chosen on the switch, I must use a certain property in Where (or Label_name_en, or Label_name_es or Label_name_pt, etc).

  • Another question is who passes this value for example it-IT because then I would create an extension method making the two solutions!

  • It comes in the signature of the method... public Transportationrasgrid Obtenrasbusca(Int32 Pagenumber, Int32 Pagesize, String Carrier, Boolean Status, Guid cdUsuario, String Language).. is just a string that contains one of the values inside the switch

  • So please put all this in your question, there is no way to know if you do not post everything we need to know to answer a certain question! Okay? If you could?

  • Sure.. I edited the question @Virgulio Novic.

  • Take a test if there’s a mistake and tell me!

Show 6 more comments

1 answer

1


Also use a expression to define the filter of this search and that has to do with the sort, example:

public TransportadorasGrid ObterTransportadorasBusca(Int32 PageNumber, Int32 PageSize, String NomeTransportadora, Boolean Status, Guid cdUsuario, String Idioma)
{
    TransportadorasGrid Grid = new TransportadorasGrid();
    List<Transportadora> Transp = new List<Transportadora>();
    Int32 idLoja = Identidade.ObterIdLoja(cdUsuario);

    try
    {
        using (IFEcommerce ctx = new IFEcommerce())
        {
            BaseEcommerce.Definir(ctx);
            ctx.Configuration.ProxyCreationEnabled = false;

            Int32 StartLine = PageNumber * PageSize;

            Func<vw_Painel_FretesLoja_GridTransportadoras, object> ExpressionOrdenacao;
            Func<vw_Painel_FretesLoja_GridTransportadoras, bool> ExpressionWhere;
            String NomeColunaBusca = String.Empty;

            switch (Idioma)
            {
                case "en-US":
                    ExpressionOrdenacao = p => p.Label_Nome_EN;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_EN == NomeTransportadora                 
                    break;
                case "es-ES":
                    ExpressionOrdenacao = p => p.Label_Nome_ES;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_ES == NomeTransportadora                 
                    break;
                case "fr-FR":
                    ExpressionOrdenacao = p => p.Label_Nome_FR;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_FR == NomeTransportadora                     
                    break;
                case "de-DE":
                    ExpressionOrdenacao = p => p.Label_Nome_DE;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_DE == NomeTransportadora                     
                    break;
                case "it-IT":
                    ExpressionOrdenacao = p => p.Label_Nome_IT;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_IT == NomeTransportadora                     
                    break;
                default:
                    ExpressionOrdenacao = p => p.Label_Nome_PT;
                    ExpressionWhere = c => c.idLoja == idLoja && c.flExcluido == false && c.Label_Nome_PT == NomeTransportadora                     
                    break;
            }


            //Eu coloquei o NomeProp no where Somente para exemplificar...
            List<vw_Painel_FretesLoja_GridTransportadoras> tr = ctx.vw_Painel_FretesLoja_GridTransportadoras
                .Where(ExpressionWhere)
                .OrderBy(ExpressionOrdenacao)
                .Skip(StartLine)
                .Take(PageSize)
                .ToList();

            List<tb_Param_TipoAcrescimoDesconto> AcoesAcDesc = ctx
                .tb_Param_TipoAcrescimoDesconto
                .ToList();

            List<ParametroAcrescimoDesconto> Acoes = ObterParametrosAcrescimoDesconto(cdUsuario);

            foreach (vw_Painel_FretesLoja_GridTransportadoras trnsp in tr)
            {
                Transportadora t = new Transportadora();

                tb_Param_TipoAcrescimoDesconto Frete = AcoesAcDesc.FirstOrDefault(a => a.idTipoDescontoAcrescimo == trnsp.idTipoDescontoAcrescimoFrete);
                tb_Param_TipoAcrescimoDesconto Pedido = AcoesAcDesc.FirstOrDefault(a => a.idTipoDescontoAcrescimo == trnsp.idTipoDescontoAcrescimoPedido);

                t.AcaoFrete = Acoes.FirstOrDefault(a => a.CodigoParametro == Frete.cdTipoDescontoAcrescimo);
                t.AcaoPedido = Acoes.FirstOrDefault(a => a.CodigoParametro == Pedido.cdTipoDescontoAcrescimo); 
                t.CodigoTransportadora = trnsp.cdTransportadora;
                t.LabelInformacoes = trnsp.lbInformacoes;
                t.LabelNomeExibicao = trnsp.lbNomeExibicao;
                t.Traducoes = new List<TraducoesLabel>();
                t.ValorFrete = trnsp.vlDescontoAcrescimoFrete;
                t.ValorPedido = trnsp.vlDescontoAcrescimoPedido;
                t.Status = trnsp.flStatus;

                Transp.Add(t);
            }

            Grid.Transportadoras = Transp;

            Int32 Count = ctx.vw_Painel_FretesLoja_GridTransportadoras.Count(u => u.idLoja == idLoja && u.flExcluido == false);

            Grid.Count = Count;
        }
    }
    catch (Exception Exc)
    {
        ExceptionDispatchInfo.Capture(Exc).Throw();
    }

    return Grid;
}

References

  • 1

    Almost that noble @Virgilio Novic, but his reply gave me the north I needed. Instead of Expression<Func<vw_Painel_FretesLoja_GridTransporters, Object>> Expressionwhere; I needed to use Func<vw_Painel_FretesLoja_GridTransporters, bool> Expressionwhere... With this worked perfectly. Thanks for the help and I will edit the answer.

  • You’re absolutely right... @Juliosoares is bool with Where.

Browser other questions tagged c#

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