Using SPQuery to filter lists by User & Group Column

If you want to filter a list based on the value in the look up field that is of type ‘Person or Group’, We have to consider the following options:

• Filter by the User Name.
• Filter by SPUser ID Property
• Filter by Domain User account

Filter by the User Name:

By default the settings for the ‘Person or Group’ column will have the following settings.

The Show Field will have the “Name with Presence” selected.

When we run a SPQuery search on the list using the following code we will be able to filter the list based on the fullname of the user.

 using (SPSite oSite = new SPSite("http://moss:80"))

          {

              using (SPWeb oWeb = oSite.OpenWeb())

              {

                  oList = oWeb.Lists["Project Tasks"];

                  SPQuery query = new SPQuery();

                  query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>Tushar Parikh</Value></Eq></Where>";

                  SPListItemCollection items = oList.GetItems(query);



            }

         }

Filtering by SPUser ID :

If you want to filter the list based on the SPUser ID then follow the steps below.

• Add an additional attribute ‘LookupId’ for the queried field in your CAML query

<FieldRef Name='AssignedTo' LookupId='TRUE'/>

using (SPSite oSite = new SPSite("http://moss:80"))

           {

               using (SPWeb oWeb = oSite.OpenWeb())

              {

                  oList = oWeb.Lists["Project Tasks"];

                  SPQuery query = new SPQuery();

                  query.Query = "<Where><Eq><FieldRef Name='AssignedTo' LookupId='TRUE'/><Value Type='User'>7</Value></Eq></Where>";

                 SPListItemCollection items = oList.GetItems(query);

               }

         }

Filtering by Domain User Account :

If you want to filter the list based on the Domain User Account then follow the steps below.

• Change the ‘Show Field’ Settings of the Person or Group lookup column to ‘Account’

Modify your code to include the domain account in the filter value.

using (SPSite oSite = new SPSite("http://moss:80"))

{

using (SPWeb oWeb = oSite.OpenWeb())

{

oList = oWeb.Lists["Project Tasks"];

SPQuery query = new SPQuery();

query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>komal\tparikh</Value></Eq></Where>";

SPListItemCollection items = oList.GetItems(query);

}

}