Linq to Sql: return a limited number of rows in a group

Question

Imagine a product table with the 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

There is also another table called UserLimit:

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

This table indicates the maximum number of products that must be returned for each user.
I want to execute 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

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

Answer 1

Using grouping on the user, you can easily get into each group containing the right amount 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)
                   };

Answer 2

, sql , , DENSE_RANK. , 3 . , .

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: LINQed . , , , .

        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;

, , , , :

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

Answer 3

If the query should 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));