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 have a table which contains four columns, and about 10,000 rows. It is the message lookup table for the application. The first column is the ID <int> field, column EngMsg are the English messages, column three is the IndoMsg, i.e. Indonesian messages etc. Before using LS, the application would open a dataset based on a SQL query, i.e. if the language selected was 0 zero (English) we would perform an SQL command of Select ID, EngMsg As Msg from Messages, if the language selected was 1, i.e. Indonesia the SQL becomes, Select ID, IndoMsg As Msg from Messages. The result being two columns of Id, and Msg, regardless of language. We then have a GetMsg function which given an Id would return the Msg column as a string. As we are now converting the app to use LS, we wish to do the same thing with strongly typed classes instead of datasets. However, I can achieve the loading part using the LINQ to LS interface, but it returns an anonymous typed object, which cannot easily be passed between functions or searched later for the correct message. I am looking for a way to store and pass an object which can select a limited number of fields, including the AS part, i.e. EngMsg AS Msg, and to allow me to search for a single record based on ID. I know I could do this by using the GetMsg to find the correct String each time by passing the ID, and Language election, but this would entail thousands of database accesses to retrieve the messages, and would involve changing thousands of function calls to include the selected language. I am at my wits end, and am considering using DataSets, but then I loose the main advantages of LS, i.e. Strongly typed, and database independent. I hope someone can help me with this. |
|
|
Hi Mark, If you project the results of your query into a known type rather than an anonymous type you will be able to pass it out of the method scope. e.g. define a holder class which just matches the definition of your current anonymous type selection and then select new Class() { a = a, b = b ... } rather than select new { a = a, b = b }. In terms of column aliasing, this is not something you can specifically control with the LightSpeed querying pipeline, however we do provide a couple of escape hatches for crafting custom SQL by either using a stored procedure (if the provider supports it) or by using the FindBySql() method on UnitOfWork. Another possibility, again depending on provider support if you are needing to target multiple database platfoms, would be to do the conditional selection of the msg column using a database function. Let us know how you get on with this :)
Jeremy |
|
|
Hi Jeremy,
Thanks for the advice, the holder class definition worked a treat for me. I was trying to avoid anything to do with passing actual SQL for fear of runtime errors later if the database server changes. hence the reason for not using FindBySql(). Again, thanks Mark |
|