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
|
I am a little confused, I have just ported a complete SQL database, complete with data, from a SQL Server 2005 database to a SQL Compact 3.51 database. Prior to doing this I read the Lightspeed documentation for all references to SQL Compact limitations, and in particular paging. I found no references that indicated any relevant problems, or that paging would not work with SQL Compact, althoug there were references to other DBs. But now in my application, when I try to use the paging functions, ones that previously worked well in SQL Server, I receive an error message; Message: The current DataProvider does not support paging Now, as far as I know, Lightspped performs paging at the the data provider level, and the SQL Compact native data provider has support for paging, so I am at a loss as to why this doesn't work. Many thanks as usual. Mark |
|
|
Hello Mark, We perform paging at the SQL level, i.e. by emitting SQL that selects only the required rows. For example, MySQL LIMIT/OFFSET or SQL Server ROWNUM() queries. As far as we've been able to determine, SQL Compact doesn't support ROWNUM(), or provide any other way of specifying a range of rows. This link is a bit out of date; this is a bit more recent but still more than a year old; but perhaps there's something in the latest drop of SQL Compact that we're missing? I had a look through Books Online to see if I could find it, but no luck -- if we are missing something then please let us know and we'd be pleased to implement this for you. Otherwise, apologies for not mentioning this in the docs. We'll get that fixed. |
|
|
Hi Ivan, thanks for the update. FYI, below is a paragraph copied from the Microsoft website with relation to their Entity Framework. This would imply there is limited ability for paging within SQLCE. SQL Server Compact does not support SKIP expressions in paging queries when it is used with the Entity Framework. On the other hand, SQL Server Compact supports the LIMIT and TOP expressions in paging queries. |
|
|
I have tried to use LINQ to perform the paging that I need, however, despite the note above, I am still unable to get this to work also. See code below.
var l = from pi in uow.PoSInvoices I still receive an error that the current Data Provider (SQLCE) does not support paging. Do you have work arounds that I can use, or perhaps a better embedded database that would enable me to do this? Thanks
|
|
|
I have found a workaround, see code below. Becuase the returned list should never be more than a few hundred rows, this will work without too much overhead, obvioulsy this would give terrible performance if there were thousands or millions of rows. It's not perfect, but it will do until SQLCE supports paging. ;-) note the I.ToList() prior to the paging operations, then another ToList() to convert it to a BindingList<>. |
|
|
If you have flexibility over which embedded database to use, we would recommend SQLite. It fully supports paging. It also supports xcopy deployment and is free. Its main limitation is that it supports a slightly smaller set of SQL functions than SQL CE. Some aggregation operations may also behave slightly differently (specifically, we have noticed that SQL CE rounds off averages of integer columns to integer values, whereas SQLite returns the unrounded value). |
|
|
By the way, I'd be happy to look at adding Take() support on SQL CE (which is what LIMIT and TOP give you), but without Skip(), I don't think it's going to be that useful to you -- you'd end up still having to use the ToList() hack for every page except the first. And the way I read the note from Microsoft, it still looks like the SQL CE engine just doesn't support Skip (i.e. I believe this is an engine limitation, not a limitation of the SQL CE EF provider.) However, if Take() on its own would be useful to you, let us know and we'll see what we can do. |
|
|
I agree, without the Skip part the Take is limited in how useful it would be. I will take a look at SQLLite, my only concern, is that the central database to this system is SQL Server 2005/8, and the data is duplicated in both locations. I have always been afraid of incompatibilities when working cross-vendor. Although saying that, SQLCE is not 100% compatible with SQL Server 2005/8 either.
|
|
|
If it helps, we've noted the following differences between SQL Server and SQLite: * SQLite does not round the average of an integer column to an integer I don't suggest this is an exhaustive list and I make no promises that you won't run into more significant issues than these! There are obviously SQL dialect differences as well. For the most part, LightSpeed should insulate you from these. The main issue will probably be with database setup scripts. The designer and the migrations feature may be able to help you here. Finally, of course, SQL CE has integration points into SQL Server that SQLite does not. For example if you want to use the Sync Framework to synchronise data across the central database and the local stores, then SQL CE will obviously be the better bet. |
|
|
I just read the FAQ's on the SQLLite website, and got a little worried about some of the limitations, i.e. no support for true decimal numbers etc. About two years ago we had a bad experience of saving money as a floating type (real) number, so we took a corporate decision to save all monetary amounts as decimals, I don;t want to open that can of worms again. So as the only limitation we have found to date with SQLCE is the paging, and I have a workaround for that, I think I will stay with SQLCE. Many thanks for your advise and help.
|
|
|
Hello Mark, Just to haul this thread back from the grave, Microsoft have added paging support to SQL CE in the new version 4, and we will have beta support for this in nightly builds beginning 30 July. Since SQL CE 4 is currently a pre-release, I suspect you won't want to switch at this stage, but I thought I'd mention it just in case. Information on our CE 4 support is here (please note the additional reference required). |
|
|
This is great news. But you are correct, I will will wait until it is a more stable platform before I look to incorporate it into our applications. Thanks for the update.
|
|