- Caml Query Sharepoint List
- Update List Item Using Caml Query Sharepoint 2013
- Caml Query Sharepoint List View
This post demonstrates how to use the JOIN syntax in CAML.
You cannot use this kind of complex query is you don't have column 'Yellow' and its count. Assuming your ripeness column has value Yellow you can use below code. Autocad mechanical 2012 activation codes. We have a SharePoint custom list with the name ‘MySPList’ which has the following columns.Title, AssignedTo and Status. We want to get all the data from the list for which AssignedTo has a value of ‘Ramprasad’. CAML Query: For the above scenario, the CAML query should look like the following. Apr 11, 2015 CAML QUERY to retrieving the items from a list using CSOM Next Recommended Reading Update A List Item In SharePoint Using CSOM (Announcement List). This script allows to a CAML query against an existing list in a SharePoint Site Collection. The script defines a function that uses standard cmdlets such as SPSite, SPWeb to get the specific list we want to query. In order to do a CAML query, it’s required to instantiate a SPQuery object in PowerShell.
SharePoint 2010 adds the ability to create relational lists. To use the JOIN operator in CAML, your lists must have a defined relation. Let’s start by creating the lists and their relationships.
Creating the Lists
I first add a standard Contacts list named “DSE” and populate it with some data.
Go to the list settings for your new contact list and hover over the column names. Many SharePoint developers forget this, the name you see in the web UI is the .Title property, which may be different than the internal name that SharePoint uses (the .InternalName property of the SPField object). For a quick way to see what the actual field name is, hover over the column name and look in the address bar in your browser, and we see that the “Last Name” column is actually the “Title” field.
Next, I create a custom list named “Projects” and add a column named “Manager”. The type is a lookup column. Note the name “Manager”, we’ll refer to this name in our code.
In the additional column settings section for the “Manager” column, I set the lookup to the ID column for the DSE list, and additionally show the First Name and Last Name.
When I add a new item to the Projects list, I get a drop-down that lets me select a value from the lookup column.
The result looks like this:
Now that we have the lists, a lookup field, and some data, let’s query it.
Querying Using JOINS and SPQuery
Nothing speaks louder than a code sample. At the top of my code, I add a few using directives.
The first thing to notice is the SPQuery.Joins property which lets me provide 1 or more joins for the list. The list must already have a lookup column and a relation defined to the list being joined to. Notice in the Joins property that we refer to the name that we provided when we created the lookup column, “Manager”.
The second thing to notice is the ProjectedFields property. This is where we tell SharePoint how to project the lookup columns into the result. In a contacts list, the “First Name” column has an internal name of “FirstName”. The “Last Name” column is actually the Title column (see above for how to determine the .InternalName of a field).
Finally, the ViewFields property lets us define which fields are included in the result. We use the same name that we used in the ProjectedFields property. This name could be anything, so long as the name in ProjectedFields and the name in ViewFields match.
The results are pretty unimpressive, but it proves that our CAML query works.
And there you have it, your first CAML JOIN query.
Caml Query Sharepoint List
Querying Using LINQ
Update List Item Using Caml Query Sharepoint 2013
Just for kicks, I will throw in a LINQ sample, too. LINQ doesn’t support the JOIN operator, nor does it support a projection to a referenced entity. What you can do, though, is just reference the entity in the results.
I opened the Visual Studio 2010 Tools command window and ran the following command:
This generates the strongly-typed entities for me behind the scenes with metadata that tells LINQ how to query the results. I include the result, PFE.cs, in my Visual Studio project and run the following code. Notice I add the ctx.Log setting to log output to the Console window. That lets me include a picture of a WHOLE BUNCH of CAML in the screen shot below.Caml Query Sharepoint List View
Using your own JOIN operation can be more efficient, but test the results to be sure. Often times LINQ will generate more efficient queries, but not always.