LINQ to SharePoint

Now available: 0.2.4.0 alpha for Orcas RTM

2007-11-30T08:27:02Z

It's finally here: LINQ to SharePoint 0.2.4.0 Alpha. This is our first official release targeting Visual Studio 2008 and .NET Framework 3.5 RTM.

What's new in this release?

The focus for this release was primarily on getting the bits ready for Visual Studio 2008 RTM. Although there were no major API changes between Orcas Beta 2 and Orcas RTM that affected LINQ to SharePoint, we needed to validate quite some things.

While working on entity updating support in a separate prototyping branch (something planned for v0.3, which will be our first Beta, planning TBD) a few bug fixes made it in this alpha.

One major point of focus was reducing the dependencies by introducing a provider model. In the past alphas, there was a strong dependency on Microsoft.SharePoint, aka the SharePoint Object Model. Normally, this assembly is only available on machines where Windows SharePoint Services is installed. Using LINQ to SharePoint previously introduced the need to redistribute this assembly because:

we had a few internal dependencies on it in the parser;
the generated data context object had a constructor taking in an SPSite object.

First of all, we eliminated the dependencies in the parser, making the parser "provider-agnostic". Next, the communication layer was divided into two pieces:

a webservice based provider, which is integrated in BdsSoft.SharePoint.Linq;
a SharePoint Object Model based provider, that lives in a separate assembly called BdsSoft.SharePoint.Linq.ObjectModelProvider.

By default, we only import BdsSoft.SharePoint.Linq which uses webservice communication and doesn't depend on Microsoft.SharePoint. If you want to use the SharePoint Object Model you'll need to opt-in for it explicitly. Since the SharePoint Object Model can be used only on the local machine where WSS is installed, you'll have the Microsoft.SharePoint assembly over there anyhow.

The way this looks in the Entity Wizard is shown below:

This checkbox has to be explicitly marked if you want to use the SharePoint Object Model. It causes the SPML to include an attribute that switches on the SharePoint Object Model data provider (click to enlarge):

When generating the code for the SPML definition, we'll emit an additional constructor that supports the SharePoint Object Model (click to enlarge):

Under the covers, the SharePointDataContext base class has a constructor overload accepting a Uri (for the web services provider which comes with the LINQ to SharePoint main assembly) and one that takes in an ISharePointDataProvider, a new interface that abstracts away the underlying provider implementation. Theoretically 3rd parties could implement another provider but the Entity Generator tool won't be aware of it (theoretically we could have provider registration stuff in the registry and load the provider list dynamically, asking it to generate the desired constructor overloads, etc but we don't expect there's a big need for additional providers - tell us if the assumption is wrong!).

Only if the SharePoint Object Model provider was enabled, we'll import the required assemblies for you:

Notice our setup package that integrates with Visual Studio 2008 includes these assemblies but the goal of this feature is to reduce the runtime (and deployment) dependencies.

For the SPMetal freaks we have an -enablesom flag that does exactly the same as the wizard:

Download now

Get it here: LINQ to SharePoint 0.2.4.0 Alpha. Notice all usual disclaimers apply; this software is still alpha quality so we don't make any guarantees whatsoever concerning the functional or quality.

Enjoy!

LINQ to SharePoint 0.2.4.0 Alpha for Orcas RTM is here

2007-11-30T00:19:00Z

I'm happy to announce you the availability of LINA to SharePoint 0.2.4.0 Alpha, ready for VS2008 and .NET Framework 3.5 RTM. Read the full details on the LINQ to SharePoint team blog.

New blog series on LINQ to SharePoint

2007-08-22T10:46:09Z

Dimitri Clement has jumped on the LINQ to SharePoint trained and started a blog series on using LINQ to SharePoint. His first entry can be found on his blog over here. In his entry, he outlines the installation of the 0.2.3 build and how to get a jumpstart with LINQ to SharePoint. Enjoy and keep an eye on his blog for more posts in the near future!

Alpha 0.2.3 now available

2007-08-13T21:00:49Z

Last weekend, we did the finishing touch on the 0.2.3 release, so here it is today: please welcome LINQ to SharePoint v0.2.3.0 alpha. This is the first release to target Orcas Beta 2 and covers the following enhancements:

SPML has been extended and can now map lists, fields and choices on (Multi)Choice fields. The LINQ to SharePoint MSI setup registers the XSD file for SPML in the appropriate folder so you get full IntelliSense support in the VS IDE.

Also, the code generator sitting behind the SPML files in VS 2008 doesn't require any connection to the server when generating code since SPML is now fully self-describing. As a matter of fact, the SPML code generator runs in so-called "offline codegen" mode, as explained further in the SPMetal section.
The LINQ to SharePoint Entity Wizard is fully layered on top of the entity generator and supports enhanced list and field mapping (including the ability to set aliases on mappings to make the generated classes more user-friendly). It also support list name (English) depluralization (see below):
SPMetal enhancements:
- Full support for SPML using two times two modi: online and offline; export and codegen (but offline-export isn't meaningful). So you can do things like this:
  - Online - export: generate an SPML file for the specified list + optional context
  - Online - codegen: generates C# or VB code for the specified list + optional context (internally, it first generates the corresponding SPML)
  - Offline - codegen: takes an SPML file as the input and generates C# or VB code for it; this allows people to export SPML first (online-export), modify it and then generate code out of it
- List name (English) depluralization. E.g. a list called Products will create an entity called Product, a list called Categories will create an entity called Category, etc.
Partial method support for entity properties on generated entities, both for C# and VB:

This feature will get more important once we move in the 0.3 timeframe where entity updates will be supported.
Grouping support; you can now write things like:

var res = from s in ctx.Suppliers group s by s.Country;

-or-

var res = from s in ctx.Suppliers group s by s.Country into g select g;

and iterate over the results as follows:

foreach (var g in res)
{
   Console.WriteLine(g.Key ?? "(null)");
   foreach (var s in g)
      Console.WriteLine("- {0}", s.CompanyName);
   Console.WriteLine();
}

Furthermore, we're working on making the parser more intelligent. That's the case with grouping support as well (and it's the first place where you can see it in action), more specifically when using the IGrouping<K,T>'s Key as follows:

var res = from s in ctx.Suppliers group s by s.Country into g select g.Key;

This will return the keys of the grouping, in this case all of the countries where some supplier is based. If you'd inspect this query using the debugger visualizer, you'd see that only the Country list field will be present in the query's ViewFields element. Because g.Key can only refer to the 'by'-part of the corresponding grouping operation, the parser can infer the meaning of g.Key. In the future you'll probably see more intelligende get built-in in the parser, but some of that development is done in parallel in an experimental code branch.

Nested Lookup field subqueries: in the past you could write things like this:

var res = from p in ctx.Products where p.Category.CategoryName == "Beverages" select p;

However, assume you have another list that contains orders, so you'd like to query for all of the orders of products in some category:

var res = from o in ctx.Orders where o.Product.Category.CategoryName == "Beverages" select o;

This didn't work in previous builds, but it does now. However, one should be vigilant when writing such queries because of the imposed runtime overhead. The query above will cause three roundtrips to the server because of CAML restrictions that are overcome by the query parser using a patching mechanism (see previous post on Lookup fields). Now we can have nested patches that are run in a depth-first manner. The code above is translated into the following query:

So, first the execution engine will go out to the server to get all of the categories that have a CategoryName equal to "Beverages". Once we've obtained the IDs for those categories, we replace the patch (<Patch Field="Category">) with a nested <Or> structure to select all of the products that match these categories. On its turn, this result is substituted in the original query as a replacement for the corresponding patch (<Patch Field="Product">). As you can imagine, these queries can get pretty large before we get to the stage of retrieving the actual query results. Nevertheless, there's no easier escape in SharePoint to do this. Here's the "query plan" for the query above:

There's much more to nested lookup field subqueries than you might initially think. For example, consider the query below:

var res = from o in ctx.Orders where o.Product.Supplier.Country == "USA" && o.Product.Supplier.Region == "WA" && o.Product.ProductName.StartsWith("Ch") select o;

In a naive query parser implementation you'd end up with as much as five patches, so a total of six roundtrips to the server:

<And>
   <And>
      <Patch Field="Product">
         <Patch Field="Supplier">
            <Eq>
               <FieldRef Name="Country" />
               <Value>USA</Value>
            </Eq>
         </Patch>
      </Patch>
      <Patch Field="Product">
         <Patch Field="Supplier">
            <Eq>
               <FieldRef Name="Region" />
               <Value>WA</Value>
            </Eq>
         </Patch>
      </Patch>
   </And>

parser warnings

(Nested) LookupMulti subqueries are now supported too. In the sample above, we assumed that each of the orders in the Orders list just contains one single product reference. Of course, one might have a list that has a "multi lookup" column that refers to multiple items from another list (e.g. more than one product). Assume you have such an Orders list and you want to get all of the orders that have a product from a certain category in it:

var res = from o in ctx.Orders where o.Products.Any(p => p.Category.CategoryName == "Beverages") select o;

In here, the Any call - which is an extension method on System.Linq.Enumerable - is crucial because it's used as the only mechanism available to pass in a subquery predicate, in this case applied to the Products field. Again, this works with patches:

In this sample, the inner patch is corresponding to the subquery's p.Category.CategoryName Lookup field traversal, and the outer patch corresponds to the outer query's o.Products.Any call. Notice, for these kind of lookup-related constructs, an alternative implementation could be using joins but we don't have these in LINQ to SharePoint (at least not) at the moment.
Debugger visualizer improvements were made behind the scenes but also auto-linkage to Online F1 help information has been added: so, if you have a query problem right now, you'll be able to click on the SP**** error message and get help in the Document Explorer. However, notice that at this moment the help and documentation project is not integrated in the build yet, so you'll stumble upon invalid help documentation links in this build.
Code quality improvements thanks to extended coverage by code analysis and use of additional FxCop rules. However, unit test coverage is to be extended further in subsequent releases, which is a top priority in moving forward to the 0.3 timeframe. Furthermore, the code has been refactored into additional assemblies, e.g. for the debugger visualizer, to reduce the runtime workingset. In a subsequent release, the provider model will be enhanced so that people who want to use LINQ to SharePoint over web services alone don't require Microsoft.SharePoint.dll on their machine (the reason for this right now is that we depend on a few parse methods from the SharePoint Object Model, but we'll port these to be natively supported by LINQ to SharePoint).
The MSI Setup has improved much:

it's faster because we reconfigure Visual Studio 2008 in a different way
it registers the XSD schema of SPML
the .spml extension is registered with an icon and linking to devenv.exe

We hope you'll enjoy this release; keep in mind it's still alpha and send us your suggestions, comments, etc. Get it here.

LINQ to SharePoint ready for Orcas Beta 2

2007-08-02T00:09:36Z

Last week, Microsoft shipped Orcas Beta 2; today, the Visual Studio SDK that goes with it was released as well. Ready for us to move LINQ to SharePoint to Orcas Beta 2. More specifically, the query provider core was changed slightly to adapt the new IQueryProvider/IQueryable interface split and a few adjustments were made concerning Visual Studio 2008 integration. If you can't wait any longer, you can download the latest sources over here. Or just wait another few days for a new release that targets Orcas Beta 2 together with some new features.

What can you expect from the next interim release?

SPML has been extended and can now map lists, fields and choices on (Multi)Choice fields.
The LINQ to SharePoint Entity Wizard is fully layered on top of the entity generator and supports enhanced list and field mapping (including the ability to set aliases on mappings to make the generated classes more user-friendly). It also support list name (English) depluralization (see below):
SpMetal enhancements:

Full support for SPML using two times two modi: online and offline; export and codegen (but offline-export isn't meaningful). So you can do things like this:

Online - export: generate an SPML file for the specified list + optional context
Online - codegen: generates C# or VB code for the specified list + optional context (internally, it first generates the corresponding SPML)
Offline - codegen: takes an SPML file as the input and generates C# or VB code for it; this allows people to export SPML first (online-export), modify it and then generate code out of it

List name (English) depluralization. E.g. a list called Products will create an entity called Product, a list called Categories will create an entity called Category, etc.

Grouping support. That's right, you can now write things like:

var res = from s in ctx.Suppliers group s by s.Country;

-or-

var res = from s in ctx.Suppliers group s by s.Country into g select g;

and iterate over the results as follows:

foreach (var g in res)
{
   Console.WriteLine(g.Key ?? "(null)");
   foreach (var s in g)
      Console.WriteLine("- {0}", s.CompanyName);
   Console.WriteLine();
}

There's more to come, so stay tuned!

The 0.2.2 alpha interim release - an overview

2007-07-20T21:01:17Z

Today, a new alpha of LINQ to SharePoint will be released to web: please welcome build 0.2.2. It's considered to be an interim release in between the 0.2 and 0.3 milestone. Remember from earlier posts that the goal in the 0.3 milestone is to provide update support through the entity model. The road ahead to reach that goal is still fairly long, but huge steps have been taken since the 0.2 release to support this. Although most of the work in the 0.2.2 interim release is focusing on stability and enablers for future release highlights, there are quite a bit features that will certainly interest you:

Refactoring of the core model
- SharePointDataSource<T> is now obsolete, SharePointDataContext and SharePointDataList<T> are the public classes that replace the SharePointDataSource<T>.
  - SharePointDataContext is the heart of the framework, responsible to keep track of lists, entity updates, connecting to the underlying providers, etc.
  - SharePointDataList<T> can be compared to a Table<T> in LINQ to SQL, but it refers to a SharePoint list instead, where T stands for the entity class.
- The entity model has been changed, SharePointListEntity no longer exists.
  - Entities are regular classes but decorated with a ListAttribute and FieldAttributes on the properties.
  - The deprecation of the SharePointListEntity base class means we don't have a Dictionary<string, object> based storage of fields anymore. Field values are just kept in properties and their backing private class members.
  - To support updates, entities will typically implement INotifyPropertyChang[ing|ed].
  - The entity generator, SPML and SpMetal (see further) take care of generating the "ideal" entity type for a given SharePoint list.
- Provider-based model to carry out SharePoint communication such as querying and updating. We'll provide a web services and object model implementation out of the box. Right, no other SharePoint communication mechanisms exist today (AFAIK), but this model allows us to separate concerns inside the core engine.
- Close correspondence to the LINQ to SQL model: if you learn LINQ to SQL, it shouldn't be too difficult to get up to speed with LINQ to SharePoint because of similar principles and constructs (e.g. data contexts, deferred loading, entity refs and sets, ...)
Improved query parser
- The query parser (QueryParser) was split from the execution part (CamlQuery).
- Lots of bugs were fixed, thanks to a more complete code coverage by unit tests.
- Remodeled support for URL fields.
- Parser can now report parse errors instead of throwing exceptions, depending on the run mode.
Tool support enhancements
- The enhanced debugger visualizer runs the parser in error checking mode and indicates errors in a written query.
- SpMetal is now a pure front-end to an entity generator that also hosted in Visual Studio 2008 as a "single file generator".
  - Entities are mapped using SPML, the "SharePoint Mapping Language". Today it's still minimalistic, but the mapping (XML-based) language will get more complete in subsequent releases.
- There's an MSI setup for the library together with the tool support. This is required to wire up all the necessary registry settings for VS IDE integration.

Tool support

Let's take a closer look at the tool support side of the 0.2.2 story. First of all, we have a setup available to install the LINQ to SharePoint binaries. Important: you should install the Windows SharePoint Services SDK prior to installing LINQ to SharePoint. The setup can take a while since it needs to reconfigure Visual Studio 2008 Orcas Beta 1 for tools integration:

Once you've done the setup, a new "item template" should appear in Visual Studio. Create a new application project and go to Add, New Item:

You'll see LINQ to SharePoint in this dialog right now:

This allows you to create a SPML mapping file with support of a wizard. Give it a useful name, such as Northwind.spml (on my demo machine I've copied the Products, Categories and Suppliers tables from SQL Server's Northwind sample database to SharePoint lists) and click Add. Now the wizard appears:

Click Next to continue:

Now specify the URL to the server and the method to log on. Click Test connection to validate the connection info, which will make the Next button available if everything's okay. So, click Next:

This dialog is where the real work happens: check the lists of your choice at the left-hand side. If you select a list at the left, the right-hand side will show all of the fields. In the current release, the right portion doesn't affect entity creation yet. In a subsequent release you'll have more control over the entity creation process than just selecting lists. Click Next:

And click finish to create the SPML file:

This file is turned into a C# of VB file automatically, as you can see in Solution Explorer. Feel free to browse through the generated code file to see the entity declaration:

This is it for what the export of entities is concerned. Now it's time to write some queries. Start by creating an object of type NorthwindSharePointDataContext, which is the "root" of the LINQ to SharePoint library. From this custom data context (that inherits from SharePointDataContext), you can start to write queries as shown below. Notice the IntelliSense that's available during the whole query writing process (click to enlarge):

Finally we can execute the code:

Or you could write a more complex query:

Tip: Convince yourself this is consistent with the results of the Northwind database's "Ten Most Expensive Products" sproc. This would be a great LINQ to SQL exercise as well.

On to the debugger visualizer. Set a breakpoint on the first Console.WriteLine statement and run under the debugger:

Now click the magnifier glass that appears on the res variable. This will bring up the visualizer:

In here you can see your query as well as the corresponding CAML. This can be used to spot bugs in the query, i.e. constructs not supported by CAML. This is shown in the screenshot below:

Hover of the red text to get more information about the problem. If you continue the execution of the program, a runtime exception will be raised when the query has to be executed, in our case at iteration time:

The error message corresponds to the first error you saw in the debugger visualizer (in this case there was only one).

Trivia - what has happened to 0.2.1?

Due to various modifications to the entity model, tools were getting out of sync with the query parser and execution engine back-end. Because of this we decided not to publish the 0.2.1 release. In 0.2.2 we're back in sync and the desired quality level of the various components is getting close to the ultimate goal.

What's coming after 0.2.2?

Another interim release, likely 0.2.3 and planned for mid August, will cover:
- List attachments
- Additions to SPML and SpMetal improvements (generate SPML, consume SPML) + improvements for the entity wizard
- Grouping support (under investigation)
- Support for User fields (under investigation)
The 0.3 alpha release:
- Almost-complete entity model
- Update support
- Support for Views (under investigation)

Depending on the Orcas beta, RC, RTM schedule, we might decide to postpone releases in order to get in sync with the latest Orcas build. Other features on the wish list are enhanced tool support, compiled queries, etc but time will tell how things evolve.

Lookup fields inside out

2007-07-10T23:14:28Z

Introduction

Lookup fields are one of the most powerful list-related features of SharePoint, together with their big brothers, the LookupMulti fields. First of all, let's introduce these field types briefly. In the screenshot below you can see a Northwind Products list in SharePoint with at the right-hand side two Lookup fields: Category en Supplier. These two fields refer to items in another list, in this case Categories and Suppliers:

The definition of such a field is shown below:

Using 'Allow multiple values', you can turn the column into a LookupMulti list, referring to multiple items instead of just one.

How to map? SPML is your friend!

So far so good. But how can we use these fields in LINQ to SharePoint? Of course it all starts by creating the entity types. To do this, we can either use the SpMetal command line tool, or (starting with the upcoming 0.2.1 interim release) the SPML mapping language. It's sufficient to map just the Products list, the entity generator will automatically figure out all of the lists that are referred to in the list's definition recursively, either using Lookup or LookupMulti fields. The entity generator is smart enough to eliminate cycles in such a list cross-reference graph and export all of the entity types you need. In this case, I'll just export the Products list with the following SPML definition:

As with all SPML files you still need to set the Custom Tool manually:

This will change in the future when we have a Project Item for SPML files, as well as a designer down the road. Now, when we run the entity generator as follows:

it will generate the entity types for the Products lists as well as the Suppliers and Categories lists that are referred to:

Introducing deferred loading

Time to write some simple query now:

DemoSharePointDataContext ctx = new DemoSharePointDataContext(new Uri("http://wss3demo"));
ctx.Log = Console.Out;
var res = (from p in ctx.Products where p.ProductName == "Chai" select p).AsEnumerable().Single();
Console.WriteLine("{0} costs {1} per {2}", res.ProductName, res.UnitPrice, res.QuantityPerUnit);

On the fourth line you can see a very simple query that just selects on the product's name. The Single query operator isn't implemented in LINQ to SharePoint currently, so we insert an AsEnumerable() call to perform the subsequent Single call via LINQ to Objects. After all, this is how the execution looks like:

However, what about accessing the Category property of the entity object? To illustrate this, add the following line of code:

Console.WriteLine("Category of {0} is {1}", res.ProductName, res.Category.CategoryName);

In here, we refer to res.Category which is obtained using deferred loading, meaning that we only get the Category data back when the user asks for it:

If you want to get the data right away when executing the query, you could use it in the projection portion of the query:

var res = (from p in ctx.Products where p.ProductName == "Chai" select new { p.ProductName, p.UnitPrice, p.QuantityPerUnit, p.Category }).AsEnumerable().Single();

Observe the place where the query gets executed, it's now part of the Products list query operation. You might wonder why we can't do something SQL'ish to join between lists and get data back that way. The answer is pretty simple: in CAML you're restricted to querying one list at a time, so we can't do any better.

Notice the way the "subquery" is composed, as a CAML query using the ID field. This is because we're able to get identity information of child lists used in Lookup fields (i.e. Category and Supplier) when querying the parent list (in this case Products). Internally, LINQ to SharePoint keeps this data in a so-called LazyLoadingThunk object that's stored in the entity object's fields dictionary. When the data for such a field is retrieved using an entity property (which calls into the GetValue method of the base SharePointListEntity class, see fragment below), the LazyLoadingThunk grabs the ID and sends a request to the data context object to go and retrieve that object using a "GetEntityById" call:

[global::BdsSoft.SharePoint.Linq.FieldAttribute("Category", global::BdsSoft.SharePoint.Linq.FieldType.Lookup, Id="...", LookupField="CategoryName")]
public Categories Category {
    get {
        return ((Categories)(base.GetValue("Category")));
    }
    set {
        base.SetValue("Category", value);
    }
}

LookupMulti fields are treated in a similar way with an entity property that's a collection type instead (in the current release we use IList<T> as the property type for a LookupMulti field in an entity type, where T stands for another SharePointListEntity subclass). E.g. if the Products list would have a LookupMulti Categories property instead, you'd get an entity definition with a property similar to the following:

[global::BdsSoft.SharePoint.Linq.FieldAttribute("Categories", global::BdsSoft.SharePoint.Linq.FieldType.LookupMulti, Id="...", LookupField="CategoryName")]
public IList<Categories> Categories {
    get {
        return ((IList<Categories>)(base.GetValue("Categories")));
    }
    set {
        base.SetValue("Categories", value);
    }
}

Note: If you don't want to write a projection that calls the Category property (or another Lookup or LookupMulti field) manually in order to retrieve the data right away (which also returns non-entity types which won't support updating), we'll provide some other feature modeled after LINQ to SQL "data shapes" in a future release. Such a data shape instructs the data provider to retrieve some fields (in our case typically Lookup or LookupMulti fields) right away when writing a query, even when such a query doesn't include that field directly (e.g. in a projection). I'll blog about this feature once we get there in the release cycle.

Using subqueries

However, there's more than just deferred loading when talking about Lookup(Multi) fields. What about querying the parent list in a way like this:

var res = from p in ctx.Products where p.Category.CategoryName.StartsWith("Con") select p;
foreach (var p in res)
Console.WriteLine("{0} in category {1} costs {2} per unit", p.ProductName, p.Category.CategoryName, p.UnitPrice);

Since we can't query for multiple lists using just one CAML query, e.g. by doing joins, we have to play quite some tricks to get this to work. When we set a breakpoint on the foreach loop, we can take a look at the res object at runtime:

This is the result as shown in the debugger visualizer:

Observe the portion I've indicated in the red rectangle. It isn't valid CAML, it's a so-called patch. Such a patch gets generated by the front-end portion of the parser whenever it observes a "traversal" (such as p.Category.CategoryName.StartsWith("Con")) of a Lookup field in the query predicate (where clause). When the query has to be sent to the server in order to retrieve results, the contents of each patch is wrapped in a separate query that's executed against the entity type specified by the Patch element's Field attribute. Based on the results of this subquery, the Patch element is replaced by a tree of <Or> tags in order to select only those list items from the parent list that reference a category that's matching with the patch's query. This looks pretty much like a foreign key lookup in relational databases.

Note: In releases prior to the 0.2.1 interim release, the situation was much more complex (cf. FieldAttribute::IsUnique and EnforeLookupFieldUniqueness) because of the lacking documentation of how to do a "lookup field query by value". There's however an undocumented LookupId attribute on the CAML FieldRef element that allows to do exactly this.

For our sample query, this is the resulting execution plan:

First, the Category patch is executed. This subquery retrieves the ID field values for all of the categories that start with Con.

<Query>
<Where>
    <BeginsWith>
      <FieldRef Name="CategoryName" />
      <Value Type="Text">Con</Value>
    </BeginsWith>
</Where>
</Query>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
Now, the Category patch is cut from the original pre-parsed query and replaced by a tree of <Or>-conditions chaining all matching category IDs together, like this:

As an example, let's make it a little more difficult with two patches involved. Observe the following query:

var res = from p in ctx.Products
          where p.Category.CategoryName.StartsWith("Con")
                && (p.Supplier.Country == "USA" && p.Supplier.Region == "LA"
                    || p.Supplier.Country == "Canada" && p.Supplier.Region == "Québec")
          select p;

Time to see what the parser builds out of this LINQ query, so take a look at the visualizer screenshot below:

Observe we have two patches this time. The parser is smart enough to coalesce adjacent lookup field traversals, in this case the whole query predicate portion for the supplier condition (USA/LA or Canada/Québec) is brought together in just one patch. This means that LINQ to SharePoint will launch two subqueries before taking on the parent query:

Query corresponding with p.Category.CategoryName.StartsWith("Con"):

<Query>
<Where>
    <BeginsWith>
      <FieldRef Name="CategoryName" />
      <Value Type="Text">Con</Value>
    </BeginsWith>
</Where>
</Query>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>

Query corresponding with (p.Supplier.Country == "USA" && p.Supplier.Region == "LA" || p.Supplier.Country == "Canada" && p.Supplier.Region == "Québec"):

<Query>
<Where>
    <Or>
      <And>
        <Eq>
          <Value Type="Text">USA</Value>
          <FieldRef Name="Country" />
        </Eq>
        <Eq>
          <Value Type="Text">LA</Value>
          <FieldRef Name="Region" />
        </Eq>
      </And>
      <And>
        <Eq>
          <Value Type="Text">Canada</Value>
          <FieldRef Name="Country" />
        </Eq>
        <Eq>
          <Value Type="Text">Québec</Value>
          <FieldRef Name="Region" />
        </Eq>
      </And>
    </Or>
</Where>
</Query>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>

Next, it will patch the parent query with all of the patch information gathered, resulting in the following:

Finally this piece of CAML is ready for execution by the server to get the final results back.

As a final feature under the umbrella of Lookup field support, you can also use entity objects in the query predicate directly, like this:

var cat = (from c in ctx.Categories where p.CategoryName == "Beverages" select c).First(); //we don't support the Single query operator, so use First to get the 'singleton result'
var res = from p in ctx.Products where p.Category == cat select p;

This will grab the primary key field value from the cat variable and use it in similar way as the samples above.

Quiz

If you think you've understood everything in this post, here's a little quiz. How many distinct queries will be sent to the server in order to execute the following LINQ query?

var res = from p in ctx.Products where p.UnitPrice <= 20 && (p.Category.CategoryName == "Beverages" || p.Category.CategoryName == "Seafood") && p.Supplier.Country == "USA" orderby p.UnitPrice descending select p;
foreach (var p in res)
Console.WriteLine("{0} in category {1} costs {2} and is supplied by {3}.", p.ProductName, p.Category.CategoryName, p.UnitPrice, p.Supplier.CompanyName);

The output is shown below (tip: why is this relevant?).

Boston Crab Meat in category Seafood costs 18,4 and is supplied by New England Seafood Cannery.
Steeleye Stout in category Beverages costs 18 and is supplied by Bigfoot Breweries.
Sasquatch Ale in category Beverages costs 14 and is supplied by Bigfoot Breweries.
Laughing Lumberjack Lager in category Beverages costs 14 and is supplied by Bigfoot Breweries.
Jack's New England Clam Chowder in category Seafood costs 9,65 and is supplied by New England Seafood Cannery.

Conclusion

As you can see, this feature in LINQ to SharePoint frees developers from the burden of having to execute more than one CAML query in order to get the desired results back. In the upcoming 0.2.1 interim release, this feature is fully functional, ready for you to play with it. Notice however that we don't support lookup field traversals deeper than one. For example, if an item from a Product list has a supplier from a Suppliers list with an address stored in an Addresses list, it won't be possible to write a predicate like p.Supplier.Address.City == "Seattle" (Address is one bridge too far). This might be subject of a future release if time permits.

Improving the parser and more debugger visualizer fun

2007-07-05T08:00:00Z

Welcome back to what's going to end up as "LINQ to SharePoint: The Cruel Sequel" :-). The last couple of days, LINQ to SharePoint has been a full-time job and the result is getting better and better build after build. In this post, I'd like to highlight another feature that was planned from the start but didn't make its way to the 0.2 release of last month: parser enhancements.

So, what's up? Simply stated, the query parser so far was a runtime parser only. When executing LINQ to SharePoint queries, the LINQ query expression tree gets parsed sooner or later, possibly throwing exceptions in case something can't be translated into CAML. A typical example is the following:

var res = from u in users where u.FirstName.EndsWith("t") select u;

The reason this can't be translated is the EndsWith call on the FirstName entity property. Since CAML doesn't support an equivalent in its query language, we can't provide a translation. There are much more such things that make the parse operation fail, due to the relatively limited expressiveness of CAML. The problem however, especially with big queries, is for developers to get to know where the problem is located exactly. In the previous alphas an InvalidOperationException is thrown with some message, possibly referring to something in the query that couldn't be translated (e.g. "Unsupported string filtering query expression detected: EndsWith. Only the methods Contains and StartsWith are supported."). Although this sample message is pretty easy to understand, there are more complex ones that deserve a better approach.

<Intermezzo>

To put this in a broader context, you should be aware of the fact that LINQ lacks support for compile-time query validation by custom query providers. All the LINQ-capable compilers (C# 3.0, VB 9.0) do, is generating an expression tree representing the query. Therefore, the only way to find out about problems in the query is to execute the code, which triggers the IQueryable-supported (custom query provider's) query expression tree parser that can signal issues in the query by throwing some exception. All LINQ providers expose such a behavior. As an example, take a look at the following situation in LINQ to SQL:

Luckily the message is pretty clear in order to figure out what's going wrong. Also observe the time when and the place where the exception occurs: not at definition time of the query (the query - i.e. var res = ... in our case - remains an expression) but when the iteration statement is executed.

Note: LINQ to SharePoint alpha 0.1 did produce parse errors at query expression definition time instead; this has been fixed in 0.2 so that the query parser isn't invoked before query execution time (i.e. iteration over the results).

So what's wrong with this? Not that much, except for the fact that we would be able to signal such problems at compile time if we had the appropriate infrastructure in place at the compiler's side. This would mean that the C# and VB compiler would have to pass the generated expression tree to the custom query provider's query parser (which could be interfaced for communication with a front-end compiler) as part of the compilation job. Our query parser could then feed a set of warnings and errors back to the compiler, which are then presented to the developer as regular compiler warnings or errors (albeit generated by the custom query provider instead of the compiler itself).

Since we don't have such a thing at this very moment, alternatives have to be invented. That's exactly what we've done in LINQ to SharePoint in order to help the developer spot the location of the problem in his/her query.

</Intermezzo>

So, what's our approach? Of course we don't drop the NotSupportedException approach: if your query can't be translated, you're out of luck and we need to signal this in some way or another at runtime. However, when debugging we provide a debugger visualizer for LINQ to SharePoint queries that allows you to inspect the query, including the generated CAML. Essentially, the debugger visualizer triggers the parser albeit in a slightly different "parser run mode": instead of throwing exceptions for parse-time errors, all errors are collected and fed back to the visualizer with enough information to spot the problem. A picture is worth a thousand words, so take a look at this:

This is the debugger visualizer for LINQ to SharePoint that will become available in a later release (keep an eye on my blog). At the top of the dialog you can see the LINQ query. Admitted, it's not in its original shape anymore but it's the best we can do right now (the original LINQ query in either C# 3.0 or VB 9.0 has been eaten by the respective compiler at this stage of execution). The original query looks as follows:

var res = from t in lst where !(t.FirstName.Contains("Bart") && t.Age >= 24) || t.LastName.EndsWith("De Smet") && CamlMethods.DateRangesOverlap(t.Modified.Value) orderby 1 select t;

The LINQ query you can see in the dialog above is basically the query's expression tree ToString() call result. With a little knowledge about extension methods and expression trees, you can read such an expression string representation in just a few seconds (as a little exercise play the human compiler, translating a LINQ query to an expression tree followed by a mental ToString-call).

What the LINQ to SharePoint parser does when running in "debug mode" - in addition to its regular parsing job - is the identification of subexpressions that can't be translated while continuing the parsing (instead of throwing an exception). All places where something went wrong are marked by <ParseError /> placeholders in the CAML query and each of these have a unique identifier that's linked (bidirectionally) with the subexpression in the LINQ query that caused the problem. This way, developers can identify problems in a more visually attractive way.

Even more, in the example above we can see four problems with the query at once. If we'd run the application we'd get only one single exception (which would result in at least four "run-crash-fix" iterations). The goal is to take this to the maximum level possible, providing links from the debugger visualizer to specific help information about the parser issues that occurred (observe the unique identification code on the error, in this case SP0011). In case you're curious why you're seeing SP0011 in the fragment above: observe that the t.FirstName.Contains("Bart") expression is nested inside a Not expression. CAML doesn't have a Boolean negation operator in its query schema, so we can't express the !t.FirstName.Contains("Bart") expression as a whole.

Stay tuned for more LINQ to SharePoint fun soon!

Technorati tags: LINQ, SharePoint, CAML, LINQ to SharePoint, Orcas

Introducing SPML

2007-07-04T08:00:52Z

In today's post I'd like to introduce a new concept that will be introduced in LINQ to SharePoint v0.3: SPML or the SharePoint Mapping Language. It didn't make it for the 0.2 release due to time restrictions, but tonight the first portion of the POC code has been checked in to CodePlex. So what's it?

Let's start with the very beginning: SpMetal. As you might already know, the SpMetal tool is used to generate entity classes from a SharePoint list definition. It connects up to the SharePoint site, grabs the list definition and converts it into some entity class type that can be used to write queries using SharePointDataSource<T>. Therefore, the core of its work is to generate code either in C# 3.0 or VB 9.0. To set our minds, here's a little sample:

The syntax of SpMetal

A list with various fields

SpMetal in action, exporting the Demo list

Code generated by SpMetal

In the very first alpha of LINQ to SharePoint (before we went live on CodePlex), there was no such tool at all and the creation of entity classes was a manual job (after all, it wasn't so difficult at all yet: there was no base class to derive from and using automatic properties in C# 3.0 the mapping was just a matter of minutes). Lazy as developers (including myself) are, the SpMetal tool was created as a very simple tool based on a quick-n-dirty string concatenation and formatting technique (take a look at the sources to see how it's done in 0.2). However, things were getting more complex and a few weeks ago work was started to port the tool to a CodeDOM-based approach for code generation. I decided not to merge these changes with the 0.2 release since full testing on the tool's correctness hasn't been done yet, so it will become part of 0.3 instead.

However, there's more than just a new back-end to SpMetal. The cool thing about it is its potential for reuse elsewhere, including the VS 2008 IDE. Over time, the goal is to provide entity creation as easy as dragging and dropping lists from an add-in in Server Explorer to a designer surface. We're not there yet, but an important milestone is under development right now: SPML. Designers are just overlays on top of some source definition, for example a partial class with Windows Forms designer generated code or a resx file or ... In a similar way, SPML is the source-side of a SharePoint list mapping for LINQ to SharePoint. Currently it's very minimalistic, but over time it will get more and more expressiveness to drive the mapping process (e.g. you'll be able to decide which fields to include in the entity mapping and you'll be able to control a few aspects associated with entity updating, another 0.3 feature that's under development right now). Let's take a brief look at it:

Observe a few things:

The file extension of an SPML file is .spml (duh!).
SPML files contain the definition of a SharePointDataContext, something that will be introduced in 0.3 (I'll blog about it once we get closer to 0.3). For now, think of it as a set of list entities (see <Lists> section).
The SPML file has a Custom Tool associated, called LINQtoSharePointGenerator. You can specify a code namespace as well.

What the LINQtoSharePointGenerator does is pretty straightforward: it parses the SPML file, finds enough information to connect to the WSS site and lets the SpMetal back-end (now called the EntityGenerator) do the rest of the work, returning a code file (support for VB and C#) that's added to the solution. Furthermore, it adds a reference to BdsSoft.SharePoint.Linq if it's not already present. All of this magic is done automatically when you build the project (or you can trigger it manually). This means that using LINQ to SharePoint doesn't require SpMetal anymore: just write an SPML file and add it to the project with the right Custom Tool setting. Here's an example:

Manual triggering of the LINQtoSharePointGenerator...

...the result: a .Designer.cs file

With VB support included!

Start to write LINQ queries right away

If you want to play with this already, you can grab the sources from CodePlex (change set 7418). However, the VS Orcas integration requires you have the VS Orcas SDK installed as well. Also remember this is very early work in progress but step by step we're getting there.

Enjoy!

Technorati tags: LINQ, SharePoint, CAML, LINQ to SharePoint, Orcas