How to: Query Using LINQ to SharePoint

Applies to: SharePoint Foundation 2010

This topic explains how to query Microsoft SharePoint Foundation lists by using the LINQ to SharePoint provider.

Step 1: Getting a Reference to a Web Site

All coding against the LINQ to SharePoint provider must begin by creating a DataContext object. This object represents a subset of a content database, specifically, the lists and list items of a SharePoint Foundation Web site. The simplest way to create such an object is to pass the absolute URL of the Web site you want to query, as a literal string, to the class constructor , as shown here.

DataContext teamSite = new DataContext("http://MarketingServer/SalesTeam");

More commonly, however, your solution will need to run against many sites on many farms and you will not know the complete URL when you are writing your code. If your code runs in any situation in which there is an HTTP context, such as in a Web Part or custom application page, and you are querying against the current Web site, you can use the SPContext object to get the URL, as shown in this example.

DataContext teamSite = new DataContext(SPContext.Current.Web.Url);

You can also use the context to get, indirectly, the URLs of other Web sites within the site collection, or even the other site collections of the Web application. Site The following code, for example, creates a DataContext for the top level Web site of the oldest site collection in the Web application.

String rootOfOldestURL = this.Site.WebApplication.Sites[0].RootWeb.Url;      
using (DataContext topSiteOfOldestSiteCollection = new DataContext(rootOfOldestURL))
{
}

Notice that you must dispose the DataContext object because it is using a different SPWeb object from the one supplied by the SPContext.

For other ways to get references to Web sites within a SharePoint Foundation farm, see Getting References to Sites, Web Applications, and Other Key Objects.

If there is no HTTP context, such as in a console application, and the name of the server is not known when you are coding, you can use the "localhost" alias to get a reference to the root Web site, as shown in the following examples.

using (DataContext topLevelSite = new DataContext("https://localhost"))
{
}

using (DataContext mySite = new DataContext("https://localhost/sites/MySite"))
{
}

Since there is no HTTP context, you should dispose the DataContext object.

You can derive a class from DataContext, in which case you would use your class’s constructor, as shown in the following example.

ContosoTeamData teamSite = new ContosoTeamData(SPContext.Current.Web.Url);

Step 2: Getting a Reference to a List

Use the GetList<T>(String) method to get an EntityList<TEntity> object. This object is an IQueryable<T> representation of a list. The following is an example.

EntityList<Announcement> announcements = teamSite.GetList<Announcement>("Announcements")

Note that the content type of the list must be represented by an explicitly declared class, in this case "Announcement". The class must be decorated with a ContentTypeAttribute that specifies the name of the content type in the SharePoint Foundation Web site and the ID of the content type. In general, you should know what lists your code is going to query when you are writing your code. At a minimum, the class that represents the content type must include at least one property that represents a column in the list, and that property declaration must be decorated with a ColumnAttribute that specifies at least the name of the field and its type. The following example shows the minimum declarations required to enable calling code to call the GetList<T>(String) (where T is Announcement) method.

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

But a query can only refer to columns that are represented by properties in the content type class, so if only this minimum declaration is provided, your calling code could only refer to the Title field (column), as shown in this example.

var excitingAnnouncements = from announcement in announcements
                            where announcement.Title.EndsWith("!")
                            select announcement;

Content types can inherit from other content types, and for the call to the GetList<T>(String) method, you can use, as the type parameter, any content type that is higher in the inheritance tree. For example, because all content types derive from the basic Item content type, you can use Item as the type parameter, as shown in this example:

EntityList<Item> announcements = teamSite.GetList<Item>("Announcements")

Your source code must provide a declaration of an Item class, and that class must declare properties for each of the columns in the content type that your queries reference.

If you are using the Item content type, you can query lists without knowing the name of the list or its derived content type when you are coding, as shown in this example.

DataContext topLevelSite = new DataContext("https://localhost");
SPSite siteCollection = new SPSite("https://localhost");
EntityList<Item> someList = topLevelSite.GetList<Item>(siteCollection.RootWeb.Lists[0].Title);

var first3Items = from item in someList
                  where item.Id <= 3
                  select item;

foreach (var item in first3Items)
{
    Console.Writeline("{0} is one of the first 3 items in {1}", item.Title, someList.Title);
}

However, most realistic scenarios of useful LINQ to SharePoint coding will involve the columns that are unique to particular lists, so as a practical matter you need to know the list names and their specific derived content types. Specifically, when you are coding, you must be able to assume that certain lists are present on the Web sites that your query code will be run against. Moreover, because your queries of a specified list will refer to particular columns, you must be able to assume, for a specified list, that a certain subset of columns will be present on the list. This implies that your SharePoint Foundation solution will be one of two types:

• It will be designed to query well known list types that are included with SharePoint Foundation or that are included with enhanced functionality products such as Microsoft SharePoint Server.

• It will be developed in conjunction with, and installed together with, a set of one or more custom lists as part of a feature set or a custom site definition.

We recommend that you use the SPMetal tool to generate the needed class and property declarations.

Step 3 (optional): Turning Off Object Change Tracking

If your code only queries lists, rather than adding, deleting, or editing list items, then you can turn off object change tracking. Doing so will improve performance. Set the ObjectTrackingEnabled property to false.

teamSite.ObjectTrackingEnabled = false;

Step 4: Define the LINQ Query

The value of LINQ is that queries are written in essentially the same way, regardless of the data source or the LINQ provider. Except for slight differences in how a reference to the data context and the IQueryable<T> object are obtained, LINQ to SharePoint queries are the same as queries that you would use for LINQ to SQL or LINQ to XML. For more information, see LINQ to SQL: .NET Language-Integrated Query for Relational Data and LINQ to XML.

However, no two LINQ providers are exactly the same. The differences in the native query languages of the data sources (into which LINQ queries are translated by the provider) sometimes force different limitations on query possibilities. In particular, there is a limit on list joins, whether implicit or explicit, in queries that use the LINQ to SharePoint provider. A LINQ to SharePoint query can join two lists, either explicitly or implicitly, but only if one has a Lookup type column that looks up to a column in the other table. If the Lookup field allows only one value, this relationship between the lists must be represented in your code with an EntityRef<TEntity> field in the class that represents the content type of the list. If the field allows multiple values, the relationship must be represented with an EntitySet<TEntity> field and an EntitySet<TEntity> property that wraps it.

#if DEBUG
teamSite.Log = Console.Out;
#endif

Step 5: Enumerate the Query Results

As with all LINQ Providers, a LINQ to SharePoint query is not executed until it is enumerated. This usually occurs in a foreach loop. In an unusual case where you require custom enumeration, such as skipping every other item in the result, you can use the methods of IEnumerable and IEnumerator.

You can also assign the results of the query to an IEnumerable<T> variable such as an IList<T> or an ICollection<T> object, instead of the anonymous type var (Dim in Visual Basic). This causes the query to execute immediately so that the variable can be populated. The following is an example:

EntityList<Announcement> announcements = teamSite.GetList<Announcement>("Announcements")

IList<Announcement> excitingAnnouncements = from announcement in announcements
                                            where announcement.Title.EndsWith("!")
                                            select announcement;

Step 6 (optional): Merge Results from Multiple Lists and Multiple Data Sources

You can merge results from more than one list into a single IList<T> which can then be further filtered by using LINQ to Objects. The following example shows how to produce an IList<T> of both corporate and team events and how to generate a report of the current day’s events that take place in an auditorium.

DataContext corpSiteData = new DataContext("https://localhost/CorpSite");
DataContext markTeamData = new DataContext("https://localhost/Marketing");

EntityList<Event> allCorpEvents = corpSiteData.GetList<Event>("Calendar");
EntityList<Event> allMarkTeamEvents = markTeamData.GetList<Event>("Calendar");

List<Event> todaysCorpEvents = (from ev in allCorpEvents
                  where ev.StartDate = DateTime.Now
                  select ev).ToList();

List<Event> todaysTeamEvents = (from ev in allMarkTeamEvents
                  where ev.StartDate = DateTime.Now
                  select ev).ToList();

IEnumerable<Event> mergedEvents = todaysCorpEvents.Union(todaysTeamEvents);

var todaysAuditoriumEventTitles = from ev in mergedEvents
                  where ev.Location.Contains("Auditorium")
                  select new { ev.Title };

foreach (var eventTitle in todaysAuditoriumEventTitles)
{
    Console.WriteLine(eventTitle.Title);
}

The two IList<T>objects must have the same type parameter.

Step 7 (optional): Join Results from Multiple Data Sources

The technique used in step 7 to merge data from multiple SharePoint Foundation lists can be used to merge data from SharePoint Foundation lists with data from other sources, provided that the records from the other source are castable to the class that represents the SharePoint Foundation content type. For example, suppose that your code uses LINQ to SQL to produce an IList<T> (where T is Client) object named oldClients and suppose it uses LINQ to SharePoint to produce a IList<T> (where T is Patient) object named activePatients. If the properties and other members of the Client class are a subset of the members of the Patient class and the corresponding members have the same signatures, then you could merge the data from the SQL source with the data from the SharePoint Foundation list as shown in this example.

foreach (Patient patient in activePatients)
{
    oldClients.Add((Client)patient);
}

An even better option when you need to merge data from two different LINQ providers is to use the same item type class for both. This is possible because you can put the attribute decorations that are needed for LINQ to SharePoint and those needed for another LINQ provider on the same class declaration. The following example shows the signature of a class declaration that is decorated with LINQ to SharePoint’s ContentTypeAttribute and LINQ to SQL’s TableAttribute.

[ContentType(Name="Item", Id="0x01" List="Customers")]
[Table(Name = "Customers")]
public partial class Customer : Item
{
}

With these attributes in place, no type casting is needed to merge data that comes from the SQL table with data from the SharePoint Foundation list.

See Also

Reference

SPMetal

ColumnAttribute

ContentTypeAttribute

DataContext

EntityList<TEntity>

EntityRef<TEntity>

EntitySet<TEntity>

ICollection<T>

IEnumerable

IEnumerator

IList<T>

IQueryable<T>

SPContext

SPWebApplication

TableAttribute

Concepts

Content Type IDs

Unsupported LINQ Queries and Two-stage Queries

Getting References to Sites, Web Applications, and Other Key Objects

How to: Write to Lists Using LINQ to SharePoint

Other Resources

LINQ to Objects

LINQ to SQL: .NET Language-Integrated Query for Relational Data

LINQ to XML

Web Parts Overview