SharePoint Fun

Hey all, I just wanted to point out a sneaky little problem we ran into today at work. I’ll outline it briefly here, but Donabel wrote a great summary that we’ve posted on the Black Ninja blog.

The problem to be aware of is related to SPQuery objects that have the LookupId set to TRUE and the Value Type set to Lookup. An example:

1
2
3
4

SPQuery oQuery = new SPQuery();
 
oQuery.Query = "<Where><Eq><FieldRef Name='Employee' LookupId='TRUE'/>" +
            "<Value Type='Lookup'>161</Value></Eq></Where>";

With a NON indexed column, the above query works fine. Once you change the Employee field so it is an indexed column, the above query will fail to work. Changing Value Type from Lookup to Integer seems to make it work again. So somehow, something is changing when a column is indexed thus causing the query to behave strangely.

Removing the index on that column makes everything work magically again. Thanks to the following post for affirming what we had already painfully discovered.

Ok, if you’re like me, writing CAML queries can sometimes make your head hurt. Not because it’s terribly complicated, but because there’s not much useful information out there that demonstrates, with REAL examples, how these should be written. So let’s see if we can clarify this:

Scenario 1

Get me all items in a list WHERE fullName equals the currently logged in user.

1
2
3
4
5
6
7
8
9
10
11

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
 
oQuery.Query = 
    "<Where>" + 
    "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" + 
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 2

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’.

1
2
3
4
5
6
7
8
9
10
11
12
13

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" + 
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 3

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’ AND manager is James Lane.

This is where it gets a bit tricky. The following example is INCORRECT and will produce an error when run:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

This is the correct way to do it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<And>" +
            "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "</And>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 4

Get me all items WHERE fullName equals the currently logged in user AND status equals ‘Complete’ OR status equals ‘On Hold’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Or>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>On Hold</Value></Eq>" +
        "</Or>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

I really do recommend if you’re doing any work with CAML queries that you download the U2U CAML Query Builder 2007. It doesn’t come without it’s issues, for example, doing a query on a list with a column of type Lookup does not always yield the results I would expect, but it’s still quite helpful. I also often find myself changing the Value Type from Lookup to Text but other than that it’s a huge resource when trying to determine if the data is actually in the list or if there is a problem with the query i’ve written.

Any feedback or questions, please drop me a line.

To my knowledge (and someone please post and correct me if I’m wrong) there doesn’t seem to be a solid way to build an SPQuery object that can return distinct values from a list. I’ve seen some clever tricks for getting around this particular problem, but nothing quite like this.

If you dig through this post a little bit, you’ll find the technique to getting distinct values comes down to leveraging a DataView.ToTable() method to return only distinct results.

So let’s break this down a bit. A typical scenario for me is to build a data table that contains all of my data (via an SPQuery to the list), set the data source of my control to that data table and then bind.

(In my case, I work specifically with the Telerik RadGrid and the Telerik RadComboBox but the same idea applies to the standard ASP.NET controls)

1
2
3
4
5

DataTable groupedDataTable = originalDataTable.DefaultView.ToTable(true, "Title")
 
dataGrid.DataSource = groupedDataTable;
 
dataGrid.DataBind();

The ToTable() method accepts two parameters as demonstrated above.

1. bool distinct simply indicates whether or not to supply distinct values
2. params string[] columnNames is a string array that contains a list of the column names in the orignal data table that you’d like to be returned