Error when simulating distinc in query with Linq and lambda Expression

Asked

Viewed 46 times

2

I have 2 tables in a Sqlserver bank. One tbState call and another tbCity. I am trying to return all states of the table tbState that have at least one city (tbCity) and with the properties tbCity.bnlAtivarCity and tbCity.blnExibirNoPortal = true. The result should be sorted alphabetically by the state name.

I’m trying like this:

IQueryable<tbEstado> ListaDeEstados = ctx.tbEstado
            .Join(ctx.tbCidade, estado => estado.idEstado, cidade => cidade.idEstado, (estado, cidade) => new { estado, cidade })
            .Where(e => e.cidade.bnlAtivarCidade == true && e.cidade.blnExibirnoPortal == true)
            .Select(e => e.estado)
            .GroupBy(e => e.idEstado)
            .Select(group => group.First()).OrderBy(e => e.txtNomeEstado);

But I’m getting the following error:

Additional information: The method 'First' can only be used as a final query Operation. Consider using the method 'Firstordefault' in this instance Instead.

Could someone help?

c# entity-framework linq lambda-expressions

  • Have you figured it out yet? I remember going through something like this and doing a gambit. Like ListaDeEstados receives the normal list (without ordering) and then you order.. drew??

1 answer

1


To search all states that are associated with the city search in the city repository.

Ex. 

ctxCidade.Where(...).Select(x => x.Estado).Distinct().ToList();

Send your City and State Entities. There seems to be something wrong there.

  • But this cannot be done because in Orderby I will not have access to the properties of the table as for example txtNomeState. After all I’ll have a set of records.

  • Send your State and City entities so I can take a look

  • They are super simple. tbState has Id(int) and Name(nvarchar). City already has Id(int), Name(nvarchar), Activeness(bool) and blnExibirnoPortal(bool) and idState(int). Only.

  • city must have reference to state. It’s strange. And if you want state you can have a list of cities.

  • Face the error is not in the entities. The query works perfectly in sql. the problem is when passing to lambda expression. Specifically the snippet [.Groupby(e => e.idState) . Select(group => group.First())] Which only exists to simulate the distinctness. If remove this stretch tbm works, but comes several repeated states.

  • No SQL Server posso fazer assim que dá certo: SELECT DISTINCT tbEstado.idEstado, txtNomeEstado FROM tbEstado inner join tbCidade &#xA; ON tbCidade.idEstado = tbEstado.idEstado &#xA; WHERE tbCity.bnlAtivarCity=1 ORDER BY txtNomeACE state

  • But to search for all states that have city you can use the Linq to filter cities and give select in states using distinct. Ex.: ctxCidade.Where(...). Select(x => x.State). Distinct(). Tolist();

  • You should not always look at the object model as if it were a relational model. Try the strategy I put above. The above will return a list of cities.

  • I understand. But for a semantic question I would really like to search starting with the state... Anyway, I tested and it worked directly with a . distinct(). But there is the doubt. After all when I can use distinct() and when I need to use . Groupby(e => e.id) gambiarra. Select(group => group.First()) ???

  • Groupby is only used in conjunction with aggregation functions (Count, Max, Min). to do only the distinct sorting by name use the same orderby. Groupby by Id is unnecessary. Will not group for anything since id is unique.

  • The test you did was using the proposed solution or your?

  • It was using the 2. Both work with distinct() , dispensing with the Groupby gambiarra. I will mark as answered. After all the doubt now is another and so for another topic. " When can I use distinct and when I need to force distinct with Groupby’s gambiarra"? Maybe I’ll create another topic. But this one’s over. Thanks!

Show 7 more comments

Browser other questions tagged c# entity-framework linq lambda-expressions

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