Repository pattern with LINQ to SharePoint DSL Extension (part 1)

Some weeks ago, I released a new version of the LINQ to SharePoint DSL Extension for Visual Studio 2010. This new version contains a new feature to generate automatically  the data access layer (DAL) from an existing LINQ to SharePoint Data Model (l2sp file).

In my previous post about the best practices with LINQ to SharePoint, I explained why it's important to implement a good data access layer in a SharePoint custom application.  Until now, I used this layer by hand in applications, above the entities layer managed by the extension.  For some time, I wanted to add a new feature to automate the generation of this layer directly from a LINQ to SharePoint data model. The goal of this new feature is to generate automatically  all the classes and interfaces needed to implement a repository pattern, and to generate some helpers method to help developers to manage data contained in SharePoint lists.

This post explains how to use this feature and how the repository pattern is implemented in this version.

How this feature is embedded in Visual Studio 2010

This feature apppears like a new Visual Studio item available for C# and .Net projects, called "LINQ to SharePoint Data Model Repository Generator" :


You can notice that this file in T4 file (*.tt). It means that this new item is a code generator that you can launch at any time in Visual Studio thanks to the "Run Custom Tool" command.

When you're clicking on Add button a wizard appears to select the l2sp file (LINQ to SharePoint Data Model) associated with this generator:

If you select the source data model file, and click on Finish, a new item appears in your project:


The TT file contains the code to launch the generation, the *.cs file the repository pattern factory code, *.Interfaces.cs the interfaces and *.LinqClasses.cs the concrete implementation of the pattern with LINQ to SharePoint. If you changed the input model of this item (the l2sp file), you can generate again the code when you want. To do that, right click on the "*.tt" file in the VS solution explorer, and click on the "Run Custom Tool" command. The *.cs file will be updated automatically. 

The generated object model

The first thing provides by this layer, is an object model to use the entities through this pattern.

First, the code contains a simple object model to use a factory system. This factory provides properties to get the repositories. To get an instance factory, you must use the static GetNewInstance method from the static Factory class. The object model looks like it:

 The repositories properties of the factory return objects wich implement repositories interfaces. These interfaces look like it:

 The repositories looks like the EntityList<T> objects returned by the LINQ to SharePoint data context:

  • The LINQ methods (Attach, DeleteOnSubmit, InsertOnSubmit, etc.) are available in the repositories too
  • The All property provides a way to make LINQ queries thanks to the IQueryable<t> type

This conception provides the way to use the LINQ technology on your LINQ to SharePoint data context, but you use only abstracted and extendable types in your code.

More over, the LINQ to SharePoint Data Model Repository Generator will create automatically methods specialized for every repository. These methods are called helper methods.

The principle of these helpers is to provide methods specialized for all the properties of your entities. These methods and prototypes are useful for the developers to develop quickly code without high skills with Lambda LINQ expressions. For example, you have an entity called Category. This entity has the following properties: Id, Version, Content Type, Parent, Products. Then the generated repository has the following methods:

public interface ICategoryRepository


All of these methods only return collection of type IQueryable<T>. The list of methods generated for a property is different for each entity property, and depends of the property type. For example, an Integer property generates different methods than a String property.

The list of generated methods by property type is the following:

Property type Available methods for a property called Category
All not lookup types GetWhereCategoryEqualsGetOrderByCategory
Integer and Double GetWhereCategroyEqualGetWhereCategoryIsGreaterThanGetWhereCategoryIsGreaterOrEqualThanGetWhereCategoryIsLessThanGetWhereCategoryIsLessOrEqualThanGetWhereCategoryIsContainedBetweenGetSumByCategoryGetAverageByCategoryGetMinByCategoryGetMaxByCategory
String GetWhereCategoryContainsGetWhereCategoryStartsWithGetWhereCategoryEndsWith
DateTime GetWhereCategoryIsContainedBetween
Single lookup GetByCategoryId
Multi lookup GetWhereCategoryContains

Each specialized generated method implements 8 different prototypes to help developer to quickly build queries:

For example you have a method called GetWhereIdIsGreater, you also have the following methods available:

IQueryable<Category> GetWhereIdIsGreaterThan(System.Nullable<int> value);
IQueryable<Category> GetWhereIdIsGreaterThan(System.Nullable<int> value, int limit);
IQueryable<Category> GetWhereIdIsGreaterThan(System.Nullable<int> value, bool ascending);
IQueryable<Category> GetWhereIdIsGreaterThan(System.Nullable<int> value, bool ascending, int limit);
IQueryable<Category> GetWhereIdIsGreaterThan(IQueryable<Category> source, System.Nullable<int> value);
IQueryable<Category> GetWhereIdIsGreaterThan(IQueryable<Category> source, System.Nullable<int> value, int limit);
IQueryable<Category> GetWhereIdIsGreaterThan(IQueryable<Category> source, System.Nullable<int> value, bool ascending);
IQueryable<Category> GetWhereIdIsGreaterThan(IQueryable<Category> source, System.Nullable<int> value, bool ascending, int limit);


A simple sample: how to use the generated object model

This sample shows how to work with a repository pattern generated code:

using (IAdventureWorksFactory factory = AdventureWorksFactory.GetNewInstance(SPContext.Current.Web.Url))
//Get a category from parent id
       IQueryable<Category> categoriesByParentId = factory.Categories.GetByParentId(parentCatId);
//Create a direct LINQ query on all categories
       IQueryable<Category> res = from t in factory.Categories.All where t.Id == 2 select t;
       foreach (Category entity in res)

       Category newEntity = new Category();
       newEntity.Title = "My new category";



Please, read the next post about the Reposity Pattern to view more samples about how to use this reposity pattern in your code.