SPMetal

1. SPMetal Pranav Sharma http://pranavsharma.infoIndependent SharePoint Consultant 5-Year Marriage to SP Twitter: @ePranav

2. 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)

3. 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

4. Let’s create a sample list

5. Setting up SPMetal • Create PreBuild.bat and save with encoding: Unicode (UTF-8 without signature) – codepage65001 • Include generated codefile in project • Add project reference to Microsoft.SharePoint.Linq.dll • Sign project using key file

6. Options (1 of 3)

7. Options (2 of 3)

8. Options (3 of 3) <?xmlversion="1.0"encoding="utf-8"?> <WebAccessModifier="Internal"xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal"> <ContentTypeName="Contact"Class="Contact"> <ColumnName="ContId"Member="ContactId" /> <ColumnName="ContactName"Member="ContactName1" /> <ColumnName="Category"Member="Cat"Type="String"/> <ExcludeColumnName="HomeTelephone" /> </ContentType> <ExcludeContentTypeName="Order"/> <ListName="Team Members"Type="TeamMember"> <ContentTypeName="Item"Class="TeamMember" /> </List> </Web>

9. Logging using(var context = newSp2010DataContext("http://sp2010")) { varcontextLog = newStringBuilder(); context.Log= newStringWriter(contextLog); Console.WriteLine(contextLog); } Use the DataContext’s Log property to inspect underlying SPQueries

10. Iterating foreach (varmyItemincontext.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());

11. Complex field types (1 of 3) privateTaxonomyFieldValue _myColumnManagedMetadata; [ColumnAttribute(Name = "MyColumnManagedMetadata", Storage = "_myColumnManagedMetadata", FieldType = “Taxonomy")] publicTaxonomyFieldValueMyColumnManagedMetadata { get{ return _myColumnManagedMetadata; } set { if((value != _myColumnManagedMetadata)) { this.OnPropertyChanging("MyColumnManagedMetadata", _myColumnManagedMetadata); _myColumnManagedMetadata = value; this.OnPropertyChanged("MyColumnManagedMetadata"); } } }

12. Complex field types (2 of 3) [CustomMapping(Columns = newString[] {"MyColumnManagedMetadata"})] publicvoidMapFrom(objectlistItem) { varitem = (SPListItem) listItem; this.MyColumnManagedMetadata= item["MyColumnManagedMetadata"] asTaxonomyFieldValue; } publicvoidMapTo(objectlistItem) { var item = (SPListItem)listItem; item["MyColumnManagedMetadata"] = this.MyColumnManagedMetadata; }

13. Complex field types (3 of 3) publicvoid Resolve(RefreshMode mode, objectoriginalListItem, objectdatabaseListItem) { varorigItem = (SPListItem) originalListItem; vardbItem = (SPListItem) databaseListItem; varorigValue = (TaxonomyFieldValue) origItem["MyColumnManagedMetadata"]; vardbValue = (TaxonomyFieldValue) dbItem["MyColumnManagedMetadata"]; if(mode == RefreshMode.KeepCurrentValues || (mode == RefreshMode.KeepChanges&& MyColumnManagedMetadata != origValue)) { dbItem["MyColumnManagedMetadata"] = MyColumnManagedMetadata; } elseif (mode == RefreshMode.OverwriteCurrentValues || (mode == RefreshMode.KeepChanges&& MyColumnManagedMetadata == origValue && MyColumnManagedMetadata != dbValue)) { MyColumnManagedMetadata = dbValue; } }

14. Updating foreach (varmyItemincontext.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();

15. Inserting varnewItem = newMyListItem { Title = "Choice 3", MyColumnChoice= "Donald Trump" }; context.MyList.InsertOnSubmit(newItem); context.SubmitChanges();

16. 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

17. 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 • SPMetaland the Managed Metadata Column http://bit.ly/HteBbp • Large list performance: SPMetal vs. SPQueryhttp://bit.ly/sYvAEL

18. Questions? Pranav Sharma http://pranavsharma.infoIndependent SharePoint Consultant 5-Year Marriage to SP Twitter: @ePranav