SQL Linq, Row Number() Over, equiv in VB

jkirkerx

So I had someone help me write this years ago. It's a Navigator fly out tab, in which if your working on a product in the editor, you can activate the tab, and the tab will populate with lets say 4 products before and 4 products after the selected product in the database table. This is so you don't have to go back to the index to load the next or previous product. Since SQL Linq doesn't support the custom SQL Server Function, I need to think or create an alternative way to do this. I guess I can create a List(Of with a blank column, and then go back and fill in the row numbers get the row number of the item and ask for -3 and +3 of the row. Just looking for some suggestions. Here's the old TSQL

WITH CTE AS
(
SELECT DISTINCT
ProductID
, PostageImage
, PartNumber
, ShortDescription
, Price
, Thumbnail
, ActionThumbnail1
, ActionThumbnail2
, ActionThumbnail3
, FlatFee
, FlatFeeName
, VendorName
, ROW_NUMBER() OVER(ORDER BY PartNumber DESC) AS RowId
FROM PRODUCTINFO p
), CTE1 AS (
SELECT RowId FROM CTE WHERE PartNumber = @PartNumber
)
SELECT * FROM CTE WHERE RowId BETWEEN (SELECT RowId-4 FROM CTE1) AND (SELECT RowId+3 FROM CTE1) ORDER BY PartNumber

This is what I was building in Linq Pad. It's pretty weak now, but I'm getting an idea of what I can do. But before I go off in some strange direction that will lead nowhere or be super slow, I thought I would just try and get a general consensus on this. I tried to mark it as code but it gets chopped off, end of the day for me, going home now. Dim p_partNumber as string = "06-SM4" Dim context as new DBcontext Dim ordersAsList = _ ( From pi in context.ProductInfo Select pi ).ToList() Dim orders = _ ( From pi in ordersAsList take 3 Select pi.ProductID ) orders.Dump()

Richard Deeming

A custom extension method is probably the simplest option here: C#:

static class Extensions
{
public static IEnumerable<T> TakeWindow<T>(this IEnumerable<T> source, Func<T, bool> pivot, int numberBefore, int numberAfter)
{
if (source == null) throw new ArgumentNullException("source");
if (pivot == null) throw new ArgumentNullException("pivot");
if (numberBefore < 0) throw new ArgumentOutOfRangeException("numberBefore");
if (numberAfter < 0) throw new ArgumentOutOfRangeException("numberAfter");

    if (numberBefore == 0)
    {
        return source.SkipWhile(x => !pivot(x)).Take(1 + numberAfter);
    }

    return TakeWindowIterator(source, pivot, numberBefore, numberAfter);
}

private static IEnumerable<T> TakeWindowIterator<T>(IEnumerable<T> source, Func<T, bool> pivot, int numberBefore, int numberAfter)
{
    var queue = new Queue<T>(numberBefore);

    foreach (T item in source)
    {
        if (queue != null && pivot(item))
        {
            foreach (T previous in queue)
            {
                yield return previous;
            }

            queue = null;

            yield return item;
        }
        else if (queue == null)
        {
            if (numberAfter == 0)
            {
                yield break;
            }

            yield return item;
            numberAfter--;
        }
        else
        {
            if (queue.Count == numberBefore)
            {
                queue.Dequeue();
            }

            queue.Enqueue(item);
        }
    }
}

}

VB.NET:

Module Extensions
<System.Runtime.CompilerServices.Extension>
Public Function TakeWindow(Of T)(ByVal source As IEnumerable(Of T), ByVal pivot As Func(Of T, Boolean), ByVal numberBefore As Integer, ByVal numberAfter As Integer) As IEnumerable(Of T)
If source Is Nothing Then Throw New ArgumentNullException("source")
If pivot Is Nothing Then Throw New ArgumentNullException("pivot")
If numberBefore < 0 Then Throw New ArgumentOutOfRangeException("numberBefore")
If numberAfter < 0 Then Throw New ArgumentOutOfRangeException("numberAfter")

jkirkerx

I didn't know you can do that. I know how slow it can get when the record count get high. That's why I didn't want to do the list. I'll give that a try for now, and then redesign the whole thing later. I knew that using special DB function would take a tool in the future when I implemented it. Thanks Richard!

jkirkerx

Works like a charm. Took awhile to figure out how to implement it. Did you write that? it's pretty elegant and wild in how it works. It would of taken me months to figure that out! But that should be the most complex TSQL remaining in my program. Oh, this conversion I'm making is mentally draining on me every day, but I'm 1/2 way there now. The reward will pay off for me when done. Thanks again for taking the time to write that, that was beyond my current knowledge level.

Richard Deeming

jkirkerx wrote:

Did you write that?

Yes. It seemed like the logical approach. The hardest part was choosing a name!

jkirkerx wrote:

it's pretty elegant and wild in how it works.

:-O

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

I've just made a minor modification, to cope with the possibility that more than one item in the input sequence could match the pivot predicate:

' Old:
' If pivot(item) Then

' New:
If queue IsNot Nothing AndAlso pivot(item) Then

It probably won't happen with your data, but better safe than sorry! :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

Got It! Thanks, Does work great! pretty fast as well