Nested Query doesn't include order by

This query

var attachments = from a in DBAttachments
                  where a.CropType == CropType.Header && (
                    from usi in DBUserSetItems
                     join at in DBAttachments on usi.ImageId equals at.Id
                     orderby usi.Id descending
                     where at.ParentObjectNumber != null
                       && usi.UserSetId == 58
                       && !string.IsNullOrEmpty(at.CdnUrl)
                     select at.ParentObjectNumber
                  ).Contains(a.ParentObjectNumber)
                  select a.CdnUrl;
 attachments.FirstOrDefault().Dump();

is translated to:

SELECT
  Attachment.*
FROM
  (
    SELECT
      Attachment.CdnUrl AS [Attachment.CdnUrl],
      ROW_NUMBER() OVER(ORDER BY Attachment.Id) as RowNumber
    FROM
      Attachment
    WHERE
      ((Attachment.CropTypeId = 2 AND Attachment.ParentObjectNumber IN (SELECT
        t1.ParentObjectNumber AS [t1.ParentObjectNumber]
      FROM
        UserSetItem t0
      INNER JOIN
        Attachment t1
      ON
        t0.AttachmentId = t1.Id AND t1.DeletedOn IS NULL
      WHERE
        ((t1.ParentObjectNumber IS NOT NULL AND t0.UserSetId = 58) AND NOT ((t1.CdnUrl IS NULL OR t1.CdnUrl = '')))
      )) AND Attachment.DeletedOn IS NULL)
  )
  Attachment
WHERE
  RowNumber <= 1

which seems to be missing the order by clause. Tested on todays nightly build

I presume you are referring to "orderby usi.Id descending" in the sub-query? If so this is being deliberately stripped as the provider (SQL Server) doesn't support ORDER BY in subqueries.

Jeremy

I think I emailed about a similar issue. Could LightSpeed throw "unsupported by provider" or "not implemented" exceptions along some of these paths?

Our normal approach for syntax which is not supported by the provider is that we just let it report the problem so its easier for you to look up details about this within the product documentation/external sources. We could change this for this case also but because this would be a breaking change we wont be considering this right now. The reason we are stripping this is that the ORDER BY isn't actually needed and since its not supported we can work around the problem automatically - maybe a case of the product being a bit too smart for itself perhaps :)

As an aside we are going to be improving the way we collate/track feature requests and user interest soon so hopefully that should help improve the transparency of that process :) There has been a marked increase of this type of activity on the forum so it would be good to have a more appropriate forum for hosting that since to date we have been tracking this all internally.

Jeremy

Should have mentioned this in the original post, my bad: An order by in a nested subquery will work if you specify a TOP clause which I also can't express using LINQ, unless I'm mistaken, As wrapping the query and doing a Take on it threw another exception.

Can you provide an example of the query and some details on the exception that was thrown?

Thanks!
Jeremy