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
|
Hi, I'd like to select a subset of 50 items from a large table using a couple "wheres" to weed out some rows and an "order by" to raise some to the top. That's easy enough, but then I'd like to "subselect" 5 rows out of those 50 using some random element, like NEWID(). Is there a way to accomplish this without bringing the 50 records back from the database and then filtering again in memory? Thanks! James |
|
|
There's no LightSpeed API per se for this. LightSpeed can call SQL functions (see http://www.mindscape.co.nz/blog/index.php/2008/06/24/using-sql-functions-in-lightspeed/); I don't think you'll be able to use NEWID() or RAND() this way, because LightSpeed SQL functions always have to be applied to an expression (a column), but you may be able to think of something based on this approach. One other possibility is to take advantage of stored procedure support in current nightlies (see http://www.mindscape.co.nz/blog/index.php/2008/10/20/stored-procedures-and-lightspeed/) -- you can do the randomisation in the sproc. Obviously the downside of this is that it pushes the business logic
of filtering and ordering into the database, which may be undesirable. (The very latest nightlies also have top secret support for multiple views of the same entity type, using the Query.ViewName property, so you might be able to use a view if you don't like sprocs. The advantage of views over sprocs from the LightSpeed point of view is that you can supply filtering, ordering and paging from the LightSpeed end instead of it all being baked in as in the sproc scenario.) |
|
|
I'm wondering. Can this solution: http://weblogs.asp.net/fmarguerie/archive/2008/01/10/randomizing-linq-to-sql-queries.aspx help to retrieve random rows from DB using Lightspeed? SQL functions in Lightspeed are not supported? |
|
|
You can't use that solution directly because LINQ to LightSpeed doesn't have a custom function extensibility mechanism like LINQ to SQL. But in 2.2 you can use the same trick via a stored procedure or a view: CREATE VIEW RandomContributions AS IList<Contribution> randomContributions = unitOfWork.Find<Contribution>(new Query { ViewName = "RandomContributions", Page = Page.Limit(5) }); (If you're using the designer and LINQ, the designer generates a LINQ UOW property for each view, so you can just write unitOfWork.RandomContributions.Take(5).) Note that you will NOT be able to use this technique to page results in a random order. The order will be re-randomised every time you perform the query. I believe the LINQ to SQL technique you cite would have the same limitation. |
|
|
Hello! Can this be used to randomise not only one query? When you look at example on page I provided function GetNewId is not referring to any table so it can be used to randomise various queries. Can this be achieved by creating procedure instead of function and using it in LightSpeed? Thanks |
|
|
I don't think this could be made to work without some nasty and per-table kludging. LightSpeed expects order and query parameters to be related in some way to the entity being queried -- for example if you are querying for Contributions you can order by Title or by Contributor.UserName, because these are attributes (directly or indirectly) of a Contribution, but I can't think of a way to order by a completely unrelated value. Hmm, wait a minute. Actually, there *is* a way to do this using the core query API. (I don't think it can be done in LINQ at the moment because we don't have the extensibility mechanism.) It involves some truly dastardly and shameful hackery, but if you have a strong stomach, read on. 1. Create the RandomView view as in Fabrice's article. 2. Create the GetNewId function as in Fabrice's article, except that it takes an argument (which is ignored) of the same type as your entities. For example, if your entities have INT identity type: CREATE FUNCTION GetNewId ( 3. Write your query to use an Order along the lines of: Query query = new Query(); 4. Enjoy randomly ordered return values! The trick we've used here is the addition of the @ignored parameter to the GetNewId function. This allows us to hang the function on an entity attribute (in this case the Id attribute). LightSpeed is now happy because the ordering criterion is related to the entity. Caveat: this seems to have intermittent issues when you add paging into the mix, possibly due to wiring up of associations. I have not attempted to investigate or resolve these issues -- if you run into them then I would try experimenting with lazy loading to see if that resolves them -- in any case please be aware that there are potential problems and be ready to handle exceptions. |
|
|
I benchmarked this workaround. Unfortunately it's 7-10x slower than ORDER BY NewID() -- and my version of SSIS crashes if you turn on "show actual query plan." Ah, Microsoft. A trick not mentioned above is to create a computed column: ALTER TABLE Widget ADD Random AS NEWID(); Then ORDER BY Random performs the same as ORDER BY NEWID() in my tests. However this does break paging as LightSpeed issues two separate queries and expects the sorted records to match up by row number between the two calls. ... Read that last sentence again. Disabling eager load solves it. However the scenario has me wondering what happens if other records get inserted between the two calls under normal eager-load. GuidComb and KeyTable blocks in particular would seem to increase the odds of this happening. |
|