A group blog from members of the VB team
Ingredients:
· Visual Studio 2008 (Beta2 or Higher)
Categories: LINQ to SQL
Introduction:
As we add cookbook entries, we will include performance improvement tips where appropriate. Here’s a tip that you can use with LINQ to SQL to speed up queries that you will call numerous times: Pre-compiled queries. By pre-compiling a query, you perform the processing to compile the query once, and then skip the compile step every consecutive time that you run the query.
Pre-compiled queries are created using the Compile method of the CompiledQuery class in the System.Data.Linq namespace. You pass a Lambda expression that contains the variables used by your query (the DataContext object, filter variables, and so on) to the Compile method to create a compiled query as shown in the following example.
Dim query As Func(Of NorthwindDataContext, Customer, IQueryable(Of Order)) = _
System.Data.Linq.CompiledQuery.Compile( _
Function(database As NorthwindDataContext, cust As Customer) _
From order In database.Orders _
Where order.Customer Is cust)
The first time the query executes, the query will be compiled and stored in the specified variable. After that, the compiled query is used when the query executes.
In this recipe, you will create a classic master-detail application using the Northwind sample database. The “master” list will display the customers from the Northwind database using a LINQ query, and the “detail” list will display the orders for the selected customer using a pre-compiled query.
Instructions:
· Create a Windows Forms Application with a connection to the Northwind database. Add a LINQ to SQL Classes item named Northwind.dbml and drag the Customers and Orders tables to the data classes design surface in the Object Relational Designer. For steps on creating an application that uses the Northwind database, see LINQ Cookbook Recipe 6: Your first LINQ application using Northwind.
· From the Toolbox, drag a ListBox control (to display customer information), a DataGridView control (to display order information), and a Label control (to display timer information) onto the form. Resize the form and controls as needed.
· Double-click the Form to edit the application code.
· Replace the default Form1 class with the following code that defines class-level variables and the Form1.Load event handler:
Imports
Public
Private orderQuery As Func(Of NorthwindDataContext, Customer, IQueryable(Of Order))
' LINQ to SQL DataContext object for Northwind.
Private db As New NorthwindDataContext
' Timer to show performance difference.
Dim queryTimer As New Stopwatch
Dim slowestTime As Long?
Dim currentTime As Long
Private Sub Form1_Load() Handles MyBase.Load
' Get the list of Customers and bind it to ListBox1.
ListBox1.DataSource = From cust In db.Customers _
Select cust _
Order By cust.CompanyName
ListBox1.DisplayMember = "CompanyName"
End Sub
End Class
· After the Form.Load event, add the following GetOrders method to retrieve orders for a customer.
Private Function GetOrders(ByVal selectedCustomer As Customer) As List(Of Order)
' If the query for orders has not been compiled yet, compile it. Otherwise,
' use the compiled query.
If orderQuery Is Nothing Then
orderQuery = CompiledQuery.Compile( _
End If
' Execute the compiled query by calling the ToList method and
' return the results.
Return orderQuery(db, selectedCustomer).ToList()
End Function
· After the GetOrders method, add the following event handler for the ListBox.SelectedIndexChanged event. The event handler will pass the current selected customer to the GetOrders method to call the pre-compiled query and retrieve the orders for that customer. Some simple timer code is added to show the difference between the first time the query executes, and the current execution time.
Private Sub ListBox1_SelectedIndexChanged() Handles ListBox1.SelectedIndexChanged
queryTimer.Reset()
queryTimer.Start()
' If a customer has been selected, get the list of orders for the customer
' and bind it to DataGridView1.
If ListBox1.SelectedValue IsNot Nothing Then
DataGridView1.DataSource = GetOrders(ListBox1.SelectedValue)
queryTimer.Stop()
currentTime = queryTimer.ElapsedMilliseconds
If slowestTime Is Nothing OrElse currentTime > slowestTime Then
slowestTime = currentTime
Label1.Text = "Elapsed Time: " & currentTime & "/" & slowestTime
Press F5 to see the code run. Click on different customers in the ListBox to see the performance difference using a pre-compiled query.
Sorry for offtopic, but... Why are we forced to write
Dim data As Integer()()() = {New Integer()() {New Integer() {1, 2}, New Integer() {2, 3}}}
instead of
Dim data As Integer()()() = {{{1, 2},{2, 3}}}
Isn't it typesafe?
It's not a type-safety issue. It's just syntax. VB doesn't support the second syntax example for jagged arrays.
Here's a summary of all the content the VB team members, including myself, have created for you on
Hello
I am trying to do something similar in order to create a compiled query for reuse and using your code have created the same GetData function and variable using my own data context tables
Private orderQuery As Func(Of HCADataContext, String, IQueryable(Of T_HCA_Suministro))
Private Function GetData(ByVal pCity As String) As IQueryable(Of T_HCA_Suministro)
Function(database As HCADataContext, pLocal As String) _
From data In db.T_HCA_Suministro
Where data.HCAPS_Localidad = pLocal
Select data)
'From data In db.T_HCA_Suministro
' Where data.HCAPS_Localidad Is pLocal
' Select CUPS = data.HCAPS_CUPS, Localidad = data.HCAPS_Localidad, Provincia = data.T_Tipo_Provincias.Pr_Nombre)
Return orderQuery(db, pCity).ToList()
Call to populate grid is:
If lbCity.SelectedValue IsNot Nothing Then
DataGridView1.DataSource = GetData(lbCity.SelectedValue)
With the ToList method I am receiving the error:
No se puede convertir un objeto de tipo 'System.Collections.Generic.List`1[LINQ_Test_2.T_HCA_Suministro]' al tipo 'System.Linq.IQueryable`1[LINQ_Test_2.T_HCA_Suministro]'.
If I remove the toList method I receive no error but the data is not displayed in the datagridview even though I can see that the datagridviews datasource contains the correct data.
Any ideas where I might be going wrong?
Okay I have got this working by making the following changes:
Return orderQuery(db, pCity)
DataGridView1.DataSource = GetData(lbCity.SelectedValue).ToList()