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;

       }

Sorting SPListItemCollection using LINQ

Using LINQ to sort SPLiStItemCOllection:

SPList objRelationShipList;

SPListItemCollection objRelationShipListItems;

objRelationShipList = SPContext.Current.Web.Lists["ListName"];

objRelationShipListItems = objRelationShipList.Items;

List<SPListItem> results = (from SPListItem item in objRelationShipListItems           

                            orderby item.ID

                            select item).ToList();

foreach(SPListItem in results)

{

....

}

Cheers

LINQ to SharePoint provider - Part 1

LINQ to SharePoint provider enables us to use Language Integrated Query (LINQ) queries in our Microsoft SharePoint Foundation solutions. Rocking features of this provider are, it enables changing data using optimistic concurrency, object change tracking and resolution of conflicts is also supported by LINQ to SharePoint. Here I am introducing a very simple usage of LINQ in sharepoint 2010. To use the attached code first of all create a list named "Customers" having columns "Name", "CustomerId", "City".

Download Code : http://www.min.us/mb2BFA1cb6oztV