Nancy Brown

SharePoint's REST: an OData Overview

Nancy Brown — Fri, 27 Jul 2012 09:19:00 GMT

What is REST exactly? Representational State Transfer, aka REST, is an architectural style for web-based data access, an alternative to other techniques like SOAP Web Services and Remote Procedure Calls. Or as Todd Bleeker so aptly put it, it's "Query by URL." It's simpler than SOAP or RPC, and is catching on everywhere. OData is a protocol - a standardized way to implement REST to surface, query, and manipulate data. REST isn't necessarily focused on data, but OData is, so think "Oh DATA!". This post is a thumbnail sketch of REST/OData in general, and SharePoint REST/OData 2010 and 2013. Note that all SharePoint 2013 comments are based on the SharePoint Server 2013 Technical Preview, a preliminary version subject to change.

OData: Query by URL, Answer by RSS

OData is an open web-data protocol developed by Microsoft. OData is all about web-based data access; it's a natural evolution of Web Services and Object-Relational Mappings. It's like having LINQ to SQL classes (or some other Object-Relational Mapping) available by URL.

OData can make almost any kind of structured data collection available to any kind of platform, because all data access is via plain old HTTP, and the data is served up as XML (or JSON) in an Atom-style RSS feed. Imagine - a database (or other structured data) can be available for virtually any ad hoc query from almost any internet-connected device. And not just queries- all CRUD operations are available.

SharePoint REST/OData is client-side data access. For code targeting SharePoint 2010 or SharePoint 2013 data, if any of the Client-Side Object Models (CSOM) are available, those are likely the best choices. But if the code is in a non-Windows context, maybe a LAMP application or an Android/iOS phone App, then SharePoint REST/OData is likely the way to go. On the other hand, REST/OData could be a means of bringing external data into SharePoint; Netflix, ebay, twitpics, StackOverflow, and NuGet all have OData feeds. A general understanding of REST could also be useful for consuming Twitter, LinkedIn, or Facebook's REST APIs.

Exploring OData

OData.org lists a number of OData services available for playing around with REST queries right in the browser. If LINQ is comfortable, then another way to wrap your head around REST-speak (or at least get help translating) is LINQPad. It's a free tool for exploring LINQ, OData, and code in general. Point LINQPad at an OData service (select "WCF Data Services" as the Data Context to build automatically, enter URL, set Database), write and run a LINQ query, then click the SQL button to see the REST URL that was generated. (Or the RequestLog button if it's LINQPad targeting .NET Framework 4.) Here's LINQPad 4 querying an Announcements List in a SharePoint 2010-style ListData.svc service - the URL is in the bottom right pane:

(Sadly, pointing LINQPad at the new SharePoint 2013 OData service _vti_bin/Client.svc results in an error.) Another gotta-have free tool for OData is Fiddler2. This Web Debugging Proxy lets you inspect each HTTP request and response.

Entity Data Model

OData is built on the Entity Data Model - yes, think .NET Entity Framework and WCF Data Services, which can be used to create OData services in a flash. It's like any Object-Relational Mapping (ORM); one just needs to learn the lingo. In a RDBMS like SQL Server, tables contain rows; in OData, Collections contain Entries. In a database, tables can be related; in OData, Collections can be associated. A row has columns. An Entry has properties. Tables may have keys; Collections always have keys. Browsing to the service root of an OData service usually displays an Atom+XML list of all available Collections. Here's the Microsoft Northwind database as OData:

To see the metadata for this service, append $metadata to the service URL and OData responds with EntityTypes and Properties and NavigationProperties.

Wait a minute- Collections have Entries, so why does the metadata contain EntityTypes? As is often the case, there are multiple sets of terminology at work. Entity Data Model (EDM) and OData have parallel terms:

Query an Entity Set (Collection) by appending its name to the service URL - here's the Customers Collection URL: http://services.odata.org/Northwind/Northwind.svc/Customers

This returns a feed full of Entries - just like rows in a data table. There's a bunch of metadata for each Entry, like id, title, author, date-time updated, and there are links to related items, which contain relative URLs like "Customers('ALFKI')/Orders". Note in the screenshot above, in the metadata for the first entry, the id is a URL: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI') - that's how to retrieve just that one Entry. The actual data for this Entry is contained inside the entry/content/properties element at the bottom of the screenshot: CustomerID, CompanyName, ContactName,…

URL Structure

An OData Url has three parts: a Service root, a resource path, and (optionally) query string options. We've seen the service root, which typically returns a list of all available Collections. The resource path is kind of like a relative URL, and identifies a Collection or a single Entry or a property of an Entry. Basically the resource path drills down through the entity model to get to a particular object. OData URLs are usually case-sensitive, so take care with spelling. For example, http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI') returns just the ALFKI Customer, which is a single Entry identified by its key:

To get just one Property of an Entry, tack on the Property name (note the forward slash delimiter): http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/Address

To return just the value of the Property, with no XML wrapper, add /$value to the URL: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/Address/$value

Related Entries can be retrieved by following the associations in the URL: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/Orders(10643)/Order_Details

Or to return just the collection of related Links, without the actual entries, use the $links option between the Entry and its related Collection, like this: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/$links/Orders

Query string options, like $value and $links used above, begin with "$" and are part of the query language.

Query Language

The query language provides

options ($filter, $sort, $orderby, $top, $expand, $skip, $take, $metadata, …)
operators (eq, ne, gt, ge, lt, le, and, or, not, mod, add, sub, …)
functions (startswith, substring, replace, tolower, trim, round, ceiling, day, month, year, typeof, …)

Since these will be part of a URL query string, symbols like "+", "-", and "=" already have assigned meanings, hence the need to represent them by text like "add", "sub", and "eq". Most of these options, operators, and functions have familiar names, and do just what their names suggest.

To query Northwind for the top three UK Customers, as sorted by Postal Code, add options as needed: http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=Country eq 'UK'&$orderby=PostalCode&$top=3

$expand is used to return related Entries inline (inside the Entry), like this: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')?$expand=Orders

In the screenshot above, note there is an entire feed inside the inline element - all the related orders are returned. Instead of XML, this could be returned as JavaScript Object Notation (JSON) by appending $format=JSON to the query string: http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')?$expand=Orders&$format=JSON

When the browser receives the JSON, it will probably offer to save it rather than show it. Opening that saved JSON file in Visual Studio looks like this:

Note: appending $format=JSON does not work with SharePoint REST/OData, but there are other ways to get SharePoint REST/OData to return JSON.

CRUD via HTTP Verbs

OData uses the Atom Publishing Protocol, (AtomPub), as the means of Creating, Reading, Updating, and Deleting (CRUD-ing) content. Typically the payload of the HTTP Request is Atom+XML or JSON formatted Entries, like the Entries returned from a query. The HTTP verb is what determines the CRUD action.

Create = HTTP POST
Read = HTTP GET
Update = HTTP PUT or HTTP MERGE
Delete = HTTP DELETE

Update is an HTTP PUT or MERGE; a PUT replaces an existing entry, by updating all values with the new ones in the request, and setting to default all others. MERGE replaces old values, but leaves anything not specified untouched. Concurrency is maintained by ETags, but we're getting beyond the scope of this post.

SharePoint and REST/OData

In SharePoint 2010, only Lists were available as OData services. The service URL was based on the site and looked like this: http://server/site/_vti_bin/ListData.svc. Probably the most common way for a SharePoint developer to interact with SharePoint 2010 REST/OData was via a service proxy (a DataContext). Working with a service proxy is easy-peasy; just add a Service Reference to a Visual Studio project, and a tool will generate the proxy for you. Instantiate the proxy class, passing the constructor the URL of the site, set credentials, and issue LINQ queries. Under the covers, that LINQ query will be translated into a REST URL and sent to the server, where it gets translated into a LINQ to SharePoint query, which in turn gets translated into CAML. The proxy will translate the returned XML back into .NET objects.

Here's a short C# code example that uses a proxy to retrieve a SharePoint 2010 List as a Generic .NET List:

public List<OperationsProxy.ProjectRoleItem> RoleList

{

get

{

OperationsProxy.OperationsDataContext ctx =

new OperationsProxy.OperationsDataContext(

new Uri("http://intranet/operations/_vti_bin/ListData.svc"));

ctx.Credentials = CredentialCache.DefaultCredentials;

return ctx.ProjectRole.ToList();

}

In SharePoint 2013, REST/OData access is no longer limited to Lists - it covers virtually everything the CSOM does, including Site Collections and Webs. This expanded REST/OData service is Client.svc, which can be accessed in two ways:

http://server/site/_vti_bin/Client.svc
http://server/site/_api

The "_api" friendly name is preferred, since it's easier to read. The old SharePoint 2010 /_vti_bin/ListData.svc is still there in SharePoint 2013, and is handy if only List data is needed.

In a Visual Studio project, adding a Service Reference for an _api service will fail. The way to talk to _api is typically JavaScript, probably using jQuery, which makes AJAX requests and parsing JSON much simpler. However, this means manually constructing the correct URL, rather than using LINQ and a proxy class to do a translation. Since SharePoint Site Collections and Webs are complex objects, not simple tables, OData functions (methods) are much more prevalent.

There are 5 access points for the _api service:

Site Collections - http://server/site/_api/site
Webs - http://server/site/_api/web
User Profiles - http://server/site/_api/userProfiles
Search - http://server/site/_api/search
Publishing - http://server/site/_api/publishing

Happily, many OData URLs can be constructed by starting with the corresponding CSOM method, then replacing "." with "/". For example:

Digging just a little deeper, retrieve just the Title and Author fields from List Item with ID=2 of the Announcements List, as HTML:

http://spb2/sites/team/_api/web/lists/getbytitle('Announcements')/items(2)/FieldValuesAsHtml/?$select=Title,Author

Note that the Author field is formatted with presence information, including a link, which navigates to the My Site for that user:

This is just the proverbial tip of the iceberg - see Get started with the SharePoint 2013 REST service in MSDN to really whet your whistle.

Integrating jqGrid with SharePoint QuickStart

Nancy Brown — Tue, 15 May 2012 06:42:00 GMT

Want an ajax-type grid to display tabular data in SharePoint? Try jqGrid. It's built on jQuery, easily configured, substantial documentation, sponsored by Microsoft, and there's even a "Redmond" jQuery theme which looks like it was made for SharePoint's out of the box style.

jqGrid can do a lot more than just display tabular data; it offers CRUD operations, subgrids, grouping, searching, and more. Check out the demos page for some ideas. This post is just about getting off the ground with jqGrid in SharePoint. For easy data, we'll use the venerable Northwinds sample database from an OData source. Basic steps are:

Download jqGrid here
Download a jQuery theme there
Register jqGrid CSS and JavaScript with SharePoint
Configure jqGrid
Create a Data Adapter for jqGrid

Register jqGrid CSS and JavaScript with SharePoint

We'll build this jqGrid in a SharePoint Visual Web Part. Start with a SharePoint 2010 project containing a Visual Web Part, and add a "Layouts" Mapped Folder. It's a good idea to keep all the project stuff corralled, so the first subfolder under Layouts might well be named for the project. Then add css and js subfolders for the downloaded jQuery components.

Open the downloaded jQuery theme's css folder and copy the subfolder with your theme name to the SharePoint project's css folder. In this example, that's the "redmond" folder, including all its contents. Also from the downloaded jQuery theme, open the js subfolder, and copy the custom js file to the SharePoint project's js folder. In this example, that's the jquery-ui-1.8.20.custom.min.js file. OK, theme's done.

Now we just need four files from the jqGrid download:

Copy to the SharePoint css folder: ui.jqgrid.css from the jqGrid download's css folder
Copy to the SharePoint js folder:

jquery.jgGrid.min.js and jquery-1.7.2.min.js from the jqGrid download's js folder
grid.locale-en.js (or the language pack of your choice), from the download's js/i18n folder

Keeping things SharePoint-y, we'll register these with SharePoint constructs. To the Visual Web Part's Elements.xml file, add CustomAction elements similar to these just above the closing </Elements> tag:

<CustomAction

Sequence="100"

ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery-1.7.2.min.js"

Location="ScriptLink" />

<CustomAction

Sequence="110"

ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery-ui-1.8.20.custom.min.js"

Location="ScriptLink" />

<CustomAction

Sequence="120"

ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/grid.locale-en.js"

Location="ScriptLink" />

<CustomAction

Sequence="130"

ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery.jqgrid.min.js"

Location="ScriptLink" />

Most CustomAction elements add a link somewhere, but when the Location attribute is ScriptLink, it registers a script. The Sequence attribute is critical to getting these on the page in the right order. Be sure to edit the ScriptSrc value to match the path in your project.

To the Visual Web Part's ascx HTML source, add CssRegistration tags similar to this just below the default directives:

<SharePoint:CssRegistration runat="server"

Name="/_layouts/Mindsharp.OData.JQuery/css/redmond/jquery-ui-1.8.20.custom.css"

ID="CssRegistration1" >

</SharePoint:CssRegistration>

<SharePoint:CssRegistration runat="server"

Name="/_layouts/Mindsharp.OData.JQuery/css/ui.jqgrid.css"

ID="CssRegistration2" >

</SharePoint:CssRegistration>

Once again, the path in the Name attribute must match your project.

Configure jqGrid

This will be a very basic and minimal configuration. Check out the documentation for all the options. To the Visual Web Part's ascx source, just below the CssRegistration tags, add this html and script:

</div>

$(document).ready(function () {

$("#Northwinds").jqGrid({

url: L_Menu_BaseUrl + '/_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx',

datatype: 'xml',

mtype: 'GET',

autowidth: true,

height: 230,

colNames: ['Customer ID', 'Company', 'Address', 'City', 'Country'],

colModel: [

{ name: 'id', width: 125 },

{ name: 'company', width: 250 },

{ name: 'address', width: 125, sortable: false },

{ name: 'city', width: 100, sortable: false },

{ name: 'country', width: 100, sortable: false }

pager: '#NorthwindsPager',

rowNum: 10,

rowList: [10, 20],

sortname: 'id',

sortorder: 'asc',

viewrecords: true,

gridview: true, // insert all the data at once (speedy)

caption: 'Northwinds Customers via OData'

});

// Add Navigator functions

$("#Northwinds").jqGrid('navGrid', '#NorthwindsPager',

{ edit: false, add: false, del: false, search: false });

});

</script>

The table with id="Northwinds" will be replaced by the grid (as specified by the $("#Northwinds").jqGrid script bit at the top of the script), and the div with id="NorthwindsPager" will be replaced by the navigation and paging bar below the grid (that's the pager: '#NorthwindsPager' bit near the middle of the script).

Like many jQuery-based controls, a url attribute specifies the datasource, which is the NorthwindsXML.aspx which we'll build soon. SharePoint relative urls can confuse jQuery controls, so help them out by using the SharePoint-generated javascript variable L_Menu_BaseUrl like this:

url: L_Menu_BaseUrl + '/_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx'

Most of the configuration is self-explanatory. datatype and mtype tell jqGrid to use a GET request and expect an XML response. (JSON is another data type option.) colNames is an array of column headers for the grid. colModel defines those columns. Here, name is a unique name for the column, and will be used in the querystring, width sets a starting width (these will be adjusted to fit the parent element's width, due to the autowidth attribute also specified), and sortable determines whether that column can be sorted. Since jqGrid sends an Ajax query every time a sortable column header is clicked, or the page number is changed, or the pager dropdown is changed, or a pager button is clicked, only enable sorting on columns you'd like to code for. sortable is true by default, so turn it off where not wanted.

rowNum sets the initial number of rows to show in the grid, so it should be one of the values in rowList, which is the set of values for the number-of-rows-to-fetch dropdown in the pager bar. viewRecords: true instructs jqGrid to write the "View X to Y of Z" text on the right side of the pager bar.

sortname is the initial sort field and sortorder the initial sort direction ("asc" or "desc"). The Navigator bar is left of the pager, in the same bar. Since this is a read-only data source, all editing functions and search will be turned off by the navGrid part of the script, and only the refresh button will appear in the Navigator bar.

Create a Data Adapter for jqGrid

The grid needs a few global values returned in addition to the actual data. Specifically, it wants the number of the page returned, the total number of pages available, and the total number of records available. In XML data, jqGrid expects a "rows" root element, then page, total, and records elements, and a row element for each row of data returned. The row element should contain an id attribute (the primary index value), and cell elements for each column. CDATA sections are fine for data that needs it. Looks like this:

Since most web services don't emit this structure, we'll need to build a data adapter. Often this would be a generic HTTP handler (a .ashx file extension), but a simple aspx page will work just fine, and be a little less trouble to install in SharePoint. Add a text file to the project-named subfolder under the Layouts folder, but give it an .aspx extension; in this example, that's NorthwindsXML.aspx. Add another text file with the same name + .cs (like NorthwindsXML.aspx.cs), and Visual Studio will combine it with the .aspx added earlier. Paste something like this into the .aspx file (adapted to your project name and aspx name, of course):

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="NorthwindsXML.aspx.cs" Inherits="Mindsharp.OData.JQuery.NorthwindsXML" %>

Since we're only sending back XML, we don't need a lot of directives. To the code behind file, (NorthwindsXML.aspx.cs in this example), add something like:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Web;

using System.Xml.Linq;

using System.Collections.Specialized;

namespace Mindsharp.OData.JQuery

{

public partial class NorthwindsXML :

System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.ContentType = "text/xml";

Response.Write(CreateXML());

}

The Northwinds database is published as an OData feed courtesy of OData.org. If you've ever used ListData.svc in SharePoint, (example: http://intranet/_vti_bin/ListData.svc), you've used an OData feed. If OData is new to you, look into http://odata.org and Query SharePoint Foundation with ADO.NET Data Services. Basically it is entity classes served up in an Atom Pub style RSS feed, queryable by URL - it's REST for SharePoint List data. But back to the Northwinds OData - add a service reference to http://services.odata.org/Northwind/Northwind.svc, and give the proxy class a namespace- in this example it's NorthwindProxy.

Now we can create some XML from our OData feed. This particular feed is read-only, and has a server-side page limit of 20 items. For simplicity, we'll stay within that limit with our jqGrid configuration. Here's the CreateXML method:

private string CreateXML()

{

NorthwindProxy.NorthwindEntities ctx =

new NorthwindProxy.NorthwindEntities(

new Uri("http://services.odata.org/Northwind/Northwind.svc"));

int totalRecords = (from c in ctx.Customers

select c).Count();

// Initialize variables to contain validated query string parameters

string sortField = default(string);

string sortDirection = default(string);

int maxRowsToReturn = default(int);

int pageToReturn = default(int);

// Computed variable to send back to jqGrid

int totalPages = default(int);

CreateValuesFromQuerystring(Request.QueryString, totalRecords,

out sortField, out sortDirection,

out maxRowsToReturn, out pageToReturn, out totalPages);

var query = CreateQuery(ctx, sortField, sortDirection,

maxRowsToReturn, pageToReturn, totalRecords);

var customerQuery = from c in query

select new

{

c.CustomerID,

c.CompanyName,

c.Address,

c.City,

c.Country

};

XElement xrows = new XElement("rows",

new XElement("page", pageToReturn),

new XElement("total", totalPages),

new XElement("records", totalRecords));

foreach (var c in customerQuery)

{

xrows.Add(

new XElement("row", new XAttribute("id", c.CustomerID),

new XElement("cell", c.CustomerID),

new XElement("cell", c.CompanyName),

new XElement("cell", c.Address),

new XElement("cell", c.City),

new XElement("cell", c.Country))

);

}

// XDocument does not provide the XML declaration, so here it is

string xmlDeclaration = "<?xml version =\"1.0\" encoding=\"utf-8\"?>"

+ Environment.NewLine;

return xmlDeclaration + xrows.ToString();

}

The CreateValuesFromQuerystring() method reads the querystring and guarantees that all the variables harvested from it will have a valid value. A typical query for this project may look like this:

GET /_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx?_search=false&nd=1337027722102&rows=10&page=2&sidx=id&sord=asc

Note that jqGrid throws in a Unix-style timestamp (nd=1337027722102), which helps defeat inadvertent browser caching.

Linq allows chaining of queries- meaning a single query can be built up in multiple statements, and a query can be the subject of another query. Take a look at these methods which build the Linq query dynamically by chaining:

private IQueryable<NorthwindProxy.Customer> CreateQuery(

NorthwindProxy.NorthwindEntities ctx,

string sortField, string sortDirection,

int maxRowsToReturn, int pageToReturn, int totalRecordsAvailable)

{

var query = from customer in ctx.Customers

select customer;

query = AddOrderBy(query, sortField, sortDirection);

query = query.Skip(maxRowsToReturn * (pageToReturn - 1))

.Take(maxRowsToReturn);

return query;

}

private IQueryable<NorthwindProxy.Customer> AddOrderBy(

IQueryable<NorthwindProxy.Customer> query, string field, string direction)

{

if (direction == "asc")

{

switch (field)

{

default:

case "id":

query = query.OrderBy(c => c.CustomerID);

break;

case "company":

query = query.OrderBy(c => c.CompanyName);

break;

}

else

{

switch (field)

{

default:

case "id":

query = query.OrderByDescending(c => c.CustomerID);

break;

case "company":

query = query.OrderByDescending(c => c.CompanyName);

break;

}

return query;

}

There are cooler ways to dynamically add the OrderBy clause, but we're keeping things simple. For the curious, see Scott Guthrie's post on Dynamic Linq queries.

That should take care of the data adapter for the grid, so it's ready to deploy. Add the Web Part to a page and enjoy!

LINQ query on a DataTable to filter, group, sort, and select distinct

Nancy Brown — Fri, 27 Apr 2012 06:36:00 GMT

I love LINQ, and I wish I had more time to become really proficient in it. <sigh/> But there's so much to do and so little time, it's on a need to know basis. When I need to know it, I experiment and learn more, firing up LINQPad and pulling out my trusty copy of LINQ Pocket Reference by Joseph & Ben Albabari. A recent LINQ adventure was this:

from a DataTable containing names and times,
pull out the most recent DataRow for each name, provided it is inside a certain time window, and
present just those DataRows sorted by name

That's not hard for a human to think about, but translating it to LINQ took a while. In the process I rediscovered the lovely let function in LINQ, which aided readability, and made the returned set a simple IEnumerable<DataRow>. That's really helpful, because the typical result of a grouping operation is an IEnumerable of IEnumerables.

Here's the test DataTable as loaded up in LINQPad:

DataTable dt = new DataTable("SharePointUsers");

DataColumn LoginColumn = new DataColumn("Login", typeof(string));

dt.Columns.Add(LoginColumn);

DataColumn PageUrlColumn = new DataColumn("PageUrl", typeof(string));

dt.Columns.Add(PageUrlColumn);

DataColumn AccessTimeColumn = new DataColumn("AccessTime", typeof(DateTime));

dt.Columns.Add(AccessTimeColumn);

dt.BeginLoadData();

dt.LoadDataRow(new object[] { "Office14\\User3", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-5) }, true);

dt.LoadDataRow(new object[] { "Office14\\User3", "http://intranet/Default.aspx", DateTime.Now.AddSeconds(-119) }, true);

dt.LoadDataRow(new object[] { "Office14\\Admin", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-2) }, true);

dt.LoadDataRow(new object[] { "Office14\\Admin", "http://intranet/Default.aspx", DateTime.Now }, true);

dt.LoadDataRow(new object[] { "Office14\\User2", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-1) }, true);

dt.LoadDataRow(new object[] { "Office14\\User1", "http://intranet/Default.aspx", DateTime.Now.AddSeconds(-121) }, true);

dt.LoadDataRow(new object[] { "Office14\\User1", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-3) }, true);

dt.EndLoadData();

The time window will be 2 minutes (120 seconds). So if the AccessTime column value is within 2 minutes of right now, that row should be included for further evaluation. Those rows that pass the time test need to be grouped by names - which is the Login column. The LINQ group keyword essentially creates a list of lists. So the LINQ expression

from row in dt.AsEnumerable()

group row by row.Field<string>("Login") into userGroup

creates little lists for each name found, like this:

Ok, so far so good, but that query basically results in a list of lists. Each one of those little name-lists needs to be sorted by DateTime, and just the most recent one pulled out. This is where the let keyword shines. The let keyword in LINQ creates a new variable right there in the middle of the LINQ query, or as Microsoft puts it, the let keyword "introduces a range variable to store sub-expression results in a query expression". And that new variable can be queryable! If the group keyword is followed by a let clause, then those name-lists can be sorted on DateTime, just the top one taken (the most recent one), and all that stored in a new variable, like this, where userGroup refers to those name-lists:

group row by row.Field<string>("Login") into userGroup

let lastAccessRows =

userGroup.OrderByDescending(r => r.Field<DateTime>("AccessTime"))

.Take(1)

Now we're cooking. Adding some initial filtering and final sorting and selecting, the complete query and supporting variables look like this:

Double timeWindow = 120;

DateTime startTime = DateTime.Now.AddSeconds(-timeWindow);

var query = from row in dt.AsEnumerable()

                     where // AccessTime is later than startTime

                           DateTime.Compare(row.Field<DateTime>("AccessTime"), startTime) > 0

                     group row by row.Field<string>("Login") into userGroup

                     let lastAccessRows =

                           userGroup.OrderByDescending(r => r.Field<DateTime>("AccessTime"))

                           .Take(1)

                     from dr in lastAccessRows

                     orderby dr.Field<string>("Login")

                     select dr;

Here it is in LINQPad:

If I were ever stranded on a desert island, with an inexplicable wireless network and a laptop, and I could only have three free tools, I'd grab LINQPad, Reflector (free version), and Fiddler - couldn’t live without them.

Hope this helps!

Restoring an SSRS 2012 Shared Data Source in SharePoint: the Missing Link

Nancy Brown — Wed, 11 Apr 2012 06:55:00 GMT

Scenario: SQL Server 2012 Reporting Services (SSRS) is installed in SharePoint integrated mode. A Visual Studio 2010 Report Server Project deploys Reports and a Shared Data Source to SharePoint 2010 Document Libraries "Dashboard" and "Data Connections". Everything works as expected. Then the SharePoint Library "Data Connections", (which contains the deployed Shared Data Source "TimeBillingDataSource"), is deleted. A new SharePoint Library is created to replace it, on the same Web, with the same name, same Content Type, same everything. The untouched Visual Studio project is redeployed, creating again the same Shared Data Source in a SharePoint Library with the same name, on the same Web. But the Reports in the "Dashboard" Library employing that Shared Data Source are broken.

Here's the new Library and new Shared Data Source:

Clicking either report name in the Dashboard Library provokes this error message:

The report server cannot process the report or shared dataset. The shared data source 'TimeBillingDataSource' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source.

That's because the link is missing.

But it can be repaired. Click the down-arrow next to a Report name to reveal the context menu, and choose "Manage Data Sources".

On the Manage Data Sources page, click the name of the Data Source…

…which leads to a page to manage the actual Shared Data Source, where the Link is missing. Click the ellipsis button (the button with three dots on it)…

...and use the Select an Item dialog to find that newly created Shared Data Source in the Data Connections library.

Click OK enough times to get that cemented into place, and then go test that Report.

Much better! This process must be repeated for each report that uses the restored Shared Data Source. Another way to fix this for the reports in the Visual Studio project: define a new Shared Data Source with a different name, but the same characteristics. Then for each report that used the deleted Data Source, open the report, and replace the report's Data Source with the new one, then deploy everything.

Why did the link break when the names are the same? Typically SharePoint identifies everything with a GUID, and this is no exception. The ID changed when the Shared Data Source was deployed the second time, and that's when the links went missing.

Here's the ID before the Data Connections library was deleted, as seen by poking around in the Reporting Service database in SQL Server Management Studio:

Then the SharePoint Library containing the Shared Data Source was deleted and recreated, and the Visual Studio project was re-deployed…

------ Deploy started: Project: Mindsharp.Reports.Dashboard, Configuration: Debug ------

Deploying to http://intranet/

Deploying data source 'http://intranet/Management/Data Connections/TimeBillingDataSource.rsds'.

Deploying report 'http://intranet/Management/Dashboard/Hours Summary.rdl'.

Deploying report 'http://intranet/Management/Dashboard/Hours Detail.rdl'.

Deploy complete -- 0 errors, 1 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

…which leads to a new ID and missing links in the Report Server database:

Note that the first deployment task is to deploy to the Report Server itself, then to SharePoint.

Hope that helps!

Module Element (Module) 101

Nancy Brown — Wed, 29 Feb 2012 14:09:00 GMT

What is a Module? According to the Bing dictionary, the first definition is:

self-contained interchangeable unit: an independent unit that can be combined with others and easily rearranged, replaced, or interchanged to form different structures or systems

In SharePoint, <Module> is the part of CAML (Collaborative Application Markup Language) that provisions files into the Content Database. It has one permissible child element: <File>. So maybe it could have been more mnemonically named <FileProvisioner> or <AddTheseFilesToTheContentDatabase>. However, it does rearrange an independent unit (a file) to form different structures.

<Module> comes in two flavors: Module Element (Module) and Module Element (Site). They have the same set of attributes, and the same single child element: <File>. The difference is where each is found, and the starting location for the Module's Path attribute. Module Element (Module) is found in a Feature's Elements.xml, and accordingly the Path attribute is relative to {SharePointRoot}\TEMPLATE\FEATURES\FeatureName. Module Element (Site) is found in a Site Definition's Onet.xml, and the Path attribute is relative to {SharePointRoot}\TEMPLATE\SiteTemplates\Site_Definition.

Modules can rename, repurpose, and provision one physical file to many locations

This post will focus on Module Element (Module) - the one found in Feature xml - but the concepts are the same for both flavors. For simplicity, Farm Solution Features will be the target of the initial discussion - it is easier to see where the physical files are when they're on the file system. The essence of a Module Element (Module) is taking a file found in its Feature's folder structure, and provisioning that file into the Content Database, so that it becomes a file in an SPWeb's folder or in an SPWeb's list/library.

Why is a file "provisioned" rather than "copied"? When the Module provisioning process deposits a file into the database, it deposits an SPFile object. When that SPFile is first created (provisioned), it is designated "uncustomized", meaning it works like a pointer to the actual physical file on the Web Front End (WFE) server's file system - the file in the Feature's folder. In Module provisioning, a single physical file can be virtually renamed, provisioned to multiple locations, and properties can be set, or objects added, depending on what kind of file it is. If the file is an aspx page, navigational elements or web parts can be added. If it is a webpart file, properties can be set. Consider this somewhat contrived illustration:

In the illustration above, a Module takes a single web part page (BasicLeftRightZones.aspx) and renames, repurposes, and provisions it to five different locations in a web. This is really awesome for scaling purposes. There's just one physical copy of BasicLeftRightZones.aspx, and if that physical copy is changed, the changes are propagated everywhere. However, often a single file just gets provisioned to a single location, but since this is part of Feature deployment, provisioning might happen many times, on multiple Sites or Webs. What if one of those Sites/Webs needs to alter such a file?

Customized or Uncustomized, that is the question

Imagine that a Feature named "AlphaApp" contains a Module which provisions a file named "Alpha.aspx", and the AlphaApp Feature has been activated on 3,274 sites. In the database, that's 3,274 "uncustomized" SPFile objects which are pointers to the one physical "Alpha.aspx" in the {SharePointRoot}\TEMPLATE\Features\AlphaApp folder. Then on one of those 3,274 sites, the "Management" site, someone uses SharePoint Designer to alter the source of Management's Alpha.aspx. The Management site's Alpha.aspx SPFile is now "customized", and no longer functions like a pointer to the file system; instead, the customized Alpha.aspx source is now actually in the database. The SPFile class has a property, CustomizedPageStatus, which indicates whether the file's status is currently "uncustomized" (and cached), "customized" (and not cached), or "none" (never cached). "Cached" means cached in memory on the WFE server. What's with that caching? It goes hand-in-hand with the customized page status. The SPFile class also has method RevertContentStream() which returns the file to its uncustomized state.

Files can be uncustomized in SharePoint Designer, in code, and through the browser by choosing Site Actions > Site Settings > Reset to Site Definition. This last option lands on the "Reset Page to Site Definition Version" page, where one can select one file to revert, or the entire site. Charmingly, the actual webpage name there is "reghost.aspx", which is a reference to the old SharePoint way of labeling SPFile files as ghosted or unghosted. In SharePoint 2010 terms, ghosted = uncustomized, unghosted = customized.

FROM and TO

If a Module Element (Module) provisions files from a Feature folder to the database, it must have something like a FROM and a TO. Or maybe the <File> element has the FROM and TO. Confusingly, both <Module> and <File> elements have FROM and TO bits.

Modules elements have a bunch of attributes, but the ones primarily involved in the FROM/TO business are these: Url, Path, and SetupPath. Similarly, the File element has attributes Url, Path, and Name. Some of these do double duty, sometimes acting as a FROM, sometimes acting as a TO. What attributes are required? For a Module, the Name attribute is required; for a File, the Url attribute is required. Let's explore some examples, all based on a web whose Url is http://intranet. The simplest Module could look like this:

<?xml version="1.0" encoding="utf-8"?>

</Module>

</Elements>

In the above example, the File element's Url attribute is both the FROM and the TO. This CAML code says that Sample.aspx is in the Feature's root folder, and will be provisioned to the Web's root folder. After provisioning, one could browse to http://intranet/Sample.aspx. However, this is an uncommon configuration; in fact, one cannot build this in Visual Studio, since it will not allow files to be added to the Feature's root folder. (There are examples of the Url attribute being both FROM and TO and files living in the Feature's root folder in the built-in Features, such as the BasicWebParts Feature.)

Since the Url attribute is required for the File element, it will always function as either the FROM or the TO (or both, as above). In the File element, if the Name attribute is added, Name becomes the TO, thus making Url the FROM. If instead the Path attribute is included, Path becomes the FROM, thus making Url the TO. (See why this is confusing?) For the rest of the code examples, just the Module and File elements will be shown. Here's an example using just Url and Name:

</Module>

Now one could browse to http://intranet/Alice.aspx. The Sample.aspx file has been renamed Alice.aspx and placed in the web's root folder.

How about this:

</Module>

Now one could browse to http://intranet/Books/Alice.aspx - the Sample.aspx file has been renamed Alice.aspx, and a folder (Books) has been automatically provisioned to contain it. How about this:

</Module>

One could still browse to http://intranet/Books/Alice.aspx, but the original file is now found in the BookPages folder under the Feature folder. This is typically what one would see in Visual Studio, except that it would default the Url attribute to BookPages/Sample.aspx. What if one tried using Path, Name, and Url in the same File element?

<File Path="BookPages\Sample.aspx"

Url="Books/Alice.aspx"

Name="Books/Fiction/Alice.aspx"/>

</Module>

Name wins! This will provision Alice.aspx and two nested folders, so that one can browse to http://intranet/Books/Fiction/Alice.aspx. The Url attribute is ignored - but remember it cannot be removed, it is required, and it cannot be empty. Clearly this is not an optimal situation; a better practice would be choosing Url+Name attributes or Url+Path attributes, but not all three.

A couple of other attributes for the File element deserve special attention. Contemplate the attribute IgnoreIfAlreadyExists. Sounds like that means one can overwrite an already provisioned file, but nope, that's wrong. IgnoreIfAlreadyExists is Boolean, and if set to True, the Module will not throw an exception if it encounters an existing file. If IgnoreIfAlreadyExists is not present, or is set to False, and the Module encounters an existing file, it will throw an exception. So, if you'd rather not be notified that the Module failed to provision a file because the file was already there, set IgnoreIfAlreadyExists="True". Maybe it should be renamed DontThrowExceptionIfFileAlreadyExists.

<File Path="BookPages\Sample.aspx"

Url="."

Name="Books/Fiction/Alice.aspx"

IgnoreIfAlreadyExists="TRUE">

</File>

</Module>

Why would the file already be there? Because Modules do not clean up behind themselves; they provision when the Feature is activated, but do not un-provision when it deactivates. Typically the FeatureDeactivating method is used to clean up files left lying around by Modules. Then the coast is clear for a new provisioning. Of course, if the business user has made customizations through the browser, those will be lost - so there may be times when that mysterious IgnoreIfAlreadyExists="True" attribute is useful.

Visual Studio quietly takes care of deployment conflicts (like the file already exists) by default. Watch the Output window when deploying a Module Feature the second time, and you'll see something like "Found 1 deployment conflict(s). Resolving conflicts ...Deleted file 'http://intranet/Books/Fiction/Alice.aspx' from server." This is great for development, but be aware that in production, re-activating a Feature with a Module will throw an exception if there's been no cleanup of those files orphaned by the Module. This Visual Studio behavior can be controlled by the Deployment Resolution Conflict property on a Module's folder. In Solution Explorer, select the Module folder, then peek in the Properties window.

GhostableInLibrary and Ghostable

Let's look at provisioning to a List - a specialized List known as a Document Library in this case:

<File Path="BookPages\Sample.aspx"

Url="Alice.aspx"

Type="GhostableInLibrary"/>

</Module>

The File element has sprouted another attribute, Type="GhostableInLibrary". If this is left off, the file will not provision to the Library. Remember the old SharePoint terms "ghosted" and "unghosted"? Yep, this hearkens back to those older terms, and specifies that the file provisions to a Library, and is cached. The List attribute in the Module element is not required, but it can help alert one to a missing library. Without it, SharePoint will merrily create a folder if no such library exists at the Module's Url. With it, SharePoint will throw an exception if there's no library. (A spurious folder when one expected a Library can be hard to troubleshoot.) The number 101 in List="101" is the List Template Type. For a listing of List Template Types, see SPListTemplateType Enumeration.

The final Url is now a concatenation of the Module's Url attribute (which is the List's Url) and the File's Url attribute (which is the final name of the file), so one can browse to http://intranet/Docs/Alice.aspx, and see the file in the Library Docs. The Module element can also have a Path attribute, which prefixes any Path attribute in the File element.

Take a look at this variation which uses the Path attribute in the Module element to provision a folder in the Library, and stuffs Alice.aspx in it, resulting in a Url of http://intranet/Docs/Books/Alice.aspx:

<File Path="Sample.aspx"

Url="Alice.aspx"

Type="GhostableInLibrary"/>

</Module>

But what about the Type=Ghostable attribute?

<File Path="BookPages\Sample.aspx"

Url="Alice.aspx"

Type="Ghostable"/>

</Module>

Switching to Type="Ghostable" creates a "Docs" folder at http://intranet/Docs, with Alice.aspx inside it, and now again one can browse to http://intranet/Docs/Alice.aspx. Since there's already a Library at http://intranet/Docs, this could be seriously confusing.

SetupPath, Sandboxed Solutions, and the Visual Studio view

There's one more Module attribute to mention: SetupPath. Pick Path OR SetupPath, but not both. SetupPath is the physical path to a folder which is relative to {SharePointRoot}\TEMPLATE, so it's a way to reference files in folders there or below. If you inadvertently include both, SetupPath will win. Want to grab Default.aspx from the Team Site definition and add it to the Docs Library as Nathan.aspx?

<File Path="Default.aspx"

Url="Nathan.aspx"

Type="GhostableInLibrary"/>

</Module>

So far, this has all been based on Farm Solutions, which write to the {SharePointRoot}\TEMPLATE\FEATURES\ folder, because one can see the Feature structure there easily. Recall that the Path attribute in a Module Element (Module) is relative to the {SharePointRoot}\TEMPLATE\Features\FeatureName folder. Modules work the same way in Sandboxed Solutions, but the Feature Folder structure is inside the WSP.

Switching a project from Farm Solution to Sandboxed Solution needs a little care, as Visual Studio is caching a bunch of stuff. First deactivate all Features in the Solution, then retract the Farm Solution, which removes the files from the {SharePointRoot}\TEMPLATE\FEATURES\ folder, the GAC, etc. Flip the Project's Sandboxed Solution property to TRUE, and close Visual Studio, flushing the cache. Reopen Visual Studio and redeploy. Now the WSP is in the Solutions Gallery. On the site, choose Site Actions > Site Settings > Solutions to navigate to the Solutions Gallery, or just open Visual Studio's bin/debug folder in Windows Explorer to see it there. Peering inside the WSP is easy with the Expand command.

In a command prompt window, type:

expand [PathToWSP] -f:* [PathToDestinationFolderForExpandedWSP]

The -f:* switch says expand all the folders and files inside the WSP. The Destination folder must already exist.

The Feature structure inside the Cabinet file, (the WSP), is the same as it would be in the {SharePointRoot}\TEMPLATE\FEATURES folders for a Farm Solution:

This is not so easy to see in the Solution Explorer in Visual Studio:

Bear in mind that when deployed, all those Visual Studio folders containing Elements.xml files will end up inside a Feature folder. There's more to Modules, but I hope this has de-mystified the basics. Happy coding!

SharePoint REST, Concurrency Conflicts, and Persisting Changes

Nancy Brown — Wed, 25 Jan 2012 16:18:00 GMT

What's a concurrency conflict? Picture this: Zach fires up a WPF application that does CRUD operations on a SharePoint List, the app loads a bunch of List Items, then Zach pauses to answer the phone. Two seconds later, Rebekah browses to that same SharePoint List, and updates Item 3. Zach gets off the phone, updates the same item in the same list, and clicks the Save button. (There was a little office miscommunication.) The WPF app detects a concurrency conflict - the item it is trying to update has changed since the app read it from the server.

How are these concurrent updates handled? Typically there are two kinds of concurrency options: pessimistic and optimistic. Pessimistic concurrency involves locking the entity being updated, so that nothing else can change it until after the current update operation finishes. Optimistic concurrency does not lock the entity being updated, so it is possible that the entity on the server changed in the time between reading it from the server's data source and attempting the update, resulting in a concurrency conflict.

SharePoint REST supports optimistic concurrency, and monitors concurrency conflicts by ETags, which are sort of like version numbers. (ETags, or Entity Tags, are part of the HTTP protocol: http://www.w3.org/Protocols/rfc2616/rfc2616-sec3.html#sec3.11) When the ETag in a WPF application (or Windows Forms or Silverlight or …) does not match the ETag in the REST service, that's a concurrency conflict. Sometimes changes should be pushed through, even if such conflicts exist. In SharePoint REST, there are at least a couple of different persist-in-spite-of-concurrency-conflicts scenarios that might occur.

The first scenario occurs when there are detached entities- entities that are not part of a Data Context collection- the client ETag can be set to * with an overload of the Data Context AttachTo method. The REST service will interpret the * ETag from the client as a "we don't care about any concurrency conflicts" message, and do the update. Detached entities typically occur when there is no query filling the collection. Instead, perhaps a list of known IDs is passed to some method, and it creates entities for existing List Items, then attaches them to a Data Context collection for updating purposes. That could look something like this for a SharePoint List called ProjectMembers:

OperationsDataContext ctx = new OperationsDataContext(

new Uri("http://intranet/operations/_vti_bin/ListData.svc"));

ctx.Credentials = CredentialCache.DefaultCredentials;

ProjectMembersItem pmi = ProjectMembersItem.CreateProjectMembersItem(10);

pmi.ProjectID = 4;

// Set various properties...

ctx.MergeOption = MergeOption.OverwriteChanges;

ctx.AttachTo("ProjectMembers", pmi, "*");

ctx.UpdateObject(pmi);

try

{

ctx.SaveChanges();

}

catch (Exception ex)

{

// network conditions, server availability...

}

The Data Context's MergeOption setting determines what happens when the SaveChanges method is called. Merge option OverwriteChanges causes all current values in the client collection, (ProjectMembers in this example), to be overwritten with fresh data from the REST service, effectively syncing what's already in the local collection with the data source. This overload of the AttachTo method adds a detached entity to the Data Context collection, and sets the ETag value. Since the Data Context was not tracking this entity before, (but it is now), UpdateObject is called to explicitly set the freshly attached entity's state to Modified.

Note that the DataContext method AttachTo is used for existing entities, which can be modified or deleted. In the proxy classes created by adding a service reference, there are methods with the naming convention AddToListName, like AddToProjectMembers for the ProjectMembers List. These methods are wrappers for the Data Context's AddObject method, which creates a brand new entity in the Data Context collection, ready to be inserted in the data source.

The second scenario involves a Data Context collection that has been filled by a query. In that case, persisting updates in spite of concurrency conflicts looks a bit different. Consider this demo WPF application, which updates a SharePoint List called Members; just a DataGrid, a button, and a TextBlock.

Here's the initial load code, with the query using a view projection to grab just two columns for updating purposes:

OperationsDataContext ctx = null;

IQueryable<MembersItem> query = null;

DataServiceCollection<MembersItem> members = null;

public MainWindow()

{

InitializeComponent();

ctx = new OperationsDataContext(new Uri("http://intranet/operations/_vti_bin/ListData.svc"));

ctx.Credentials = CredentialCache.DefaultCredentials;

query = from m in ctx.Members

select new MembersItem

{

MemberName = m.MemberName,

LoginName = m.LoginName

};

members = new DataServiceCollection<MembersItem>(query);

dataGrid1.ItemsSource = members;

}

When this first loads, the Data Context begins tracking all the entities loaded by the query, which means they have ETags already, they are already attached to the collection, and their state is Unchanged. Since the Data Context collection provides change tracking, the entity state for each item will be automatically updated to Modified, Deleted, or Added as the business user makes changes in the WPF application. In order for any of these updates to be applied by the SharePoint REST Service, the ETags in this local collection have to match the ETags in the service.

When a concurrency conflict occurs in this situation, it is possible to reload the local collection from the REST service, updating the ETags, but preserving the changes that were made in the client, so the ETags in the client collection match the service ETags, and the update can be tried again. Unless another concurrency conflict occurs, (not likely, but possible), a second call to SaveChanges() after that reload will probably push all changes through, except for Delete operations on already-deleted entities. If that WPF app attempts to delete an entity, but somebody else already deleted it on the server, the delete operation can fail on both first and second attempts, leaving the deleted entity hanging around in the local collection. So, after a second attempt to push through updates, it may be reasonable to just refresh the local collection.

The Data Context's MergeOption controls how queries load the local collection, as well as how SaveChanges updates that local collection. It is set to a value in the enumeration System.Data.Services.Client.MergeOption: AppendOnly, PreserveChanges, OverwriteChanges, NoTracking. The Save Changes and Refresh button code from the WPF app demonstrates this:

private void saveButton_Click(object sender, RoutedEventArgs e)

{

try

{

// If all updates complete successfully, a response object will be returned

DataServiceResponse response = ctx.SaveChanges(SaveChangesOptions.ContinueOnError);

this.textBlock1.Text = "Update successful!";

}

catch (DataServiceRequestException dse)

{

// If any update fails, the response object can be found

// as a property of the DataServiceRequestException

foreach (ChangeOperationResponse cor in dse.Response)

{

// if details are needed, process each ChangeOperationResponse

}

// Try to persist updates, ignoring concurrency conflicts

// Execute query again, getting fresh data from OData service,

// preserving any uncommitted changes in the local collection (members),

// and refreshing ETags

ctx.MergeOption = MergeOption.PreserveChanges;

members = new DataServiceCollection<MembersItem>(query);

this.dataGrid1.ItemsSource = members;

try

{

DataServiceResponse response = ctx.SaveChanges(SaveChangesOptions.ContinueOnError);

this.textBlock1.Text = "All updates persisted";

}

catch (DataServiceRequestException dse2)

{

// It is unlikely, but possible, that more concurrency conflicts occurred.

// However, an attempt to delete a List Item that is already deleted

// in SharePoint will create the DataServiceRequestException - but isn't

// really an issue, since the end result (deletion) is the same.

this.textBlock1.Text = "Some updates may not have been persisted";

}

catch (Exception ex)

{

// Other issues may cause errors - server availability, network

this.textBlock1.Text = "Unable to persist all updates. Error=\"" +

ex.Message + "\"";

}

// Refresh local collection

// Execute query again, getting fresh data from OData service,

// overwrite any lingering uncommitted changes in the DataGrid

ctx.MergeOption = MergeOption.OverwriteChanges;

members = new DataServiceCollection<MembersItem>(query);

this.dataGrid1.ItemsSource = members;

// Set Merge Options back to default

ctx.MergeOption = MergeOption.AppendOnly;

}

catch (Exception ex)

{

// Other issues may cause errors - server availability, network

this.textBlock1.Text = "Unable to persist all updates. Error=\"" +

ex.Message + "\"";

}

Another enumeration, SaveChangesOptions, can be used to tell the REST service whether to stop on the first error - that's the default, SaveChangesOptions.None - or to try all change operations in spite of errors, as in the code above, (SaveChangesOptions.ContinueOnError), or send the changes as a single batch, or use the PUT verb for updates. (MERGE is used by default.) If details of each change operation are needed, the DataServiceResponse object can be inspected. This is returned by the SaveChanges() method if all goes well, or found on the DataServiceRequestException exception if not. If it is needful to sort out which changes failed, any entity in the DataServiceResponse object with a state other than Unchanged denotes a failed operation.

To see what happens in these exchanges, it can be helpful to check things out in the QuickWatch window. Set a break point and drill down into the Data Context object like this:

Another way to see what's happening is Fiddler2, a free and wonderful tool for web debugging (http://www.fiddler2.com/fiddler2/). In this screenshot, a successful update by the WPF app can be seen, with before (W/"12") and after (W/"13") ETags visible. When an update operations succeeds, the HTTP response is 204 No Content.

Happy RESTful coding!

Trouble in the Sandbox: a Site-Scoped ItemDeleting Event Receiver

Nancy Brown — Tue, 20 Dec 2011 05:36:00 GMT

Sandboxed Solutions are a very cool, very new feature in SharePoint 2010, a version 1.0 feature. Translation: watch out for bugs.

When a List Item Event Receiver is added to a SharePoint Project in a Sandboxed Solution, the associated Feature is automatically Web-scoped, and works as expected. But if the Feature scope is changed to Site, weird stuff happens. Let's consider the ItemDeleting event, which has extra weirdness.

The Visual Studio Event Receiver wizard has an Event Source dropdown for List Item Events:

For a List Item Event, that event source is typically a type of List, which is identified by a ListTemplateId attribute in the Receivers element:

With this setup in place, this Receiver will automatically be bound to all the Announcements-type lists in any Web where the Feature has been activated, and it will not apply to any other types of Lists. (Announcements Lists are type 104 - see the List Template Type enumeration for a rundown on these - http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splisttemplatetype.aspx.)

In this example, the code will simply cancel the ItemDeleting event, and direct the business user to expire the Announcement instead. That code might look like this:

public override void ItemDeleting(SPItemEventProperties properties)

{

properties.Status = SPEventReceiverStatus.CancelWithError;

properties.ErrorMessage = "Please expire, rather than delete.";

}

As long as the Feature remains at Web scope, this works as advertised - Announcements cannot be deleted, and business users are presented with the message to expire rather than delete:

Try changing to a Site-scoped Feature, and deployment succeeds the first time. But the very next deployment fails, with Visual Studio muttering (in the Output window) that retraction failed, so Visual Studio just quits right there, and throws up its hands (figuratively speaking). Curiously, this does not happen with the ItemAdding or ItemUpdating events. Weirdly, if one attempts to deploy again, (third time's a charm), the "retraction" succeeds (!?!), and deployment succeeds. Here's what happened: during the failed deployment, the Sandboxed Solution is "retracted" (that is, deactivated in the Solutions Gallery), then Visual Studio chokes on deleting it. The next time, the Solution is already deactivated, so Visual Studio just attempts to delete it, succeeds in deleting it, then succeeds in deploying the new Solution.

Does this Site-scoped version work? Yep, a little too well. Try to delete an Announcement, and voila, the deletion is blocked, and the message appears. Switch to another type of List, a Tasks List. Try to delete a Task item, and that deletion is blocked, and the message appears. Try to delete an item from any type of List, and the deletion is blocked. Oops. The ItemDeleting Event Receiver now applies to every list, not just whatever was specified by the ListTemplateId attribute in the Receivers element in Elements.xml.

Remember that Event Source dropdown list in the Visual Studio Event Receiver wizard? Event Receivers are bound to Event Hosts, which are SharePoint objects: SPContentType, SPFile, SPList, SPListItem, SPWeb, SPSite, or SPWorkflow. (BTW," bound" is just another way to say "registered".) By poking around with PowerShell, one can see where an Event Receiver is bound. It turns out that when the Feature is Web-scoped, the Item Event Receiver is actually bound to individual Lists. When the Feature is Site-scoped, the Item Event Receiver is bound to the SPSite object. Here's the Web-scoped version as seen in the SharePoint 2010 Management Shell:

And here's the Site-scoped version:

So, a simple workaround for the Site-scoped version is to filter the Lists in the Event Receiver code, like this:

public override void ItemDeleting(SPItemEventProperties properties)

{

base.ItemDeleting(properties);

// At the Site Scope, the event receiver is registered with

// the Site Collection, not with individual Lists,

// so restrict it here

if (properties.List.BaseTemplate == SPListTemplateType.Announcements)

{

properties.Status = SPEventReceiverStatus.CancelWithError;

properties.ErrorMessage = "Please expire, rather than delete.";

}

To alleviate the annoying deployment issue, use a PowerShell script as a pre-deployment command to retract the Sandboxed Solution, leaving Visual Studio to just carry out the deletion. (Note: "Retract" is spelled "Uninstall" in PowerShell, hence the "Uninstall-SPUserSolution" below.) Here's the PreDeploy.ps1 PowerShell script:

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

$wsp = "Mindsharp.ListItemEvent.SiteScope.wsp"

$url = "http://intranet"

Echo " Retracting Solution..."

Uninstall-SPUserSolution $wsp -Site $url -Confirm:$False

Pre-deployment commands are found on the SharePoint tab of the Project's properties. (Just double-click the Properties folder in Solution Explorer to see it.) Don’t forget to use the SysNative alias in the pre-deployment command, so that Visual Studio launches the right version of PowerShell:

%WINDIR%\SysNative\WindowsPowerShell\v1.0\powershell.exe -command "& '$(ProjectDir)PreDeploy.ps1'"

More Trouble in the Sandbox: Feature Receiver code in a Site-scoped Item Event Receiver Solution

What if this Sandboxed Solution also needs Feature Receiver code? Typically, a Feature Receiver would just be added to the existing Feature. If this were Web-scoped, that would work. If this were a Farm Solution, that would work. However, in a Sandboxed Solution, adding a Feature Receiver to the existing Site-scoped Item Event Receiver Feature can cause the WSP to get stuck in the Solutions Gallery - really, really stuck - can't delete it from the browser, can't delete using PowerShell. (Weirdly, it can be activated and deactivated.) Manual intervention can fix this.

To unstick the stuck WSP, save the Feature Receiver code somewhere, then delete the Feature Receiver. Build and package the solution (these options are on the Build menu). If the Solution's configuration is Debug, the newly packaged WSP will be in the Solution's bin/debug folder. Use the Show All Files button to see it in the Solution Explorer.

Browse to the Solutions Gallery, and upload the just-packaged WSP, (the one with no Feature Receiver code), overwriting the existing "stuck" WSP. Then deactivate and delete the WSP using the Ribbon.

With the stuck WSP out of the way, go back to Visual Studio, and add a new Site-scoped Feature, then add the Feature Receiver code to the new Feature. This should take care of the "stuck WSP" issue, and allow normal iterative development to keep flowing.

Hope that helps!

Provisioning Web Parts in a DIY Sandboxed WebTemplate

Nancy Brown — Tue, 08 Nov 2011 04:56:00 GMT

The new (2010) way to create custom site definitions is the Web Template, which is Sandbox-safe. These can be created using the Save-site-as-template on the Site Settings page, but that creates a humongous beast containing all the Fields, Content Types, Lists… way more stuff than is truly needed, so a better solution is the Do-It-Yourself (DIY) WebTemplate.

A DIY Sandboxed WebTemplate solution might look like this in Solution Explorer, with a Site Feature to deploy the WebTemplate to the Solutions Gallery, and another hidden Feature, (WebFeature in this case), to handle the custom provisioning of a new site.

This post is about just one aspect of this scenario: how to provision web parts to a page using a provisioning Feature in a Sandboxed WebTemplate Solution. The page in this example is default.aspx, a clone of the STS#1 (Blank Site) default.aspx, with some added text and an image. SiteFeature deploys the actual Web Template - that's everything in the FRED folder: ONet.xml and Elements.xml (which contains only a WebTemplate element). WebFeature deploys everything in the ProvisionModule folder: default.aspx and Elements.xml, which contains such bits as ListInstance elements, Modules, and PropertyBag elements. WebFeature is called by ONet.xml to help provision a new site.

List View Web Parts

Typically, the ONet.xml file for a Do-It-Yourself WebTemplate is created by selectively copying/pasting from the base site definition's ONet.xml. This could lead one to thinking "Oh, I’ll just grab the <List> elements too." Unfortunately, that won't work. Well, specifying the <List> element in ONet.xml will create the list, but if a <View> element is used in the provisioning Feature's xml to create the matching web part, that fails with this sort of error message in the log:

Failed to find a suitable list id for doc 'default.aspx' given List template attribute 'Lists/Announcements'.

Failed with 0x80004005 to create the view query or web parts for web /fred, site collection (unspecified), URL default.aspx

Solution - instead of using <List> in ONet.xml, use the new ListInstance element in the provisioning Feature (WebFeature in this example). Looks like this:

<?xml version="1.0" encoding="utf-8"?>

<ListInstance FeatureId="{00bfea71-d1ce-42de-9c63-a44004ce0104}" TemplateType="104"

Title="Announcements"

Description=""

Url="Lists/Announcements"

OnQuickLaunch="TRUE"

RootWebOnly="FALSE" >

<Data>

<Rows>

<Row>

Use this List to post any SharePoint 2010 tips or tricks

</Field>

</Row>

</Rows>

</Data>

</ListInstance>

<View List="Lists/Announcements"

BaseViewID="1"

WebPartOrder="0" WebPartZoneID="Left" >

</View>

</File>

</Module>

</Elements>

Other web parts, with customizations

There are at least a couple of options here. Both involve customizing a web part through the browser, and then harvesting that customization somehow. Option one is exporting the customized web part and using that data in an AllUsersWebPart element. Option two is using the Save-site-as-template to create a WSP that is then imported into Visual Studio, followed by extracting the BinarySerializedWebPart element from it.

AllUsersWebPart

First customize a web part through the browser. The image web part is pretty straightforward, and we can use images in the {SharePointRoot} images folder, so let's use that. For option one, the AllUsersWebPart, we'll set the web part to show templogo.gif, left-justified, with no chrome and no title, then use the Export option.

Open the exported .dwp or .webpart file in Notepad, and copy the entire WebPart element. The opening WebPart tag only needs the xmlns="http://schemas.microsoft.com…" attribute, so delete any others. Usually it is best also to delete the child elements that are default values as well. In the provisioning Feature's Elements.xml, add an AllUsersWebPart element with a CDATA section to contain the WebPart element just created from the export. (The AllUsersWebPart element can "host" almost every web part.) Note that the WebPartZoneID and WebPartOrder should be attributes of the AllUsersWebPart element, not child elements of the WebPart element. The AllUsersWebPart is added as a child element to the File element, just like the View element in the example above. The AllUsersWebPart should look something like this:

<![CDATA[

<FrameState>Normal</FrameState>

<MissingAssembly>Cannot import this Web Part.</MissingAssembly>

<Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>

<TypeName>Microsoft.SharePoint.WebPartPages.ImageWebPart</TypeName>

<ImageLink xmlns="http://schemas.microsoft.com/WebPart/v2/Image">/_layouts/images/templogo.gif</ImageLink>

<VerticalAlignment xmlns="http://schemas.microsoft.com/WebPart/v2/Image">Middle</VerticalAlignment>

</WebPart>

]]>

</AllUsersWebPart>

BinarySerializedWebPart

Going back to the site where the image Web Part was customized earlier, let's add another web part, an XML Viewer, and use it to display the first five titles in a blog - that means adding some XSL. The XML Viewer Web Part is a bit different from most other web parts - one of its properties is an XSL transform. That means if one exports it, there is already a CDATA section in the XML. Here's an example with the embedded CDATA section highlighted:

<Title>XML Viewer</Title>

<FrameType>Default</FrameType>

<Description>Transforms XML data using XSL and shows the results.</Description>

<FrameState>Normal</FrameState>

<MissingAssembly>Cannot import this Web Part.</MissingAssembly>

<Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>

<TypeName>Microsoft.SharePoint.WebPartPages.XmlWebPart</TypeName>

<XMLLink xmlns="http://schemas.microsoft.com/WebPart/v2/Xml">http://www.tinyurl.com/SPTeamBlog</XMLLink>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html" indent="yes"/>

<xsl:param name="TITLE"/>

<xsl:template match="rss">

<xsl:for-each select="channel/item[position() < 6]">

</xsl:for-each>

</div>

</xsl:template>

</xsl:stylesheet>]]></XSL>

</WebPart>

CDATA sections cannot be nested, so the AllUsersWebPart element is not much help here; but a BinarySerializedWebPart can be. Create a WSP for that site using the Save-site-as-template option on the Site Settings page. In the Solutions Gallery, click the name of the solution to download a copy to the hard drive. Fire up Visual Studio, and create a new Import SharePoint Solution Package project. Import the solution just downloaded, taking all the defaults. In Solution Explorer, expand the Modules folder, and find a folder that starts with "_SiteTemplates" - that's where the BinarySerializedWebPart will be. (In this example, the Site used for the web parts is a Blank Site - STS#1). Double-click Elements.xml to view it in the Visual Studio editor.

Copy the entire BinarySerializedWebPart element, and paste it inside the File element as before. Be sure to edit the Url attribute to match the deployment scenario, which in this case, would be just "default.aspx", and don't forget to update the WebPartOrder and WebPartZoneId attributes. The Module element with these three web parts now looks like this:

<View List="Lists/Announcements"

BaseViewID="1"

WebPartOrder="0" WebPartZoneID="Left"

</View>

<![CDATA[

<FrameState>Normal</FrameState>

<MissingAssembly>Cannot import this Web Part.</MissingAssembly>

<Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>

<TypeName>Microsoft.SharePoint.WebPartPages.ImageWebPart</TypeName>

<ImageLink xmlns="http://schemas.microsoft.com/WebPart/v2/Image">/_layouts/images/templogo.gif</ImageLink>

<VerticalAlignment xmlns="http://schemas.microsoft.com/WebPart/v2/Image">Middle</VerticalAlignment>

</WebPart>

]]>

</AllUsersWebPart>

<WebPart ID="{d5d0e278-b7b8-4ca0-987a-08643cf85f50}"

WebPartIdProperty="" List="" Type="0" Flags=""

DisplayName="" Version="2"

Url="default.aspx"

WebPartOrder="2" WebPartZoneID="Left"

IsIncluded="True" FrameState="0"

WPTypeId="{1077a241-f086-1411-9623-a67ec78bc114}"

SolutionId="{00000000-0000-0000-0000-000000000000}"

Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"

Class="Microsoft.SharePoint.WebPartPages.XmlWebPart"

Src=""

AllUsers="AQUAAAACKgBJACkAAz4AAAIqAEoAKQADPQAAAQIAKQAD//80VHJhbnNmb3JtcyBYTUwgZGF0YSB1c2luZyBYU0wgYW5kIHNob3dzIHRoZSByZXN1bHRzLg8BIAApAAP//xsvX2xheW91dHMvaW1hZ2VzL21zeG1sbC5naWYPAVEAXQAD///EAWh0dHA6Ly9zaGFyZXBvaW50Lm1pY3Jvc29mdC5jb20vYmxvZy9fbGF5b3V0cy9mZWVkLmFzcHg/eHNsPTImd2ViPSUyRmJsb2cmcGFnZT0yMjY1NDg5Ny01MzU1LTQwOWItODZiNy0xOTI5NTViYjU3ZDgmd3A9NjhjODA0NWYtMzcyZC00Zjg3LTkzNTMtNzk5MzZhY2ZhZDQ4JnBhZ2V1cmw9JTJGYmxvZyUyRlBhZ2VzJTJGZGVmYXVsdCUyRWFzcHgPARQAXQAE///sAzw/eG1sIHZlcnNpb249IjEuMCIgZW5jb2Rpbmc9IlVURi04Ij8+DQo8eHNsOnN0eWxlc2hlZXQgdmVyc2lvbj0iMS4wIiB4bWxuczp4c2w9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvWFNML1RyYW5zZm9ybSI+DQo8eHNsOm91dHB1dCBtZXRob2Q9Imh0bWwiIGluZGVudD0ieWVzIi8+DQo8eHNsOnBhcmFtIG5hbWU9IlRJVExFIi8+DQo8eHNsOnRlbXBsYXRlIG1hdGNoPSJyc3MiPg0KICA8ZGl2IHN0eWxlPSJiYWNrZ3JvdW5kOiNmZmZmZmY7IHBhZGRpbmc6MDsgZm9udC1zaXplOjEwcHg7Ij4NCiAgICAgIDx4c2w6Zm9yLWVhY2ggc2VsZWN0PSJjaGFubmVsL2l0ZW1bcG9zaXRpb24oKSAmbHQ7IDZdIj4NCiAgICAgICAgPGEgaHJlZj0ie2xpbmt9IiB0YXJnZXQ9Il9uZXciPjx4c2w6dmFsdWUtb2Ygc2VsZWN0PSJ0aXRsZSIvPjwvYT48YnIvPg0KICAgICAgPC94c2w6Zm9yLWVhY2g+DQogIDwvZGl2Pg0KPC94c2w6dGVtcGxhdGU+DQo8L3hzbDpzdHlsZXNoZWV0Pg8P"

PerUser="AQUAAAACKgBJACkAAz4AAAIqAEoAKQADPQAAAQQAKQAD//8UU2hhcmVQb2ludCBUZWFtIEJsb2cPDw==" />

</BinarySerializedWebPart>

</File>

</Module>

Here's a new site is created from that DIY WebTemplate, displaying the customized web parts on default.aspx.

Hope that helps!

The DIY Sandboxed Web Template Situation Comedy

Nancy Brown — Wed, 26 Oct 2011 20:49:00 GMT

If new to Visual Studio 2010, a few gotchas may get you when first starting to work with Do-It-Yourself (DIY) Sandboxed Web Templates, the preferred SharePoint 2010 way to create custom site definitions. (If using the Save-site-as-template version, this won't apply.)

Web Templates created by the Save-site-as-template option on the Site Settings page are kinda like html emitted by MS Word – very faithful to the original object, way more "markup" than optimal for those who are looking for simple and clean. Manually created Web Template solutions can be simple and clean, and have two possible deployment scopes: Farm and Site. This post focuses on creating a Web Template Solution from scratch and Site-level deployment, since Sandboxed Solutions are welcome in the cloud. When doing so, it is possible to trip in a number of spots. If fact, it can feel like a situation comedy at times, as we follow the plight of a star-crossed default.aspx when it deploys (or doesn't), or deploys too many times, or gets deleted by accident.

But first off, what can be learned from a Save-site-as-template version? Create one, import it to Visual Studio, and discover that the ONet.xml file consists basically of the NavBars element, and one Configuration element with its SiteFeatures and WebFeatures child elements. A simple and clean way to create a custom ONet.xml is to copy & paste portions from the base site definition's ONet.xml, and imitate the Save-site-as-template variety by copying just the NavBars element, and one Configuration element, with its Site and Web Features only. (It's actually a requirement - Web Template ONet.xml files may only have one configuration element.)

If the Configuration element contains Module elements, probably delete them, and use a Feature instead. (Features can be versioned and upgraded.) Also, beware - not all "Module"s are the same. There's Module Element (Site) – the one in ONet.xml - and then there's Module Element (Module) – the one used in regular old Feature xml. Looking up Module Element (Site) on MSDN reveals that the Path attribute is "Optional Text. Specifies the physical path to the file set relative to %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\SiteTemplates\Site_Definition". OK, so that's just for grabbing the original core site definition files. Trying to bend this Module element into deploying a custom file in the solution, (like default.aspx), will result in the first Situation Comedy episode (SitCom # 0) - the original file deploys, not the custom one.

A simple custom (DIY) Sandboxed Web Template Solution needs just two Features and three basic files:

a Site scoped Feature to deploy the WebTemplate to the Solutions Gallery, containing

an Elements.xml for the WebTemplate element, which says something like "Hi, my name is 'Fred' and I'm based on the 'BlahBlah' core site definition"
an ONet.xml file in the same Visual Studio folder, which contains provisioning details

a Web scoped Feature for custom provisioning

this provisioning Feature will be listed in ONet.xml; its Elements.xml file handles most of the customizations that make this site different from the base site

Alrighty then, that Web scoped Feature needs to be Hidden and should not be activated by Visual Studio on deployment (the default), so in the Feature properties, set IsHidden to True, and Activate on Default to False…deploy…and what the heck?!? The Web Feature auto-activated anyway, accidentally customizing the Project's test site, Intranet, with a new default.aspx. Turns out the Activate on Default property does not apply to Site and Web scoped Features. Bummer. So, go to the Project's properties, find the SharePoint tab, and set the Active Deployment Configuration to No Activation. Use a PowerShell post-deployment script to activate the Site Feature, or manually activate it. Open the site in SharePoint Designer to fix default.aspx. And save a copy - just in case.

OK, that's fixed up, try again…deploy. Browse over to the Intranet site, just to be sure that WebFeature didn't sneak by somehow… and Gaaaaah! Intranet displays a 404 Not Found. What happened? Look in the Output window, where Visual Studio narrates what it's doing. Hmmm… Found 1 deployment conflict(s) … deleting http://intranet/default.aspx .... not good. Inspect the Module's properties in the properties window. Aha. Deployment Conflict Resolution is set to Automatic. Change that to None. Use SharePoint Designer [again] to fix the missing default.aspx. Good thing there was a backup copy of default.aspx.

Last gotcha: things that must match.

The WebTemplate's BaseConfigurationID and Onet.xml's Configuration Id must match. If they don't, Visual Studio will complain:

"Error occurred in deployment step 'Add Solution': …The Mindsharp.WebTemplates.Research_SiteFeature\FRED\onet.xml file must contain a Configuration element whose ID attribute is equal to the BaseConfigurationID attribute declared in the WebTemplate element."

Finally, the WebTemplate element's Name attribute and the Folder name which contains the Elements.xml file must be identical - in this case, it's FRED.

Have fun creating DIY Web Templates!

LINQ to SharePoint Extensions (Reprise) & I Love LINQPad

Nancy Brown — Thu, 20 Oct 2011 06:43:00 GMT

At SPC 2011 a couple weeks ago, I gave a short talk from the Mindsharp booth on extending LINQ to SharePoint to the ListItem's Attachments property and property bag - which was the subject of an earlier blog post. The example for the original blog post was taken from our Developer's Course, which is continually being revised. In order to make the code a bit smaller for the SPC talk, I extracted just the extension code, and created a simpler Application page for working with it. The slides and code from the SPC talk can be downloaded here, from our Free Resources section.

If you are working with LINQ in any way, are just learning LINQ, would like to know LINQ better, would like a way to test LINQ queries without deploying some project somewhere, would like a C# scratch pad for testing short chunks of code, or an F# tutorial - well then, LINQpad is for you! It's all that and more.

It's free!!! (There is also a not-free-version with IntelliSense.) There are two flavors - one targets .NET Framework 3.5, the other targets .NET Framework 4.0 - SharePoint Devs want both, of course. Get it here: http://www.linqpad.net/ To try out LINQ to SharePoint queries, you'll also need the LINQPad SharePoint driver from Codeplex: http://linqpadsp2010driver.codeplex.com/

Here's what it looks like - resembles SQL Server Management Studio a bit - and queries can be saved in the same way. I wanted to see if I could try the extensions code in LINQPad, and I could, using the Static version of the SharePoint driver:

Want to see the translated CAML query? Click the SQL button on the bottom right pane.

Perhaps some LINQ to SQL queries need a bit of testing? LINQPad has an awesome extension method called Dump() which creates a visualization of whatever object calls it:

Tutorials? LINQPad has a built-in tutorial for LINQPad and for LINQ and for F# and … click the Samples tab in the bottom left corner to discover them all!

Happy LINQ-ing!