CODEWITHSUNDEEP
sqllinqt-sqllinq-to-sqlstored-procedures
Androme
Androme

Reputation: 2449

Need help creating a linq select

I need some help creating an LINQ select, i have a table with some columns in it, but only 2 of interest to this problem. 

userid, type

Now this table have many thousands entries, and I only want the top, let’s say 50. So far so good, but the hard part is that there a lot of rows in success that should only be counted as 1. Example

Type  UserId
============
Add   1
Add   1
Add   1
Add   2

I would like this to only be counted as 2 in the limit of rows I am taking out, but I would like all the rows to be outputted still. Is this possible with a single SQL request, or should I find another way to do this?

Edit: I can add columns to the table, with values if this would solve the problem. Edit2: Sotred procedures are also an solution

Example 2: This should be counted as 3 rows

Type  UserId
============
Add   1
Add   1
Add   2
Add   1

Upvotes: 4

Views: 110

Answers (4)

paparazzo
paparazzo

Reputation: 45096

Are you stuck on LINQ?

Add a PK identity.
Order by PK.
Use a DataReader and just count the changes.
Then just stop when the changes count is at your max.

If you are not in a .NET environment then same thing with a cursor.

Since LINQ is deferred you might be able to just order in LINQ and then on a ForEach just exit.

Upvotes: 1

Timothy Shields
Timothy Shields

Reputation: 79461

You can do this with LINQ, but I think it might be easier to go the "for(each) loop" route...

data.Select((x, i) => new { x.Type, x.UserId, i })
    .GroupBy(x => x.Type)
    .Select(g => new
    {
        Type = g.Key,
        Items = g
            .Select((x, j) => new { x.UserId, i = x.i - j })
    })
    .SelectMany(g => g.Select(x => new { g.Type, x.UserId, x.i }))
    .GroupBy(x => new { x.Type, x.i })
    .Take(50);
    .SelectMany(g => g.Select(x => new { x.Type, x.UserId }));

Upvotes: 1

D Stanley
D Stanley

Reputation: 152521

You could do it with Linq, but it may be a LOT slower than a traditional for loop. One way would be:

data.Where((s, i) => i == 0 || 
                     !(s.Type == data[i-1].Type && s.UserId == data[i-1].UserId))

That would skip any "duplicate" items that have the same Type and UserID as the "previous" item.

However this ONLY works if data has an indexer (an array or something that implements IList). An IEnumerable or IQueryable would not work. Also, it is almost certainly not translatable to SQL so you'd have to pull ALL of the results and filter in-memory.

If you want to do it in SQL I would try either scanning a cursor and filling a temp table if one of the values change or using a common table expression that included a ROW_NUMBER column, then doing a look-back sub-query similar to the Linq method above:

WITH base AS
(
SELECT 
    Type,
    UserId, 
    ROW_NUMBER() OVER (ORDER BY ??? ) AS RowNum
    FROM Table
)
SELECT b1.Type, b1.UserId
FROM base b1
LEFT JOIN base b2 ON b1.RowNum = b2.RowNum - 1
WHERE (b1.Type <> b2.Type OR b1.UserId <> b2.UserId)
ORDER BY b1.RowNum

Upvotes: 1

Xtian Macedo
Xtian Macedo

Reputation: 835

I'm not close to a computer right now so I'm not sure is 100% correct syntax wise, but I believe you're looking for something like this:

data.Select(x => new {x.Type, x.UserId})
    .GroupBy(x => x.UserId)
    .Take(50);

Upvotes: 1

Related Questions