SPMetalPranav Sharmahttp://pranavsharma.info

Independent SharePoint Consultant5-Year Marriage to SP

Twitter: @ePranav

What is SPMetal and LINQ?“SPMetal is a command-line tool that generates entity classes, which provide an object-oriented interface to the Microsoft SharePoint Foundation content databases. These classes are primarily used in LINQ to SharePoint queries; but they are also used to add, delete, and change list items with concurrency conflict resolution. Finally, they can be used as an alternative to the regular SharePoint Foundation object model for referencing content.

The tool is included with SharePoint Foundation and is located in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\BIN”

Language-Integrated Query (LINQ) is a set of features that extends powerful query capabilities to the language syntax of C# and VB. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store” (MSDN)

Why SPMetal? For quick wins when low on time

For low usage applications where list size doesn’t cause performance concerns

For easily binding SharePoint queries to data grids and repeaters

For complex query writing (Ex: Multi-list Lookups)

For new SP developers to avoid run-time errors by catching them at compile-time

Let’s create a sample list

Setting up SPMetal Create PreBuild.bat and save with encoding: Unicode (UTF-8 without

signature) – codepage 65001

Include generated codefile in project

Add project reference to Microsoft.SharePoint.Linq.dll

Sign project using key file

Options (1 of 3)Option Value definition Example Commentsweb The complete,

absolute URL of the Web site whose data is modeled by the entity classes.

/web:http://ContosoServer/Marketing

Required. You can have port numbers in the server name; for example, /web:http://ContosoServer:5555/Marketing.Do not include the home page or any other page in the URL.

code The relative or absolute path and file name of the output file.

/code:MarketingSite.cs If this option is not used, the generated code is streamed to standard output.If no file name extension is specified or the file name extension is not either "cs" or "vb", the language option must be used.The file name (exclusive of the extension) is also used to form the beginning of the name of a class that derives fromDataContext; in this example the class is namedMarketingSiteDataContext. The derived class represents the lists and data of the whole Web site, so choose a file name that conveys that meaning. (You can override this naming behavior with an SPMetal Parameters XML file.)

language The programming language of the generated code.

/language:csharp The only possible values are "csharp" and "vb".If the value of the code option has either "cs" or "vb" as a file name extension, SPMetal can infer the language and thelanguage option is not needed.

namespace The namespace that contains the entity class declarations.

/namespace:Contoso.TeamActivityReports

If this option is not used, the generated code specifies no namespace and the compiled assembly treats the default namespace specified in the properties of the Visual Studio project as the namespace of the generated classes.

Options (2 of 3)Option Value definition Example Commentsuseremoteapi No value. /useremoteapi This option signals that the value of the web parameter

points to a server that is not the one on which SPMetal is running. One possible use for this parameter is to generate code against a Web site on an online deployment of SharePoint to which you intend to upload your solution as a sandboxed solution.

user The user in whose context SPMetal executes.

/user:Contoso\bob Use this option if you do not want SPMetal to run in your own context. Specify the domain.

password The password for the user specified in theuser option.

/password:$5U+ryz Use in conjunction with the user option.

serialization Specifies whether objects that instantiate the generated classes are serializable.

/serialization:unidirectional The only possible values are "unidirectional" and "none". Specify "unidirectional" if you want the objects to be serializable. SPMetal adds appropriate attributes from theSystem.Runtime.Serialization namespace to the class and property declarations and adds handlers for the Deserializing event.If this option is not used, "none" is assumed.

parameters Identifies the path and name of an XML file that contains overrides of SPMetal default settings.

/parameters:MarketingSite.xml You typically will not reuse exactly the same parameters XML file for different Web sites, so name the file the same as the Web site.For more information about the parameters file, see Overriding SPMetal Defaults by Using a Parameters XML File.

Options (3 of 3)<?xml version="1.0" encoding="utf-8"?><Web AccessModifier="Internal" xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal">

<ContentType Name="Contact" Class="Contact"><Column Name="ContId" Member="ContactId" /><Column Name="ContactName" Member="ContactName1" /><Column Name="Category" Member="Cat" Type="String"/><ExcludeColumn Name="HomeTelephone" />

</ContentType><ExcludeContentType Name="Order"/><List Name="Team Members" Type="TeamMember">

<ContentType Name="Item" Class="TeamMember" /></List>

</Web>

Logging

using (var context = new Sp2010DataContext("http://sp2010")){ var contextLog = new StringBuilder(); context.Log = new StringWriter(contextLog); Console.WriteLine(contextLog);}

Use the DataContext’s Log property to inspect underlying SPQueries

Iterating foreach (var myItem in context.MyList) {

Console.WriteLine(myItem.Title);Console.WriteLine(" ->" + myItem.MyColumnChoice);//Console.WriteLine(" ->" +

myItem.MyColumnManagedMetadata); }

Querying Console.WriteLine((from myItem in context.MyList where myItem.MyColumnChoice.Equals(MyColumnChoice.BarackObama) select myItem.Title). FirstOrDefault());

Complex field types (1 of 3)

private TaxonomyFieldValue _myColumnManagedMetadata;

[ColumnAttribute(Name = "MyColumnManagedMetadata", Storage = "_myColumnManagedMetadata", FieldType = “Taxonomy")]

public TaxonomyFieldValue MyColumnManagedMetadata { get { return _myColumnManagedMetadata; } set { if ((value != _myColumnManagedMetadata)) { this.OnPropertyChanging("MyColumnManagedMetadata", _myColumnManagedMetadata); _myColumnManagedMetadata = value; this.OnPropertyChanged("MyColumnManagedMetadata"); } } }

Complex field types (2 of 3)

[CustomMapping(Columns = new String[] {"MyColumnManagedMetadata"})] public void MapFrom(object listItem) { var item = (SPListItem) listItem; this.MyColumnManagedMetadata = item["MyColumnManagedMetadata"] as TaxonomyFieldValue; }

public void MapTo(object listItem) { var item = (SPListItem)listItem; item["MyColumnManagedMetadata"] = this.MyColumnManagedMetadata; }

Complex field types (3 of 3) public void Resolve(RefreshMode mode, object originalListItem, object databaseListItem) { var origItem = (SPListItem) originalListItem; var dbItem = (SPListItem) databaseListItem;

var origValue = (TaxonomyFieldValue) origItem["MyColumnManagedMetadata"]; var dbValue = (TaxonomyFieldValue) dbItem["MyColumnManagedMetadata"];

if (mode == RefreshMode.KeepCurrentValues || (mode == RefreshMode.KeepChanges && MyColumnManagedMetadata != origValue)) { dbItem["MyColumnManagedMetadata"] = MyColumnManagedMetadata; } else if (mode == RefreshMode.OverwriteCurrentValues || (mode == RefreshMode.KeepChanges && MyColumnManagedMetadata == origValue && MyColumnManagedMetadata != dbValue)) { MyColumnManagedMetadata = dbValue; } }

Updating foreach (var myItem in context.MyList) { switch (myItem.Title) { case “Choice 1": myItem.MyColumnChoice = MyColumnChoice.BarackObama; break; case “Choice 2": myItem.MyColumnChoice = MyColumnChoice.MittRomney; break; default: myItem.MyColumnChoice = MyColumnChoice.None; break; } } context.SubmitChanges();

Inserting var newItem = new MyListItem

{Title = "Choice 3",MyColumnChoice = "Donald

Trump"};

context.MyList.InsertOnSubmit(newItem); context.SubmitChanges();

When to use SPMetal? “While testing with a list of 45K items, SPQuery performed at 0.06s

compared to SPMetal’s performance at 9.98s” – Pranav Sharma (See Resources for full article)

When to use SPMetal? For quick wins when low on time

For low usage applications where list size doesn’t cause performance concerns

For easily binding SharePoint queries to data grids and repeaters

For complex query writing (Ex: Multi-list Lookups)

For new SP developers to avoid run-time errors by catching them at compile-time

Resources SPMetal (MSDN) http://bit.ly/icXOmf

How to: Use SPMetal (MSDN) http://bit.ly/hLPbjo

Overriding SPMetal Defaults by Using a Parameters XML File (MSDN) http://bit.ly/M3zSyH

SPMetal and the Managed Metadata Column http://bit.ly/HteBbp

Large list performance: SPMetal vs. SPQuery http://bit.ly/sYvAEL

Questions? Don’t forget to fill out evaluations online

Show the sponsors some love

Pranav Sharmahttp://pranavsharma.info

Independent SharePoint Consultant5-Year Marriage to SP

Twitter: @ePranav