Early on in your contact with SharePoint lists, you should have encountered the term content type. Though they are ignored by many, they are, as a matter of fact, a core concept. The Microsoft definition on MSDN crystallizes their essential quality:

When designing a smart data model for your application, your first inclination will most likely be toward a similar structure as you would create for a relational database. You probably know that a lookup field can be used to define a many-to-many relationship without a helper table in between. It seems that this database-centric approach will result in a great data model.

But stop, this is not true! SharePoint is not just another UI layer on top of SQL Server. If you look into the SQL model, you will see that all list data is stored in the same table. It doesn't make sense to create a classic relational model within another generic one. Apart from the flawed performance, you will miss out on the advantages.

SharePoint has more subtle and powerful ways to structure data. That's necessary, because human-readable data is not well structured at all. Consider a taxonomy for storing documents in your company. You might keep the offers, invoices, and project documents in different libraries. Alternatively, you could store the documents by customer—collecting all the customer-specific data in one place, regardless of the type of document. In a relational model, things get complicated. You have a customer table, an invoice table, an offer table, and so on. They have relationships, of course, and retrieving data necessitates creating several joins over some or all the tables. However, not everyone needs everything. There are different consumers for the data. A co-worker will retrieve the offers, while another needs a summary of the projects in progress. A report for the board of directors includes a report on the overdue invoices. A developer modeling the data diagram needs access to everything.

With SharePoint, the data model is arranged very differently. The documents are stored in essentially an unstructured way. All documents are kept in one large library. Searching, filtering, reporting, and performing similar tasks are more efficient and easy to construct. On the other hand, to get access to particular data, more information about the documents is required. "More" is often expressed as "meta" in information technology. Instead of creating a complex and error-prone data model—which is still a compromise—each document has a distinct set of semantic data to describe itself. Each search instance can use all or some of the metadata to find the desired documents. If you think that additional columns in a relational data model table would offer equivalent functionality, that's not entirely true. If several different types of document share the same table—because they are "similar enough"—the number of columns would eventually exceed the capabilities of the database. Adding metadata means that you have the right amount of additional data restricted to the actual document type.

Such a predefined set of metadata fields is called a content type. Like a database table, lists in SharePoint have columns. Metadata expressed as content types are sets of columns. The clever feature is that each document in a list can be associated with its own content type. The content type has a private set of columns that describe the document (or data item) further without breaking the model. In other words, content types encapsulate the data schema on a very high level. The physical location of the data is no longer part of the schema. Having two schemas in one data container resolves the oldest conflict of a relational database—the target it's written for can be either one or another, but not both. In contrast to relational tables, it's like you have a superset of columns while each item (row) refers to a subset of columns. Each content type defines such a schema and several content types can be bound to the same list.

A content type includes a collection of information, such as the following:

Creating and using content types is possible using the UI and the object model. In the section "Content Types" later in this chapter we explore the object model and learn how to program against lists and content types.

SharePoint comes with several built-in lists (see Table 4-1). For simple projects, you probably don't need to create anything to store your user's data.

The base type is defined in an SPBaseType enumeration that is defined like this:

public enum SPBaseType
{
    UnspecifiedBaseType = −1,
    GenericList = 0,
    DocumentLibrary = 1,
    Unused = 2,
    DiscussionBoard = 3,
    Survey = 4,
    Issue = 5,
}

Additional lists are available for specific purposes. The meeting workspace defines several lists in the number range from 200, and the blog site defines lists with an ID starting at 300.

An empty list definition called "Generic" is available to create your own set of columns. The base type shown in the table allows SharePoint to internally track the right columns for the specific item.

You can easily create a new list using the Add method of the SPListCollection type. Listing 4-1 demonstrates this in a console application.

using (SPSite site = new SPSite("http://sharepointserve"))
{
  using (SPWeb web = site.OpenWeb())
  {
    SPList list = null;
    string listName = "Books";

// Check whether the list already exists
    try
    {
      list = web.Lists[listName];
    }
    catch (ArgumentException)
    {
    }
    if (list == null)
    {
      Guid listId = web.Lists.Add(listName, "All our books",
                    SPListTemplateType.GenericList);
      list = web.Lists[listId];
      list.OnQuickLaunch = true;
      list.Update();
    }
    Console.WriteLine("Created list {0} with id {1}", list.Title, list.ID);
  }
}

This code runs in a console application and creates a new generic (custom) list without any additional columns. Before creating a new list, you should check whether it already exists. You can either loop through all lists or try to access the new list and catch the exception thrown if it doesn't yet exist. An alternative approach is to use a LINQ query:

bool exists = (from l in web.Lists.OfType<SPList>()
               where l.Title.Equals(listName)
               select l).Count() > 0;

The list itself is not queryable and must be copied into a List<T> element using the OfType operator. That means the list is copied completely and uses a significant amount of memory if you have many lists. However, from the perspective of clean code, the pattern seems smarter than a try...catch block.

Once the new list is created, you can start changing properties and adding columns. In the example, only one property has been set—OnQuickLaunch. This enables instant access to the new list from the quick menu, as shown in Figure 4-1.

Figure 4.1. The new list has one default column—Title.

Once the list exists, you can add fields. Fields represent the columns. Internally, a field is a type derived from SPField. SharePoint includes a number of predefined fields for specific types. The following example demonstrates adding several fields for scalar types:

list.Fields.Add("ISBN", SPFieldType.Text, true);
list.Fields.Add("LeadAuthor", SPFieldType.Text, true);
list.Fields.Add("Price", SPFieldType.Currency, false);
list.Update();

The SPFieldType enumeration contains all possible types. You can also define custom types and extend the list of field types. A field type is not only a scalar or complex value; it can also have a relationship to a custom field editor. (For more information, see the "Custom Field Types" section later in this chapter.)

The Add method has three overloads. These are the five possible parameters:

After all the fields have been added, you need to call the Update method, which changes the model in the database. Figure 4-2 shows the edit window of the modified list.

Figure 4.2. Editing programmatically added fields

Adding a lookup requires more information. A lookup creates a relationship between two lists. Instead of using the Add method, call AddLookup. This method requires three or four parameters:

Adding a lookup field follows a similar process to adding any other field type:

using (SPWeb web = site.OpenWeb())
{
   SPList authorList = web.Lists["Authors"];
   SPList bookList = web.Lists["Books"];
   Guid listId = authorList.ID;
   bookList.Fields.AddLookup("Authors", listId, false);
   bookList.Update();
}

This code adds a lookup from books to the authors list (see Figure 4-3).

Figure 4.3. A programmatically added lookup field in the edit form

Again, the Update method commits the changes to the database model.

Certain properties are not available through the UI. Even SharePoint Designer, with its advanced capabilities, eventually reaches its limits. The example in Listing 4-2 shows how to enable an invisible field to appear in the dialogs again and remove the read-only flag.

using (SPSite site = new SPSite("http://sharepointserve"))
{
  using (SPWeb web = site.OpenWeb())
  {
    SPList questions = web.Lists["Questions"];
    SPField field = questions.Fields["Voters"];
    field.Hidden = false;

field.ReadOnlyField = false;
    field.Update();
  }
}

The change affects only the SPField object—not the list. The code snippet assumes you have a Questions list with a Voters field that is hidden and read-only. The changes are assigned to the underlying content type if the column is defined there. There is no need to change the particular content type directly.

Once the fields have been added—either programmatically or by using the UI—you can iterate through them and change properties. The foreach approach very simply iterates through all the fields:

SPList bookList = web.Lists["Books"];
foreach (SPField field in bookList.Fields)
{
   Console.WriteLine("{0}, Hidden:{1}, ReadOnly:{2}",
                       field.Title,
                       field.Hidden,
                       field.ReadOnlyField);
}

LINQ is an effective tool to filter the list:

foreach (SPField field in
         bookList.Fields.OfType<SPField>().Where(f => !f.ReadOnlyField) )
{
    Console.WriteLine("{0}, Type:{1}", field.Title, field.Type);
}

The OfType operator reads the complete list and converts it into List<SPField> type. Even if the result shows only a few fields, this operation reads all the fields and filters them in memory.

In the output, as shown in Figure 4-4, all the read-only fields are omitted. You can filter by any property the SPField type exposes.

Figure 4.4. A filtered list of fields

Adding items is as straightforward as adding fields. However, the procedure is not yet type-safe because the column's ID must be provided as a string or Guid. The field name you need to use here is the internal Name, not the DisplayName.

Using a string that clearly describes the field is recommended:

SPListItem item = list.Items.Add();
item["Title"] = "ASP.NET Extensibility";
item["ISBN"] = "978-1-4305-1983-5";
item["LeadAuthor"] = "Joerg Krause";
item["Price"] = 59.99;
item.Update();

An ArgumentException is thrown if a field name does not exist. Alternatively, you can use the field's zero-based index or its Guid to identify the field. This is recommended if the index or Guid is returned from any previous operation, to avoid errors due to mistyping.

Some properties the SPList class exposes have specific types. Imagine that you have a Voters column, which can contain multiple users. The following code fetches the item with ID 1 from the Questions list. If there is no voter already attached, the current user executing the code is added. If there is already a user present, all users of the current web are added.

SPListItem item0 = questions.GetItemById(1);
if (item0.Fields["Voters"].Type == SPFieldType.User)
{
    if (item0["Voters"] == null)
    {
        SPFieldUserValue uv = new SPFieldUserValue(web, web.CurrentUser.ID,
                                                        web.CurrentUser.Name);
        SPFieldUserValueCollection coll = new SPFieldUserValueCollection();
        coll.Add(uv);
        item0["Voters"] = coll;
    }
    else
    {
        // add all users
        var users = from u in web.AllUsers.Cast<SPUser>()
                    where
                        !u.Name.Contains(web.CurrentUser.Name)
                        && !u.Name.StartsWith("NT AUTHORITY")
                        && !u.Name.StartsWith("SHAREPOINT")
                        && !u.Name.EndsWith("SYSTEM")
                    select new SPFieldUserValue(web, u.ID, u.Name);
        SPFieldUserValueCollection coll =
                                 (SPFieldUserValueCollection)item0["Voters"];
        foreach (SPFieldUserValue user in users)
        {
            coll.Add(user);
        }
        item0["Voters"] = coll;
    }
    item0.Update();
}

An interesting observation is that access to a field containing a collection is pernickety. If there is no user in the list, the item0["Voters"] call returns null. To add a new user, an object of type SPFieldUserValueCollection must be assigned. Note that when you merely want to add another user to an already existing collection, you must assign the whole collection too. You can call Add for the SPFieldUserValueCollection object, but not directly to the property. This code will not work as expected:

((SPFieldUserValueCollection)item0["Voters"]).Add()

Unfortunately, it will not throw an exception but simply does nothing.

Each item in a SharePoint list has an associated SPAttachmentCollection array. The collection contains strings, which may seem odd, as you would expect SPFile objects instead. What you see is a direct representation of the underlying storage model. Attachments are part of the containing web. This means that all lists share all their attachments in one library. The names collection must be combined with the web's URL to give the complete address to the file in the repository.

Basic Techniques

Fortunately, the SPAttachmentCollection has several useful direct methods, such as Add, Delete, and Recycle.

SPList list = web.Lists["MyList"];
foreach (string fileName in item.Attachments)
{
    SPFile file = item.ParentList.ParentWeb.GetFile(
                  item.Attachments.UrlPrefix + fileName);
    // Work with SPFile
}

Listing 4-3 shows access to a specific item of a list.

using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {
        SPList list = web.Lists["WordDocuments"];
        SPListItem item = list.Items[id];
        if (item.Attachments.Count > 0)
        {
            SPFile attachment = item.ParentList.ParentWeb.GetFile(
                                item.Attachments.UrlPrefix + item.Attachments[0]);
            Stream content = attachment.OpenBinaryStream();
            using (Package p = Package.Open(content))
            {
                // handle as a package (just a suggestion)
            }
        }
    }
}

This code assumes that you have a list, called WordDocuments. The current item is selected by using the variable id. The code checks whether one or more attachments are present, and if so, it reads the first attachment (index zero). The attachment is loaded into an SPFile object and read as a binary stream. We suggest you use the Package class to access files in the package format (such as XPS, DOCX, XSLX, and so forth). The Package class is defined in the namespace System.IO.Packaging—the containing assembly is WindowsBase.dll. (You can find more about working with Office packages in the section "Working with Office Documents.")

The example in Listing 4-4 retrieves the very same attachment, using the folder structure instead of the full URL.

SPList list = web.Lists["WordDocuments"];
SPListItem item = list.Items[id];
SPFolder folder = web.Folders["Lists"]
                     .SubFolders[list.RootFolder.Name]
                     .SubFolders["Attachments"]
                     .SubFolders[item.ID.ToString()];
if (folder.Files.Count > 0)
{
    SPFile attachment = folder.Files[0];
    Stream content = attachment.OpenBinaryStream();
    using (Package p = Package.Open(content))
    {
         // handle as a package (just a suggestion)
    }
}

The twist is the direct access to the Attachments folder of the web, where the files are organized. As before, the variable id must contain a valid ID for the specified list.

Example: Adding an Attachment to an Item

In the same way that you can read an attachment, you can add an attachment to a list item. This is the preferred way to overcome the limitations of the common upload dialog and implement something more sophisticated (see Listing 4-5).

using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {
      SPList list = web.Lists["WordDocuments"];
      SPListItem newItem = list.GetItemById(id);
      byte[] contents = null;

      if (fileUpload.PostedFile != null && fileUpload.HasFile)
      {
         using (Stream fileStream = fileUpload.PostedFile.InputStream)
         {
           contents = new byte[fileStream.Length];
           fileStream.Read(contents, 0, (int) fileStream.Length);
           fileStream.Close();
         }

         SPAttachmentCollection attachments = newItem.Attachments;

string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);
         attachments.Add(fileName, contents);

         newItem ["AttachmentName"] = fileName;
         newItem.Update();
      }
   }
}

This code assumes you have an .aspx page with a FileUpload control named fileUpload. The user can use this to upload a file, where it's attached directly to the item. It is further assumed that your WordDocuments list has an AttachmentName column that takes the attachment's name for further reference. This column is of type text.

Example: Deleting an Attachment from SPList

As with reading and adding, you can delete an attachment (see Listing 4-6). The item is again selected by using the variable id.

using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {
      SPList list = web.Lists["WordDocuments"];
      SPListItem delItem = list.GetItemById(id);
      SPAttachmentCollection atCol = delItem.Attachments;
      if (delItem["AttachmentName"] != null)
      {
         string strFileName = delItem["AttachmentName"].ToString();
         delItem["AttachmentName"] = string.Empty;
         atCol.Delete(strFileName);
         delItem.Update();
      }
   }
}

In this example, the WordDocuments list contains an additional column, AttachmentName, that holds the URL of the attachment. As you can see, this avoids the URL operations to get the real URL of the attachment.

Example: Downloading an Attachment

To download an attachment, you must first find the download link and then redirect to another page. The ending of the response from current page does not affect the functionalities on the second one. The code shown in Listing 4-7 assumes you have a WordDocuments list and the AttachmentName column, as shown in the previous examples.

using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {
      string AttachmentURL = string.Empty;
      SPList list = web.Lists["WordDocuments"];
      SPListItem attItem = myList.GetItemById(NodeID);
      if (attItem["AttachmentName"] != null)
      {
          AttachmentURL = "/Lists/ WordDocuments/Attachments/" +
                          NodeID.ToString() + "/" +
                          attItem["AttachmentName"].ToString();

          System.Web.HttpContext.Current.Session["FileName"] =
                                     attItem["AttachmentName"].ToString();
          System.Web.HttpContext.Current.Session["Attachment"] =
                                     AttachmentURL.Trim();
       }
       else
       {
          lblReport.Text = "No File name found";
       }
       if (AttachmentURL != string.Empty)
       {
           Reponse.Redirect("download.aspx");
       }
    }
}

On the download.aspx page, you need to the code shown in Listing 4-8 to force the browser to download the file.

if (System.Web.HttpContext.Current.Session["Attachment"] != null)
{
  string strName = System.Web.HttpContext.Current.Session["FileName"].ToString();
  string sbURL = System.Web.HttpContext.Current.Session["Attachment"].ToString();
  System.Web.HttpResponse response;
  response = System.Web.HttpContext.Current.Response;
  System.Web.HttpContext.Current.Response.ContentEncoding =
                                                    System.Text.Encoding.Default;
  response.AppendHeader("Content-disposition", "attachment; filename=" + strName);
  response.AppendHeader("Pragma", "cache");
  response.AppendHeader("Cache-control", "private");
  response.Redirect(sbURL);
  response.End();
}

The download page is standard fare used to set the appropriate headers that cause the browser to display an attractive download dialog with some values preloaded.

Example: Copying an Attachment from One Item to Another Using SPList

Copying an attachment is simply a combination of the previous techniques (see Listing 4-9). There is no direct copy method—only reading an attachment into a stream and writing it back into the new attachment.

private void CopyAttachment(int fromID, int toID, string attachedFile)
{
  byte[] contents = null;
  SPList list = w.ParentWeb.Lists["Item List"];
  SPListItem toItem = list.GetItemById(toID);
  SPListItem fromItem = list.GetItemById(fromID);

  SPAttachmentCollection attColl = fromItem.Attachments;

  SPFile attFile = fromItem.ParentList.ParentWeb.GetFile(
                   fromItem.Attachments.UrlPrefix + attachedFile);
  string fileRead = fromItem.Attachments.UrlPrefix.ToString() + attachedFile;

  StreamReader fsReader = new StreamReader(attFile.OpenBinaryStream());
  using (Stream fStream = fsReader.BaseStream)
  {
    contents = new byte[fStream.Length];
    fStream.Read(contents, 0, (int)fStream.Length);
    fStream.Close();
  }

  toItem.Attachments.Add(attachedFile, contents);
  toItem.Update();
}

If you plan to copy very large files, such as 1 MB or more, we recommend you copy it in chunks. This reduces the amount of memory you need, because the byte array (contents) will hold a copy of the whole file into memory while the opened stream is doing the same already. A loop that reads chunks of 2,048 bytes, for example, is appropriate for most projects.

If a list becomes huge—Microsoft allows theoretically up to 50 million items—the direct handling using SPList and LINQ to Objects will fail. Instead, a CAML query, as described in the section "Query Data Using CAML and LINQ," can dramatically reduce the result set. However, if you want to modify all items in the list or need to page through a still substantial result set, this will not help. In a query, you can specify the current result set position using the SPListItemCollectionPosition class. The query is based on the SPQuery class, but the position does not require adding a specific condition—an empty query will also work. (See the section "Efficient Access to Huge Lists" in Chapter 5.)

Defining a view is very straightforward. You can see from the Change View dialog (select a list and then select List Tools

SPList books = web.Lists["Books"];
SPView view = books.Views["All Items"];
view.ViewFields.Add("ISBN");
view.ViewFields.Add("LeadAuthor");
view.ViewFields.Add("Price");
view.Update();

The AllItems.aspx page now displays these fields, along with Title. As explained earlier, the internal properties sometimes return new objects with each call. Regarding ViewFields, it still works as shown, because the collections are synchronized internally. However, the recommended coding style is as follows:

SPList books = web.Lists["Books"];
SPView view = books.Views["All Items"];
SPViewFieldCollection svfc = view.ViewFields;
svfc.Add("ISBN");
svfc.Add("LeadAuthor");
svfc.Add("Price");
view.Update();

If a view does not yet exist, you can create one programmatically. This can be part of a feature—usually in the feature receiver—or any tool a user can use to change the current list.

The code in Listing 4-12 assumes you have a Books list with some entries. It has at least two fields: Publisher, which is a selection field, and Author, containing text.

using System.Collections.Specialized;
using System.Xml.Linq;
using Microsoft.SharePoint;

namespace Apress.SP2010.CreateNewView
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://sharepointserve"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList books = web.Lists["Books"];
                    StringCollection fields = new StringCollection();
                    fields.Add("Title");
                    fields.Add("Publisher");
                    fields.Add("Author");
                    var query = new XElement("Where",
                                    new XElement("Eq",
                                        new XElement("FieldRef",
 new XAttribute("Name", "Publisher")),
                                        new XElement("Value",
                                          new XAttribute("Type", "CHOICE"),
                                          "Apress")
                                        )
                                    ).ToString(SaveOptions.DisableFormatting);
                    SPView view = books.Views.Add("ApressBooks",
                        fields,
                        query,
                        100,
                        false,
                        false,
                        Microsoft.SharePoint.SPViewCollection.SPViewType.Html,
                        false
                        );
                }
            }

        }

    }
}

You can add a new view to the SPViewCollection exposed by the Views property. You need to provide the fields as a StringCollection object. If the view should filter the data, a query can be applied using CAML. The XElement and XAttribute types aid in creating valid XML on the first attempt. The query is then assigned as a string. The Add method has some overloads. Table 4-2 explains the parameters this method supports.

There is no Update method—after adding a view, users can use it immediately.

In this chapter, provided we're not referring to documents explicitly, we don't distinguish between lists and libraries. Content types have two base types, one for lists and one for libraries. As a result, if you want to create a new content type for a document library, it must inherit from the appropriate base type. A content type designed for a library will not work with a standard list.

The previous sections described fields. Content types define not only fields to create columns. They also define events or associated workflows—the item's behavior. For example, you can create a content type that is bound to a list, and when an item is created with that type, an associated event receiver uses the item to perform some calculations or check validity. Because of this power, creating and using content types is complicated. However, it's worth examining closer, for any real life project.

Like the content type itself, the fields it contains are not explicitly defined there. Instead, you create global fields (in the scope of the web or site) and assign a reference to such a field. SharePoint comes with many predefined global field definitions that can be used to create content types with standard field types. These appear as site columns in the SharePoint UI.

Internally, a content type is of type SPContentType. This type contains two collections, Fields (of type SPFieldCollection) and FieldLinks (of type SPFieldLink). If you want to add a field to the content type, you need to use an SPFieldLink object. The Fields collection is the corresponding list that gives access to the final definition of each column including a column's attributes. The resulting SPField object is a merged view of the part defined in the references field and the one inherited from.

Before you start creating your own fields, examine the list of predefined ones. It is likely that you will find one appropriate for your new content type, and it will save you time and effort. Figure 4-6 shows how site columns appear in the UI.

Figure 4.6. Some predefined site columns available for creating content types

You can find the complete list on MSDN—there are about 410 field definitions available. Hence, instead of creating one, take your time to find the right one. The following example shows both techniques, using an existing field type (site column) and a new one previously added to the field collection:

SPContentTypeCollection cts = web.ContentTypes;
SPContentType newctpy = new SPContentType(
   cts[SPBuiltInContentTypeId.Contact],
   cts,
   "Certificates");
// Add content type to the site
cts.Add(newctpy);
// Create site fields and link to the content type
SPFieldCollection siteFields = web.Fields;
siteFields.Add("Certificate", SPFieldType.Text, true);
siteFields.Add("Score", SPFieldType.Integer, true);
web.Update();

// Add fields to the new content type
newctpy.FieldLinks.Add(new SPFieldLink(siteFields["Certificate"]));
newctpy.FieldLinks.Add(new SPFieldLink(siteFields["Score"]));
newctpy.FieldLinks.Add(new SPFieldLink(web.Fields["Start Date"]));
newctpy.Update();
// Add content type to the list
SPList authors = web.Lists["Authors"];
authors.ContentTypesEnabled = true;
if (authors.IsContentTypeAllowed(newctpy))
{
   SPContentType certCT = authors.ContentTypes.Add(newctpy);
   authors.Update();
}

The content type is used to add certificates an author has earned to the Authors list used in the previous examples. The code starts with a reference to the web's ContentTypes collection. The new content type Certificates is added. Then two fields are added to the web's field collection: Certificate and Score. These and an existing field, Start Date, are added to the FieldLinks collection of the new content type. If no errors occurred, the list is enabled to support content types, and the content type is added to the list. The result can immediately be checked by comparing the content types in the site administration with those in the list.

In Figure 4-7 you can see that the custom site column is available.

Figure 4.7. Custom content type with the custom column definition, the content type, and the editor

Also available are the custom content type using these columns and the built-in columns. When you start editing the items of the list, you can select your content type. This is because the existing list already implements the Contact content type. The new Certificates content type inherits from Contact and hence has the same fields plus the new ones. You can delete the existing content type if no existing items are using it:

list.ContentTypes["Name"].Delete();

In code that runs every day in your application, you will probably not use content types. Programmatic access to a site's content type is usually an administrative operation. Typically you deploy a new feature, and the users can create custom lists within your application. To minimize their effort, a custom content type is ideal. Consequently, the feature must be able to create the content type on activation. That's exactly when you invoke code similar to that shown previously.

There is some interaction between the UI and the settings made by custom code. First, when adding a content type to a list, the fields in the content type are copied to the list. That's why the edit form shows the fields immediately. If a user with appropriate rights now changes the field's definition, they change the copied definition. That means such modified fields remain local to that list and do not change the content type. Second, when changing the content type using the web's Site Settings dialog, the user has the choice to push changes to existing copies (see Figure 4-8).

Figure 4.8. The user has the option to push changes to existing content type copies.

SharePoint is rich with the events it exposes. We can categorize SharePoint events in two ways: by the "level" that fires the event (site, list, item) and by the type of the event (synchronous or asynchronous).

Table 4-3 shows the full list of synchronous events you can handle with your receivers.

Synchronous events are also called "before" events. They appear before the internal operation occurs. The event handler is also blocking the current flow. For this reason, we recommend you avoid time-consuming operations here.

Asynchronous events (see Table 4-4) are classified as "after" events. They are called asynchronously. Lengthy operations do not block the current thread. Because the internal operation is completed, you can safely manipulate the freshly added or changed data.

List events (see Tables 4-5 and 4-6) occur for list operations that affect the whole list, such as schema changes. As for the list item events, you can choose synchronous and asynchronous events. The implementation uses the SPListEventReceiver base class—you simply override the appropriate methods.

Each Receivers node has following child nodes:

The crucial parts here are the Assembly and Class tags. They must exactly match the class definition when they are deployed.

The code snippet created by the wizard (see Listing 4-14) attaches the two events to their event handlers. This is shown "as is." No changes were made to fix namespaces or anything else.

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;

namespace Apress.SP2010.EventReceiver1
{
    /// <summary>
    /// List Item Events
    /// </summary>
    public class EventReceiver1 : SPItemEventReceiver
    {
       /// <summary>
       /// An item is being added.
       /// </summary>
       public override void ItemAdded(SPItemEventProperties properties)
       {

base.ItemAdded(properties);
       }

       /// <summary>
       /// An item is being updated.
       /// </summary>
       public override void ItemUpdated(SPItemEventProperties properties)
       {
           base.ItemUpdated(properties);
       }

    }
}

The receiver class must inherit from SPItemEventReceiver. To attach other events, you can override the corresponding methods in this class and extend the Elements.xml to connect the handler with the event source. A closer look into the SPItemEventProperties reveals some options. Listing 4-15 shows an example.

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;

namespace Apress.SP2010.EventReceiver2
{

    public class EventReceiver2 : SPItemEventReceiver
    {
        public override void ItemAdded(SPItemEventProperties properties)
        {
            updateItemPermissions(properties);
        }

        public override void ItemUpdated(SPItemEventProperties properties)
        {
            updateItemPermissions(properties);
        }

        private void updateItemPermissions(SPItemEventProperties properties)
        {
            try
            {
                this.EventFiringEnabled = false;
                SPListItem item = properties.ListItem;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    SPList parentList = item.ParentList;
                    SPSite elevatedSite = new SPSite(parentList.ParentWeb.Site.ID);
                    SPWeb elevatedWeb =

elevatedSite.OpenWeb(parentList.ParentWeb.ID);
                    SPList elevatedList = elevatedWeb.Lists[parentList.ID];
                    SPListItem elevatedItem =
                          elevatedList.Items.GetItemById(properties.ListItem.ID);
                    if (!item.HasUniqueRoleAssignments)
                        item.BreakRoleInheritance(false);

                    SPUser editor = elevatedWeb.EnsureUser(
                           (new SPFieldLookupValue(item["Editor"].ToString()))
                                .LookupValue);
                    SPUser author = elevatedWeb.EnsureUser(
                           (new SPFieldLookupValue(item["Author"].ToString()))
                                .LookupValue);
                    SPRoleDefinition RoleDefReader =
                          elevatedWeb.RoleDefinitions.GetByType(
                                               SPRoleType.Reader);
                    SPRoleDefinition RoleDefWriter =
                          elevatedWeb.RoleDefinitions.GetByType(
                                          SPRoleType.Contributor);
                    SPRoleAssignment RoleAssReader = new
                                     SPRoleAssignment((SPPrincipal)editor);
                    SPRoleAssignment RoleAssWriter = new
                                     SPRoleAssignment((SPPrincipal)author);
                    RoleAssReader.RoleDefinitionBindings.Add(RoleDefReader);
                    RoleAssWriter.RoleDefinitionBindings.Add(RoleDefWriter);
                    item.RoleAssignments.Add(RoleAssReader);
                    item.RoleAssignments.Add(RoleAssWriter);
                    item.Update();

                });

                this.EventFiringEnabled = true;
            }
            catch (Exception ex)
            {
            }
        }
    }
}

The receiver is attached to a list that has at least two fields—called Author and Editor—both of User type containing a user available in the current site. Usually the creator of the item becomes the owner. The event receiver overwrites the inherited permissions by setting the users added in the Author field with write permissions and in the Editor field with read permissions.

In addition, the code shows some basic principles. To change the permissions, you need sufficient rights. You can achieve this by elevating the privileges or by assigning a specific user's token to the OpenWeb method. In this example, the RunWithElevatedPrivileges method is used. The code checks whether the item already has specific permissions using the HasUniqueRoleAssignments property. This avoids rewriting the values again. If the permissions are at the default level, the inheritance hierarchy is broken by calling BreakRoleInheritance. Now you can assign user's roles. EnsureUser checks that the site's user is available in the list's web. The specific permissions are retrieved from the web's RoleDefinitions property and set to standard permissions (SPRoleType.Reader and SPRoleType.Contributor).

Because the changes will update the item and an event receiver connects to ItemUpdating event, you need to suppress events temporarily. This is done by setting the EventFiringEnabled property.

Each site contains a library called Shared Documents. Listing 4-16 shows how to retrieve a file as an SPFile object.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
      if (web.GetFolder("Shared Documents").Exists)
      {
         SPFolder folder = web.GetFolder("Shared Documents");
         SPFile file = folder.Files["WordTemplate.docx"];
      }
   }
}

There is a more direct way to access a specific file using the SPDocumentLibrary (Listing 4-17).

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
      string url = SPUrlUtility.CombineUrl(web.Url, "/Shared Documents");
      SPDocumentLibrary lib = (SPDocumentLibrary) web.GetList(url);
      SPFile file = lib.RootFolder.Files["WordTemplate.docx"];
   }
}

Both examples fetch the same file. However, both will fail with an ArgumentException if the file does not exist. To check this, you cannot test the return value for null. Even an if statement such as the one shown next will not help:

if (lib.RootFolder.Files[["WordTemplate.docx"] != null)

Instead, use the GetFile method provided by SPWeb:

if (web.GetFile(fileName).Exists)

The interesting thing is that this method always returns an object. If the file does not exist, the method returns an SPFile object whose Exists property is set to false. There are still circumstances in which GetFile could return null. Hence, a helper method as in Listing 4-18 is desirable if you have many such file operations.

static SPFile GetExistingFile(SPWeb web, string serverRelativeUrl)
{
    SPFile file = null;
    bool exists = false;
    try
    {
        file = web.GetFile(serverRelativeUrl);
        if (file != null)
        {
            exists = file.Exists;
        }
    }
    catch (ArgumentException)
    {
        exists = false;
    }
    if (!exists)
    {
        return null;
    }
    return file;
}

Once you have the SPFile, you can do many things with it. The SPFile object gives access to some powerful properties and methods.

The main functions are the OpenBinaryStream method, which opens the file as a stream, and OpenBinary, which returns a byte array. Apart from these, you can do everything programmatically that users with appropriate rights can do from the UI.

If you work with folders, your users might not be satisfied with the UI support. For greater capability, you can create a function (see Listing 4-19) that adds folders programmatically.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
      SPList lib = web.Lists["Shared Documents"];
      SPFolder parent = lib.RootFolder;
      SPFolder child = parent.SubFolders.Add("SubFolderName");
      parent.Update();
  }
}

This code adds a SubFolderName folder to the library's root. Exactly the same functionality is possible (see Listing 4-20) if you prefer to work with SPList.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb spweb = site.OpenWeb())
   {
       spweb.AllowUnsafeUpdates = true;

       SPList lib = spweb.Lists["Shared Documents"];
       SPListItem child =lib.Items.Add(lib.RootFolder.ServerRelativeUrl,
                                            SPFileSystemObjectType.Folder,
                                            "SubFolderName");
       child.Update();
   }
}

The previous example reveals that a folder is simply a special kind of item. That's indeed true and is the reason why some collections return all elements, regardless of the folder to which they are currently assigned.

To upload a file to a library, you use a similar approach (see Listing 4-21). The file must be available as a Stream or a byte array.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
       spweb.AllowUnsafeUpdates = true;

SPFolder folder = web.Folders[site.URL + "/Shared Documents/"];
       byte[] content = null;
       using (FileStream filestream = new FileStream(@"C:Sample.docx",
                                                      System.IO.FileMode.Open))
       {
          content = new byte[(int) filestream.Length];
          filestream.Read(content, 0, (int) filestream.Length);
          filestream.Close();
       }

       SPFile file = folder.Files.Add("Sample.docx", content, true);
       // do something usefil with file
     }
}

You can add the file to a specific folder:

SPFile file = folder.SubFolders["SubFolder"].Files.Add("Sample.docx",
                                                        content,
                                                        true);

The content variable is the same as shown previously in Listing 4-21.

Deleting a file is elementary. Listing 4-22 shows how to delete a file directly.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
       web.AllowUnsafeUpdates = true;
       SPFolder folder = web.Folders[web.Url + "/Shared Documents/"];
       folder.Files["Sample.docx"].Delete();
       folder.Update();
    }
}

Recycling a file (Listing 4-23) uses a variation on the same approach. Recycling recycles the item into the Recycle Bin folder SharePoint provides.

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
       web.AllowUnsafeUpdates = true;
       SPFolder folder = web.Folders[web.Url + "/Shared Documents/"];
       folder.Files["Sample.docx"].Recycle();
       folder.Update();

}
}

Deleting a file from a subfolder follows the same rules (see Listing 4-24).

using (SPSite site = new SPSite("http://sharepointserve"))
{
   using (SPWeb web = site.OpenWeb())
   {
       web.AllowUnsafeUpdates = true;
       SPFolder folder = web.Folders[site.Url + "/Shared Documents/"];
       folder.SubFolders["SubFolder"].Files["Sample.docx"].Delete();
       folder.Update();
    }
}

The best way to add placeholders is via controls. To add placeholders, you first need to activate the developer toolbar (see Figure 4-12).

Figure 4.12. Adding controls from the Developer tab in Word 2010

Before you can fetch a document from a library, you must have one. As shown in Figure 4-11, through the XML you have access to any part of the document. To build a template, you need to create a regular Word document and add placeholders at the appropriate positions. To construct a report, insert a table. Add one data row as a template and repeat this row for each line of data. Building the document can be accomplished with a few clicks, and an end user with some Word experience should be able to master this. They will, however, add an unpredictable amount of formatting styles to such a document. Therefore, you must be able to recognize the placeholders reliably.

On the activated Developer tab, click Design Mode, and you can add controls to your layout. For this exercise, use Text controls. In the Properties options, as shown in Figure 4-12, you can set the Title of the control (as it appears in design view) and the Tag name (as it appears in the XML).

Once the placeholders are inserted, you can save the document and upload it to the appropriate document library.

The complex system based on SharePoint, Word, and package formats is now as easy as reading and writing XML. Using LINQ to XML makes this even more straightforward. However, you need the XML first. Begin by fetching the document into an SPDocumentLibrary object:

SPDocumentLibrary templateLib = web.Lists.Cast<SPList>().FirstOrDefault(
                                list => list.RootFolder.Name == "WordTemplates")
                                as SPDocumentLibrary;

The lambda expression is used to reference the list using the RootFolder.Name property. If you can use the Title, the Lists property's indexer will work, too. Call the OpenBinaryStream method to retrieve the file as a stream:

string documentUrl = SPUrlUtility.CombineUrl(
                     web.Site.MakeFullUrl(templateLib.RootFolder.Url),
                     templateName);
SPFile template = templateLib.RootFolder.Files[documentUrl];
Stream templateStream = template.OpenBinaryStream();

To work with OPC packages, you need classes from the System.IO.Packaging namespace. The assembly containing the classes is called WindowBase.dll.

You have to first unpack the document. SharePoint libraries return files as a stream, as shown previously. To open a file as a package, you can use code like this:

Package package = Package.Open(templateStream,
                               FileMode.Open,
                               FileAccess.ReadWrite);

The variable templateStream is of type Stream. From the Package object, you can now read the actual file:

Uri uri = new Uri("/word/document.xml", UriKind.Relative);
PackagePart part = package.GetPart(uri);

The PackagePart provides access to the document, as a stream that can be loaded into an XDocument:

Stream partStream = part.GetStream(FileMode.OpenOrCreate, FileAccess.ReadWrite);
XmlReader xmlReader = XmlReader.Create(partStream);
XDocument doc = XDocument.Load(xmlReader);

Once the XML is in the right type, any further manipulation is simple using LINQ to XML. With minimal instructions you can locate the <w:sdt> element that creates a table:

XElement tablePlaceHolder = doc.Descendants(W.sdt).FirstOrDefault(
         x => x.Element(W.sdtContent).Descendants(W.sdt).FirstOrDefault() != null);

The content is found within <w:stdContent>, which this statement returns. From here you extract the table's row to use as a prototype for the rows being generated:

XElement prototype = tablePlaceHolder.Element(W.sdtContent).Descendants(W.tr)
.Where(x => x.Descendants(W.sdt).FirstOrDefault() != null).FirstOrDefault();

Before proceeding, you need to read the data from a SharePoint list. For the phone list, read from a list called Contacts:

SPList dataList = web.Lists.Cast<SPList>().FirstOrDefault(
                                      list => list.RootFolder.Name == "Contacts");
IEnumerable<SPListItem> allItems = dataList.GetItems(
                                      new SPQuery()).Cast<SPListItem>();

The items in allItems contain the data to display in the report rows. Listing 4-25 shows the complete code. The inner foreach statement reads the placeholders and replaces their contents with the data read from SharePoint. ContainsField is used to avoid exceptions if a user named the placeholder field accidentally with a name the list does not provide. li[celltag] fetches the data as input for a format instruction. Imagine additional operations here following either fixed rules or instructions added to the document. The prototype row is replaced with a number of rows—one for each line in the data list. The file is written back into the package, and the package is sent to another document library in SharePoint as the final report. You can find the complete solution in Listing 4-25.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.IO;
using System.IO.Packaging;
using System.Xml;
using System.Xml.Linq;

namespace Apress.SP2010.DocxDemo
{
    class Program

{
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://sharepointserve"))
            using (SPWeb web = site.OpenWeb())
            {
                SPDocumentLibrary templateLib =
                   web.Lists.Cast<SPList>().FirstOrDefault(
                   list => list.RootFolder.Name == "WordTemplates")
                   as SPDocumentLibrary;
                SPDocumentLibrary reportLib =
                   web.Lists.Cast<SPList>().FirstOrDefault(
                   list => list.RootFolder.Name == "WordReports")
                   as SPDocumentLibrary;
                SPList dataList = web.Lists.Cast<SPList>().FirstOrDefault(
                   list => list.RootFolder.Name == "Finances");
                string templateName = "ReportTemplate.docx";
                string reportName = String.Format("DailyReport{0}.docx",
                                    DateTime.Now.ToShortDateString());

                string documentUrl = SPUrlUtility.CombineUrl(
                       web.Site.MakeFullUrl(templateLib.RootFolder.Url),
                       templateName);
                SPFile template = templateLib.RootFolder.Files[documentUrl];
                Stream templateStream = template.OpenBinaryStream();
                Stream documentStream = new MemoryStream();
                BinaryReader templateReader = new BinaryReader(templateStream);
                BinaryWriter documentWriter = new BinaryWriter(documentStream);
                documentWriter.Write(
                    templateReader.ReadBytes((int)templateStream.Length));
                documentWriter.Flush();
                templateReader.Close();
                templateStream.Dispose();

                Package package = Package.Open(documentStream,
                                               FileMode.Open,
                                               FileAccess.ReadWrite);

                Uri uri = new Uri("/word/document.xml", UriKind.Relative);
                PackagePart part = package.GetPart(uri);


                Stream partStream = part.GetStream(
                       FileMode.OpenOrCreate, FileAccess.ReadWrite);
                XmlReader xmlReader = XmlReader.Create(partStream);
                XDocument doc = XDocument.Load(xmlReader);
                xmlReader.Close();

                XElement tablePlaceHolder = doc.Descendants(W.sdt)
                         .FirstOrDefault(x =>
                         x.Element(W.sdtContent)
                         .Descendants(W.sdt)
                         .FirstOrDefault() != null);

XElement prototype = tablePlaceHolder.Element(W.sdtContent)
                                     .Descendants(W.tr)
                                     .Where(x => x.Descendants(W.sdt)
                                     .FirstOrDefault() != null)
                                     .FirstOrDefault();

                IEnumerable<SPListItem> allItems = dataList.GetItems(
                       new SPQuery()).Cast<SPListItem>();

                prototype.Parent.Add(allItems.Select(
                  li =>
                  {
                    var result = new XElement(prototype);
                    foreach (var placeholder in result.Descendants(W.sdt))
                    {
                       string celltag = placeholder.Element(W.sdtPr)
                                        .Element(W.tag)
                                        .Attribute(W.val)
                                        .Value;

                       if (li.Fields.ContainsField(celltag))
                       {
                           placeholder.Element(W.sdtContent)
                                      .Descendants(W.t)
                                      .Single()
                                      .Value = String.Format("{0}", li[celltag]);
                       }
                       else
                       {
                           placeholder.Element(W.sdtContent)
                                      .Descendants(W.t)
                                      .Single()
                                      .Value = String.Empty;
                       }
                    }
                  return result;
               }));

                prototype.Remove();

                partStream.SetLength(0);

                XmlWriter writer = XmlWriter.Create(partStream);
                doc.WriteTo(writer);
                writer.Close();
                package.Flush();

                string reportUrl = SPUrlUtility.CombineUrl(
                   web.Site.MakeFullUrl(reportLib.RootFolder.Url),
                   templateName);
                SPFile report = reportLib.RootFolder.Files.Add(
                   reportUrl, documentStream, true);
                SPListItem reportItem = report.Item;
                reportItem["Title"] = reportName; // Set Metadata

reportItem.Update();
            }
        }
    }
}

Because you need access to WordML elements, it makes sense to create a helper class with predefined XName objects (in Listing 4-26).

public static class W
{
  public static XNamespace w = "http://schemas.openxmlformats.org/
                                wordprocessingml/2006/main";
  public static XName body = w + "body";
  public static XName sdt = w + "sdt";
  public static XName sdtPr = w + "sdtPr";
  public static XName tag = w + "tag";
  public static XName val = w + "val";
  public static XName sdtContent = w + "sdtContent";
  public static XName tbl = w + "tbl";
  public static XName tr = w + "tr";
  public static XName tc = w + "tc";
  public static XName p = w + "p";
  public static XName r = w + "r";
  public static XName t = w + "t";
  public static XName rPr = w + "rPr";
  public static XName highlight = w + "highlight";
  public static XName pPr = w + "pPr";
  public static XName color = w + "color";
  public static XName sz = w + "sz";
  public static XName szCs = w + "szCs";
}

This is the small subset of WordML tags used in the previous example. For other projects, it may be necessary to extend this list.

Document sets have several advantages:

Before you can access and use document sets programmatically, some preparatory steps are required.

The document sets feature is available at the site collection level. To activate it, open the site settings and click "Site collection features." On the next page, click the Activate button near the Document Sets feature entry (see Figure 4-13).

Figure 4.13. Activating document sets

A document set can now be created by adding a new content type, which inherits from the built-in content type Document Set. As with any other content type, you can add any number of metadata to modify the behavior appropriately. Additionally, you can configure multiple templates to support the documents the set can contain. The Document Set Settings function is available to modify the templates (see Figure 4-14).

Figure 4.14. Edit the document set settings

The various settings of the new content type are as follows:

After setting the appropriate properties, you can assign the new content type to any document library. Henceforth, this library supports document sets. The document library must generally support content types.

All types required to access document set functions are defined in the namespace Microsoft.Office.DocumentManagement.DocumentSets. The namespace contains the classes shown in Table 4-7.

The most valuable class is DocumentSet. This class has three static methods you need frequently:

Some methods are provided by a class instance:

The relationship between a folder and a document set reveals the underlying approach. A document set is—from a developer's perspective—a folder that is associated with a specific content type. The examples shown in the next few sections express this in various ways.

A document set is specific folder based on a custom content type that inherits from a built-in content type. The new API necessary to deal with document sets is added to the existing model without changing basic classes such as SPList or SPFolder. To achieve this several new classes have been added, which provide some static methods (see Listing 4-27).

SPListItem item = SPContext.Current.ListItem;
DocumentSet set = DocumentSet.GetDocumentSet(item.Folder);
Console.WriteLine("ContentType: {0}", item.ContentType.Name);
Console.WriteLine("Title: {0}", item.Title);
Console.WriteLine("WelcomePageUrl: {0}", set.WelcomePageUrl);
Console.WriteLine("ItemCount: {0}", set.Folder.ItemCount);
Console.WriteLine("Welcomepage Fields:");
DocumentSetTemplate template = set.ContentTypeTemplate;
WelcomePageFieldCollection fields = template.WelcomePageFields;
foreach (SPField field in fields)
{
     Console.WriteLine("{0}", field.Title);
}

The DocumentSet class encapsulates a document set. The GetDocumentSet method is used to convert the technical base (SPFolder) into a more appropriate object type. The home page for each document set is defined using the WelcomePageFields property. The ContentTypeTemplate property returns the template information as a DocumentSetTemplate type.

You often need to know whether an item is already in a set. The method in Listing 4-28 checks this.

public bool IsDocumentSetItem(SPListItem itemToCheck)
{
   bool documentSetItem = false;
   if (itemToCheck.File != null)
   {
      DocumentSet documentSet;
      documentSet = DocumentSet.GetDocumentSet(itemToCheck.File.ParentFolder);
      if (null != documentSet)
      {
          documentSetItem = true;
      }
   }
   return documentSetItem;
}

The code asks the current item to return the containing set. If there is no set, then the item is not part of any document set.

SharePoint provides a set of web services to enable client interaction with the data model. Some of these are specifically designed for data retrieval or to support at least a subset of related functions. These services are still supported for backward compatibility and interoperability with web service clients. For reasons of both performance and ease, Microsoft recommends that you use either the object model API or the ADO.NET Data Services Framework. (Both methods are described in full in Chapter 5.)

Access to SharePoint deployments using RPC calls into the owssvr.dll is still supported. However, Microsoft says that this is only to provide troubleshooting assistance to existing client applications. For maximum application compatibility, Microsoft does not recommend using this method of client access. We do not recommend it either and exclude it from further consideration.

The Query schema is well documented, and numerous examples are available. However, the first time you try it, you'll discover it's not self-explanatory. All queries must be executed against a list. You need a reference to either an SPList or SPView object, which accepts a CAML query and can run it. The following examples demonstrate the bare bones of the technique.

The basic way to query data from SharePoint lists is to use queries constructed with CAML. Listing 4-29 shows a simple CAML query to fetch all items from a SharePoint list called Books whose Publisher is Apress.

public IEnumerable<SPListItem> GetBooksFromAPress()
{
    SPList list = SPContext.Current.Web.Lists["Books"];
    SPQuery query = new SPQuery();
    query.Query = @"<Where>
                      <Eq>
                        <FieldRef Name='Publisher' />
                          <Value Type='Text'>Apress</Value>
                      </Eq>
                    </Where>";
    IEnumerable<SPListItem> books = list.GetItems(query).OfType<SPListItem>();
    return books;
}

In this example, the list is obtained from the current SPWeb instance. A new SPQuery object is instantiated to contain the query. The query is written as an XML string fragment.

With CAML, you can easily build your own custom queries, including conditions (Where) and logical operators (AND, OR) in XML format. Internally, SharePoint translates the CAML query into a SQL query to retrieve the data from the SharePoint content database. You can consider CAML as a necessary abstraction layer to the database—necessary because it is responsible for security trimming and caching. A SQL query to the content database returns all queried items, even though the user who made the request may not be permitted to view the items. "Security trimming" means filtering out the items the user lacks permission to see. This is done internally by SharePoint's data access layer. There are also some caching mechanisms implemented that ensure that if the same CAML query is executed in quick succession, no direct database access has to be made after the first call.

One big disadvantage of manually constructed CAML queries, such as the one shown in Listing 4-30, is a high error rate. If you make one small mistake, such as encapsulating your CAML query within <Query></Query> tags, the query will execute without errors, but it will return all items instead of only those items whose Publisher is APress. This result can be very dangerous and subtle, and it can be time-consuming to find the error. There are alternative ways to construct a CAML query.

You can use LINQ to XML and the XElement type to create a query:

SPQuery queryl = new SPQuery();
queryl.Query = new XElement("Where",
                 new XElement("Eq",
                   new XElement("FieldRef", new XAttribute("Name", "Company")),
                   new XElement("Value", new XAttribute("Type", "Text"))))
               .ToString();

While this approach takes care of closing tags correctly and will always create valid XML, it does not seem easier. The CAML keywords are strings, and hence they can suffer from typing errors. You might consider using constants to predefine such keywords:

const string WHERE = "Where";
const string EQ = "Eq";
const string FIELDREF = "FieldRef";
const string VALUE = "Value";
const string NAME = "Name";
const string TYPE = "Type";
SPQuery queryc = new SPQuery();
queryc.Query = new XElement(WHERE,
                 new XElement(EQ,
                   new XElement(FIELDREF, new XAttribute(NAME, "Company")),
                   new XElement(VALUE, new XAttribute(TYPE, "Text"))))
               .ToString();

That's indeed safe and compact, but it's still a manually built query. To overcome the manual construction of CAML queries, there are some useful implementations of CAML query builders that support creating a CAML query in a safe manner. To demonstrate this, check out an example using CAML.NET from John Holliday, which is a free project hosted on CodePlex (www.codeplex.com/camldotnet). Listing 4-30 shows how to use it.

public IEnumerable<SPListItem> GetBooksFromAPress2()
{
    SPList list = SPContext.Current.Web.Lists["Books"];
    SPQuery query = new SPQuery();
    query.Query = CAML.Where(
                    CAML.Eq(
                      CAML.FieldRef("Publisher"),
                      CAML.Value("APress")
                    )
                  );
    IEnumerable<SPListItem> books = list.GetItems(query).OfType<SPListItem>();
    return books;
}

If you need to use many manually constructed CAML queries, we strongly recommend using a CAML query builder such as CAML.NET. This is the least error-prone and most efficient method.

The primary schema starts with a <Query> element that resembles the structure of a SQL query:

<Query>
  <Where>
    ...
  </Where>
  <GroupBy>
    ...
  </GroupBy>
  <OrderBy>
    ...
  </OrderBy>
</Query>

The <GroupBy> and <OrderBy> elements are optional. Within the elements a field reference is required. For the <Where> part, a more complex clause can be constructed that contains static values.

Referencing Fields and Values

To reference a field, use the <FieldRef> element. The Name attribute is compulsory. The element is also used in other CAML dialects, but several of its attributes are not supported in data queries. Table 4-8 shows the subset that are used for data retrieval. Boolean types are expressed by either TRUE or FALSE strings.

The <Value> element is used to reference static values against which the field's content is compared. In a query, this would look like the following for a simple field:

<Query>
   <Where>
      <Or>
         <IsNull>
            <FieldRef Name="Expires" />
         </IsNull>
         <Geq>

<FieldRef Name="Expires" />
            <Value Type="DateTime">
               <Today />
            </Value>
         </Geq>
      </Or>
   </Where>
   <OrderBy>
      <FieldRef Name="Modified" Ascending="FALSE" />
   </OrderBy>
</Query>

Using lookups is more complex—the value's type must be set to Lookup. If the lookup uses the lookup table's ID field, set the LookupId="TRUE" attribute of the <FieldRef> element:

<Query>
  <Where>
    <Eq>
      <FieldRef Name="CatalogItem" LookupId="TRUE" />
      <Value Type="Lookup">1</Value>
    </Eq>
  </Where>
</Query>

Using Comparison Operators

To use comparison operators, you first need to join them. Logical joins are accomplished by the <Or> and <And> elements. Both elements accept two child elements. If you need to create more branches, you can use another logical join. For a three-part <Or> clause, this would look like the following:

<Or>
   <Or>
    <Eq>
     <FieldRef />
     <Value />
    </Eq>
    <Eq>
     <FieldRef />
     <Value />
  </Eq>
  </Or>
   <Eq>
     <FieldRef />
     <Value />
  </Eq>
</Or>

The equality operator <Eq> is used merely to express the syntax. Table 4-9 lists all such operators.

Joins between tables are well known in relational databases. The complexity and power of SharePoint lists overcome the need for frequent usage of joins. However, when working with views, you will find that it's occasionally desirable to add the odd field from another list. Lookups are a good way to do this, but they require navigating from the parent list to the related list in order to view fields. If you want to create a view that spans several lists and show the results in just one list, you need to use joins. Direct joins are a new feature in SharePoint 2010. Projections define the fields that become part of the result set's field list. This is comparable with the mapping of fields in the SELECT clause in SQL.

Regular lookups can be multilookups—meaning that you can form a many-to-many relationship. Using joins, this is not possible; you must use a single-value lookup.

Joins and projections have a direct representation in views and queries. This includes the corresponding properties—Joins and ProjectedField—of the SPQuery and SPView objects. While both objects use CAML, the XML snippets do not become part of a regular query, and they are not child elements of the <Query> element. Instead, the root elements are <Joins> and <ProjectedField>, respectively. The XML is assigned to the properties of the SPQuery and SPView objects without their respective root elements.

Joins

From SQL you may know that there are different kinds of joins. In CAML you can define two kinds of joins, INNER and LEFT, using the Type attribute:

<Joins>
  <Join Type="INNER" ListAlias="Authors">
   <Eq>
    <FieldRef Name="FullName" RefType="Id" />
    <FieldRef List="Authors" RefType="Id" />
   </Eq>
  </Join>
</Joins>

An inner join links two lists using an existing lookup. The result is a combination of both result sets. A left join (known in SQL as a LEFT OUTER JOIN) will return the same combination but includes the result of the parent list even if the related list does not have a matching entry. The common right join available in SQL is not supported by CAML. However, you can create an opposite lookup and reverse the parent and child lists before applying a left join.

As with SQL, in CAML you can combine several joins together. A chain of joins works like a JOIN statement in SQL but without the additional parentheses. A join B join C means that B acts as a parent for C, and A acts as a parent for the result from B and C. The default limitation for a chain of joins is eight. You can override this by setting the MaxQueryLookupFields property of the SPWebApplication object. However, keep in mind that the result set can become very large when using joined lists, and the more joins you have, the more data must be retrieved. Having more than a few (three or four) joins probably indicates an architectural mistake in your data model.

The meaning and usage of the ListAlias attribute (see the Join element in the previous sample) is worthy of a closer look. As described on MSDN, the second FieldRef, which references the actual list, must have the same name as the ListAlias attribute. That's irritating, as the term alias suggests that you can replace the internal name with something more descriptive. This is not so for the first join of chained joins or for the only join when there is just one. The name of the list must be defined using this List attribute, and the alias must correctly echo this name. If you have a second (subsequent) join to the same list that joins through to another, a conflict in the reference attribute occurs. The alias is used to resolve the conflict. However, the second join still needs a reference to an actual list. Therefore, the first FieldRef element gets yet another element List that tells the parser to which list it refers.

Projected Fields in Views

The ProjectedFields element enables fields from the foreign lists to be viewed in the list view. The fields must also be identified in the ViewFields child element of the View element. The foreign lists are identified by their aliases, as defined in the Joins element. Again, if there is only one join, then there is no alias that is different from the list's internal name. In this case, the reference in ViewFields uses the actual list name.

The ShowField attribute identifies which field from the foreign list is used in the view. This must be the internal name. The Type attribute always has the value Lookup—it does not indicate the data type of the field. The source type of a projected field is limited to a number of simple types:

The ViewFields elements refer to the name of a field as defined in the ProjectedFields' Field element.

Example: Using Joins and Projected Fields

The following example shows how to use joins and projections. It is a console application that uses two lists: Books and Authors. The Books list relates to Authors through a field called LeadAuthor. The Authors list derives from Contacts and has an additional field called FullName.

The application (Listing 4-31) retrieves data from these two lists.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Xml;

namespace Apress.SP2010.CAMLJoins
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://sharepointserve"))
            {

using (SPWeb web = site.OpenWeb())
                {
                    SPList bookList = web.Lists["Books"];
                    SPList authList = web.Lists["Authors"];
                    SPField la = bookList.Fields["LeadAuthor"];
                    SPField fa = authList.Fields["Full Name"];
                    string join = @"<Join Type='LEFT' ListAlias='Authors'>
                                         <Eq>
                                           <FieldRef Name='" + la.InternalName
                                                             + @"' RefType='ID' />
                                           <FieldRef List='Authors' Name='ID' />
                                         </Eq>
                                       </Join>";
                    string projField = @"<Field Name='Fullname' Type='Lookup'
                                           List='Authors'
                                           ShowField='" + fa.InternalName + "' />";
                    SPQuery query = new SPQuery();
                    query.Query = "";
                    query.Joins = join;
                    query.ProjectedFields = projField;
                    query.ViewFields = @"<FieldRef Name='Fullname' />
                                         <FieldRef Name='Title' />";
                    SPListItemCollection items = bookList.GetItems(query);
                    foreach (SPListItem item in items)
                    {
                        Console.WriteLine("{0} has these lead authors: ",
                                               item.Title);
                        if (item["Fullname"] == null)
                        {
                            Console.WriteLine("  no authors assigned");
                            continue;
                        }
                        SPFieldLookupValue sc =
                           new SPFieldLookupValue(item["Fullname"].ToString());
                        Console.WriteLine("  - {0}", sc.LookupValue);
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

The join is from the parent list Books to the child list Authors. This is defined by List='Authors' in the join's second FieldRef element. The ListAlias attribute must echo this, as it is the only join. The first FieldRef refers to the parent list's lookup field, using the internal name. It's read from the SPField object that has a property InternalName.

The variable pfld stores the projected fields. The declaration contains one field, FullName, from the Authors list. ShowField is also defined using the internal name. The name defined in the Name attribute is used in the ViewField definition. The Title field is also added to the result set. The XML snippets are assigned to the appropriate properties of SPQuery.

The GetItems method executes the query. Because item["Fullname"] returns a lookup, the value it returns is in the form of "#1;Joerg Krause." To process it, we create an SPFieldLookupValue object, passing the data as a string into the constructor to re-create the lookup value. From this object the LookupValue returns the full name. Figure 4-15 shows the output.

Figure 4.15. The console output of a joined result set

If you create joins and projections in a site template or in the schema.xml of a list definition, you can use the same syntax, as shown earlier. Because the XML is now placed somewhere in between all the other schema elements, the root elements <Joins>, <ProjectedFields>, and <ViewFields> must be added appropriately.

You can find a thorough explanation of templates in Chapter 7.

It is essential that you know at least the fundamental architecture of LINQ. Each data source has its own library containing methods to connect, retrieve, and update data. Figure 4-16 shows this.

Figure 4.16. LINQ architecture

LINQ includes a well-defined set of keywords that is known to the API and the language compiler. This provides an abstraction level that allows you to write LINQ queries without needing to know the internals of SharePoint data object retrieval. However, once the objects have been fetched successfully, you need to work with them. From this point, LINQ drops out of the picture, and you're back into the world of CAML and SharePoint objects.

LINQ uses keywords that are independent of the data source. The C# compiler supports this directly with new language keywords. The next example shows a simple array definition and a LINQ query to retrieve a filtered set from the array elements:

var data = new [] {0,1,2,3,4,5,6,7,8,9,10};
var res = from e in data
          where e < 5
          orderby e
          select e;

This query fetches all the numbers less than 5 from the array and stores them in the variable res. The keyword var is a variable type. Do not confuse this with object. A var type is set to a specific type at compile time by the compiler implicitly and is strongly typed then. That makes it somewhat type-safe, but you need not worry about the type before you get the data back. That's indeed crucial for working with SharePoint. Imagine you read data from a list. The list's columns might change depending on the query. The receiving variable must be robust enough to handle this. On the other hand, it's obviously a good idea to get the data from named properties instead of just string names.

The particular meaning of this small example doesn't matter here—rather, the internal structure is significant:

This syntax is geared toward human readability. from and in are mandatory and must appear in that order. The compiler converts this into the corresponding lambda expressions. These are anonymous functions that represent queries internally. If you prefer, you could write such expressions directly:

IEnumerable<int> res = data.where(e => e < 5).orderby(e => e).select(e => e);

From this format with anonymous methods, the compiler can then compile the expression into IL code.

Each extension method returns at least an object that implements the IEnumerable<T> interface. Some queries also return the IQueryable<T> interface that provides additional features. This means that every query returns enumerable objects—like a collection. Even if the collection has only one element, it's still a collection. To access a single element, you can either use an iterator keyword, such as for or foreach, or use extension methods to get the first, last, or any other element that matches particular conditions.

The operators are similar to SQL operators. Table 4-10 summarizes them.

Before you start querying SharePoint, you should have a basic understanding of LINQ. Let's examine some examples. The use of where to add a condition to a query is the most common scenario:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var res =
  from n in numbers
  where n > 5
  select n;

To sort the results, add the orderby keyword:

var res =
    from n in numbers
    where n > 5
    orderby n
    select n;

Because each keyword is translated into its extension method counterpart, you could write the previous example as follows:

var res =
  (from n in numbers
   where n > 5
   select n).OrderBy(x => x);

Grouping follows a similar format, using the GroupBy operator.

var res =
  from n in numbers
  orderby n
  group n by n > 5 into g
  select new { GreaterFive = g.Key, Numbers = g};

In this example, the numbers are grouped into two groups. One group matches the condition n > 5, and the other group contains the leftovers. There are numerous ways to return the data. Here the select clause creates an anonymous type with two properties: GreaterFive and Numbers. GreaterFive contains the group's key to identify it later, while the property Numbers contains the retrieved data itself. Remember that the whole statement returns an enumerable object. In this case, the grouping would return two elements. Let's examine a more sophisticated example that deals with remainders:

var res =
  from n in numbers
  group n by n % 3 into g
  select new { Class = g.Key, Numbers = g};

Here the numbers are grouped together in classes dependent on the remainder after they are divided by three.

Anonymous types do not imply they are "untyped." The type is not explicitly defined in code, unlike a class. As soon as the data is assigned in the statement, the type is "safe." You cannot change it later. Hence, creating new enumerable constant data is easy:

var people = new [] {
   new {Name = "Langhirt", GivenName = "Christian", Author=true},
   new {Name = "Krause", GivenName = "Jörg", Author=true},
   new {Name = "Meurisch", GivenName = "Jörg", Author=false}
 };

var authors = from person in people
             where person.Author
             select new { Name=person.GivenName, person.Name };

The anonymous type is created with the new statement. The variable people is an array containing three objects. The object has three public properties. The types are inferred by the compiler and set to string, string, and bool, respectively.

If you want a specific person from the list, a suitable function is used. The first element could be retrieved like this:

string FirstAuthor = people.First().GivenName;

The more complex your questions, the more such operators are needed. Fortunately, LINQ comes with many of them. To get a selection of combined data, the SelectMany operator is helpful:

var books = new[] {
  new {Title = "ASP.NET Professional", Authors = Ã
    new[] {new {Name = "Fischer"}, new {Name = "Krause"}}},
  new {Title = "Windows Communication Foundation (WCF)", Authors = Ã
    new[] {new {Name = "Fischer"}, new {Name = "Krause"}}},
  new {Title = ".NET 3.5", Authors = Ã
    new[] {new {Name = "Fischer"}, new {Name = "Krause"}}},
  };

var publications =
   from book in books
   where book.Authors.Count() > 0
   from author in book.Authors
   select new { book.Title, author.Name };

If you have two lists and desire a cross-joined table, LINQ is even able to handle this:

int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
int[] k = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

  var cross = from x in k
              where x > 2
              from y in i
              where y > 3
              select new {x, y, product = x*y};

Remember that cross joins return the multiplied number of elements from both tables. In the example, you have 8 times 7 numbers, causing 56 elements being returned. More likely, you will deal with regular joins that follow a defined condition.

var names = new[]
{
  new {Name = "Fischer", id = 1},
  new {Name = "Krause", id = 2}
};

var givennames = new[]
{
  new {GivenName = "Jörg", id = 2},
  new {GivenName = "Matthias", id = 1}
};

var persons =
    from name in names
    join givenname in givennames
    on name.id equals givenname.id
    select new {givenname.GivenName, name.Name};

Aggregators are functions that reduce enumerable types. The most common is Count.

int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
int count = i.Count();

The LINQ library provides some other aggregate operators, such as Sum<T>(), Min<T>(), Max<T>(), and Average<T>(). The basic methods can handle scalar types only. To deal with complex types, you can use overloads that accept lambda expressions. The expressions resolve the data to be transformed in a way the aggregator can handle it, for example:

var objects = new [] {
   new {number = 0},
   new {number = 1},
   new {number = 2},
   new {number = 3},

new {number = 4}
};

int sum = objects.Sum(x => x.number);

You will often find that a query returns only one element. Even so, the query is built using either IQuerable or IEnumable. Both are enumerable and appear as collections. To extract the one and only element from the collection, the First selector is the best approach. This can fail, though, if the collection is empty. Using FirstOrDefault calls the default(T) method if there are no elements. You will usually get a new, empty element back.

int[] a = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 }.First();

This example returns the number 5.

int[] a = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
int res = (from n
                in a
                where (n > 3 && (n & 1) == 1)
                orderby n select n).FirstOrDefault();

To use a lambda expression, the same selectors are used:

int res = (a.OrderBy(n => n)).FirstOrDefault(n => (n > 3 && (n & 1) == 1));

You can retrieve the last element with Last<T>() and LastOrDefault<T>(), respectively. However, when processing large amounts of data, more functions are required. The extension methods Take<T>(int n) and Skip<T>(int n) either aid in selecting the first specified element or skip a number of elements and begin looping through the rest after this position. Using lambda expressions, this is all possible in a conditional manner using SkipWhile<T>(Func<T,T,bool>) and TakeWhile<T>(Func<T,T,bool>).

int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
var r1 = i.Skip(5); // Skip 5 elements
var r2 = i.Take(6); // Take 6 elements
var r3 = i.SkipWhile(x => x < 6); // Skip all with x less than 6
var r4 = i.TakeWhile(x => x > 6); // Take all x greater than 6

The LINQ to SharePoint provider is defined in the Microsoft.SharePoint.Linq namespace. It translates LINQ queries into CAML queries, which means that you do not need to know how to write CAML queries or learn a different query language for each type of data source. In addition, LINQ queries can be executed against both the server and client operating models.

The gateway class for the LINQ to SharePoint provider is Microsoft.SharePoint.Linq.DataContext. It is equivalent to the System.Data.Linq.DataContext class in the LINQ to SQL provider. Just as the latter class has a GetTable method, which returns a Table<T> object that implements the IQueryable<T> interface, DataContext class has a GetList method. It returns an EntityList<T> type, which in turn implements the IQueryable<T> interface.

The following is an example of how to use LINQ to query a SharePoint list:

// Get DataContext from page context
DataContext data = new DataContext(SPContext.GetContext(this.Context).Web.Url);
// Get the SharePoint list
EntityList<Customer> Customers = data.GetList<Customer>("Lists/Customers");
// Query for customers from London
var londonCustomers = from customer in Customers
                      where customer.City == "London"
                      select customer;

You can now bind these data to some sort of control.

Query Joined Lists

One important aspect of the LINQ to SharePoint provider is that queries can be inefficient even if they appear to be well written and they produce valid results. An inefficient query is defined as one that requires more queries to the server than there are lists referenced in the query. For example, if there is a subquery to list B for every row of list A, the query is inefficient. The LINQ to SharePoint provider comes with a restriction. Such queries are not supported. If you require such a style of execution, use nested loops (and accept that it is suboptimal).

Queries that assume an implicit join between two SharePoint lists will be supported if and only if the joining field is configured as a lookup field in the SharePoint list.

Restrictions of LINQ to SharePoint Provider

The provider does not supported two LINQ operators:

Some queries cannot be completely translated into CAML. However, it is possible to execute such a query by downloading the entire SharePoint list to the client and enumerating through it to carry out the non-CAML parts of the query. You can specify whether to allow such queries by setting the DataContext.AllowInefficientQueries property to true. It is obvious that this is not an efficient way to retrieve data.

Assume you have a LINQ select clause like the following:

select new { c.Name, c.ZipCode }

This query would translate into CAML as a <ViewFields> tag with two <FieldRef> child elements. But the following select clause contains a mathematical function that is not supported in CAML:

select new { c.Price*2, c.Orders, c.Customer }

A query that contains a mathematical function or another unsupported aggregator function clause is considered inefficient. If inefficient queries have been enabled, the query will be executed, but only up to the select clause, by LINQ to SharePoint on the server. Then the results that are received from the CAML query are sent to the client as an IEnumerable object. A new LINQ query on the client will then execute the select clause projection on the object by using the System.Linq.Enumerable.Select method. From here on, the LINQ to Object provider takes over and finishes the querying. It's obvious that this handover is less efficient.

The following LINQ operators are semi-efficient:

Semi-efficient means that the provider will probably additionally to the CAML query one or more LINQ to Object operations to fulfill the task. That means additional memory consumption and CPU resources. Such queries are also called two-stage queries.

Entity Classes and the SPMetal Tool

As the LINQ to SharePoint provider gives typed access to list data, there must be a way to retrieve the column information. Reading it at runtime would decrease performance appreciably. As with other LINQ providers, the solution is a proxy class. To create such a class, a command-line tool is used. This tool, called SPMetal, reads the SharePoint list's definition and generates a proxy class. This autogenerated class could look like the following:

[List(Name="Customers")]
partial public class Customer
{
   [Column(Id=true)]
   public int CustomerId;

   [Column]
   public string City;
}

You can of course write such classes by hand, but using the tool is very helpful. It's part of the SDK for SharePoint 2010. (Refer to the section "Creating the Layer with SPMetal.exe" later in this chapter.)

Creating list proxies is probably not the most efficient way. It is of course a way that will work just fine out of the box.

You can use the gateway class, DataContext, to write changes to the SharePoint content database. Simply call the DataContext.SubmitChanges method. The following is an example demonstrating how to add an item to a list and save the changes to the database:

Customer customer = new Customer();
DataContext data = new DataContext(SPContext.GetContext(this.Context).Web.Url);
EntityList<Customer> Customers = data.GetList<Customer>("Lists/Customers");
// write data into "customer" object
Customers.Add(customer);
data.SubmitChanges();

Entity Identity

The LINQ to SharePoint provider keeps track of all entities that are returned by queries and all changes to those entities. When a specified entity is returned more than once, the provider will always return the same instance of the entity that it returned the first time. This behavior ensures that an application is always working with the same instance of a specified entity and that it never works with an entity that has been changed by another application.

When SubmitChanges executes, it compares the current state of the entity in the database with the state of the entity when it was first returned. If there is a discrepancy, then some other application has changed the entity after the first return. You can configure the behavior of SubmitChanges to stop writing more changes when it finds the first discrepancy, or you can set it to continue writing, regardless of discrepancies.

The tool is included with SharePoint Foundation and is usually located here:

%ProgramFiles%Common FilesMicrosoft Sharedweb server extensions14in

You can use this tool in any kind of batch file, typically as a prebuild command in Visual Studio. The generated code should be placed somewhere in the project to make it available for subsequent building steps.

SPMetal reads the custom lists found in the specified site and creates a class. The class is marked partial so that you can add members without worrying that your code could be overwritten by subsequent building steps. SPMetal has several options to modify the code generation:

A typical command with parameters looks like this:

SPMetal /web:http://sharepointserve /code:Authors.cs /namespace:Apress.Sp2010.Linq

This call will generate the code in C# within the current directory.

SPMetal has a few parameters, as described earlier. Several defaults apply for the process of code generation. To override those values, parameters can be supplied via an XML file. A typical file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Web AccessModifier="Internal"
     xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal">
  <ContentType Name="Contact" Class="Contact">
    <Column Name="ContId" Member="ContactId" />
    <Column Name="ContactName" Member="ContactName1" />
    <Column Name="Category" Member="Cat" Type="String"/>
    <ExcludeColumn Name="HomeTelephone" />
  </ContentType>
  <ExcludeContentType Name="Order"/>
  <List Name="Team Members" Type="TeamMember">
    <ContentType Name="Item" Class="TeamMember" />
  </List>
</Web>

The <Web> element is always the root. It supports two attributes:

Within the <Web> element, several other elements can be used to modify the behavior. Use the <List> element to manage lists. It supports two operations. First, you can include lists that SPMetal would not model by default, such as hidden lists. Second, you can change the name SPMetal uses—by default, the list's name. Supported attributes are as follows:

While <List> can include lists, <ExcludeList> excludes lists SPMetal would normally model by default. The only attribute is Name. If you want to exclude all lists and include only a few specific ones, add an empty <ExcludeOtherLists /> element and use the appropriate <List> elements. If you want to include all hidden lists, add the <IncludeHiddenLists /> element. This element cannot be used together with <ExludeOtherLists />.

Lists are made of columns, and several columns are defined in content types. The <ContentType> element can be used globally (under <Web>) or within the scope of a list (under <List>). Two attributes are supported:

To modify the <ContentType> element, columns can be added to or removed from the model using the <Column> and <ExcludeColumn> elements, respectively. An empty <ExcludeOtherColumns /> element can exclude all columns. If you want to include all hidden columns the <IncludeHiddenColumns /> element is used. To have the same function for the whole content type, the <ExcludeOtherContentType /> element can exclude all, and <IncludeHiddenContentType /> includes those with the Hidden attribute.

Once the XML file is created with the appropriate values, you can use the following syntax to assign the file:

spmetal /parameters:<path>params.xml

The basic parameters are still required and must be provided as described earlier.

SPMetal has some basic rules that apply (except where the particular default rule has been overridden). First, any class is marked public by default. You can override this with the AccessModifier attribute of the <Web> element. Second, the class is named FileNameDataContext, where FileName is the name of the file without the file extension that is specified by the /code option on the SPMetal command. For example, if /code:Personnel.cs is on the command line, the class is called PersonnelDataContext. You can override this with the Class attribute of the <Web> element. A property is generated in the DataContext-derived class for every nonhidden list in the web site. This can be overridden with the <ExcludeList> or <ExcludeOtherLists> elements. The type of the property is EntityList<(Of <(TEntity>)>). These properties are marked public. No property is generated for hidden lists. The <IncludeHiddenLists> element or a <List> element whose Name attribute is assigned to a hidden list can be used to override this behavior. A property that represents a list has the same name as the list with three exceptions. First, if there are spaces in the list name, they are removed from the property name. Note that this is different from the behavior SharePoint uses internally, such as replacing spaces with the _x0020_ string. Second, if the first letter of the list name is lowercase, it is capitalized in the property name. Third, if SPMetal judges the list name to be an English singular term, it attempts to give the property a plural version of the name. In several common cases, this is doomed, because the complex and arbitrary English pluralization rules such as child/children are not recognized correctly. We suggest overriding any tricky names with suitable values in the XML parameter file, as described earlier.

The type parameter, T, of the EntityList<(Of <(TEntity>)>)<T> property is the content type class generated for the list. If there is more than one content type associated with the list, T is the class that represents the basic Item content type of SharePoint. Use the Type attribute of the <List> element to override this.

A content type class named Item is generated to represent the basic SharePoint content type. You can override this with the <ExcludeContentType> element for that content type. For any list that has not been excluded, a content type class is generated for every defined content type assigned to the list. Again, overriding this behavior is possible using the <ExcludeContentType> or <ExcludeOtherContentTypes> element. For any defined content type, including the basic Item content type, the generated class has the same name as the content type. You can override with the Class attribute of a <ContentType> element. For any list that does not use a defined content type but to which one or more columns have been added, a class is generated to represent the implied content type. This means that the generator assumes a generic content type to use the same rules. The class that represents an implied content type will be named listpropertynameItem, where listpropertyname is the name of the property that represents the list.

If a content type inherits from another, the generator creates the same hierarchy for the classes. The classes are marked both partial and public. Use the AccessModifier attribute of a <ContentType> element to override this behavior. There is no way to suppress the partial modifier.

The class that represents the basic Item content type implements ITrackEntityState, ITrackOriginalValues, INotifyPropertyChanged, and INotifyPropertyChanging interfaces. A property is generated in a content type class for every nonhidden field. You can override this with the <ExcludeColumn> or <ExcludeOtherColumns> element. The property is marked public. It has the same name as the column it represents, but spaces in a column name are removed.

If the first letter in the column name is lowercase, it is made uppercase in the property name. The type of a column property is determined from the property of the field in accordance with the mapping presented in SharePoint to .NET Type Mapping. You can override this with the Type attribute of a <Column> element. No property is generated for columns that are on the list, but not in the content type. The same applies to hidden columns, except the hidden Id and Version columns, which are always present.

For every Choice field that does not allow Fill-in choices, an enumerated class is generated. For every MultiChoice field that does not allow Fill-in choices and has fewer than 31 choices, an enumerated class decorated with the [Flags] attribute is generated. The values Invalid and None are added as possible values to the enumerations. Additionally, a sealed class is generated with a String constant for each defined value.

To represent lookup list relationship fields' names, EntityRef, EntitySet, and LookupList are generated. For every column that is a lookup to a field on another list and that does not allow multiple values, the property that represents the column wraps a private field of type EntityRef<(Of <(TEntity>)>)<T>, where T is the content type of the lookup list. If the lookup to a field allows multiple values, the property that represents the column wraps a private field of type EntitySet<(Of <(TEntity>)>)<T>, where T is the content type of the lookup list.

When talking about the content type and its representation in .NET classes, the type mapping is worth a closer look. Table 4-11 shows the complete mapping list.

With this information in mind, using LINQ with SharePoint seems to be easy and smart.

Assuming that a proxy class created by SPMetal exists, the following example (see Listing 4-32) shows the entries of items in the list named Authors.

using System;
using System.Linq;
using Microsoft.SharePoint;

namespace Apress.SP2010.Linq
{
    class Program
    {

static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://sharepointserve"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    DatacontextDataContext ctx =
                       new DatacontextDataContext("http://sharepointserve/");
                    var authors = from a in ctx.Authors
                                  select a;
                    foreach (var ac in authors)
                    {
                        Console.WriteLine("{0}, {1}, {2}",
                                            ac.Company,
                                            ac.FirstName,
                                            ac.EMail);
                    }
                    Console.ReadLine();

                }
            }
        }
    }
}

The DatacontextDataContext class is generated with SPMetal using the default settings. The LINQ query does not have any options and fetches the complete list. The object is retrieved directly using the syntax select a, where a is the loop variable defined in the LINQ query. In the foreach statement, the current author is held in the loop variable ac. The type defined by the context is AuthorsContact, the name constructed from the current list (Author) and the base content type (Contact). The loop could also look like this:

foreach (AuthorsContact ac in authors)

However, the var type is both short and type-safe and is the preferred way to express variables and to improve readability. Instead of accepting the type defined by the list, you can easily create a new anonymous type that fits your needs, as shown in Listing 4-33.

var authors2 = from a in ctx.Authors
               select new
               {
                   Firm = a.Company,
                   Name = a.FullName,
                   Mail = a.EMail
               };
foreach (var ac in authors2)
{
    Console.WriteLine("{0}, {1}, {2}", ac.Firm, ac.Name, ac.Mail);
}

The type created by select new is anonymous and contains three public properties (Firm, Name, Mail). The properties types are determined from the source type (a.Company and so on). This is still type-safe, but you don't have to care about the types.

To read a joined list, you can use the join operator. The order of the join is vital. Creating an output type is a common technique to get exactly what you want (see Listing 4-34).

EntityList<AuthorsContact> authorsj = ctx.GetList<AuthorsContact>("Authors");
EntityList<BooksItem> booksj = ctx.GetList<BooksItem>("Books");
var result5 = from book in booksj
              join author in authorsj on book.LeadAuthor.Id equals author.Id
              select new
              {
                  Book = book.Title,
                  Author = author.FullName
              };
result5.ToList().ForEach(ab => Console.WriteLine("{0} was written by {1}",
    ab.Book,
    ab.Author));

The code produces the output shown in Figure 4-17. The conversion with ToList was used only to get access to the ForEach method for easy output.

Figure 4.17. Output produced by the join example

Another strategy is to use EntityList to get a list representation, to which you can add new elements (see Listing 4-35).

EntityList<AuthorsContact> list = ctx.GetList<AuthorsContact>("Authors");
foreach (var ac in list)
{
   Console.WriteLine("{0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail);
}
EntityList<AuthorsContact> list2 = ctx.GetList<AuthorsContact>("Authors");
AuthorsContact newAuthor = new AuthorsContact();
newAuthor.FirstName = "Bernd";
newAuthor.Title = "Pehlke";
newAuthor.EMail = "[email protected]";

newAuthor.Company = "Computacenter";
list2.InsertOnSubmit(newAuthor);
ctx.SubmitChanges();
foreach (var ac in list2)
{
    Console.WriteLine("{0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail);
}

The foreach loop is merely to show that the element has been successfully added. The new item is created using the type AuthorsContact and its default constructor. Once all required properties are filled in with appropriate values, the element can be added using the InsertOnSubmit method. As the name implies, the SubmitChanges method invoked on the data context writes the data into the SharePoint database.

Deleting an item is similar to the insertion procedure. The item is part of a list. Hence, the DeleteOnSubmit method can be found in the list's class, Authors, as in Listing 4-36.

var authors3 = from a in ctx.Authors
               where a.Title.Equals("Pehlke")
               select a;
foreach (var ac in authors3)
{
   ctx.Authors.DeleteOnSubmit(ac);
   Console.WriteLine("Delete: {0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail);
}
ctx.SubmitChanges();

The context class sends the delete task to the list with the SubmitChanges call. Instead of deleting an item, you can use RecycleOnSubmit to remove the item and put it into the Recycle Bin list (see Listing 4-37).

var authors4 = from a in ctx.Authors
               where a.Title.Equals("Pehlke")
               select a;
foreach (var ac in authors4)
{
   ctx.Authors.RecycleOnSubmit(ac);

Console.WriteLine("Recycled: {0}, {1}, {2}",
                      ac.Company, ac.FullName, ac.EMail);
}
ctx.SubmitChanges();

Updating an item is similar and simply requires access to a single item to change its properties and call the SubmitChanges method.

When changing or accessing all items, a foreach loop is the only way. All previous examples use this pattern. However, the extensibility model allows a custom definition of such a ForEach pattern, in the same style as ForEach method supported by the List<T> and Array<T> types.

ctx.Authors.ForEach<AuthorsContact>(ac => ac.Company = "Microsoft");
ctx.SubmitChanges();
ctx.Authors.ForEach<AuthorsContact>(ac => Console.WriteLine("{0} at {1}",
                                          ac.FirstName, ac.Company));

In this example, a generic ForEach method is used to invoke an Action<T>. Each element of the collection is used to call the action defined in the lambda expression. System.Action is a predefined delegate that has no return value. The definition encapsulates the foreach statement:

public static class Extensions
{
   public static void ForEach<T>(this IQueryable<T> source, Action<T> func)
   {
      foreach (var item in source)
         func(item);
   }
}

This defines the ForEach extension method for the commonly used type IQueryable. The Action parameter takes any lambda expression. A return value—if any—is thrown away. In the previous example, the method is used to change a property and to write the content to the console. Assuming the extension method is defined once somewhere in your project, the LINQ lines are obviously shorter and nonetheless perfectly readable.

Sometimes you deal with detached data. That happens if you serialize an object and send it over the wire to some other application. If you work with Windows Communication Foundation (WCF) connections, Workflow Foundation (WF), or web services at all, this can happen. You receive back, according to the previous examples, an object of type AuthorsContact, but it is not generated in the context of the previous class. Inserting such an object using the InsertOnSubmit method is risky. If the same object already exists, the tracking does not matter. If all properties are not unique, this will work, but the object is now in the list twice. If the list requires unique properties, such as an ID, an exception is thrown. To avoid such behavior, you can use the Attach method. Attaching means that the item is inserted with tracking. (See the section "Track Changes" to read more about how the conflict resolver works internally to deal with such situations.)

As shown at the beginning of the chapter, the LINQ to SharePoint provider translates the LINQ query into CAML. (The CAML query is converted again into SQL clauses.) As a result, you need to be aware of what CAML is able to express efficiently. Simple select constructs, whether they use anonymous or typed objects, are always efficient. However, if the statement contains a formula you can't express with CAML but is allowed in LINQ, the query is rejected. You can, however, take advantage of another LINQ provider—LINQ to Objects. LINQ to Objects is not recommended if the amount of data retrieved from a list is high. If you access a list's item collection directly, the whole list is held in memory. If this is on a per-session basis, it could have a detrimental effect on performance. If you put a result set into the cache, this might work, but you lose the ability to issue per-user queries.

To overcome such limitations, you can retrieve the data first using either CAML directly or LINQ to SharePoint. This should reduce the number of items drastically. The result is then copied to an IEnumerable type that you can easily filter using LINQ to Objects. Copy your results using a method such as ToList<T> (see Listing 4-38), ToDictonary<T>, ToArray<T>, or Cast<T>.

DataContext data = new DataContext("http://sharepointserve");
DataContext subData = new DataContext("http://sharepointserve/SubTeamSite");

EntityList<Announcement> announcement =
                       data.GetList<Announcement>("Announcements");
EntityList<Announcement> subannouncements =
                       subData.GetList<Announcement>("Announcements");
List<Announcement> annsList = (from ann in announcements
                                 select ann).ToList();
List<Announcement> annsSubList = (from ann in subannouncements
                                  select ann).ToList();

IEnumerable<Announcement> allAnnotations = annsSubList.Union(annsAnns);

foreach (Announcement ann in allAnnotations)
{
    Console.WriteLine(ann.Title);
}

This example uses the LINQ to SharePoint provider to get two result sets from lists. Both results are copied to List<T> objects. They are combined using the Union method, which SharePoint does not support directly.

Splitting queries into two parts can be cumbersome work. Fortunately, some of such operations are done internally. Such operations are called semi-efficient queries.

The generation of the LINQ layer is a onetime process. If you run your layer in an environment where users can add fields or where custom field types are used, the layer can't handle this. The reason for the good performance of the LINQ provider is at the same time its biggest weakness—the statically generated classes.

Apart from custom fields, the generator oversees public properties too. Internally SPMetal reads the content type and its fields. Properties that the list provides by default, such as Attachments, are not recognized.

To overcome this limitation, you can use a custom mapping that provides new public properties and handle the calls to the provider's internal data handling. To attach new properties, the generated class is marked as partial. The first step is to create a new class with the same name and is also marked as partial. This new class implements the ICustomMapping interface. The methods the interface requires and the properties you add create the mapping.

public partial class Book : ICustomMapping
{
  [CustomMapping(Columns = new String[] { "ISBN", "UPCA" })]
  public void MapFrom(object listItem)
  {
    SPListItem item = (SPListItem)listItem;
    this.ISBN = item["ISBN"];
    this.UPCA = item["UPCA"];
  }

  public void MapTo(object listItem)
  {
    SPListItem item = (SPListItem)listItem;
    item["ISBN"] = this.ISBN;
    item["UPCA"] = this.UPCA;
  }

  public void Resolve(RefreshMode mode,
                      object originalListItem,
                      object databaseObject)
  {
  }

    // New property declarations go here.

}

The custom mapping is managed by the CustomMapping attribute. The Columns array contains the mapped columns.

This does not address how to handle columns added by users after your code has been deployed. To handle "any" column, you can use a placeholder such as the following:

[CustomMapping(Columns = new String[] { "*" })]

The next example shows how to handle such columns properly:

[CustomMapping(Columns = new String[] { "*" })]
public void MapFrom(object listItem)
{
    SPListItem item = (SPListItem)listItem;
    foreach (var field in item.Fields)
    {

this.Properties[field.InternalName] = item[field.InternalName];
    }
}

public void MapTo(object listItem)
{
    SPListItem item = (SPListItem)listItem;
    foreach (var kvp in this.Properties)
    {
        item[kvp.Key] = this.Properties[kvp.Key];
    }
}

The ICustomMapping methods can also be used to map properties to hash table entries of the Properties property the generated class provides:

[CustomMapping(Columns = new String[] { "*" })]
public void MapFrom(object listItem)
{
    this.PreviousManager = ((SPListItem)listItem).Properties["PreviousManager"];
}

public void MapTo(object listItem)
{
    ((SPListItem)listItem).Properties["PreviousManager"] = this.PreviousManager;
}

Managing Concurrency Conflicts for the New Columns

To ensure that your properties are participating in the object change tracking system, check that the set accessor of the properties is calling the content type class's OnPropertyChanging and OnPropertyChanged methods, as shown in the following example. These methods are part of the code generated by SPMetal. They handle the PropertyChanging and PropertyChanged events, respectively. The following is an example for one of the columns discussed earlier in this topic that uses a custom field type. Note the custom field type is ISBNField in this example.

public ISBNField ISBN
{
    get
    {
        return iSBN;
    }
    set
    {
        if ((value != iSBN))
        {
            this.OnPropertyChanging("ISBN", iSBN);
            iSBN = value;
            this.OnPropertyChanged("ISBN");
        }
    }
}

The LINQ to SharePoint provider checks changes made in the database against its current state. This is the default behavior. If you access the lists in a read-only manner, the tracking can be suppressed to optimize performance:

ctx.ObjectTrackingEnabled = false;

As a developer, you must always be aware of changes made by other applications, including the standard SharePoint UI. LINQ to SharePoint uses so-called optimistic concurrency to resolve conflicts. If a discrepancy has been found, the provider stops writing data back to the database. The SubmitChanges method has a parameter ConflictMode to control the behavior:

ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
ctx.SubmitChanges(ConflictMode.FailOnFirstConflict);

In either case, data is written, but if the option opts to continue any further, the same data record is tried again. Optimistic means that the operation does not fail definitely. The user can decide how to proceed and any possible actions are allowed—cancel the change or overwrite existing data. That means that your code must provide a way to inform the user and let them decide what to do or assume a default action. To help you to do the right thing, the EntityState property is used. In a proxy class created by SPMetal, it looks like this:

[Microsoft.SharePoint.Linq.ContentTypeAttribute(Name="Item", Id="0x01")]
public partial class Item : ITrackEntityState, ITrackOriginalValues,
                            INotifyPropertyChanged, INotifyPropertyChanging
{
    private EntityState _entityState;

    private IDictionary<string, object> _originalValues;

    EntityState EntityState
    {
        get
        {
            return this._entityState;
        }
        set
        {
            this._entityState = value;
        }
    }

    IDictionary<string, object> OriginalValues
    {
         get
         {
             return this._originalValues;
         }
         set
         {
             this._originalValues = value;
         }
    }

    public Item()

{
        this._entityState = EntityState.Unchanged;
        this.OnCreated();
    }

}

As shown in the exhibit from SPMetal-generated code, the EntityState property is private. You may wonder how to get the information directly from the code. The state is relevant only during a conflict. Such a conflict throws a ChangeConflictException. The exception details expose some information that's internally retrieved from EntityState and OriginalValues properties. The following example triggers the exception by writing values to the same item using two different data contexts:

AuthorDataContext ctx2 = new AuthorDataContext(ctx.Web);
var a1 = (from a in ctx2.Authors where a.Title.Equals("Krause") select a).First();
a1.Title = "Krause (LastName)";
// Change same with another value in default context
var a2 = (from a in ctx.Authors where a.Title.Equals("Krause") select a).First();
a2.Title = "Krause (Title)";
try
{
   ctx2.SubmitChanges();
   ctx.SubmitChanges();
}
catch (Microsoft.SharePoint.Linq.ChangeConflictException ce)
{
   foreach (var cc in ctx.ChangeConflicts)
    {
        Console.WriteLine("Conflict for {0}", cc.Object);
        if (cc.MemberConflicts.Count() > 0)
        {
            foreach (MemberChangeConflict mcc in cc.MemberConflicts)
            {
                Console.WriteLine(" Current: {0}, Database: {1}, Original: {2}",
                    mcc.CurrentValue,
                    mcc.DatabaseValue,
                    mcc.OriginalValue);
            }
        }
    }
}

Using a second data context, the same LINQ query ensures that the same item is fetched from the database twice. The Title property is changed to two different values within the two separate contexts. The conflict occurs when the second SubmitChanges method is called. This throws the ChangeConflictException, which is caught. The ChangeConflict property of the second context (ctx) exposes all the information you need in order to decide how to proceed (see Figure 4-18).

Figure 4.18. Investigating conflict information reveals current, old, and original values

As shown in the previous example, you can easily use multiple data context objects against the same site. As long as all contexts but one are read-only, there should be no conflicts. The read-only contexts could also be optimized by switching off the tracking:

ctx.ObjectTrackingEnabled = false;

Resolving Conflicts

If writing from multiple contexts is necessary for your business layer or if changes from other parties are expected, the ChangeConflict property (of MemberChangeConflict type) provides everything you need. Within the object, the property MemberConflicts resolves the conflict separately for each field (see Table 4-12).

To resolve a conflict, call the Resolve method for each item in MemberConflicts collection. The parameter expects a value from RefreshMode enumeration:

It is worth noting that all changed properties are exposed in this collection, even if only one property throws the exception. That's necessary to resolve conflicts, because returning to the original value of the conflicting item may require doing so for all or some other properties. But it's up to you to resolve multiple conflicts.

Using SPMetal is powerful and provides a great set of features to access SharePoint lists with type-safe objects. However, under rare circumstances, it may be necessary to define your own classes. This is, typically, the case if the lists are themselves based on an external definition. Such a definition might use XML Schema (XSD) to define lists and their data types. The schema can be distributed with the feature, and it should support both the list creation as well as the data access layer using LINQ to SharePoint.

At least two attributes are required to decorate the classes and properties appropriately:

[ContentType(Name="Announcement", Id="0x0104")]
public partial class Announcement
{
    [Column(Name = "Title", FieldType = "Text", IsId=true)]
    public String Title { get; set; }
}

In this example, the only field you can retrieve is Title. Consequently, you are not limited to the fields defined by the underlying content type in both directions. You can add more fields, and you can leave columns you don't need. However, if a column is not defined as a property, it is not available from the LINQ provider.

When you create a lookup, you can choose to enforce referential integrity between elements in the related lists, as shown in Figure 4-19.

Figure 4.19. Enforcing referential integrity

There are two options available here: Restrict Delete and Cascade Delete. The Restrict Delete option enables you to enforce that you cannot delete from the list any item that has related data. For example, if the item you are trying to delete has one or more child items, you cannot delete the item. Cascade Delete, on the other hand, means that when you're trying to delete an item with related data, it will delete the item and the related items as well. If there is no such option, the related data becomes orphaned.

When working with such lists, it's sometimes helpful to know about the current settings before starting a particular action (see Listing 4-39).

using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {

        SPRelatedFieldCollection RelatedFields =
                                      web.Lists["Authors"].GetRelatedFields();
        foreach (SPRelatedField RelatedField in RelatedFields)
        {
            Console.WriteLine("Field <{0}>{5}   

                               bound to <{1}>{5}  

                               lookup on <{2}>{5}   

SPRelationshipDeleteBehavior.{3}{5}Web <{4}>",

                web.Lists[RelatedField.ListId].
                                    Fields[RelatedField.FieldId].InternalName,
                web.Lists[RelatedField.ListId].Title,
                RelatedField.LookupList,
                RelatedField.RelationshipDeleteBehavior,
                site.AllWebs[RelatedField.WebId].Title,
                Environment.NewLine);
        }

    }
}

In this code, the GetRelatedFields method is used to get access to the current related data. The SPRelatedField type returns mostly GUIDs of the particular objects. These are used to resolve the names. The code produces the output shown in Figure 4-20.

Figure 4.20. Show a list's relationships.

You can use the same approach to establish a relationship programmatically. This is typically part of a complete list creation process (see Listing 4-40). Usually, you do this as part of a feature to configure an existing SharePoint site properly.

string lookupFieldName = "RelatedField";
using (SPSite site = new SPSite("http://sharepointserve"))
{
    using (SPWeb web = site.OpenWeb())
    {
        SPListCollection lists = web.Lists;
        Guid SourceListId = lists.Add("Parent List",
            "",
            SPListTemplateType.GenericList);
        Console.WriteLine("Parent List Done...");
        Guid TargetListId = lists.Add("Child List",
            "",
            SPListTemplateType.GenericList);
        Console.WriteLine("Child List Done...");
        SPList SourceList = lists[SourceListId];
        SPList TargetList = lists[TargetListId];

SPFieldCollection Fields = TargetList.Fields;
        Fields.AddLookup(lookupFieldName, SourceList.ID, true);
        Console.WriteLine("Lookup Field Created");
        SPFieldLookup NewLookupField = Fields[lookupFieldName] as SPFieldLookup;
        NewLookupField.Indexed = true;
        NewLookupField.LookupField = "Title";
        NewLookupField.RelationshipDeleteBehavior =
                                   SPRelationshipDeleteBehavior.Restrict;
        NewLookupField.Update();
        Console.WriteLine("Lookup field integrity enforced");
        SPListItem NewSourceItem = SourceList.Items.Add();
        NewSourceItem["Title"] = "Parent Data";
        NewSourceItem.Update();
        Console.WriteLine("Source listitem created");
        SPListItem NewTargetItem = TargetList.Items.Add();
        NewTargetItem["Title"] = "Child Data";
        NewTargetItem[lookupFieldName] = new SPFieldLookupValue(1, "Source Data");
        NewTargetItem.Update();
        Console.WriteLine("Parent listitem created");
        TargetList.Update();
        SourceList.Update();
    }
}

You can test the relationship by deleting an item in the parent list either from code or from the UI. In code, use a try...catch clause to catch the exception and get an error description, as shown in Figure 4-21.

Figure 4.21. Error shown if a user tries to delete an item with relational integrity turned on

Furthermore, some properties exist to control the remaining aspects of a list. These definitions are set in the SPField object. Assume you have an SPField object called myField. To activate indexing on this column, you can write the following:

myField.Indexed = true;

To enforce unique values, set this property:

myField.AllowDuplicateValues = false;

In both cases you must call myField.Update() to effect the change.

The world of LINQ is much bigger than we can convey here. You are sure to find several ways to work with LINQ to SharePoint in real-life projects. To deal with errors or unexpected behavior, you need to know what CAML code is produced from your LINQ code. This is straightforward using the data context's Log property, as shown in Listing 4-41.

StringBuilder sb = new StringBuilder();
TextWriter tw = new StringWriter(sb);
ctx.Log = tw;
// Any LINQ activity goes here
EntityList<AuthorsContact> authorsj2 = ctx.GetList<AuthorsContact>("Authors");
EntityList<BooksItem> booksj2 = ctx.GetList<BooksItem>("Books");
var result6 = from book in booksj2
              join author in authorsj2 on book.LeadAuthor.Id equals author.Id
              select new
              {
                  Book = book.Title,
                  Author = author.FullName
              };
result6.ToList().ForEach(ab => Console.WriteLine("{0} was written by {1}",
    ab.Book,
    ab.Author));
// End of LINQ activity
Console.WriteLine(sb.ToString());
tw.Dispose();

In this example, a joined list is queried, and this will produce the following CAML:

<View>
  <Query>
    <Where>
      <And>
        <BeginsWith>
          <FieldRef Name="ContentTypeId" />
          <Value Type="ContentTypeId">0x0100</Value>
        </BeginsWith>
        <BeginsWith>
          <FieldRef Name="LeadAuthorContentTypeId" />
          <Value Type="Lookup">0x010600</Value>
        </BeginsWith>
      </And>
    </Where>
    <OrderBy Override="TRUE" />
   </Query>
   <ViewFields>
     <FieldRef Name="Title" />
     <FieldRef Name="LeadAuthorFullName" />
   </ViewFields>

<ProjectedFields>
     <Field Name="LeadAuthorFullName" Type="Lookup" List="LeadAuthor"
            ShowField="FullName" />
     <Field Name="LeadAuthorContentTypeId" Type="Lookup" List="LeadAuthor"
            ShowField="ContentTypeId" />
   </ProjectedFields>
   <Joins>
     <Join Type="INNER" ListAlias="LeadAuthor">

       <!--List Name: Authors-->
       <Eq>
         <FieldRef Name="LeadAuthor" RefType="ID" />
         <FieldRef List="LeadAuthor" Name="ID" />
       </Eq>
     </Join>
   </Joins>
   <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

If you're speaking LINQ more fluently than CAML, it's a good way to learn CAML. However, there are some verbose parts in this query, such as the restriction to a specific content type.