Accessing SharePoint List Data using Linq

Recently I wanted to return a SharePoint list as an List<> of a business object.

I did some scouring on the internet, as there is no point re-inventing the wheel.  But was generally disappointed with the results, as they required a lot of iterations and loops. Eric White’s blog here got me thinking, that we could do it with Linq

So here is what I did Projects my business object looks like this

Property	Data Type
ProjectOwner	string
KPI	string
SKU	string
DateRequired	DateTime?
DueDate	string

And here is the code, which expects a result set from a Sharepoint Web Service GetListItems() method.

Code Snippet

1. private static List<ProjectStatus> GetProjectStatusesFromXmlNode(XmlNode resultNode)
2.     {
3.         var list = (from r in resultNode.GetXElement().Descendants()
4.                     where r.Name.LocalName == "row"
5.                     select new ProjectStatus
6.                     {
7.                         Project = (string)r.Attribute("ows_LinkTitle"),
8.                         DateRequired = DateTime.Parse(((string)r.Attribute("ows_DateRequired"))),
9.                         DueDate = (string)r.Attribute("ows_ECRDueDate"),
10.                         SKU = (string)r.Attribute("ows_SKU"),
11.                         Status = (string)r.Attribute("ows_Status"),
12.                         ProjectOwner = (string)r.Attribute("ows_AssignedTo"),
13.                         KPI = (string)r.Attribute("ows_tKPI")
14.                     }
15.                     ).ToList();
16.         return list;
17.     }

You will also need Eric White’s extension

Code Snippet

1. public static class Extensions
2. {
3.     public static XElement GetXElement(this  XmlNode node)
4.     {
5.         XDocument xDoc = new XDocument();
6.         using (XmlWriter xmlWriter = xDoc.CreateWriter())
7.             node.WriteTo(xmlWriter);
8.         return xDoc.Root;
9.     }
10.  
11. }

Converting a Sharepoint Personal View into a Public View List.

I have the problem where a user has created a view and I need to get access to it and make it public.  I appreciate this is quite a slog, especially in an ideal world the user would make the List public but if he/she has left …

The first problem was identifying the information for the view. After quite a bit of digging I find it in the WebParts table in the appropriate Content Database

Health and Safety warning – do not play with this data, there are some rather large and ugly dragons who are not scared to bite you, which in turn could incur the wrath of you superiors.

The field you are after is tp_view this contains a pseudo xml file showing the view.

To progress further I created a winform application with a textbox and a button, the string from tp_view goes in to the textbox. The button_click performs the magic.

We are going to use the splist.Views.Add function

Code Snippet

1. SPView view = list.Views.Add("newView", strCollViewFields, strQuery, uint.Parse(RowLimit), true, false);

All of the information above is found in the string.

strCollViewFields is populated using, which is relatively straight forward the +13 means we get the end tag as well

Code Snippet

1. private StringCollection GetViewFields()
2.         {
3.             XmlDocument xmlDoc = new XmlDocument();
4.  
5.             //assumes Viewfields is at the beginning of the string.
6.             string xml = definition.Substring(0, definition.IndexOf("</ViewFields>") + 13);
7.             xmlDoc.LoadXml(xml);
8.  
9.             var returnvar = new StringCollection();
10.  
11.             foreach (XmlNode node in xmlDoc.SelectNodes("ViewFields/FieldRef"))
12.             {
13.                 returnvar.Add(node.Attributes[0].Value);
14.             }
15.  
16.             return returnvar;
17.         }

strQuery, here I +7 is on the start tag because I do not want the Query tag, this is a SharePoint thing and I do not know why!

Code Snippet

1. private string GetQuery()
2.         {
3.             string returnVar = string.Empty;
4.  
5.             int queryTagStartLocation = definition.IndexOf("<query>", StringComparison.InvariantCultureIgnoreCase) + 7;
6.             int queryTagEndLocation = definition.IndexOf("</query>", StringComparison.InvariantCultureIgnoreCase);
7.             int queryLength = queryTagEndLocation - queryTagStartLocation;
8.             //All we need is the contents not the actual query tag
9.             returnVar = definition.Substring(queryTagStartLocation, queryLength);
10.  
11.             return returnVar;
12.         }

RowLimit  and Paged ( I have set it to true above)

Code Snippet

1. private string GetRowLimit(out bool Paged)
2.     {
3.         //<RowLimit Paged="TRUE">100</RowLimit>
4.         
5.         string value = string.Empty;
6.         string paged = string.Empty;
7.        
8.         XmlDocument xmlDoc = new XmlDocument();
9.         xmlDoc.LoadXml(GetXMLString("RowLimit"));
10.  
11.         foreach (XmlNode node in xmlDoc.SelectNodes("RowLimit"))
12.         {
13.             paged = node.Attributes[0].Value;
14.             value = node.InnerXml;
15.         }
16.  
17.         Paged = bool.Parse(paged);
18.  
19.         return value;
20.     }

Here I have created a new function GetXMLString() which I am sure you could parameterise and deprecate GetQuery()

Code Snippet

1. private string GetXMLString(string tag )
2. {
3.    string startTag = "<" + tag ;
4.    string endTag = "</" + tag + ">";
5.  
6.  
7.    int queryTagStartLocation = definition.IndexOf(startTag, StringComparison.InvariantCultureIgnoreCase);
8.    int queryTagEndLocation = definition.IndexOf(endTag, StringComparison.InvariantCultureIgnoreCase) + endTag.Length;
9.     int queryLength = queryTagEndLocation - queryTagStartLocation;
10.     //All we need is the contents not the actual query tag
11.     return definition.Substring(queryTagStartLocation, queryLength);
12. }

The List I had also has some aggregations which we can get from GetXMLString() function. 

All in all the main function looks like this

Code Snippet

1. try
2.             {
3.                 definition = textBox1.Text;
4.  
5.                 bool paged;
6.                 var strCollViewFields = GetViewFields();
7.                 var strQuery = GetQuery();
8.                 var RowLimit = GetRowLimit(out paged);
9.                 var aggregations = GetXMLString("Aggregations"); ;
10.  
11.                 SPSecurity.RunWithElevatedPrivileges(delegate()
12.                 {
13.  
14.                     using (var site = new SPSite("http://srvJupiter:6000/"))
15.                     {
16.                         using (var web = site.AllWebs[0])
17.                         {
18.  
19.                             var list = web.Lists["Projects"];
20.  
21.                             SPView view = list.Views.Add("newView", strCollViewFields, strQuery, uint.Parse(RowLimit), true, false);
22.  
23.                             view.Aggregations = aggregations;
24.                             view.Update();
25.  
26.                         }
27.                     }
28.                 }
29.                 );

As usual test anything in a test system first.