Wednesday, October 5, 2011

Sort This, you Light of a Switch

Did you know that LightSwitch by default cannot sort or search against lookup columns?
Sad but true. The native Data Grid of LightSwitch cannot sort or search against lookup columns. The good news is that, as always with LightSwitch, you have an option. Which is not at all bad, by the way. In non-editable grids, that is in search or list-detail screens, you can use views. Yes, database views that is. Another solution would be calculated fields, but, although simpler, performance is poor IMHO.
Views? How do I create views in LightSwitch? Well, you don’t. You cannot have views in native data sources (at least I am not aware of some way). But you can import them. Either with a SQL Server data source from an existing database, or with RIA Services data source.
Having a view containing the id of the entity (it doesn’t have to be visible) you can use it to either open the default detail screen on click, or, in the list detail scenario, remove the detail group, add the original entity’s collection query and filter it based on the selected item of the view query, or have a <EntityCollection>_SinlgeOrDefault query and bind the parameter to the view query's selected item id column. Then add a detail group for this query. You might have implementation issues you should deal with, but hey, this is a tips blog.
P.S.1 If you use or planning to use 3rd party controls your commercial data grid might solve this problem without all of the above. But If you don’t…
P.S.2 The whole truth (and nothing but the truth) is that you might have issues correctly importing views from SQL Server (regarding primary key for example, have you fixed it yet George?) so if I have to be honest, I must say RIA is the best way to go…Winking smile

1 comment:

  1. This is really an interesting topic. Congratulations to the writer. I'm sure a lot of readers having fun reading your post. Hoping to read more post from you in the future. Thank you and God bless!