How to: Use LINQ to SharePoint for query

This topic describes how to query the Microsoft SharePoint Foundation list using the LINQ to SharePoint provider.

Step 1: obtain a reference to a website

The datacontext object must be created to write the code for the LINQ to SharePoint provider. This object represents a subset of the content database, specifically the list and list items of the SharePoint Foundation website. The simplest way to create such an object is to pass the absolute URL of the website to be queried as a text string to the class constructor, as shown in.

C #

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

However, more often, you will need to run solutions for many websites on many servers, but you do not know the complete URL when writing code. If your code runs on any occasion with an HTTP context (such as running on a Web part or custom application page) and you want to query the current website, you can use the spcontext object to obtain the URL, as shown in this example.

C #

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

You can also use context to indirectly obtain the URLs of other websites in the website set or even other website sets of Web applications. For example, the following code creates a datacontext for the primary website of the oldest website set in a web application.

C #

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

Note that you must release the datacontext object because the spweb object it uses is not the object provided by spcontext.

Note

References to the spwebapplication object require the using statement of the namespace Microsoft. Sharepoint. Administration (imports in Visual Basic ).

For other methods to get reference to a website in the SharePoint Foundation server farm, see get reference to a website, web application, and other key objects.

If there is no HTTP context (for example, in the console application) and you do not know the server name when writing code, you can use the "localhost" alias to obtain references to the root website, see the following example.

C #

using (DataContext topLevelSite = new DataContext(“http://localhost”)){}using (DataContext mySite = new DataContext(“http://localhost/sites/MySite”)){}

Because there is no HTTP context, you should release the datacontext object.

You can derived from datacontext. In this case, you should use the constructor of your class, as shown in the following example.

C #

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

Step 2: obtain a reference to the list

You can use the getlist <t> (string) method to obtain the entitylist <tentity> object. This object is an iqueryable <t> representation of the list. The following is an example.

C #

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

Note that the content type of the list must be represented by the class declared by the display.AnnouncementClass. You must use contenttypeattribute to modify the class to specify the content type name and content type ID of the SharePoint Foundation website. Generally, when writing code, you should know which lists your code will query. The class that represents the content type must contain at least one attribute that represents the column in the list, and the attribute Declaration must be modified with at least the specified field name and its type columnattribute. The following example demonstrates how to enable the call code function to call getlist <t> (string) (where T isAnnouncement) The minimum declaration required by the method.

C #

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

However, a query can only reference columns represented by attributes in the content type class. Therefore, if you only provide this minimum declaration, your call code can only reference the title field (column ), as shown in this example.

C #

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

Note

As shown in this example, name the classAnnouncementThis fact does not mean that it must accurately mirror the SharePoint Foundation content type with the same name. This means that you do not need to use an attribute for each column of the content type. This class can only represent a subset of content type columns, and can have more members. In fact, this is an important feature of the LINQ to SharePoint provider, because the website owner can add columns to the list, and this will actually create a new content type for the list. This class does not have to be the same as the content type of the list. It can use any name, as long as the name is used as a type parameter when calling the getlist <t> (string) method. However, if your class has the same name as the regular list content type, your code is generally more readable. By default, the spmetal tool follows this convention.

The content type can be inherited from other content types, and any content type at a higher level in the inheritance tree can be used for calling the getlist <t> (string) method. For example, because all content types areItemThe content type is derived, so you can useItemAs a type parameter, as shown in this example:

C #

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

Source code is requiredItemAnd the attribute must be declared for each column in the content type referenced by your query.

If the item content type is used, you can query the list without knowing the list name or its derived content type when writing code, as shown in this example.

C #

DataContext topLevelSite = new DataContext(“http://localhost”);SPSite siteCollection = new SPSite("http://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, in the most practical and effective application scenarios with the sensitivity to SharePoint encoding, columns that are unique to a specific list are involved. Therefore, as a feasible method, you need to know the list name and its specific derived content type. Specifically, when writing code, you must be able to assume that the website on which you want to run the query code has a specific list. In addition, because the query of the specified list will reference a specific column, you must assume that the specified list will contain a specific subset of the column. This means that your SharePoint Foundation solution will belong to one of the following two types:

• It is designed to query known list types that come with SharePoint Foundation, or known list types that come with products with enhanced functionality (such as Microsoft SharePoint Server.

• The development process combines one or more custom lists that are part of a feature set or custom website definition and is installed with these lists.

We recommend that you use the spmetal tool to generate the required classes and attribute declarations.

Step 3 (optional): Disable object Revision

If your code only queries the list without adding, deleting, or editing the list items, you can disable the object revision function. This will improve the performance. Set the objecttrackingenabled attribute to false.

Html

teamSite.ObjectTrackingEnabled = false;

Step 4: Define a LINQ Query

The value of LINQ is that the query is basically written in the same way, regardless of the data source or the LINQ provider. In addition to the slightly different ways to reference the data context and obtain iqueryable <t> objectsLINQ to SQLOrLINQ to XMLThe query is basically the same. For more information, see LINQ to SQL:. Net Language Integrated Query for relational data (this link may point to the English page) and LINQ to XML (this link may point to the English page ).

However, no two LINQ providers are identical. The difference between the local query language of the data source (the language in which the provider wants to convert the LINQ query to) Sometimes limits the query possibilities. In particular, in queries using the LINQ to SharePoint provider, there are implicit or explicit restrictions on list join. A linq to SharePoint query can be used to explicitly or implicitly join two lists, but only when one of the lists has the "View" type that can be viewed in another table column. If only one value is allowed for the "query" field, the relationship between lists must be expressed in your code by using the entityref <tentity> Field in the class that represents the list content type. If this field allows multiple values, you must use the entityset <tentity> Field and the entityset <tentity> attribute that wraps the field to indicate this relationship.

Prompt

The log attribute is a textwriter that can be used to compile a caml query to convert your LINQ query. It can be seen that the caml query is very helpful for the debugging process. Therefore, assign the textwriter object to the log attribute. In the following example, outtextwriter is allocated to log. This will cause the caml query to appear on the console when you execute a LINQ query in the console application, as shown in.

C #

#if DEBUGteamSite.Log = Console.Out;#endif

Step 5: Enumerate query results

Like all the LINQ providers, this query is executed only after you enumerate the LINQ to SharePoint queries. This usually happens in the foreach loop. In rare cases, you can use ienumerable and ienumerator if you need to customize enumeration (for example, skip the items in the result of the row.

You can also allocate query results to ienumerable <t> variables (such as ilist <t> or icollection <t> objects) instead of the anonymous VAR (dim in Visual Basic ). This causes an immediate query to fill in the variable. For example:

C #

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 the results from multiple lists into one ilist <t>, and then you can further filter the list using LINQ to objects. The following example shows how to generate an ilist for enterprise and team events <t> and how to generate a report on the events that occurred on the day in the auditorium.

C #

DataContext corpSiteData = new DataContext(“http://localhost/CorpSite”);DataContext markTeamData = new DataContext(“http://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 method used in Step 7 to merge data from multiple SharePoint Foundation lists can be used to merge data from SharePoint Foundation lists and data from other sources, the premise is that records from other sources can be converted to classes that represent the SharePoint Foundation content type. For example, assume that your code uses LINQ to SQL to generate an ilist named oldclients <t> (where T isClient) Object, and assume that it uses LINQ to Sharepoint to generate an ilist named activepatients <t> (where T isPatient) Object. If these attributes andClientOther members of the class arePatientAnd the corresponding members have the same signature, you can combine data from the SQL source and data from the SharePoint Foundation list, as shown in this example.

C #

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

When you need to merge data from two different LINQ providers, it is best to use classes of the same project type for these two providers. This can be implemented because you can put the attribute modifiers required by the LINQ to SharePoint and those required by another LINQ provider in the same class declaration. The following example demonstrates the signature of the class declaration modified by using the contenttypeattribute of LINQ to SharePoint and the tableattribute of LINQ to SQL.

C #

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

With these attributes, You can merge data in the SQL table with the data in the SharePoint Foundation list without type conversion.

See

Reference

Spmetal

Columnattribute

Contenttypeattribute

Datacontext

Entitylist <tentity>

Entityref <tentity>

Entityset <tentity>

Icollection <t>

Ienumerable

Ienumerator

Ilist <t>

Iqueryable <t>

Spcontext

Spwebapplication

Tableattribute

Concept

Content type ID

Unsupported LINQ query and two-phase Query

Obtain references to websites, web applications, and other key objects

How to: write data to a content database using LINQ to SharePoint

Other resources

LINQ to objects (this link may point to an English page)

LINQ to SQL:. Net Language Integrated Query for relational data (this link may point to an English page)

LINQ to XML (this link may point to an English page)

Web parts Overview