Advanced LightSwitch: Writing Queries in LightSwitch Code

This post describes how to query data in LightSwitch using code and how to determine where that query code is executing. It doesn’t describe how to model a query in the designer or how to write query interception code or bind a query to a screen. If you are new to queries in LightSwitch, I suggest you start here – Working With Queries. This post is all about coding and where the query code executes. The goal of the LightSwitch query programming model is to get query code to execute where it is the best performing; as close to the database as possible.

LightSwitch Query Programming Model

Here is how things are structured in the LightSwitch query programming model.  LightSwitch uses three different Types to perform its LINQ-like query syntax. They are IDataServiceQueryable, IEnumerable and IQueryable.

IDataServiceQueryable – This is a LightSwitch-specific type that allows a restricted set of “LINQ-like” operators that are remote-able to the middle-tier and ultimately issued to the database server. This interface is the core of the LightSwitch query programming model. IDataServiceQueryable has a member to execute the query, which returns results that are IEnumerable.

IEnumerable – Once a query has been executed and results are available on the calling tier, IEnumerable allows them to be further restricted using the full set of LINQ operators over the set of data. The key thing to remember, though, is that the data is all on the calling tier and is just being filtered there. LINQ operators issued over IEnumerable do not execute on the server.

IQueryable – This is the interface that provides the full set of LINQ operators and passes the expression to the underlying provider. The only place you will see IQueryable in LightSwitch is in the _PreprocessQuery method, which always resides on the service tier. Since this post is just about coding, we won’t be talking about IQueryable.

LightSwitch Code Generation

Next, let’s talk about the members that get generated when you model an entity with relationships in LightSwitch. These are the members that are used in the LightSwitch query programming model.

For each entity, LightSwitch generates:

An entity set member on the Data Source. For example, if you had a Customer entity in your SalesData data source, you’d get an entity set member like this:

C#

this.DataWorkspace.SalesData.Customers

VB

Me.DataWorkspace.SalesData.Customers

For each “many” relationship, LightSwitch generates an entity collection member and query member for the relationship. For example, if you had a 1:many relationship between Customer and Order in your SalesData model, you’d get the following members on Customer:

C#

EntityCollection<Order> Orders;  // EntityCollection is IEnumerable
IDataServiceQueryable<Order  > OrdersQuery; 

VB

Orders As EntityCollection(Of Order)'EntityCollection is IEnumerable   
OrdersQuery As IDataServiceQueryable(Of Order) 

Here’s a diagram of the members/types and transitions between them:

LightSwitch Query Scenarios

Here are some example query scenarios and the code that you could write to solve them. The code for any of these examples can be written anywhere you can write code in LightSwitch, client or server.

Scenario 1: Get the top 10 Orders with the largest discount:

- Using the entity set, the operators are all processed remotely, since they are all supported LightSwitch operators. The query doesn’t execute until it is enumerated in the “for each” statement.

VB:

Dim orders = From o In Me .DataWorkspace.SalesData.Orders
             Order By o.Discount Descending Take 10 

For Each ord As Order In orders 
Next 

C#:

IDataServiceQueryable <Order> orders =( from o in this.DataWorkspace.SalesData.Orders 
orderby o.Discount descending selecto).Take(10); 

foreach (Order ord in orders)
{  } 

Scenario 2: Get the top 10 Orders with the largest discount from a given customer:

- There are two different ways of doing this, one using the Orders member and the other using the OrdersQuery member.  One is local, the other is remote. If all of the orders are already loaded, the local query is just fine. The orders may be loaded if you have a list on a screen that contains all orders or if you’ve already accessed the “Orders” member somewhere else in code. If you’re only looking for the top 10 Orders and that’s all you’re going to load, the remote query is preferable.

VB:

  'Local execution  
Dim orders = From o In cust.Orders 
             Order By o.Discount Descending Take 10 

For Each ord As Order In orders 
Next  

'Remote execution  
Dim orders = From o In cust.OrdersQuery 
             Order By o.Discount Descending Take 10 

For Each ord As Order In orders 
Next  

C#:

// Local execution   
foreach (Order ord in (from o in cust.Orders 
         orderby o.Discount descending select o).Take(10)) 
{ 
}             

// Remote execution  
foreach (Order ord in (from o in cust.OrdersQuery 
         orderby o.Discount descending select o).Take(10)) 
{ 
} 

Scenario 3: Get the top 10 Orders with the largest discount from a query:

- This is a modeled query called “CurrentOrders”. This query executes remotely.

VB:

Dim orders = From o In Me.DataWorkspace.SalesData.CurrentOrders  
             Order By o.Discount Descending Take 10

For Each ord As Order In orders
Next 

C#:

foreach (Order ord in (from o in this.DataWorkspace.SalesData.CurrentOrders() 
         orderby o.Discount descending select o).Take(10))
{ 

} 

Scenario 4: Sum Total of top 10 Orders with no Discount

- This query retrieves the top 10 orders and then aggregates them locally. The Execute() method is what executes the query to bring the orders to the caller.

VB:

Dim suborders = From o In Me.DataWorkspace.SalesData.Orders  
                Where o.Discount = 0  
           Order By o.OrderTotal Descending   
           Take 10 

Dim sumOrders = Aggregate o In suborders.Execute() Into Sum(o.OrderTotal) 

C#:

decimal sumOrders = (from o in application.DataWorkspace.SalesData.Orders  
                     where o.Discount == 0 orderby o.OrderTotal descending   
                     select o).Execute().Sum(o=>o.OrderTotal); 

Scenario 5: Sum Totals of top 10 Orders with no Discount from a Customer

- Again, there are two different ways of doing this, one using the Orders member and the other using the OrdersQuery member.  One performs the first part of the query locally, the other is remote. The same rule applies as Scenario 2: If all of the orders are already loaded, the local query is just fine. If you’re only looking for the sum of the top 10 Orders and that’s all you’re going to load, the remote query is preferable.

VB:

'Local execution   
Dim sumOrders = Aggregate o In cust.Orders  
           Where o.Discount = 0  
           Order By o.OrderTotal Descending   
           Take 10 Into Sum(o.OrderTotal) 

'Remote & local execution  
Dim ordersQuery = From o In cust.OrdersQuery  
             Where o.Discount = 0  
             Order By o.OrderTotal Descending   
            Take 10 

Dim sumOrders = Aggregate o In ordersQuery.Execute() Into Sum(o.OrderTotal) 

C#:

// Local execution   
decimal sumOrders = (from o in cust.Orders where o.Discount == 0  
                     orderby o.OrderTotal descending   
                     select o.OrderTotal).Take(10).Sum(o=>o.OrderTotal); 

// Remote & local execution  
decimal sumOrders = (from o in cust.OrdersQuery where o.Discount == 0  
                orderby o.OrderTotal descending   
                select o).Take(10).Execute().Sum(o=>o.OrderTotal); 

Scenario 6: Sum Totals of top 10 Orders with no Discount from a query

- This is very similar to the previous scenario, except calling the modeled query always executes remote. The sum is still performed locally.

VB:

Dim orders = From o In Me.DataWorkspace.SalesData.CurrentOrders  
        Where o.Discount = 0  
        Order By o.OrderTotal Descending   
        Take 10 

Dim sumOrders = Aggregate o In orders.Execute() Into Sum(o.OrderTotal) 

C#:

decimal sumOrders = (from o in this.DataWorkspace.SalesData.CurrentOrders()  
                     where o.Discount == 0  
                     orderby o.OrderTotal descending          
                     select o).Take(10).Execute().Sum(o=>o.OrderTotal); 

I hope these scenarios have helped you see how the LightSwitch query programming model is there to help you write the queries you need while helping to keep things as performant as possible. Happy Querying!