CODEWITHSUNDEEP
linq
Developer Webs
Developer Webs

Reputation: 1021

C# Linq orderby only works for fields returned?

I want to do a Linq query that joins three tables, but only returns data from two of them (the third is only joined for ordering purposes). I'm trying to order by columns that aren't in the output of the produced query, but they seem to be ignored:

var records = from q in _pdxContext.Qualifier
      join aql in _pdxContext.ApplicationQualifierLink on q.Id equals aql.QualifierId
      join qt in _pdxContext.QualifierType on q.QualifierTypeId equals qt.Id
      where SOME_LIST.Contains(aql.ApplicationId)

      orderby aql.Sequence

      select new Qualifier 
      { 
          Id = q.Id,
          QualifierType = new QualifierType 
          { 
              Id = qt.Id, Value = qt.Value
          }
      };
return records.Distinct().ToList();

The output SQL from this does NOT have an ORDER BY clause.

If I change the orderby to read like so:

orderby q.Id

... then the output SQL has the order by clause.

Does Linq ignore orderby statements when the mentioned columns aren't used in the output (as appears to be the case here)? If so, how do I order by columns not in the output?

Upvotes: 1

Views: 125

Answers (1)

Developer Webs
Developer Webs

Reputation: 1021

It seems this is an SQL limitation. The error from the SQL Server engine: "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." So, as written, I can't do what I want to do.

I ended up using:

using (var cnn = new SqlConnection(_connectionString))
{
    string sql = @"select
            min(q.Id) Id, q.QualifierTypeId, q.QualifierTypeId, min(q.AcaId) AcaId,
            q.QualifierTypeId Id, qt.Value
        from
            qdb.Qualifier q
            inner join qdb.QualifierType qt on qt.Id = q.QualifierTypeId
            inner join ApplicationQualifierLink l on l.QualifierId = q.id
        where   l.ApplicationId in (" + string.Join(",", applicationIds) + @")
        group by q.Text, q.QualifierTypeId, qt.Value";

    qualifiers = cnn.Query<Qualifier, QualifierType, Qualifier>(sql,
        (qualifier, type) =>
        {
            qualifier.QualifierType = type; return qualifier;
        }
    ).ToList();
}

Note: When you attempt to use order by and distinct as in my original clause, no error is given, entity framework silently discards the order by without any error.

Upvotes: 1

Related Questions