Entity framework : a query is not working as expected

desanti

Hello ! I'm using vb.net 2017 and Entity Framework 6 with sql server 2008r2. I have 2 tables in database : Studnets: (ID , Name ) Status : (ID , Year , Student_ID , Active ) The table status has the status of students in a specific year. A student may have 0 or 1 status on a year . ( If a student has not a status on a year , the last status from previous years ( if exists ) is used as a status for it this year) Now I want to have a query that load on local cache only the students that are active on a specific year. This is my query :

Dim queryv As IEnumerable(Of students)
queryv = (From t1 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)
queryv.Tolist

On Database there are 2 students , one of them has an Active status this year , the other has a non-active status. The problem is that after i excute that query , i have 2 instructions , just for testing :

Messagebox.show(queryv.count)
MessageBox.show(context.studnets.local.Count)

The results are : 1 (for queryv) 2 (for local cache ) On Local cache both students exists. Why is this result ? Thank you !

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