Joining Lists using CAML and LINQ

This post demonstrates how to use the JOIN syntax in CAML.

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.

using System;
using System.Linq;
using Microsoft.SharePoint;

Next, the code to query the list. 

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.

class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://spstc.sharepoint.com"))
{
    SPWeb web = site.RootWeb;

    SPQuery query = new SPQuery();

    query.Joins = "<Join Type='INNER' ListAlias='DSE'>" +
                    "<Eq>" +
                        "<FieldRef Name='Manager' RefType='Id'/>" +
                        "<FieldRef List='DSE' Name='ID'/>" +
                    "</Eq>" +
                    "</Join>";
    query.ProjectedFields =
        "<Field Name='DSEFirstName' Type='Lookup' " +
                "List='DSE' ShowField='FirstName'/>" +
        "<Field Name='DSELastName' Type='Lookup' " +
                "List='DSE' ShowField='Title'/>";

    query.ViewFields = "<FieldRef Name='Title'/>" +
                        "<FieldRef Name='DSEFirstName'/>" +
                        "<FieldRef Name='DSELastName'/>";
    SPList customerList = web.Lists["Projects"];
    SPListItemCollection items = customerList.GetItems(query);
    foreach (SPListItem item in items)
    {
        SPFieldLookupValue dseLastName =
            new SPFieldLookupValue(item["DSELastName"].ToString());
        SPFieldLookupValue dseFirstName =
            new SPFieldLookupValue(item["DSEFirstName"].ToString());

        Console.WriteLine("{0}  {1}   {2}",
                item.Title,
                dseLastName.LookupValue,
                dseFirstName.LookupValue);
    }
}
}
}
}

The results are pretty unimpressive, but it proves that our CAML query works.

And there you have it, your first CAML JOIN query. 

Querying Using LINQ

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:

spmetal /web:http://spstc.sharepoint.com /namespace:Microsoft.PFE.DSE.Samples /code:PFE.cs
  

  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.

using (PFEDataContext ctx = new PFEDataContext("http://spstc.sharepoint.com"))
{
    ctx.Log = Console.Out;
    var results = from c in ctx.Customers
                  select c;
    foreach (var item in results)
    {
        Console.WriteLine("{0}  {1}   {2}",
        item.Title,
        item.DSE.LastName,
        item.DSE.FirstName);
    }
}

Just to show the results…

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.

Connect Infopath form controls to SharePoint List data

Please go through the screens. It's very much self explanatory 

Above is the List view in sharepoint

In Infopath Designer, create an Infopath form and put on designer surface Drop-Down List Boxcontrol, click on it and press Alt+Enter to open it properties.

There, under Data tab, against choose "Get choices from an external data source" and press add press "Add..." button against "Data Source:"

Starting data connection wizard for Drop-Down List box in Infopath Designer 2010

Note that data connection can be created separately by a dozen of variants

Step2 of Data Connection Wizard "Select the source of your data"

Step3 of Data Connection Wizard "Sharepoint site details"

Step4 of Data Connection Wizard "The Sharepoint site site that you slected has the following lists and libraries you can use as your data connection"

Step5 of Data Connection Wizard "The Sharepoint list has the following fields you can can select from"

Running it:

Running Infopath form with Drop-Down List Box linked to a column ClientList of ClientList list

Hope this helps..

How to use Log parser with IIS logs

Download and install log parser 2.2 from below link:

http://www.microsoft.com/en-us/download/details.aspx?id=24659   

Make sure you read all the software usage policies and agreements before you use any software.

Once you install the software, you will find the exe and related files at- C:\Program Files\Log Parser 2.2

Copy the files and put it in a folder of your convenience.

Copy the log file folder from C:\inetpub\logs\LogFiles\

The folder name would be something like W3SVC1612407519 in which ‘1612407519’ will be ID of the web application from IIS which you can find out as shown below:

The log files in the folder will be text files. Screenshot below:

So now that we have the folder, copy the bat files to the same folder in which we have all the files and log file folder.

Click on edit you will see a query like:

LogParser.exe -i:IISW3C "SELECT TOP 1000 cs-uri-stem as URIStem, COUNT(*) as Hits INTO C:\Users\libin.makkalikkal\Desktop\TestLogparser\URLHitDetails.csv FROM C:\Users\libin.makkalikkal\Desktop\TestLogparser\W3SVC1612407519\* WHERE URIStem Is Not Null AND date>'2013-01-15' GROUP BY URIStem" -e 1

The file path in green is the path of csv file into which we are writing the output. The file path in red is the path where IIS logs are located. Please change the date highlighted in blue to the date starting from which we require data. Edit the bat file with new file locations and date, save and run it.

You will see the ouput csv file created in the folder with the query results.

You can use the query to get information from a specific time period if used as below:

SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 3600) AS Hour,
    COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\*
WHERE date>'2010-03-01' and date<'2010-04-01' Group By Hour

Some useful links to create and edit the log parser queries:

http://blogs.msdn.com/b/carlosag/archive/2010/03/25/analyze-your-iis-log-files-favorite-log-parser-queries.aspx

http://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/

http://www.msexchange.org/articles-tutorials/exchange-server-2003/tools/Using-Logparser-Utility-Analyze-ExchangeIIS-Logs.html

How to remove 'Pages-' from SharePoint page title

This is a pain if you have activated publishing, Page title will appear as Pages-'Name'

Master page code looks like this:

This place holder will be replaced with title from the page layout.

Here goes the code from page layout:

Long story short, The two important lines here are 36 & 37. Line 36 adds the list’s name (in our case, Pages), line 37 (<SharePointWebControls:FieldValue FieldName=”Title” runat=”server”/>) adds the title value of the page (so the actual title).

I removed line 36 (and removed the dash from line 37), the 'pages' titles are now displayed as I wanted them to:

Hope this helps!