为什么此LINQ TO SQL查询不能以正确的顺序返回?

问题描述

(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()

请注意排序依据"，但是当数据返回时，它是由assetVisit表的PK排序的，那有什么用?

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)

解决方案

您的排序依据"应该在之后与众不同.

    (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);

或者，如果您希望继续使用查询语法，则可以这样编写:

    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;

(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)

解决方案

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;

这篇关于为什么此LINQ TO SQL查询不能以正确的顺序返回?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！