Select Distinct Error

Question

I have a table (audit) that contains information about audits that have been completed.  I am trying to get a distince list of the firms that have been audited.

 

If i use the following code everything works as expected when I loop through the results:

 

entities = From e In db.audits Select e.entity_id, e.entity_long_name Distinct Order By entity_long_name

For Each e In entities

'do something with e

'Next

 

If I try to filter the data using the following code i get an error when trying to loop through the results (error is thrown on the For line):

 

entities = From e In db.audits _

Where e.audit_type_id = 23 _

Select e.entity_id, e.entity_long_name Distinct Order By entity_long_name

 

For Each e In entities

'do something with e

'Next

 

ERROR: Column audit_type_id is not accessible through 'distinct'

 

If i run the following query against my database it executes correctly:

 

SELECT DISTINCT entity_id, entity_long_name FROM audit WHERE audit_type_id = 23 ORDER BY entity_long_name

 

Any ideas on what i am doing wrong?

 

 

Answer 1

Hi,

I've tried to replicate your example using Northwind and the Orders table. Nothing wrong with that query on Northwind database. Are you using Orcas beta 1? Try to write something like this and let us know if works:

 

Code Snippet

entities = (From e In db.audits _

Where e.audit_type_id = 23 _

Select e.entity_id, e.entity_long_name _

Order By entity_long_name).Distinct()

 

For Each e In entities

'do something with e

'Next

 

 

 

Best,

Fabio

Answer 2

Yes I am using Orcas Beta 1.  I followed your code sample and that fixed the problem i was having selecting distinct items BUT now the Order By clause is being ignored.  The results are listed by the entity ID instead of the entity_long_name.

Answer 3

The DISTINCT operator in SQL undoes ordering, as does UNION.  So the LINQ Union(), Intersect() and Concat() operators will also lose ordering when translated to SQL.

Answer 4

When you say that Distinct undoes the order do you mean in LINQ only since the following SQL statement returns the Distinct list in the name order.

 

SELECT DISTINCT entity_id, entity_long_name FROM audit WHERE audit_type_id = 23 ORDER BY entity_long_name

Answer 5

Yes, of course. Matt speaks about the LINQ's Distinct() method behaviour.

Best,

Fabio

Answer 6

I'm having the same error message. Column 'keyword' is not accessible through 'distinct'

I have 3 simple tables:

Questions: id, question

Keywords: id, keyword

relations: question_id, keyword_id

 

I'm getting that error when I try to run the following code 

Code Snippet

Dim k1 = From r In db.relations Where r.Keyword.keyword = "k1" Select r.question_id

Dim k2 = From r In db.relations Where r.Keyword.keyword = "k2" Select r.question_id

Dim k12 = From id In k1.Intersect(k2), q In db.Questions Select q

Dim s As String = db.GetQueryText(k12)

 

Answer 7

The trouble comes when you need to order-by something that is later removed by a Select projection.  SQL cannot have an order-by expression anywhere except on the outer-most query, except for a few special cases.  LINQ to SQL must actually rewrite your orderby expression and lift them to the outside of the query.  Generally, this means that an orderby cannot reference any member that is later removed by projection. However, LINQ to SQL attempts to lift this data to the outer query too if its referenced by the orderby.  Unfortunately, it cannot succeed at doing this when specific operaters are used such that by adding a column to the underlying select statement would end-up changing the meaning of the query. Adding an extra column to a select statement with a DISTINCT operation would cause this. That's why you were seeing the original exception.

 

Answer 8

It seems to me that there is indeed a bug in the query generator. Sql Server is perfectly fine with the following query:

 

select distinct top N t1.*
from t1 join t2 on t1.FK = t2.PK

where t2.value = 'blar'

order by t1.value;

 

There does not seem to be a way to write a linq statement that will be happily translated to this query. The exception complains that t2 columns are not accessible through 'distinct' 

Answer 9

The only way that I found to return distinct ordered rows is that you MUST have the columns that are in the where clause also appear in the select clause:

YourLinqDataContext db = new YourLinqDataContext();
var result = (from a in db.t1
             where a.t2.value == "blar"
             select new {
                 a.firstColumn, a.value ..., a.lastColumn, a.t2.value
             }).Distinct().OrderBy(a => a.value);

Anyways, the main point is that the values in your 2nd table that you are using in the where clause MUST appear in the select clause for this to work and for you to not get the exception.

So for the example in the OP, the following should produce what you wanted (at the expense of returning an extra column that you will not use)

entities = From e In db.audits _

Where e.audit_type_id = 23 _

Select e.entity_id, e.entity_long_name, e.audit_type_id Distinct Order By entity_long_name

 

For Each e In entities

'do something with e

'Next

Answer 10

Yes, this is the workaround and the real fix needs to be in the query generator.