Thursday, May 14, 2009

How to get distinct value from CAML query

Many times, we face a challenge to get unique rows froma CAML query. The main problem is that CAML does not have any feature to get the distinct rows in one go.

Way Around:

We all know that CAML query returns SPListItemCollection and we can convert the outcome to data table also.
We can convert the resultant datatable to dataview and again convert the dataview to datatable by using ToTable and arguments :
Distinct = true
and column name

So..
suppose we have SPWeb object as 'web' so

DataTable DT= web.GetSiteData(qry);
DataView v = new DataView(tbl);
return v.ToTable(true, "Type");


This code snippets returns the distinct rows by comparing column name "Type".

3 comments:

Anonymous said...

good job

Chanakya said...

Great Post.. You rock!!!

Anonymous said...

Doesn't really work on linst with >1M items. Not enough memory )))