Querying Data Using LINQ to SharePoint Share Point 2010

One of the primary functions of LINQ is querying data. In this section, we’ll dig a little deeper into how LINQ works before looking at how to retrieve data using LINQ to SharePoint.

Query Limitations

As you saw earlier, LINQ works by passing lambda expressions to extension methods that have been declared to extend the IEnumerable and IEnumerable<T> interfaces. By chaining together these extension methods, you can create complex queries, all with compile-time syntax checking and type-safe return values. Earlier we looked at LINQ to Objects as a simple starting point to discuss how LINQ works. In the case of LINQ to Objects, parsing queries is relatively straightforward since the objects are in memory and LINQ is simply being used as a shortcut for more traditional programming techniques. Ultimately, behind the scenes, LINQ to Objects simply expands the query into the more verbose code that we would have written in a non-LINQ world.There is one major difference between LINQ to Objects and LINQ to SharePoint, however, and that is the location of the data being queried. For LINQ to SharePoint, the data exists not in memory—as is the case for LINQ to Objects—but in the SharePoint content database. Because of this, LINQ to SharePoint has to work a bit harder in parsing the LINQ queries. Rather than simply expanding the queries, the parser must convert them to a syntax that can be used to query the SharePoint content database directly. Since theSharePoint platform defines its own query language in the form of the XML dialect CAML, LINQ to SharePoint must translate all LINQ queries into CAML. These CAML queries can then be processed directly by the SharePoint platform. Once the results are returned, they are mapped onto strongly typed entity objects, which are then returned as the query results.

Expression Trees

It’s worthwhile for you to understand how this process works, because it has implications when it comes to creating more complex queries—as you’ll see later. To find out a bit more, let’s start with one of the extension methods that we’d commonly use in a LINQ query. If we examine the Where extension method in more detail, we find the following method signature:

At first glance, you may think there’s too much information in this statement and decide to skip ahead a few paragraphs—but bear with me, because only one part of the signature is actually important for the purposes of this discussion. The method accepts two parameters: the first being an IQueryable data source and the second being a generic Expression object of type Func. The Func object is a delegate that references the code that’s been entered as the lambda expression, and this is passed as a parameter to the Expression object. The Expression object, however, converts the lambda expression into an ExpressionTree. The ExpressionTree is where the magic that is LINQ takes place. By using expression trees, you can programmatically analyze lambda expressions. As a result, you can convert compiled expressions into something completely different by applying logic to the expression tree. By using this process, LINQ to SharePoint converts the lambda expressions that make up a query into valid CAML syntax that can then be directly executed against the content database. (As an aside, this is exactly the same way that LINQ to SQL works—the only difference is the target query syntax.) All very interesting, you may be thinking, but why is this relevant? Well, here’s the thing:

Many extension methods are defined for LINQ, they all have a default implementation in LINQ to Objects, and it’s down to the creator of a new LINQ provider to override them with a platform-specific implementation. However, CAML doesn’t support all the available extension methods. Some things in there simply can’t be translated into CAML. In other implementations such as LINQ to SQL, where a method can’t be implemented directly in SQL, the standard LINQ to Objects method is used, meaning that a portion of the query is performed using SQL, the results are then loaded into memory, and any remaining operations are performed using LINQ to Objects extension methods. Of course, this is all done behind the scenes and is completely transparent to the user. The issue with LINQ to SharePoint is that CAML is a very limited language when compared to SQL, and as such a significant number of the standard LINQ operations are not possible. If such operations were left to revert to their default implementation in LINQ to Objects, significant performance issues could result due to the amount of in-memory processing that could be required. Without an in-depth understanding of how LINQ works, an uninitiated developer could easily create a LINQ query that, if executed concurrently by many users, could severely degrade system performance.

Inefficient Queries

At the time of writing, this problem has been highlighted by having the LINQ to SharePoint provider throw an error if an unimplemented extension method is used in a query. In previous versions, this behavior could be controlled by setting the AllowInefficientQueries flag on the DataContext object; however, the current version—at the time of writing, Beta 2—no longer allows this flag to be publically altered and therefore unimplemented expressions will not work with LINQ to SharePoint. This may change in future releases of the product. The following extension methods are considered inefficient due to inability to convert to CAML and are therefore not implemented by LINQ to SharePoint:

Inefficient Queries

Performing a Simple Query

Now that I’ve discussed the dos and don’ts of LINQ to SharePoint, let’s get started on creating a few queries using our sample application. We’ll extend our user interface to make it easier to view our results and the CAML that’s being generated behind the scenes. On Form1.cs in LinqSampleApplication, add a SplitContainer under the buttons that we added earlier. Set it to anchor to all sides. Within the left panel, drop a WebBrowser control, and set its Dock property to Fill. In the right panel, drop a DataGridView control, and set its Dock property to Fill. Add another button next to those created earlier, and label the button Basic Query. Once you’ve finished, your form should look like this:

Performing a Simple Query

In the on-click event handler for the Basic Query button, add the following code:

Notice a few interesting things about this code sample. First, notice the use of the Log property on the DataContext object. When a Text Writer object is assigned to this property,the CAML that is generated by the LINQ provider will be output to the Text Writer when itis executed. In this sample, we’ve made use of that functionality to generate a temporaryXML file that we can view using our WebBrowser control.Another thing to highlight is the ObjectTracking Enabled flag: since we’re planning toexecute queries only using this DataContext, setting this flag to false will improve performance,because the provider doesn’t need to track references to the underlying SPListItem objectsthat are represented by the result set.Finally, the last thing to note is the use of the ToList extension method when assigningthe query as the data source for our Data Grid View. LINQ queries are not actually executeduntil the result set is enumerated. Since the Data Grid View control doesn’t support the IEnumerable interface for data sources, the result set is never enumerated, and thereforethe query is never executed. The ToList extension method enumerates the result set toconvert the results into a generic list; this list is then passed to the Data Grid View, enablingus to view the results in the user interface.Running the sample application and then clicking the Basic Query button should yieldthe following result:

Performing a Simple Query

Result Shaping Using LINQ
One of the benefits of LINQ is its ability to shape result sets while still retaining a type-safe output. We can explore this functionality by adding another button to our sample application; this time, label it Basic Result Shaping and add the following code:

Notice the creation of a new anonymous type as part of the LINQ query. The new type consists of fields from the returned entity together with some string manipulation functions to get the results into the appropriate format.
Notice when examining the generated CAML for this query that the string manipulations have not been translated. Although CAML does not support these operations, the LINQ to SharePoint provider still allows them as part of the query because they are performed on the results and are therefore unlikely to cause significant performance issues.

Joining Tables Using LINQ
You may have noticed in the preceding code that the Join extension method is included in the list of inefficient extension methods earlier in the chapter and as such is not permitted within LINQ to SharePoint. However, this does not mean that retrieving related data isn’t possible, only that arbitrary joins are not supported. Where the relationship between two entities is defined by a lookup column, retrieving related data is permitted and in fact is actually achieved using a much simpler syntax than is required by the Join operator.

Simple Join
Let’s add a new button to enable us to execute a basic join query. Label the button Basic Join Query and in the event handler add the following code:

From this code example, you can see that the join is performed by making use of the lookup field. Within the LINQ query, the lookup field is of the same type as the item to which it refers. In this case, the Contract Reference lookup field is implemented as a property of type Hire Contract. This allows you to deal with entity objects within a logical hierarchy, ignoring the underlying data structure implementation. This is much simpler than the Join extension method syntax, which relies on your knowledge of the data structure to know which tables to join together and which fields to use for the join.

Complex Join
Using this syntax, you can join multiple lists together as long as appropriate lookup fields have been defined. To explore this, we’ll add another button, this time labeled Complex JoinQuery with the following code:

In this sample, we’ve joined all three lists together and have also performed some string processing as part of the Where clause. You’ll notice from the generated CAML that the Location Code filter is not included in the query; that’s because the LINQ provider brings back the resulting rows and then programmatically applies the additional filter in memory before returning the results. Again, this can be done efficiently because only one CAML query is required, so the resource usage is minimal.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Share Point 2010 Topics