Over the past couple of days I have been playing with an internal project, so I thought I would play and query the lists with LINQ... here is what I came up with!
Because a SPListItemCollection inherits from ICollection, we can attack it with a LINQ query... and that we shall!
A bit of background, I am using a Web Part to render the information to the user. The Web Part has a several properties for the user to supply information to configure it (they will appear in the "Miscellaneous" section of the Web Part Properties Pane). The properties are as follows, the CAML property is optional, but it gives an easy way to filter information from the list without going into the code:
1: Private _siteurl As String = ""
2: <WebBrowsable(True), _
3: WebDisplayName("Web Site URL *"), _
4: WebDescription("The site the list is located."), _
5: Personalizable(PersonalizationScope.User)> _
6: Public Property SiteURL() As String
7: Get
8: Return Me._siteurl
9: End Get
10: Set(ByVal value As String)
11: Me._siteurl = value
12: End Set
13: End Property
14:
15: Private _listGuid As String = ""
16: <WebBrowsable(True), _
17: WebDisplayName("Test Management List GUID *"), _
18: WebDescription("Enter the GUID for the List."), _
19: Personalizable(PersonalizationScope.User)> _
20: Public Property ListGUID() As String
21: Get
22: Return Me._listGuid
23: End Get
24: Set(ByVal value As String)
25: Me._listGuid = value
26: End Set
27: End Property
28:
29: Private _camlQuery As String = ""
30: <WebBrowsable(True), _
31: WebDisplayName("CAML query for the webpart"), _
32: WebDescription("Enter the CAML for the List."), _
33: Personalizable(PersonalizationScope.User)> _
34: Public Property CAML() As String
35: Get
36: Return Me._camlQuery
37: End Get
38: Set(ByVal value As String)
39: Me._camlQuery = value
40: End Set
41: End Property
The first thing we need to do in the process is attach to the site and then web that we want we want to query. To do this I do the following:
1: ' Create a Web object to use through the routine
2: Dim Web as SPWeb
3: ' Crate a local SPWeb Object
4: Dim _thisweb As SPWeb
5: ' Open the site with the URL supplied by the user
6: Dim SiteColl As SPSite = New SPSite(SiteURL)
7: _thisweb = SiteColl.OpenWeb
8: ' Write the SPWeb Object to the property
9: Web = _thisweb
10: ' Clean up the SPWeb Object
11: _thisweb.Dispose()
12: _thisweb.Close()
This gives us an SPWeb object to play with. In the next snippet of code we attach to the list and create a CAML query object (SPQuery) that we will apply if it was supplied:
1: ' Attach to the list
2: Dim thisListGUID As Guid = New Guid(_listGuid)
3: Dim list As SPList = Web.Lists(thisListGUID)
4:
5: ' Define the Query
6: Dim query As SPQuery = New SPQuery(list.DefaultView)
7: If CAML <> "" Then
8: query.Query = CAML
9: End If
Now we have all of that set up, we can make the LINQ query. in this query we are simply retrieving the data, but it is possible to sort, group by etc. as well.
1: Dim linqQuery = From Z As SPListItem In list.GetItems(query) _
2: Select Z
Now we have the information, we can loop through it and do what we want with it. Below I have shown we can use a Lambda Query to group SharePoint information. In the example list I have Title, Category and Status fields and I want to group the information by the Category and then do something depending on the status, it may be some conditional formatting depending on other properties or maybe assign a task... anything really.
The reason for the substring is that SharePoint formats fields with an ID and then the Human Readable value, e.g. 1;#Status. We only want what is after the #.
1: Dim CategoryTitle as String = ""
2: For Each i In linqQuery.GroupBy(Function(a As SPListItem) a("Category"))
3: CategoryTitle = i.Key.ToString.Substring(i.Key.ToString.LastIndexOf("#") + 1)
4: For Each item In i
5: Select Case item("Status").ToString
6: Case "Open"
7: ' Do Something
8: Case "Closed"
9: ' Do Something
10: Case Else
11: ' Do Something else
12: End Select
13: Next
14: Next
Hopefully that is a fairly quick and easy demo of how to get information from SharePoint as objects and use them!