Linq to Sql: returning limited number of rows per group

Imagine have a Products table with following data:

    ProductID    UserName   ProductName
    -----------------------------------
    1            User1      Product1
    2            User1      Product2
    3            User2      Product3
    4            User3      Product4
    5            User1      Product5
    6            User2      Product6
    7            User2      Product7
    8         开发者_运维技巧   User1      Product8

Also there is another table named UserLimit:

    UserName    DisplayLimit
    -------------------------
    User1       3
    User2       2
    User3       2

This table indicates maximum number of products that should be returned for each user.

What I want to accomplish is to have a Linq to Sql query that returns this:

    ProductID     UserName    ProductName
    --------------------------------------
    1             User1       Product1
    2             User1       Product2
    5             User1       Product5
    3             User2       Product3
    6             User2       Product6
    4             User3       Product4

Which number of products returned for each user is limited to the DisplayLimit value in UserLimit table.

---

Using grouping on user, you can easily get to each group containing the right number of products.

var limitedUsers = from p in dc.Products
                   group p by p.UserName into g
                   select new
                   {
                     UserName = g.Key,
                     Products = g.Take(dc.UserLimits
                                         .Where(u => u.UserName == g.Key)
                                         .Single().DisplayLimit)
                   };

---

Not sure how link-to-sql would fit into this but if it's possible you may try to use DENSE_RANK function. For example, the query below will return last 3 projects for each user. The limit is common for all users but nothing really prevents from linking to a table with limits.

declare @numberOfProjects int
set @numberOfProjects = 3

;with topNProjects(userid, projectid, createdtutc, dense_rank)
as (
    select p.userid, P.ProjectId, p.CreatedDtUtc, DENSE_RANK() OVER (PARTITION BY P.UserId ORDER BY P.ProjectId) AS DENSE_RANK
    from DS_Project P
)
select userid, projectid, createdtutc from topNProjects
where dense_rank <= @numberOfProjects
order by projectid desc

EDITED: tried LINQed solution and came up with the following. I think all you will have to do is to replace my collections with references to tables in data context.

        Dictionary<int, string> userProjects = new Dictionary<int, string>
        {
            { 1, "User1" },
            { 2, "User1" },
            { 3, "User2" },
            { 4, "User3" },
            { 5, "User1" },
            { 6, "User2" },
            { 7, "User2" },
            { 8, "User1" },
        };
        Dictionary<string, int> limits = new Dictionary<string, int>
        {
            { "User1", 3 },
            { "User2", 2 },
            { "User3", 2 },
        };


        var ranked = 
            from up in userProjects
            join l in limits on up.Value equals l.Key
            orderby up.Value
            where (from upr in userProjects
                   where upr.Value == up.Value && upr.Key <= up.Key
                   select upr).Count() <= l.Value
            select up;

The result is as follows which seems is what you are looking for:

1, User1
2, User1
5, User1
3, User2
6, User2
4, User3

---

If the query needs to be Linq you probably need something like this:

var q = tblProducts.where(p => false);
foreach (var item in tblDispLimit)
    q = q.Concat(tblProducts.where(p => p.UserName == item.UserName).Take(item.DisplayLimit));