How can i access an object in a list in c# query with ef?

Question

So I´m having a problem with a query im trying to do. I´m trying to get the last element of a list in a linq query in c# using ef.

Here is the code im trying to execute:

 public List<Viagem> getViagensToBeCompletedInBlocoTrabalho()
 {
     return this.context.Viagens
        .Where(v => v.blocosTrabalho.Count() == 0
          || !v.blocosTrabalho.LastOrDefault().noFim.Equals(v.horasPassagem.LastOrDefault().codigoNo)
        )
        .ToList();
 }

But i get this error: Queries performing 'LastOrDefault' operation must have a deterministic sort order. Rewrite the query to apply an 'OrderBy' clause on the sequence before calling 'LastOrDefault'.

What am i doing wrong here?

By the way the sorting that i want is the order of entries in the table so i don´t really want to sort anything. Although i have also tested sorting and it doesn´t work.

Thanks for the help in advance :-)

You don't say which DBMS you are using. But this doesn't really make sense "the order of entries in the table" in most systems. Tables have no defined order as such — Charlieface
– Charlieface, Commented Dec 31, 2020 at 3:08
Can you share the code of testing with sorting? You face the same problem with sorting? — Chetan
– Chetan, Commented Dec 31, 2020 at 5:29
@ChetanRanpariya not quite, it was a different one but still didnt work maybe i contructed badly the query. — Tiago Fontes Dias
– Tiago Fontes Dias, Commented Dec 31, 2020 at 10:52

Svyatoslav Danyliv · Accepted Answer · 2020-12-31 09:22:15Z

2

Actually SQL standard do not support retrieving records from the end and even do not guarantee records in concrete order. EF is trying to simulate that by correcting OrderBy operator and if you do not specify that - it will fail.

Consider to rewrite your query and make it performant:

public List<Viagem> getViagensToBeCompletedInBlocoTrabalho()
{ 
    var query =
       from v in this.context.Viagens
       from b in v.blocosTrabalho.OrderByDescending(x => x.Id)
          .Take(1).DefaultIfEmpty()
       from h in v.horasPassagem.OrderByDescending(x => x.Id)
          .Take(1).DefaultIfEmpty()
       where
          b == null || b != null && b.noFim == h.codigoNo
       select v;

     return query.ToList();
 }

answered Dec 31, 2020 at 9:22

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tiago Fontes Dias Over a year ago

That works but only if the blocoTrabalho Id is sequencial. This id the user can specify so for example if the first blocotrabalho has id of BT2 and the second BT1, this will not work. I need to get the list ordered by the entry in the table. HorasPassagem is fine though i can order it, no problems there!

Svyatoslav Danyliv Over a year ago

If you need such query, you have to introduce sequence somehow. Or choose several columns for ordering. Database engine do not guarantee records order if you do not specify OrderBy.

Tiago Fontes Dias Over a year ago

Sorry nevermind the comment i was missing a concept in the domain i can actually get everything ordered thank so much =)

Collectives™ on Stack Overflow

How can i access an object in a list in c# query with ef?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related