|
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 !
|
|
|
|
|
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
|
|
|
|
|
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.'
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Allow me to rephrase. That error message says the method isn't supported by the whatever the underlying provider is.
|
|
|
|
|
ok , but what's wrong with my query , and what should I do ?
|
|
|
|
|
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()
|
|
|
|
|
Again , on Local Cache I have all the students.The same problem as my query on my first post.
|
|
|
|
|
I don't think, I know it is not part of SQL92. It is something from a local dialect.
Bastard Programmer from Hell 
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.
|
|
|
|
|
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
|
|
|
|
|
As usual, as soon as I post, if figure it out; sorry. 
This
Me._Column = Activator.CreateInstance(GetType(T))()
Should be this
Me._Column = Activator.CreateInstance(GetType(T)).
However, now I have another problem; I'll work on this awhile.
I hope I am in the right group.
I have a Class that creates an xml file that currently works.
However, I would like to change the definition to define 1 of 2
generic types (T) when creating the file; Column1 or Column2. The code below is what
I have come up with through searches.
Everything compiles but when I create an instance of MyXmlDef I get this error.
'No Default Member Found for type 'Column1'
A search on the error says:
A late-bound index Get or Set call has been attempted.
Ensure that the object has a default property with a Get or Set, depending on which you are trying to make.
I have tried to define a Property for Column below but it doesn't fix the error.
I don't know what 'object' the error refers to.
Can someone help?
Thank you
' I am just showing the relevant code
' I have hardcoded this to Column1 to test
public Class MyXmlDef
<XmlElement("ColumnList")> _
Public _ColumnList As ColumnList(Of Column1)
Public Sub New()
Me._ColumnList = New ColumnList(Of Column1)
end sub
end Class
Class ColumnList(Of T)
<XmlElement("Column")> _
Public _Column As T
' This is test code to try and fix the error; it doesn't fix.
Public Property Column() As T
Get
Return Me._Column
End Get
Set(ByVal value As T)
Me._Column = value
End Set
End Property
Public Sub New()
' THis is the code I found on the internet to create an object from generic.
' However, it does not compile.
' Me._Column = Activator.CreateInstance(T)()
' This compiles but get the error described above for Column1.
' I do not know if this is valid or not.
Me._Column = Activator.CreateInstance(GetType(T))()
End Sub
End Class
Class Column1
<XmlElement("ProductName")> _
Public _ProductName As XMLProductName
' ... other XmlElments
Public Sub New()
'... initialize elements
end sub
End Class
Class Column2
<XmlElement("ProductName")> _
Public _ProductName As XMLProductName
' ... other different XmlElments
Public Sub New()
'... initialize elements
end sub
End Class
-- modified 3-Apr-19 13:40pm.
|
|
|
|
|
You just need a constraint on your generic type parameter:
Class ColumnList(Of T As New)
...
Public Sub New()
Me._Column = New T()
End Sub
...
Generic Types in Visual Basic (Visual Basic) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello,
I have a program in Visual Basic 2010 32 bit now i want to convert it to visual Studio 2019 since the old 2010 is almost end of life.
I have opened the program in Visual studio 2019 there was a message that there are conversions needed and when this was finished i clicked run There is a error
--> An error occured while signing. Failed to sign bin\debug\app.publish\projectname.exe . Signtool Error: No Certificates where found that met all the given criteria.
The program was written in Visual basic 2005, i imported it in VB 2008 and at last in VB 2010 . The application is running without any problem on a Old Windows XP wich is not connected to Internet so there is no issue of security since it is stand alone but the pc is getting old and need to be replaced so when i want to use the application on new Windows 10 64 bit wich will be on the internet …
Is there a solution to import and use the program in 2019 without any special issues ? if not is there a way to do this on a visual studio 2012 ? 2013 2015 ??? otherwise i try with the older version of visual studio
If it will be the same what can i do to get it up and running on Visual Studio 2019 what i prefer.
Many thanks
best regards
D.
|
|
|
|
|
You probably need to resign the application as the certificate used is too old, wrong format or expired.
Go to right click on the project and open properties, there will be a tab called signing.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Thanks. Unchecked Signing and problem solved.
|
|
|
|
|
Hello !
I'm using VB.net 2017 , Entity framework 6.
On my form I have loaded several entities and its childs.
Mylist=context.myobjs.Include("mychilds").Tolist.
Now , another user that use the same program from another computer , make some changes in some of the records and/or some childs. He save the data to database.
On my form I have a refresh button that execute the same query as above.
The problem is that when I press this button , on my list I have no updated values from database but the old values. It seems that that query does not read the database but use the data on local cache ( Why ???)
I know that disposing the context and creating a new one will resolve this problem , but I don't want to do this because on my forms I have other records from other entities that I have loaded . And if I recreate the context I need to load again all these but I don't want to do this because only records form 1 entity and its childs can be updated from other users as I described above.
Searching in internet , I've found 2 general methods doing this :
METHOD 1
For Each en As myobj In mylist
context.Entry(en).Reload()
For Each chld In en.mychilds
context.Entry(chld).Reload()
Next
Next
METHOD 2
For Each en As myobj In mylist
For i= en.mychilds.count-1 to 0 step -1
context.Entry(en.mychilds(i)).State = EntityState.Detached
Next
context.Entry(en).State = EntityState.Detached
Next
Mylist=context.myobjs.Include("mychilds").Tolist
The problem is that both methods are very slow when mylist is very large.
Is there any other way ?
Thank you !
|
|
|
|
|
Use different context instances to maintain different sets of data that you can reload separately?
There's nothing that says you have to use a single context instance for everything. That way you can just destroy the context instance and create a new one, telling it to load the data you want all over again.
|
|
|
|
|
But the entities have relation between each other , how can I keep different contexts !!!!!?????
one entity is a child for other and is parent for others. How can I use these in different contexts ????
|
|
|
|
|
It wasn't clear in your original post if these records you're trying to reload were related to the other entities or not.
There is no fast way to do this. You could have the context execute a SQL query to reload just the records you want, but the context is still going to go through each and every result record to update the cached objected.
I think your design needs to be reconsidered if you have a user making changes that forces other users to reload a large amount of data. Minimize the data each user is caching as much as possible.
|
|
|
|
|
This is what i'm asking : a query to reload all the records from an entity.
If some of objects inside myobj are updated from other users , I need a query to reload all the objects in myobj from database. But I can't find a such query to do this so I have to reload one by one as on my 2 methods described in my first question.
It's strange why there's no a method on entity framework to force reloading all object from an entity from database and not read on local cache. !!!!!
|
|
|
|
|
Then you're going to have to tell the clients which records updated from the client that wrote to the database.
Now, every client is going to have to retrieve the object again using that records ID value. Retrieving a record by ID is easy. Reloading EVERY record you have cached is going to take forever, no matter what you do.
There is no solution around this.
Even without an ORM like EF, there's no real good way to do this! It's your application design that has the problem.
|
|
|
|
|
Sorry , I think this is a problem of Entity Framework Design.
Because , if other users add or delete records , when I run my query these changes are "known" by EF and are reflected in results.
But the updated records are not read from database and old values are kept from local cache.
For me , It's ridiculous that EF does not have a command to "READ ALL THE DATA AGAIN FROM DATABASE"
|
|
|
|
|
Those changes are NOT known by EF. At least not until you run a query again to retrieve the entire set of records again. But now you have a problem. How does EF know that the records are deleted? It doesn't. The records just don't show up in the result set from the database. That in no way means that they were deleted. The only way to know that would be to get the record ID of what EF has cached and try to retrieve that record ID from the database. Now do that for every record you have cached and it'll take a long time.
Move on to the problem of just telling EF to reload a specific table. Great, you now have all of the data from that table, but how to do handle the case where the cached data is "dirty"? That means you have data in the cache that's been modified but not written back tot he database. Do you through those changes out in favor of the current data? Do you skip that changes? Do you modify only certain fields? How is the ORM supposed to know which fields to keep and which to throw out and refresh?
Another problem. What query returned the data the generated the object in the cache? There is ORM that I'm aware of that caches the query that generated a set of object graphs. What if there's multiple queries that can be run that generate a particular object in a graph? How do you know which query to run to PROPERLY regenerate that object with the correct data? It's impossible for the ORM to know that.
Google "database concurrency" for the difficulties in doing what you want to do. There is no easy solution to this problem, and not every solution is going to work for every application.
You're looking at this through the lens of what you want EF to do and not from the standpoint of how any ORM would even accomplish this in a generic way usable by all applications. I know you just want to call .Reload() and have it work, but you're not seeing the complexity behind actually making that happen.
|
|
|
|
|
Quote: How does EF know that the records are deleted? It doesn't. The records just don't show up in the result set from the database.
Ok , and why EF does not keep the records that are in local cache , but instead "use the information" that those records are not show up in the result set of database ? And why in the case of modified records , "the information from database" is not used , and it's just keep the records from local cache.
Quote: Move on to the problem of just telling EF to reload a specific table. Great, you now have all of the data from that table, but how to do handle the case where the cached data is "dirty"?
If the cached data is modified , i think EF should let the programmers to choose . If i try to reload data from database when cached data is modified , then i'm responsible to get the new data from database and overwrite those changes. But In my application for example , if a modification was made , i don't let the users to reload data from database. It's simple. And how you will resolve the problem when cached data is modified and i create a new context ( as many have suggested this idea for my problem). This is the same problem. I think these problems EF should leave in the hand of programmers.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
