LINQ using function within select new {} with lambda expression

Question

LINQ using function within select new {} with lambda expression

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I am assembling a select, with the Entity Framework, that needs to "create a column" according to 4 criteria. I'm trying this way:

IQueryable<ImoveisDTO> Resultado = ctx.Imoveis.Select(ib => new ImoveisDTO
{
    idImovel = ib.idImovel,
    idStatusImovel = ib.idStatusImovel,
    idTipoImovel = ib.idTipoImovel,
    fltValorQuartosImovel = ib.fltValorQuartosImovel,
    FaixaPreco = MinhaFuncao(ib.fltValorQuartosImovel)
}
    ).Where(ib =>
    ib.NumerosQuartos.Contains(ib.intQuartos.Value)
    && FaixasPrecos.Contains(ib.FaixaPreco)
    ).Distinct()

Being MyFunction:

private int MinhaFuncao(double? ValorQuarto)
{
    int FaixaRetornada;

    if (ValorQuarto < 150000) FaixaRetornada = 1;
    else if (ValorQuarto < 200000 && ValorQuarto > 150001) FaixaRetornada = 2;
    else if (ValorQuarto < 250000 && ValorQuarto > 200001) FaixaRetornada = 3;
    else if (ValorQuarto > 250001) FaixaRetornada = 4;
    else FaixaRetornada = 0;

    return FaixaRetornada;
}

But it seems like I can not do this because I'm getting the following LINQ message:

LINQ to Entities does not recognize the method 'Int32 MyFunction (System.Nullable'1 [System.Double])' method, and this method can not be translated into a store expression.

I have tried to place an If directly inside the Select new {FaixaPreco = if(ib. ...)} But it also did not work.

What is the best way to resolve this?

----- Edited -------------------------

As Manieiro suggested and the Marconcilio showed. I chose to use the ternary operator that is translated as a CASE ... WHEN. It looks like this:

IQueryable<ImoveisDTO> Resultado = ctx.Imoveis.Select(ib => new ImoveisDTO
{
    idImovel = ib.idImovel,
    idStatusImovel = ib.idStatusImovel,
    idTipoImovel = ib.idTipoImovel,
    fltValorQuartosImovel = ib.fltValorQuartosImovel,
    FaixaPreco = (ib.fltValorQuartosImovel < 150000 ? 1
                            : (ib.fltValorQuartosImovel < 200000 && ib.fltValorQuartosImovel > 150001) ? 2
                            : (ib.fltValorQuartosImovel < 250000 && ib.fltValorQuartosImovel > 200001) ? 3
                            : (ib.fltValorQuartosImovel > 250001) ? 4 : 0)
}
    ).Where(ib =>
    ib.NumerosQuartos.Contains(ib.intQuartos.Value)
    && FaixasPrecos.Contains(ib.FaixaPreco)
    ).Distinct()

c# .net entity-framework linq

asked by anonymous 04.01.2018 / 13:04

2 answers

1

You must first understand LINQ , before using it. See more .

When using LINQ To Entities C # codes need to be able to be converted to SQL efficiently . The best that can happen is not working. It often works and the generated code is very bad and the person gets lost.

A IQueryable is very different from a IEnumerable .

So the solution is to have a code inside the database that does this and can be invoked or create a code that can be translated for CASE...WHEN .

Using double for monetary value is also wrong .

04.01.2018 / 13:25

LINQ with JOIN and where clause, how to do? For / Foreach Sql Server For Each Different Data of a Column

score 2 · Accepted Answer

EF can not interpret methods that are not tracked by it.

One way to get around this is by writing your function into your own query.

IQueryable<ImoveisDTO> Resultado = ctx.Imoveis
.Select(ib => new ImoveisDTO
{
    idImovel = ib.idImovel,
    idStatusImovel = ib.idStatusImovel,
    idTipoImovel = ib.idTipoImovel,
    fltib.fltValorQuartosImovelsImovel = ib.fltib.fltValorQuartosImovelsImovel,
    FaixaPreco = (ib.fltValorQuartosImovel < 150000 ? 1
                        : (ib.fltValorQuartosImovel < 200000 && ib.fltValorQuartosImovel > 150001) ? 2
                        : (ib.fltValorQuartosImovel < 250000 && ib.fltValorQuartosImovel > 200001) ? 3
                        : (ib.fltValorQuartosImovel > 250001) ? 4 : 0),
})
.Where(ib => ib.NumerosQuartos.Contains(ib.intQuartos.Value) && FaixasPrecos.Contains(ib.FaixaPreco))
.Distinct()

The question goes of its use if you only use a single I see no problem, but if you have to be reusing the code in other queries may not be one of the best solutions.