问题描述:

I'm new to linq and I need a certain join. So I have two tables for certain pages I want to join with Linq that are something like

UserFavorites table:

Page.id

userClickCount

Page table:

id

everyoneClickCount

A user favorite is created when it's clicked or made a favorite so there are only a certain amount of links. I want to show all results from both tables, sort them by the most clicked by the user, then most clicked by everyone.

I have this right now but it sorts by the everyone count.

pages = (from page in context.Page

join ps in

(from favs in context.UserFavorites

select favs) on page.Id equals ps.Page.Id into temp

from t in temp.DefaultIfEmpty()

orderby t.userClickCount descending, t.Page.everyoneClickCount descending, t.Page.PageName ascending

select dash).ToList();

I'm just not sure where to go from here.

网友答案:

Try this:

var pages = context.Pages.Select(page => new { 
                                               PageData = page, 
                                               Favs = page.UserFavorites 
                                             })
                         .OrderByDescending(page => (page.Fav.Sum(userClickCount))
                         .ThenBy(page => page.PageData.everyoneClickCount)
                         .ThenBy(page => page.PageData.Name)
                         .ToList();

Note that this will order by the sum of all users' click counts. If you just want the current user, replace that line with .OrderByDescending(page => (page.Fav.Where(u => u.userID == userid).Sum(userClickCount))

网友答案:

For complex joins I suggest writing a stored procedure and then using EF and linq to call this SP.

相关阅读:
Top