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 all, I'm using LS 1.2 and I need to select some rows. I have the equivalent SQL, does anyone know how to get this into a LS Query object? Note that all the attributes are 1 to 1 mapping on my ArchivedDataTallies class.
Thanks, Todd </p><p> |
|
|
As we understand it, what you're trying to do here is get the highest-dated ArchivedDataTally object for each SpiderId in the ArchivedDataTallies table. I.e. you want to return one ADT for each SpiderId, and that one ADT will be the most recent ADT for that SpiderId. This isn't possible in LightSpeed 1.x (in fact we don't even think it's possible in LightSpeed 2.x). If the expected number of distinct SpiderIds is reasonably small, and the operation is not performance-critical (e.g. reporting), this could be done in N+1 queries (one to get the list of SpiderIds, then one for each SpiderId to select the most recent ADT for that SpiderId using a normal "order by / limit 1"). That's the best we've managed to come up with I'm afraid. (For future reference, in 2.x this would be a great fit for a stored procedure; but I appreciate that moving to 2.x is probably not an option for you at this stage.) |
|
|
I actually may need to move to 2.2 to get this working. Part of the problem is our data set is far too large to process in a single pass. As such I have to find all units that don't have a current data tally, then iterate over the first 100 or so and process them. I have to use paging to repeat this process until everything is complete. I'm working on a quick migration so I can run my tests to see if anything is broken. Is there a good tutorial on migrating from 1.x to 2.2? I'm having a hard time with all my Repository.Add calls no longer working. I can't pass a context around, I need to get it from the current thread as several classes work together in the same thread to perform updates and adds in a transaction.
Thanks, Todd |
|
|
We don't have a tutorial, but I'd suggest you check out the PerRequestUnitOfWorkScope (or possibly PerThreadUnitOfWorkScope) class. This takes care of creating and sharing unit of work objects without the need to pass them around explicitly. The idea would be that you create a static Repository class and implement the 1.x methods such as Add on that. Internally your Repository class would get its unit of work from a scope class. For example: public static class Repository { There are issues to consider here around things like disposal, so I'm not suggesting this is the best structure long term, but for a quick migration this might be enough to get you started, and in fact I think this is pretty similar to how LightSpeed 1.x works internally. |
|
|
Is it possible to use the stored procedure on a given colum to accomplish what I need in LS 1.2? I noticed that there is an SQL Function method when constructing my PathExpression for the Query object. |
|
|
You might be able to do some of what you want by applying the MAX function to the PathExpression for the ArchivedOn column. But I think the self-join would still cause problems. So my advice would be to wrap up your entire query as a stored procedure and call it as follows: unitOfWork.Find<ArchivedDataTally>(new ProcedureQuery("GetLastArchiveForAllSpiders"), Notes: * I am assuming your query is returning all the columns of the ArchivedDataTallies table. If you are selecting only a subset of the columns, you will need to define a new entity type to receive the projection, with just the two columns you need. (Unless ADT contains lots of fields, or very large BLOB-type fields, I'd advise returning all columns from the sproc so as not to clutter your app up with projection types.) * The "PriorTo" parameter is my interpretation of the literal date in your sample query -- making this clear in case I've misinterpreted what that date was doing! Let us know if you need any further info. |
|
|
On more question. My stored procedures reture all rows that meet the query I need, but there is simply too much data to process in a single pass. I'm working on doing this in sections. Is it possible to use paging on stored procedures to select the top x rows, or do I have to add that on my stored procedure?
Thanks, Todd |
|
|
You would need to add that on your stored procedure. |
|