Querying: Distinct
The Distinct
method allows you to remove duplicates from query results.
Items are compared based on the fields listed in the select
section of the query.
Sample Query with Distinct
// returns sorted list of countries w/o duplicates
IList<string> countries = session
.Query<Order>()
.OrderBy(x => x.ShipTo.Country)
.Select(x => x.ShipTo.Country)
.Distinct()
.ToList();
// returns sorted list of countries w/o duplicates
IList<string> countries = session
.Advanced
.DocumentQuery<Order>()
.OrderBy(x => x.ShipTo.Country)
.SelectFields<string>("ShipTo.Country")
.Distinct()
.ToList();
from Orders
select distinct ShipTo.Country
Paging with Distinct
A special approach must be used when calling distinct()
while paging.
Please read the dedicated article about paging through tampered results.
Count with Distinct
Use Count()
in combination with Distinct()
to get the number of unique items.
Similar to ToList(), Count() triggers query execution on the server-side.
// results will contain the number of unique countries
var numberOfCountries = session
.Query<Order>()
.Select(x => x.ShipTo.Country)
.Distinct()
.Count();
// results will contain the number of unique countries
var numberOfCountries = session
.Advanced
.DocumentQuery<Order>()
.SelectFields<string>("ShipTo.Country")
.Distinct()
.Count();
Performance Cost And Alternative Approaches
Please keep in mind that using Count()
with Distinct()
might not be efficient for large sets of data due to the need to scan all of the index results in order to find all the unique values.
- Getting the distinct items' count can also be achieved by creating a Map-Reduce index that will aggregate data by the field for which distinct count results are needed.
- This is more efficient since computations are done during indexing time and not at query time.
The entire dataset is Indexed once, whereafter the aggregated value is always kept up to date as indexing will occur only for new/modified data.
Map-Reduce Index Sample:
Index definition:
// Define static index
public class Employees_ByCountry : AbstractIndexCreationTask<Employee, Employees_ByCountry.IndexEntry>
{
public class IndexEntry
{
public string Country { get; set; }
public int CountryCount { get; set; }
}
public Employees_ByCountry()
{
// The Map phase indexes the country listed in each employee document
// CountryCount is assigned with 1, which will be aggregated in the Reduce phase
Map = employees => from employee in employees
select new IndexEntry
{
Country = employee.Address.Country,
CountryCount = 1
};
// The Reduce phase will group the country results and aggregate the CountryCount
Reduce = results => from result in results
group result by result.Country into g
select new IndexEntry
{
Country = g.Key,
CountryCount = g.Sum(x => x.CountryCount)
};
}
}
Query the index:
// Query the map-reduce index defined above
var queryResult = session.Query<Employees_ByCountry.IndexEntry, Employees_ByCountry>()
.FirstOrDefault(x => x.Country == "UK");
var numberOfEmployeesFromCountry = queryResult?.CountryCount ?? 0;
// Query the map-reduce index defined above
var queryResult = session.Advanced.DocumentQuery<Employees_ByCountry.IndexEntry, Employees_ByCountry>()
.WhereEquals("Country", "UK").FirstOrDefault();
var numberOfEmployeesFromCountry = queryResult?.CountryCount ?? 0;
Combining Faceted Queries with Map-Reduce
Faceted queries can be used together with a map-reduce index as another alternative approach.
See the article "Implementing a count(distinct) query in RavenDB" for an example.