Skip to main content Skip to footer

Searching Numeric Fields in a LightSwitch Search Screen

LightSwitch makes building data-centric applications crazy easy, but sometimes the way a screen behaves isn't initially intuitive. One of the things that trips people up is the way searches work--by default, only string fields in the current entity are searched, and the LIKE comparison is used. Two of the screens you see this behavior on are the Editable Grid Screen and the Search Data Screen.

To replicate this action, create a LightSwitch application; I'm connecting to a local Northwind database. Model an entity with both string and numeric fields (the Orders table works great).

Now, add an Editable Grid Screen, with Orders as the data source.

Now, Hit F5 to run/debug your app. On the search screen, you can search by any of the string fields--Ship Name, Ship Address, Ship Postal Code, etc. and return the results you want. Searching by order ID, however, does not return any results.

By using SQL Server Profiler, it's easy to see what the WHERE clause looks like:

This query returns the results we expect. Let's do the same thing, using an Order ID instead. No results are returned, and we can see the query:

The OrderID field isn't in the WHERE clause because it's an integer field. Fortunately, there is an easy solution.

Right-click on the Orders entity, and choose Add Query. Add a new parameter of type Integer, and expand the properties pane to make it optional. Then, add a Filter, with the OrderID equal to our OrderID parameter.

N ow, add another Editable Grid screen, but use our query as the data source.

When we run the app and choose our new screen, we can see a new search field has been added at the top. We can now retrieve an order by order ID by using this new search box.

Since an additional search input is still present by default, this obviously isn't the best UI. Although this method is not bad for one or a couple queries, it's not practical for a really flexible query system. For something really flexible, Sheel Shah on the LightSwitch team posted a sample Filter Control for Visual Studio LightSwitch, which allows users to more interactively build a query filter (in addition to being an example of how to write a LightSwitch extension).

If this extension is of interest, I suggest you read an overview on it at

Hope this helps! Let us know what you build!


comments powered by Disqus