Retrieving Distinct Records using Query Expression in Microsoft CRM 2011

distinctdynamics-crm-2011record

I have a doubt in retrieving records in CRM 2011 using C# RetrieveMultiple method in a web service.

I need to retrieve distinct records from an entity based on a attribute value(primary key). I am able to achieve this by using the code below

QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1");
query.Distinct = true;
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

Output:

primarycolumn  column1
xyz            1
lmn            2

This displays distinct records. But if I add few more columns to the columnset, the results are not distinct. This is shown in the code below

QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1", "column2");
query.Distinct = true;
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

Output:

primarycolumn  column1 column2 
xyz            1        a        
xyz            1        b 
lmn            2        a

I need the result to be distinct only based on the primary column.

Please help me in how to achieve this.

  • More information on the above question.

Actually, the code is in C#. The table below shows the table which I am querying

**Primary
column    Column1              Column2             Column3<br/>**



Xyz            Value1              Value1               Value1 <br/>
Xyz            Value2              Value2               Value2<br/>
Lmn            Value1              Value1               Value1<br/>
Lmn            Value2               Value2              Value2<br/>
Xyz            Value1              Value1              Value1<br/>
Lmn                      Value1             Value1               Value1<br/>

The query result should be like below. Where only the primary columns distinct values must be considered and all other columns can be either distinct or not distinct. Any one row of the distinct value of the primary column should only be displayed.

(This is the output I am trying to achieve)<br/>
**Prmrycolumn   Column1 Column2 Column3<br/>**
Xyz Value1  Value1  Value1<br/>
Lmn Value1  Value1  Value1<br/>

The below code gives the following output

     QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1");

    query.Distinct = true;
    EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

    **Primary column    Column1**<br/>
    Xyz Value1<br/>
    Lmn Value1<br/>

But as I add more columns to the columnset, the output is considering distinct values of other columns also

QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1", "column2");
query.Distinct = true;
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

**Primary
column  Column1 Column2 Column3<br/>**
Xyz Value1  Value1  Value1<br/>
Xyz Value2  Value2  Value2<br/>
Lmn Value1  Value1  Value1<br/>
Lmn Value2  Value2  Value2<br/>

ctually, the code is in C#. The table below shows the table which I am querying

**Primary
column    Column1              Column2             Column3<br/>**



Xyz            Value1              Value1               Value1 <br/>
Xyz            Value2              Value2               Value2<br/>
Lmn            Value1              Value1               Value1<br/>
Lmn            Value2               Value2              Value2<br/>
Xyz            Value1              Value1              Value1<br/>
Lmn                      Value1             Value1               Value1<br/>

The query result should be like below. Where only the primary columns distinct values must be considered and all other columns can be either distinct or not distinct. Any one row of the distinct value of the primary column should only be displayed.

(This is the output I am trying to achieve)<br/>
**Prmrycolumn   Column1 Column2 Column3<br/>**
Xyz Value1  Value1  Value1<br/>
Lmn Value1  Value1  Value1<br/>
The below code gives the following output

QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1");
query.Distinct = true;
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

**Primary column    Column1**<br/>
Xyz Value1<br/>
Lmn Value1<br/>

But as I add more columns to the columnset, the output is considering distinct values of other columns also

QueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1", "column2");
query.Distinct = true;
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

**Primary
column  Column1 Column2 Column3<br/>**
Xyz Value1  Value1  Value1<br/>
Xyz Value2  Value2  Value2<br/>
Lmn Value1  Value1  Value1<br/>
Lmn Value2  Value2  Value2<br/>

Best Answer

Editted Answer:

I need the result to be distinct only based on the primary column.

The above requirement is not possible using the RetrieveMultiple service call. What you are essentially asking for is a way to retrieve the first record for each record with a unique primary column. This might be possible using generated Early Bound entities but it is not possible using the standard service methods alone.

To achieve what you are after, you will need to retrieve all the records, which is what your current query does:

ueryExpression query = new QueryExpression("entityname");
query.ColumnSet.AddColumns("primarycolumn", "column1", "column2");
query.Distinct = true; // this distinct will apply over all columns
EntityCollection result1 = serviceProxy.RetrieveMultiple(query);

Once this data has been received, you will need to filter out all the records you want to ignore. In your case, you only want the first record found. You can achieve this using C# code as follows:

// this code filters out all records except the 
// first for each unique primary column
var unique = result1.Entities.GroupBy(item => item.GetAttributeValue<Guid>("primarycolumn"))
                             .Select(item => item.First());
Related Topic