Articles → LIGHT SWITCH 2011 → Preprocess Query In Lightswitch 2011

Preprocess Query In Lightswitch 2011

In this article I will explain about preprocess query in lightswitch 2011.

Software Requirement

Before you start reading this tutorial you should make sure that

Visual studio 2010 is installed on your machine.
Visual Studio 2010 service pack 1 is installed on your machine.
Microsoft Visual Studio Light switch 2011 is installed on your machine.

Prerequisite Knowledge

Before you go ahead, it is required that you should know

Basics about lightswitch.
How to create screens in lightswitch?
How to create tables in lightswitch?

What Is Preprocess Query

Preprocess query is an alternative way of filtering records of query using code.

Scenario

Consider a scenario in which we have datagrid on the screen. The sort order of data in the grid is dependent on the value in settings table (Table to store user settings). If the user changes the settings then sort order of datagrid should also change.

Steps Of Execution

In this tutorial I will execute certain steps for the demonstration of preprocess query in lightswitch application. These steps are

Create a new project
Add tables
Add screen
Add data to tables
Write preprocess query

Let us execute each step

Create A New Project

First of all create a new project. See the screen shot below for project structure.

Picture showing the project structure of the lightswitch application in visual studio

Click to Enlarge

Add Tables

For this tutorial I have added 2 tables in the project i.e. ‘Employee’ and ‘SortSettings’. The purpose of ‘SortSettings’ is to store the sorting order. See the screen shot below for table schema

Picture showing the Employee table in the lightswitch table designer

Click to Enlarge

Picture showing the SortSettings table in the lightswitch table designer

Click to Enlarge

Add Screen

Add 2 screens of type editable grid screen (one for Employee and other for SortSettings table). See the screen shot below for project structure.

Picture showing adding 2 screens in the lightswitch application

Click to Enlarge

Add Data To Tables

See the screen shots of data in both the tables

Picture showing adding the data in both the screens

Click to Enlarge

Write Preprocess Query

To write a pre process query follow the following steps

Open Employees table (from Data Sources – ApplicationData)
From the ‘Write Code’ option select ‘Employees_All_PreprocessQuery’ as shown in figure below

Picture showing selecting an event for preprocess query in lightswitch

Click to Enlarge

Once you click on the option a code behind file opens i.e. ApplicationServiceData.cs on Server\UserCode path. In the code behind write the following code.

partial void Employees_All_PreprocessQuery(ref IQueryable < Employee > query) {
  var setting = this.DataWorkspace.ApplicationData.SortSettingsSet.FirstOrDefault();
  if (setting != null) {
    if (setting.SortFieldName.CompareTo("NAME") == 0) {
      query = query.OrderBy(x => x.EmployeeName);
    } else if (setting.SortFieldName.CompareTo("DOJ") == 0) {
      query = query.OrderBy(x => x.DOJ);
    }
  }
}

In this event we are getting the value of ‘SortFieldName’ column from ‘SortSettings’ table. If the value of ‘SortFieldName’ column is ‘NAME’ then we are sorting the collection (i.e. query) on the basis of EmployeeName in ascending order and if the value is ‘DOJ’ then sorting is done on DOJ.

Output

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Wednesday, April 3, 2013

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**