Using SPQuery to filter lists by Domain User Account and SPUser ID

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://vslearnwss:801"))

             {

                using (SPWeb oWeb = oSite.OpenWeb())

               {

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

                  SPQuery query = new SPQuery();

                    query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>Karthikeyan K</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'/>

The updated code is as follows.

   using (SPSite oSite = new SPSite("http://vslearnwss:801"))

              {

                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://vslearnwss:801"))

            {

                using (SPWeb oWeb = oSite.OpenWeb())

                {

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

                    SPQuery query = new SPQuery();

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

                    SPListItemCollection items = oList.GetItems(query);

                }

            }