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.
Note
References to an SPWebApplication object require a using statement (Imports in Visual Basic) for the namespace Microsoft.SharePoint.Administration.
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;
Note
As this example shows, the fact that the class is named Announcement does not mean it must exactly mirror the SharePoint Foundation content type of the same name. This means that you are not required to have a property for every column of the content type. The class may represent only a subset of the content type’s columns, and it can have additional members as well. This is, in fact, a critical characteristic of the LINQ to SharePoint provider, because columns can be added to lists by site owners and doing so, in effect, creates a new content type for the list. The class also is not required to have the same name as the content type of the list. It can have any name, as long as that same name is used as the type parameter in the call to the GetList<T>(String) method. But your code is usually more readable if the class has same name as the official content type of the list. By default, the SPMetal tool follows this practice.
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.
Tip
The Log property is a TextWriter that can write the CAML query into which your LINQ query is translated. Being able to see the CAML query can be a helpful when debugging. To do this, assign a TextWriter object to the Log property. In the following example, the OutTextWriter is assigned to Log. This causes the CAML query to appear on the console when a LINQ query is executed in a console application, as shown here.
#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
IEnumerable
Concepts
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 SQL: .NET Language-Integrated Query for Relational Data