Microsoft actually improve the documentation on SharePoint 2010 LINQ and “fix” SPMetal to work with Lookup Columns.

In SharePoint 2010 comes new libraries to perform LINQ to SharePoint (Microsoft.SharePoint.Linq to be exact).

In a very common word, this enables you use LINQ against an ADO.NET DataServices like DataContext, which is considerably faster then using LINQ against the SPList.Items collection.

In first of all , you need to generate the “proxy” classes. For this Microsoft provided us with this “magical” tool in SPMetal[SPMetal is a command line tool that generates entity classes, which provide an object oriented interface to the Microsoft SharePoint Foundation content databases.] which resides in [The tool is included with SharePoint Foundation and is located in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\BIN.

], which, if you’ve got a basic site and content structure, shouldn’t be an issue. when we work with content types that contain Site Lookup Columns, SPMetal throws this error message (“Error, the given key was not present in the dictionary”.

So SPMetal WON’T work with Site Lookup Columns, you discover it can utilise a parameters.xml file. This is really helpful when deciding what content you actually require in your proxy classes.

<?xml version=”1.0″ encoding=”utf-8″?>

<Web AccessModifier=”Internal” xmlns=”http://schemas.microsoft.com/SharePoint/2009/spmetal”>

<ContentType>

<Column Member=”ClassId” />

<Column Member=”Name” />

<ExcludeColumn />

</ContentType>

</Web>

To solve above issue you ’ve got your parameters.xml file generated, you need to re-run SPMetal adding this extra param – /parameters: parameters.xml.

Which should generate a few classes for us.

[ContentTypeAttribute(Name = “Name of Your content Type”,Id = “0x01006b2a7fb4b26842eb9c010f4fe9d1697b”)]

public partial class Name of your class : Item

{

private string _Name;

private int _Id;

[ColumnAttribute(Name = “ID”,Storage = “_Id”, FieldType = “Number”)]

public int ID {

get { return _Id; }

set {

if ((value != this._Id)) {

this.OnPropertyChanging(“ID”, this._Id);

this._Id = value;

this.OnPropertyChanged(“ID”);

}

}

}

[ColumnAttribute(Name = “Title”, Storage = “_Name”, FieldType = “Text”)]

public string Name {

get { return _Name; }

set {

if ((value != this._Name)) {

this.OnPropertyChanging(“Name”, this._Name);

this._Name = value;

this.OnPropertyChanged(“Name”);

}

}

}

partial void OnLoaded();

partial void OnValidate();

partial void OnCreated();

public ClassName()

{

this.OnCreated();

}

}

Here now you have got a class generated, we need to add the Lookup Field property. What we’ve got is a simple Lookup Field, which is a content type inheriting from Item.

Now if you take a look at the EntityRef definition on MSDN, you’ll notice that they only briefly explain the concept of the Association attribute. What they show is you are using the “Name” of the actual field, now this is incorrect, and I only found out this the hard way. If you take a peek into the PropertyMap class in Microsoft.SharePoint.Linq and navigate to the GetSPFieldValue class.

In SharePoint 2010 comes new libraries to perform LINQ to SharePoint (Microsoft.SharePoint.Linq to be exact).

In a very common word, this enables you use LINQ against an ADO.NET DataServices like DataContext, which is considerably faster then using LINQ against the SPList.Items collection.

In first of all , you need to generate the “proxy” classes. For this Microsoft provided us with this “magical” tool in SPMetal[SPMetal is a command line tool that generates entity classes, which provide an object oriented interface to the Microsoft SharePoint Foundation content databases.] which resides in [The tool is included with SharePoint Foundation and is located in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\BIN.

], which, if you’ve got a basic site and content structure, shouldn’t be an issue. when we work with content types that contain Site Lookup Columns, SPMetal throws this error message (“Error, the given key was not present in the dictionary”.

So SPMetal WON’T work with Site Lookup Columns, you discover it can utilise a parameters.xml file. This is really helpful when deciding what content you actually require in your proxy classes.

<?xml version=”1.0″ encoding=”utf-8″?>

<Web AccessModifier=”Internal” xmlns=”http://schemas.microsoft.com/SharePoint/2009/spmetal”>

<ContentType>

<Column Member=”ClassId” />

<Column Member=”Name” />

<ExcludeColumn />

</ContentType>

</Web>

To solve above issue you ’ve got your parameters.xml file generated, you need to re-run SPMetal adding this extra param – /parameters: parameters.xml.

Which should generate a few classes for us.

[ContentTypeAttribute(Name = “Name of Your content Type”,Id = “0x01006b2a7fb4b26842eb9c010f4fe9d1697b”)]

public partial class Name of your class : Item

{

private string _Name;

private int _Id;

[ColumnAttribute(Name = “ID”,Storage = “_Id”, FieldType = “Number”)]

public int ID {

get { return _Id; }

set {

if ((value != this._Id)) {

this.OnPropertyChanging(“ID”, this._Id);

this._Id = value;

this.OnPropertyChanged(“ID”);

}

}

}

[ColumnAttribute(Name = “Title”, Storage = “_Name”, FieldType = “Text”)]

public string Name {

get { return _Name; }

set {

if ((value != this._Name)) {

this.OnPropertyChanging(“Name”, this._Name);

this._Name = value;

this.OnPropertyChanged(“Name”);

}

}

}

partial void OnLoaded();

partial void OnValidate();

partial void OnCreated();

public ClassName()

{

this.OnCreated();

}

}

Here now you have got a class generated, we need to add the Lookup Field property. What we’ve got is a simple Lookup Field, which is a content type inheriting from Item.

Now if you take a look at the EntityRef definition on MSDN, you’ll notice that they only briefly explain the concept of the Association attribute. What they show is you are using the “Name” of the actual field, now this is incorrect, and I only found out this the hard way. If you take a peek into the PropertyMap class in Microsoft.SharePoint.Linq and navigate to the GetSPFieldValue class.

Notice that they’re using the “InternalName” to try and get the field from the SPListItem. Now this is fantastic if you use SPMetal to generate your classes all the way, as it uses the correct InternalName of the fields. But if you’ve got to create the lookup column yourself, then we’ve got an issue.

The InternalName it uses isn’t, in fact, the Full InternalName of the Site Column. What SharePoint does in its wisdom, is to cutoff the InternalName value if it exceeds 32 characters in Length when it associates the Site Column with an SPListItem. So if you do SPField.InternalName it will return the full internal name which is great, but if you do SPListItem[fullInternalFieldName] it will throw an lovely ArgumentException and won’t return the actual Field for LINQ.

When we are creating our EntityRef property for our class, we need to know the actual internalName of the column in the SPListItem NOT the fullInternalName. Now if it’s a simple field without spaces, IE “UserName”, it will simply be “UserName”, but if said field has spaces, and has been autogenerated by SharePoint, you will need to kick up a Console App, get the SPList and navigate through its Internal Field dictionary. Here’s an example of one (“WWW_x005F_x0020_Content_x005F_x0”).

Now that we’ve got the internalName of the Lookup Field we need to add a couple of Properties to our ClassName class.

private EntityRef<LookupColumnItem> _nameofSiteLookupColumn;

[Association(Name = “NameOfSiteLookupColumn”, Storage = “_nameofSiteLookupColumn”,

MultivalueType = AssociationType.Single, List = “Lookup List”)]

public LookupColumnItem LookupColumnName {

get { return this._nameofSiteLookupColumn.GetEntity(); }

set {

if (_nameofSiteLookupColumn == null) _nameofSiteLookupColumn = new EntityRef<LookupColumnItem>();

this._nameofSiteLookupColumn.SetEntity(value);

}

}

You’ll notice, a few things there, we need to specify the List Name of the actual lookup list where the values are, the generic type we are specify in the EntityRef is another simple class generated by SPMetal which is the Content Type of “NameOfSiteLookupColumn”.

I hope you get something out of this and Microsoft actually improve the documentation on SharePoint 2010 LINQ and “fix” SPMetal to work with Lookup Columns.

Question and Comments always welcome

About Krishana Kumar

Krishana Kumar is SharePoint Architect/Trainer having Architecture experience with high volumes at Enterprise level and global scale - creation of highly scalable solutions with global user base and geographically distributed architectural components. Good knowledge of SharePoint best practices and governance models. I hold Two Master degree in Computer Science with over 11 years of experience working on Microsoft Technologies specially SharePoint, Project, .NET and other Information Worker Technologies. Having good exposer in Client side scripting Angular.js, backbone and Node. I am currently responsible for SharePoint Infrastructure set up and leading teams in various medium and large scale projects, architecting, designing & installing SharePoint farms, developing custom components,, and providing advanced SharePoint administration and development training to teams and customers. I regularly speaks in various SharePoint User Groups and other Events. I have MCSA Windows Azure, MCSA Office 365, MCSE & MCSD SharePoint 2013, Microsoft Certified Developer (MCD) and holds MCPD, MCTIP and MCTS for SharePoint 2010, MCTS MOSS 2007 & WSS 3.0, MCPD, MCITP (EPM 2010 & 2007) and MCSD .NET.
This entry was posted in General Interest. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *