Advanced LightSwitch: Writing Queries in LightSwitch Code

Advanced LightSwitch: Writing Queries in LightSwitch Code

Rate This
  • Comments 6

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:

image

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!

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • How can i get max of column and assign to another computed field or any local variable

  • How can i get max value of column and assign to another computed field or any local variable

  • Hi, thanks for this explanation,

    i have new requirement i am use oracle data base for my project. i am not using sequences in oracle insertion process. i have to implement max value+1 here in lightswitch application when i am creating record.

    Thanks in advanced

  • How can i get max value of column and assign to as default value of add data form??

  • You say we can write this queries everywhere we can write code in lightswitch.

    Could you be more specific and show us where to write code for each situation?

  • This doesn't seem to work for me. Intellisense certainly doesn't like this code in "beforeApplyChanges".

Page 1 of 1 (6 items)