Entity framework : a query is not working as expected

[Richard Deeming]

IEnumerable(Of students) queryv = (From t1 In context.students order by t.Name select t)

Since you've stored this result in an IEnumerable(Of students), all students will be loaded from the database into the local cache when you iterate the results. The second query will then execute against the local cache. If you only want to load the matching students into the local cache, then either use a single query:

Dim queryv As IEnumerable(Of students)
queryv = (From t1 In context.students
Let p = t1.status.Where(Function(t2) t2.year <= year1).OrderBy(Function(t3) t3.year).LastOrDefault()
Where Not IsNothing(p) AndAlso p.active = True
order by t1.Name
Select t1).ToList()

or change your queryv variable to be IQueryable(Of students):

Dim queryv As IQueryable(Of students)
queryv = From t In context.students order by t.Name select t
queryv = From t1 In queryv
Let p = t1.status.Where(Function(t2) t2.year<=year1).OrderBy(Function(t3) t3.year).LastOrDefault
Where Not IsNothing(p) AndAlso p.active = True
Select t1

Dim results As IEnumerable(Of students) = queryv.ToList()

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[desanti]

I've tried your solutions , and both produced the same error :

LINQ to Entities does not recognize the method 'Program1.status LastOrDefault[status](System.Collections.Generic.IEnumerable`1[Program1.status])' method, and this method cannot be translated into a store expression.'

[Dave Kreskowiak]

That error message means "LastOrDefault" isn't translatable to an SQL query. There is no SQL equivalent function for it. You're going to have to rewrite your query to work without LastOrDefault.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

[desanti]

I have used LastOrDefault in other cases , and there were no problems translating in SQL Query. so I think there is a SQL equivalent for LastOrDefault.

[Dave Kreskowiak]

Allow me to rephrase. That error message says the method isn't supported by the whatever the underlying provider is.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

[desanti]

ok , but what's wrong with my query , and what should I do ?

[Dave Kreskowiak]

Try this:

Dim query1 = (From t In context.students order by t.Name select t).ToList

Dim query2 = From t1 In query1
Let p = t1.status.Where(Function(t2) t2.year<=year1).OrderBy(Function(t3)
t3.year).LastOrDefault
Where Not IsNothing(p) AndAlso p.active = True
Select t1

Dim results As IEnumerable(Of students) = query2.ToList()

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

[desanti]

Again , on Local Cache I have all the students.The same problem as my query on my first post.

[Lost User]

I don't think, I know it is not part of SQL92. It is something from a local dialect.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

[Richard Deeming]

That's annoying. Try switching to OrderByDescending, and use FirstOrDefault.

Dim queryv As IQueryable(Of students)
queryv = From t In context.students order by t.Name select t
queryv = From t1 In queryv
Let p = t1.status.Where(Function(t2) t2.year <= year1).OrderByDescending(Function(t3) t3.year).FirstOrDefault()
Where Not IsNothing(p) AndAlso p.active = True
Select t1

Dim results As IEnumerable(Of students) = queryv.ToList()

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer