tags:

views:

60

answers:

2
    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??

+8  A: 

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.

Mark Byers
no the last one included a lot less code and I never got it to work so I thought maybe my issue was the surrounding code, which I am realizing is true
shogun
No, the issue is exactly the same as before - you are again ordering too early. I've updated my post to include the code you need to change.
Mark Byers
crap now I'm getting duplicates...
shogun
Then that is indeed a new question.
Mark Byers
How can I add Distinct to that finalOrdered query as well as keep the order by if I am selecting the assets and but the order by value is in assetVisits??
shogun
I think I am confused about how to use the .OrderByDescending() because I know if I am now getting duplicates that I need to add the .Distinct() to finalordered (though I am not sure why the first query's Distinct() is not enough) but as soon as I add it I must remove the 'order by' and add it to the end, however I do not know what to put into the parentheses so that it orders by a variable that is not actually part of the thing I am finally selecting (assets), does this make sense? How can this be done?
shogun
If you need to order by something that is not selected then you can join with the extra tables you need to pull that field in, order by, then select again. But don't do distinct after doing that. If you don't know why you are getting duplicate records, perhaps you should look into that first. There might be an error in your data model, or a problem with the data in your database.
Mark Byers
OK, thanks Mark!
shogun
+2  A: 

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.

Remus Rusanu
I think this is exactly what I need but which one is the outermost query?
shogun
also how can I get the order by into the outermost query, how can I make it contain the t.Count variable to sort by? I can not figure this out for the life of me...
shogun
Technically the result of `final.Take(limit)` is the outermost query. However the `Take` operator (top) is an exception case (together with `Skip`), so you can have the order before the Take. Mark has lready gave you the answer to that.
Remus Rusanu