Using the Microsoft.SharePoint.Linq

As we know if we want to query some List items with filters, we need to CAML in the Moss 2007.

CAML is powerful and has good performance than get all list item and traverse them to get the items which we need.

But CAML is hard to write, read and maintain.

Fortunately, Moss 2010 provide Microsoft.SharePoint.Linq, we can use the lambda exerssion to query the List item, it even can join several lists in the query. Actually SharePoint fundation implement a Linq provider to translate the lambda experssion to CAML.

Here is an example for using the Microsoft.SharePoint.Linq:

1. Create a list named person for testing

      a. Rename the title to name(the reference name still title)

      b. Add a look up filed for the list which reference it self, the look up named superior

      c. Add some test data into the list

2. Generate the Moss site model by execute command: 

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>SPMetal /web:http://moss/ /user:.\Administrator /password:Password01! /namespace:mos
s /code:MossModel.cs

 

3.  Use VS2010 create a visual webpart project for Moss 2010 , and add the MossModel.cs into the project

4. Drag a GridDataView into the VisualWebPart1UserControl.ascx

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %> 
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1UserControl.ascx.cs" Inherits="VisualWebPartProject1.VisualWebPart1.VisualWebPart1UserControl" %>
<p>
    <asp:GridView ID="GridView2" runat="server" CellPadding="4" 
        EnableModelValidation="True" ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:GridView>
    <br />
</p>
<p>
<asp:Label ID="Label1" runat="server" Text="CAML:"></asp:Label><br/>
    <textarea id="TextArea1" runat="server" name="TextArea1" cols="20" rows="2"></textarea></p>

5. Write the lambda to query the data, query all person which superior is tom, code in VisualWebPart1UserControl.ascx.cs:

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint.Linq;
using System.Linq;
using System.IO;
using System.Text;
using moss;

namespace VisualWebPartProject1.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (MossModelDataContext context = new MossModelDataContext("http://moss/"))
            {
                StringBuilder sb = new StringBuilder();
                StringWriter ws = new StringWriter(sb);
                context.Log = ws;  
                var persons = from person in context.Person
                              where person.Superior.Title == "Tom"
                              select new { title = person.Title, Superior = person.Superior.Title };

                this.GridView2.DataSource = persons;
                this.GridView2.DataBind();

                context.Log.Flush();
                TextArea1.Value = sb.ToString();  
            }
        }
    }
}

6. Build, deploy the solution and add the webpart to the home page,you can see:

7. The CAML generated from the lambda experssion:

<View>
  <Query>
    <Where>
      <And>
        <BeginsWith>
          <FieldRef Name="ContentTypeId" />
          <Value Type="ContentTypeId">0x0100</Value>
        </BeginsWith>
        <Eq>
          <FieldRef Name="superiorTitle" />
          <Value Type="Lookup">Tom</Value>
        </Eq>
      </And>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="superiorTitle" />
  </ViewFields>
  <ProjectedFields>
    <Field Name="superiorTitle" Type="Lookup" List="superior" ShowField="Title" />
  </ProjectedFields>
  <Joins>
    <Join Type="LEFT" ListAlias="superior">
      <!--List Name: person-->
      <Eq>
        <FieldRef Name="superior" RefType="ID" />
        <FieldRef List="superior" Name="ID" />
      </Eq>
    </Join>
  </Joins>
  <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

 

Note, please don't use the equals instead of  ==, because the sharePoint fundation will not translate the equals as a CAML filter. it will query all the item then run the equals method to filter the items. == will be translated as a CAML filter in the Where sub statement. If you use the equals instead of ==, the generated CAML will be:

<View>
  <Query>
    <Where>
      <BeginsWith>
        <FieldRef Name="ContentTypeId" />
        <Value Type="ContentTypeId">0x0100</Value>
      </BeginsWith>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="superiorTitle" />
  </ViewFields>
  <ProjectedFields>
    <Field Name="superiorTitle" Type="Lookup" List="superior" ShowField="Title" />
  </ProjectedFields>
  <Joins>
    <Join Type="LEFT" ListAlias="superior">
      <!--List Name: person-->
      <Eq>
        <FieldRef Name="superior" RefType="ID" />
        <FieldRef List="superior" Name="ID" />
      </Eq>
    </Join>
  </Joins>
  <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>