LINQ to SQL query not ordering properly, please help.

Question

    var temp = (from assetVisit in db.AssetVisits
                join assetBundle in db.AssetBundles on assetVisit.AssetID equals assetBundle.AssetID
                join groupBundle in db.GroupBundles on assetBundle.BundleID equals groupBundle.BundleID
                join userGroup in db.UserGroups on groupBundle.GroupID equals userGroup.GroupID
                where assetVisit.CompanyID == companyID &&
                      userGroup.UserID == userID
                select new { AssetID = assetVisit.AssetID, Count = assetVisit.AccessCounter }).Distinct();

    IQueryable<Asset> final = (from t in temp
                              join asset in db.Assets on t.AssetID equals asset.AssetID
                              where asset.IsActive == true
                                    && asset.AssetTypeID == assetType
                                    && asset.ShowInResults == true
                                    && (asset.CompanyID == companyID || asset.CompanyID == -12081974)
                              orderby t.Count descending
                              select asset).Except(from companyAssets in db.Assets
                                            join copiedAssets in db.Assets on companyAssets.AssetID equals copiedAssets.OriginalAssetID
                                            where copiedAssets.CompanyID == companyID && companyAssets.CompanyID == -12081974 && copiedAssets.IsActive == true
                                            select companyAssets);


    return final.Take(limit);

OK so it's suppose to give back the assets in order based on t.Count but I think it might not be working because the .Count is actually not part of asset which is what is being selected, but I have no idea how to fix this.

As you can see there is an assetVisits table and an assets table, and I need to get back the assets in order of the assetVisits.AccessCount but I can't get it to work, what the hell??

Answer 1

You asked an almost identical question a couple of hours ago, and the answer is the same: do the ordering after you have selected the rows you want to order.

Change:

return final.Take(limit);

to:

var finalOrdered = from asset in final
    join assetVisit in db.AssetVisits on asset.AssetID equals assetVisit.AssetID
    orderby assetVisit.AccessCounter
    select asset;

return finalOrdered.Take(limit);

You can also remove the premature 'orderby' from your own code, since it is not doing anything.

Answer 2

You query is missing the ordeby clause. You have one in a subquery, but placing orderby anywhere but on the outermost query expression is irelevant (except for when top is also used).

You have to specify the orderby on the outermost query.