Tuesday, August 10, 2010

Taking a crash course in LINQ - Part 3 (LINQ to SharePoint)

Hi all, 

LINQ to SharePoint is in fact making a debut with release of SharePoint 2010. Other LINQ providers such as 
  • LINQ to SQL
  • LINQ to XML 
  • LINQ to Entities are very mature.
Any developer working on .NET platform should understand the concept of LINQ which the strategic way to access data for different providers. 

Advantages
  •  Developers need not work the CAML directly. LINQ provides a seamless way to access the SharePoint list data. Apparently, when a developer write the LINQ statements, LINQ translates into CAML query and fetch data from the list. 
  •  A tool is provided by SharePoint 2010 team which points to a site and as we do that, that tool will give is a strongly typed queries. The tool name is SPMetal.exe and its a command line tool accepts site collection and .NET language(cs or vb). 
  • This tool also does compile-time checking as well. 
  • Visual Studio 2010 also provides a Intelligence that helps developer to construct query. An assembly has to used Microsoft.SharePoint.Linq.dll
Disadvantage
  •   LINQ translates the LINQ queries into Collaborative Application Markup Language (CAML) queries thus adding an extra step for retrieving the items.
How LINQ to SharePoint works? 
 
Firstly, we need to create entity classes using SPMetal tool. The SPMetal is a command line tool with the following syntax: 

SPMetal /web:<site Url> /code:<.cs or .vb>
It has to noted that after semi-colon, there MUST be not have any kind of space otherwise it will throw an exception.Once the entity classes have been generated successfully we have to add this auto-generated file in our project.  

Secondly, we have to create a "DataContext" object. DataContext class allows to the list data. Therefore, we construct any query using DataContext object.

Thirdly, because we have in number of lists within a site collection, LINQ understands any kind of list relationship as well.   

Fourthly, its possible to modify the list data via SubmitChanges() method.

So, lets create a simple demo and we will: 
>> use SPMetal that will generate the entity classes
>> Add this class to a Visual Studio 2010 project. 
>> Write LINQ queries. 

Step1: We create a team site named: http://sp2010:4848/Lab05/ via SharePoint 2010 UI.
Step2: Navigate to Site Actions >> More Options ... >> Create a "Contacts" list as illustrated below:


Step3: Add a couple of entries in "Contacts" list.


Step4: Create a new Visual Studio 2010 project, File >> New >> Project >> Select "SharePoint 2010" and name the project: "LINQtoSharePoint"


Step5 :Right click on "LINQtoSharePoint" project Add >> New Item >> Visual Web Part and name it SPLinqWebPart
 
 
Step6: In this LINQtoSharePoint, we create a batch file for SPMetal and it be empty initially. 


Step7: Edit the SetUp file and supply the web and code as discussed above: 
SET SPMETAL="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\SPMETAL.EXE"

%SPMETAL% /web:http://sp2010:4848/Lab05 /namespace:SPNorthwind /code:SPNorthWind.cs

pause

A SPNorthWind.cs file be generated.  

Step8: Right click on "LINQtoSharePoint" project Add >> Existing Item >> select SPNorthWind.cs file so that its embedded in the project. 

Step9:  Edit the "SPLinqWebPartUserControl.ascx" and add the SPGridView: 

-------------------------------

 <SharePoint:SPGridView runat="server" AutoGenerateColumns="false" ID="spGridView">

<HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" />


<Columns>
<SharePoint:SPBoundField DataField="FirstName" HeaderText="First Name">
</SharePoint:SPBoundField>

<SharePoint:SPBoundField DataField="Title" HeaderText="Last Name">
</SharePoint:SPBoundField>



</Columns>

</SharePoint:SPGridView>
------------------------------- 

Step10: Edit the "SPLinqWebPartUserControl.ascx.cs" and reference to Microsoft.SharePoint.Linq.dll 
(C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.SharePoint.Linq.dl)

Step11: Add using statements in SPLinqWebPartUserControl.ascx.cs

 //LINQ

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

Step11:  Now, we query company name to be "Google" using LINQ syntax. It has be noted that we are not using CAML here. Then we bind the query to the SPGridView

protected void Page_Load(object sender, EventArgs e)
        {
            //Create an instance of SPNorthWindContext
            //SPNorthWindDataContext dataContext = new SPNorthWindDataContext(SPContext.Current.Web.Url);

            SPNorthWindDataContext dataContext = new SPNorthWindDataContext(SPContext.Current.Web.Url);

            //Query from Contact list via LINQ
            var query = from c in dataContext.Contacts
                        where c.Company == "Google"
                        select c;

            spGridView.DataSource = query;
            spGridView.DataBind();

        }

Step12: Build, Deploy. Navigate to the http://sp2010:4848/Lab05/SitePages/Home.aspx >> Edit >> Insert >> Web Part >> choose "Custom" >> SPLinqWebPart >> Add 


Note that this web part filters people who belong to "Google". 

Cheers, 
--aaroh 

Download the source here

References: 
 

No comments:

How to display Sharegate administrative dynamic reports via Power BI on SharePoint Online

After working in the SharePoint domain for a couple of years as SharePoint consultant , I am fortunate enough to help customers from man...