LINQ using function inside select new{} with lambda Expression

Question

I am assembling a select, with the Entity Framework, which needs to "create a column" according to 4 criteria. I am 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()

Sendo Minhafuncancao:

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 I can’t do it because I’m getting the following message from LINQ:

LINQ to Entities does not recognize the method 'Int32 Minhafuncao(System.Nullable`1[System.Double])' method, and this method cannot be Translated into a store Expression.

I have tried to put an If directly inside the Select new {FaixaPreco = if(ib. ...)} But it didn’t work either.

What’s the best way to solve this?

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

Just as Manieiro suggested and Marconcilio showed. I chose to use the ternary operator which is translated as a CASE... WHEN. Stayed 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()

Answer 1

EF cannot interpret methods that are not tracked by it.

One way around this is by writing your function within 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 to your use if you only use a single one I see no problem , but if you have to keep reusing the code in other queries may not be one of the best solutions.

Answer 2

First need understand the LINQ, in full, before using. 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. Often it 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 into CASE...WHEN.

Use double for monetary value is also wrong.