Why won't this LINQ TO SQL query come back in

问题:

(from assetVisit in AssetVisits
                        join assetBundle in AssetBundles on assetVisit.AssetID equals assetBundle.AssetID
                        join groupBundle in GroupBundles on assetBundle.BundleID equals groupBundle.BundleID
                        join userGroup in UserGroups on groupBundle.GroupID equals userGroup.GroupID
                        where assetVisit.CompanyID == 32 &&
                              userGroup.UserID == 75
                        orderby assetVisit.AccessCounter descending
                        select assetVisit).Distinct()

notice the 'order by' however when the data comes back it is ordered by the PK of the assetVisit table, what gives?

SQL PER LINQPAD:

-- Region Parameters
DECLARE @p0 Int SET @p0 = 32
DECLARE @p1 Int SET @p1 = 75
-- EndRegion
SELECT DISTINCT [t0].[AssetVisitID], [t0].[CompanyID], [t0].[AssetID], [t0].[AccessCounter], [t0].[CreateBy], [t0].[CreateDate], [t0].[ModifyBy], [t0].[ModifyDate]
FROM [AssetVisits] AS [t0]
INNER JOIN [AssetBundles] AS [t1] ON [t0].[AssetID] = ([t1].[AssetID])
INNER JOIN [GroupBundles] AS [t2] ON ([t1].[BundleID]) = [t2].[BundleID]
INNER JOIN [UserGroups] AS [t3] ON [t2].[GroupID] = [t3].[GroupID]
WHERE ([t0].[CompanyID] = @p0) AND ([t3].[UserID] = @p1)

回答1:

Your "order by" should be after the distinct.

    (from assetVisit in AssetVisits
     join assetBundle in AssetBundles on assetVisit.AssetID equals assetBundle.AssetID
     join groupBundle in GroupBundles on assetBundle.BundleID equals groupBundle.BundleID
     join userGroup in UserGroups on groupBundle.GroupID equals userGroup.GroupID
     where assetVisit.CompanyID == 32 &&
               userGroup.UserID == 75
     select assetVisit).Distinct().OrderByDescending(assetVisit => assetVisit.AccessCounter);

Or if you wish to keep using the query syntax, you could write it like this instead:

    var innerQuery =
        (from assetVisit in AssetVisits
         join assetBundle in AssetBundles on assetVisit.AssetID equals assetBundle.AssetID
         join groupBundle in GroupBundles on assetBundle.BundleID equals groupBundle.BundleID
         join userGroup in UserGroups on groupBundle.GroupID equals userGroup.GroupID
         where assetVisit.CompanyID == 32 &&
                   userGroup.UserID == 75
         select assetVisit).Distinct();

    var query = from assetVisit in innerQuery
            orderby assetVisit.AccessCounter descending
            select assetVisit;

回答2:

I ran into this before. The distinct operator "undoes" any ordering that you have specified earlier in your LINQ statment.

Here's a good explaination: http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx