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.

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

   2:            {

   3:                using (SPWeb oWeb = oSite.OpenWeb())

   4:                {

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

   6:                    SPQuery query = new SPQuery();

   7:                    query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>Karthikeyan K</Value></Eq></Where>";

   8:                    SPListItemCollection items = oList.GetItems(query);

   9: 

  10:                }

  11:            }

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.

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

   2:            {

   3:                using (SPWeb oWeb = oSite.OpenWeb())

   4:                {

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

   6:                    SPQuery query = new SPQuery();

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

   8:                    SPListItemCollection items = oList.GetItems(query);

   9:                }

  10:            }

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);

                }

            }