Welcome on my SharePoint 2010 blog


My name's Roy. I'm a dutch SharePoint & BI consultant/architect at Advantive B.V. At this moment I'm busy with some big SharePoint 2010 projects in The Netherlands. In all the projects I've got different roles, like: Business consultancy, Lead Consultant, Architect (logical and technical), Development and Teaching/courses.

Products where you can ask me about are: SharePoint, Visual Studio, SQL Server, PowerPivot, Analysis and Reporting Services, Visio Services, InfoPath, PerformancePoint Services, Team Foundation Server, Office line.

I love to work and to write about Microsoft SharePoint 2010 so, feel free and read/comment my Blogs!


Wednesday, August 18, 2010

SPMetal & LINQ to SharePoint 2010

Within SP2010 you have the possibility to reach and use your lists and libraries using Language Integrated Query (LINQ).

LINQ is a feature of the programming languages C# and Microsoft Visual Basic .NET. Compilers are included with Visual Studio.

LINQ adds a SQL-like syntax and vocabulary to each of the languages, which can be used to query data sources. But unlike other languages and query syntaxes which vary from one type of data source to another, LINQ can be used to query, in principle, any data source whatsoever. For this reason, developers may find that it is the only query syntax that they ever need to know.
All that is necessary to make a data source accessible with LINQ is that someone create a LINQ provider for the data source.

A LINQ provider is an implementation of the System.Linq.IQueryable<T> and System.Linq.IQueryProvider interfaces that are included with Microsoft .NET Framework (System.Core.dll). The implementing classes must be public in a managed code assembly. The primary job of the class that implements IQueryProvider is to translate LINQ queries into the language of the data source, such as SQL or XQuery, then call the data source’s application to execute the query.

The provider must also expose a gateway class whose instances can communicate with the data source and output IEnumerable<T> objects. For example, the gateway class for LINQ to SQL is DataContext and the gateway class for LINQ to XML is XDocument. The gateway class must implement System.Linq.IQueryable<T> or have a child property that does so or have a method that returns a type that implements System.Linq.IQueryable<T>. For example, DataContext has a GetTable() method that returns a Table<TEntity> type that implements System.Linq.IQueryable<T>. The latter interface, in turn, has a property of type IQueryProvider. (The gateway class can also directly implement IQueryProvider.) It is objects of type Table<TEntity> that are queried.

In many cases, a LINQ Provider cannot be used by a .NET solution developer unless the developer creates a set of entity classes to represent the subordinate entities in the data source, such as the particular tables of a particular SQL database. Frequently, a lot of such classes must be created (e.g., a database with three dozen tables), so the developer of a LINQ provider will typically include a code generation tool to automate the process of creating these entity classes.

The LINQ to SharePoint Provider is defined in the Microsoft.SharePoint.Linq namespace. It translates LINQ queries into Collaborative Application Markup Language (CAML) queries. It is no longer necessary for developers to know how to write CAML queries. LINQ queries can be used in server code. To query from a client application, use SharePoint’s support for ADO.NET Data Services.

The gateway class for the LINQ to SharePoint provider is Microsoft.SharePoint.Linq.DataContext which represents the data of a SharePoint Foundation Web site. It is parallel in use and function to the System.Data.Linq.DataContext class in the LINQ to SQL provider. Just as the latter class has a GetTable() method that returns a Table<TEntity> object that implements System.Linq.IQueryable<T>, so too, the Microsoft.SharePoint.Linq.DataContext class has a GetList<T> method that returns an EntityList<TEntity> class that implements System.Linq.IQueryable<T>. It is objects of type EntityList<TEntity> that are queried.

How to use
  1. To use LINQ to SharePoint 2010 you have to create a new empty SharePoint visual webpart (example) project in Visual Studio 2010.
  2. Go to cmd-prompt and open the BIN-directory in your 14-hyve.
  3. Enter the following command (launch the spmetal.exe): SPMetal.exe /web:http://yoursite /code:C:\ExampleEntities.cs. The system wil now create a cs-file that you have to include in your project. In this file you can find all your lists and doc libs and fields that you can use in your LINQ queries. Note that the class name is now ExampleEntitiesDataContext. It's based on the name you specify as your code file in the SPMetal.exe command line tool.

    If you were to use /code:C:\Awesome.cs instead, it would generate a class called AwesomeDataContext.
  4. So far so good.... now the cs-file is included in your project, you can use it. Now you have to make an instance of the ExampleEntitiesDataContext class and make an example query:ExampleEntitiesDataContext edc = new ExampleEntitiesDataContext(SPContext.Current.Web.Url); var q = from Example in edc.Documenten where Example.DocumentCreatedBy == "Test" select Example.DocumentCreatedBy;
  5.  You can now use the results in a for each loop, BUT.... sometimes the SPMetal.exe makes some mistakes in his automatic process with custom lists and libraries. When you want to choose a field in your table, you can only see the system fields...

6. The problem: The SPMetal.exe does'nt use the content types that are connected to the list, he takes the default content type for a list: Item and for a Library: Document!!!!

7. Solution: Open your ExampleEntities.cs file and search for your list from which you want to use the fields. In this property change the contenttype to the content type where your fields are in (see the yellow highlighted text):
[Microsoft.SharePoint.Linq.ListAttribute(Name="Documenten")] public Microsoft.SharePoint.Linq.EntityList<Document> Documenten { get { return this.GetList<Document>("Documenten");
8. Change the text to the content type you need. After that go back to your other cs.file (e.g.: for the webpart) and again open your field property box using intellisense. And....Yes!! There they are: Your fields from the specific content type!! HAVE FUN!!