UNIQUE Query in Sharepoint, CAML

Sharepoint doesnot provides any API through which we can retrieve unique records, nor we can do this by CAML. Although we can use LINQ, but there is another way to make the records unique after retreiving from sharepoint.

We can use method of DataView to make the data unique, Then this dataView can be converted to DataTable with unique values. Below is the example to do this:

public System.Data.DataTable GetUniqueData()

       {

           SPQuery objSPQuery = null;

           SPListItemCollection objSPListItemCollection;

           SPList objSPList;

           System.Data.DataTable objDataTable;

           DataView objDataView;

           using (SPSite objSPSite = new SPSite("<Your Site URl>"))

            {

                using (SPWeb objSPWeb = objSPSite.OpenWeb())

                {

                    try

                    {

                        objSPQuery = new SPQuery();

                        //write your query here

                        objSPQuery.Query = @"<Where>

                                              <IsNotNull>

                                                 <FieldRef Name='ID' />

                                              </IsNotNull>

                                           </Where>";

                        objSPList = objSPWeb.Lists["<List Name>"];

                        //Fill the list item collection

                        objSPListItemCollection = objSPList.GetItems(objSPQuery);

                       

                        if (objSPListItemCollection.Count > 0)

                        {

                            //convert the SPListItemCollection to Datatable

                            objDataTable = objSPListItemCollection.GetDataTable();

                            //Create a Dataview for applying UNIQUE

                            objDataView = new DataView(objDataTable);

                            //Convert the datatview to Table again by

specifying paramater 'distict' to true.

                            // the second parameter should be the column to be

checked for unique.

objDataTable = objDataView.ToTable(true, "<column                                  name(s) that you want to check for unique>");

                        }

                    }

                    catch(Exception Ex)

                    {

                        throw Ex;

                    }

                }

           }

           return objDataTable;

       }