This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Am working with a VistaDB database and when I try to select a single value from the database by using the FirstOrDefault LINQ method I get "The current DataProvider does not support paging " Dim MyValue = (From R In db.AppRegistries Where R.Id = 1 Select R.KeyName).FirstOrDefaultIts a pretty simple query - so not sure what I'm doing wrong here. What should I be doing to return the first/only value returned from a query - rather than a collection? |
|
|
|
|
Okay - this is more serious than I thought... .SingleOrDefault and even .ToList are failing with "The current DataProvider does not support paging " also... |
|
|
|
|
This occurs because the VistaDB engine (at least when we last investigated it) does not support paging. That is, there is no way to select a subset of results at the engine level. And First and Single are paging operators because they effectively issue a "LIMIT 1" on the query. We may be out of date here -- we don't use VistaDB on a regular basis so we don't keep up to date with enhancements -- if so let us know and we'll gladly implement this feature. We have also considered whether to try to implement these paging features "client-side" in the LightSpeed engine, but at the moment our policy is to delegate all querying operations, including paging, to the database engine. The reasoning is we don't want users to issue what they think is an efficient paging command that will be handled by the database query engine, and find us bringing back zillions of records only to throw away all bar ten -- or, in your case, all bar the one. Writing an un-paged query ensures that users are aware of the potential performance impact. The downside is of course that it makes life unnecessarily difficult for queries like yours. You *know* it will be bringing back at most one record, so performing the "TOP 1" paging within LightSpeed would be perfectly efficient and way more convenient! But it's hard for us to distinguish this from a query such as (From b in App.ReallyBigTables Select b).FirstOrDefault(), which *would* bring back everything in ReallyBigTable (and then throw most of it away). We'd welcome your feedback on how we could improve our handling of this kind of scenario. In the meantime, the solution should be to perform the FirstOrDefault "client side" using LINQ to Objects: (From r In App.Registries Where r.Id = 1 Select r.KeyName).ToList().FirstOrDefault() i.e. do the ToList() before the FirstOrDefault(). But you mention that ToList isn't working. Can you confirm that ToList() isn't working even on a non-paging query? Thanks! |
|
|
|
|
Hmmm - What is 'Limit 1'? 'SELECT TOP 1 * FROM vistadbTable' works fine - why can't you implement the FirstOrDefault using the TOP operator in VistaDB?
|
|
|
|
|
Hmm -- I'll look into this. I haven't worked on the VistaDB provider myself so I'm not sure why this wouldn't be implemented. I will get back to you asap -- thanks for the info. |
|
|
|
|
Thanks. Looked into the .ToList and it works fine - not sure what I did, must have been confused. Sorry about that.
In the meantime I have a workaround on the .FirstOrDefault issue by doing a .ToList on the IQueryable result and then .FirstOrDefault on that list. A bit messy, but will suffice until you have a fix for the VistaDB provider.
Thanks for your help on this. Any estimate on when that might be implemented?
|
|
|
|
|
Just waiting for it to build -- unless something goes wrong, it should be in tonight's nightly. |
|
|
|
|
Sweet - you guys are awesome!
|
|
|
|
