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 have an existing Query object, which I would like to use to run three different aggregate functions in one hit on the database. I'd like the generated SELECT clause to look something like:
I suspect this requires LightSpeed's projections. E.g. adding:
But how do I go about passing the logic from the desired case statements as a parameter of this function? Cheers, -- John |
|
|
Hi John, In general we dont have direct support for representing this type of statement in the Querying API so we would suggest using UnitOfWork.FindBySql or using a stored procedure (depending on what options you have available). However we do have "undocumented" ways of representing this type of function use which we use within the LINQ provider translation. Here is an example of what you will need to do in this particular case:
|
|
|
Ah, that's crafty! I'm working with a big existing Query object here, so a stored proc or FindBySql aren't really options here. Last question I hope - how to execute that so that the projection knows which properties of the target type to populate? E.g.
All of the function and projection overrides that allow an alias or name seem to be unavailable for the solution above, so the .Project<>() call is currently unable to bind to anything. The generated SQL for the projection query does provide internal aliases for the two case statements I'm using (e.g. TableName.FieldName.CASE1_SUM1). Is it safe to depend on those? (Assuming it would work if I could somehow name my struct's fields to match.) Cheers, -- John |
|
|
You cant control the aliasing, so if you are projecting to a specific type your type property names will need to match the auto-generated names in the projection. Those appended function names are deterministic (we just count the instances of a function name that we have seen when appending them). Alternatively if that doesn't work you can use UnitOfWork.Project() which returns an IDataReader and then you can manually do the mapping out of that.
|
|
|
I was just about to update my post having discovered the returned IDataReader approach! Happy now just getting them by field index from the reader. Thanks for your help. Cheers, -- John |
|