Using LINQ to SharePoint

The SharePoint List Data Models reference implementation includes a repository class, PartManagementRepository, which contains all the data operations used by the Visual Web Parts in the solution. The functionality that is implemented in the repository class is defined by the IPartManagementRepository interface. Creating the repository class as an implementation of an interface makes the solution easier to unit test, since we can use the SharePoint Service Locator to substitute a fake implementation of IPartManagementRepository for testing purposes. All the data operations in the repository class use LINQ to SharePoint expressions, in order to demonstrate the capabilities of the new LINQ to SharePoint provider in SharePoint 2010. These expressions use the entity classes that we generated using the SPMetal command-line tool, as described in Building Entity Classes for LINQ to SharePoint.

When you review the PartManagementRepository class, the first thing to note is that the constructor instantiates a data context object, as shown by the following code example.

public class PartManagementRepository : IPartManagementRepository
  private PartsSiteDataContext dataContext { get; set; }

  public PartManagementRepository()
    dataContext = new PartsSiteDataContext(SPContext.Current.Web.Url);

Every LINQ expression in the repository class uses this data context object as the foundation for the data operation. The PartsSiteDataContext class exposes properties for each list in the site. These properties return a generic EntityList<T> object, which represents an enumerable collection of strongly typed list item entities. For example, the LINQ expression in the following method returns a subset of Machine instances from an EntityList<Machine> collection on the basis of a partial model number.

public IEnumerable<Machine> GetMachinesByPartialModelNumber(string modelNumber)
  return from machine in dataContext.Machines
    where machine.ModelNumber.StartsWith(modelNumber)
    select machine;

You can browse the PartManagementRepository class to see examples of LINQ to SharePoint expressions with varying levels of complexity.

Using ViewModel Classes with LINQ Expressions

The SharePoint List Data Models reference implementation includes several ViewModel classes. These consist of properties that represent field values from more than one list. For example, the PartInventoryViewModel class includes fields from the Parts list and the InventoryLocations list.

public class PartInventoryViewModel
  public int PartId{ get; set; }
  public string PartName { get; set; }
  public string Sku { get; set; }
  public int InventoryLocationId { get; set; }
  public string LocationBin { get; set; }
  public double InventoryQuantity { get; set; }

The use of ViewModel classes allows us to simplify the data binding logic for the user interface. For example, if we want to show a grid view that displays parts together with their inventory locations, we would typically need to:

  1. Submit a query to retrieve a list of parts.
  2. Submit another query for each part to determine the inventory location.
  3. Merge the results into a single collection and bind the grid view to the collection.

By creating queries that return a collection of PartInventoryViewModel objects, we can submit a single query and bind the grid view to the query result. For example, the following method returns an enumerable collection of PartInventoryViewModel objects for parts that are associated with a specified machine ID.

public IEnumerable<PartInventoryViewModel> GetPartsByMachineId(int 
  //get all matching parts.
  var partResults = 
    (from machinePart in dataContext.MachineParts
      where machinePart.Machine.Id == machineId
      select new PartResult { PartId = machinePart.PartSKU.Id, 
                              Title = machinePart.PartSKU.Title, 
                              SKU = machinePart.PartSKU.SKU });

  IEnumerable<int?> partIds = 
    (from part in partResults 
       where part.PartId != null 
       select part.PartId);

  //Get all matching parts that have inventory.
  List<InvResult> inventoryResults = GetInventoryListForParts(partIds);
  return MergePartInventory(partResults, inventoryResults);

The PartResult and InvResult classes are simple collections of fields that help us to merge results. The use of these classes in LINQ expressions is an example of view projection, which can result in more efficient queries. This is because the query returns only the fields of interest, rather than every field from the lists being queried.

After building a collection of part IDs for the parts that match the specified machine ID, the method calls the GetInventoryListForParts method, passing in the collection of part IDs as a parameter.

List<InvResult> GetInventoryListForParts(IEnumerable<int?> partIds)
  List<InvResult> inventoryResults = new List<InvResult>();
  foreach (int? partId in partIds)
    if (partId != null)
      var locations = 
        from location in dataContext.InventoryLocations
          where location.Part.Id == partId
          select new InvResult
            PartId = location.Part.Id,
            LocationId = location.Id,
            BinNumber = location.BinNumber,
            Quantity = location.Quantity };

      foreach (var loc in locations)
  return inventoryResults;

Finally, the GetPartsByMachineId method calls the MergePartInventory method. This method uses a LINQ join predicate to merge the inventory query results with the part query results, and then uses a view projection to return an enumerable collection of PartInventoryViewModel objects.

IEnumerable<PartInventoryViewModel> MergePartInventory(IEnumerable<PartResult> partResults, IEnumerable<InvResult> inventoryResults)
  // do a left outer join between the two result sets 
  // This associates the parts with inventory info and includes the parts  
  // that have no inventory info (inv == null).
  var results = 
    from part in partResults 
    join inv in inventoryResults on part.PartId equals inv.PartId into gj
      from subInv in gj.DefaultIfEmpty()
      select new PartInventoryViewModel
        PartId = part.PartId.HasValue ? part.PartId.Value : 0,
        Sku = part.SKU,
        PartName = part.Title,
        InventoryLocationId = (subInv != null && 
          subInv.LocationId.HasValue ? subInv.LocationId.Value : 0),
        InventoryQuantity = (subInv != null && 
          subInv.Quantity.HasValue ? subInv.Quantity.Value : 0),
        LocationBin = (subInv != null ? subInv.BinNumber : "")                                              
  return results.ToArray();

To see an example of how the GetPartsByMachineId method is used, look at the code-behind file for the ManageMachines.ascx control in the ManageMachines Visual Web Part. When the user clicks a Show Parts link on the user interface, the event handler calls the GetPartsByMachineId method, passing in the selected machine ID as a parameter.


The ShowParts method then simply binds the returned collection of PartInventoryViewModel objects to a GridView control.

public void ShowParts(IEnumerable<PartInventoryViewModel> partResultsViewModels)
  PartResultsGridView.EmptyDataText = Constants.EmptyData.MachinePartResults;
  PartResultsGridView.DataSource = partResultsViewModels;
  PartResultsGridView.EmptyDataText = string.Empty;

As you can see, the ViewModel approach substantially simplifies what would otherwise be complex user interface logic. The PartManagementRepository class includes many examples of LINQ expressions that return enumerable collections of ViewModel objects.

Efficiency of LINQ to SharePoint Expressions

When you use a LINQ expression to query a SharePoint list, the LINQ to SharePoint provider dynamically converts the LINQ expression into a Collaborative Application Markup Language (CAML) query at run time. The efficiency of the generated CAML queries varies widely according to how you construct your LINQ expressions, and it's important to review the CAML output that your LINQ expressions produce. For information on the efficiency of different LINQ constructs, and for details on how to review the CAML output for a LINQ to SharePoint expression, see Using LINQ to SharePoint.