Give Me Every Nth Row: Reproducible Sampling in SQL Server and EF Core

For a recent project, I needed a small, representative sample of a large table, and a few circumstances made that less straightforward than it sounds. Here’s the sampling approach I landed on.

The job itself was simple enough: run some processing over a configurable slice of a very large table. Not the whole set — that would take too long to iterate on — but a sample big enough to be representative. And “representative” had a specific meaning here: the data had accumulated over many years, and its shape drifted over time. A naive sample that grabbed the first or last N rows would miss that variety. I wanted a slice that spanned the full diversity of the data — roughly every Nth row, spread evenly across the table — and I wanted it to be reproducible, so I could re-run the job and get the same set.

That rules out some obvious options. OFFSET … FETCH and TOP (n) give you contiguous blocks. You could shuffle that up with ORDER BY NEWID(), but it’s random, different every run. There was another wrinkle, too: I didn’t have a nice tidy integer primary key to modulo. The natural identifier was a string.

It turns out SQL Server has the right primitive for all of this, and with a little plumbing you can call it straight from an EF Core LINQ query.

The SQL trick: ABS(CHECKSUM())

Here’s the whole idea in one line:

WHERE ABS(CHECKSUM(some_column)) % 100 = 0

This selects roughly 1 in 100 rows, deterministically, spread across the whole table.

CHECKSUM is a built-in SQL Server function that hashes one or more values down to a 32-bit integer, and it works on strings.

It does return a signed integer, which makes the sampling a little harder to reason about, so ABS() flips the negatives.

Together, this has a few nice properties:

• It’s deterministic / reproducible – as we iterate on application logic, we can run the exact same sampled set through repeatedly to get apples-to-apples comparisons.
• Roughly uniform – the checksum spreads out the data nicely, so that each of those 100 “buckets” is about the same size.
• Want a different sample? Change the right side to get a different bucket! (e.g. x % 100 = 1)

If you’re writing raw SQL, that’s the end of the story. But I wanted to use this inside an EF LINQ query, alongside all our other filters, but..

EF Core has never heard of CHECKSUM

My filters were composable LINQ expressions, and I wanted the “every Nth” filter to be just another one — something that ANDs cleanly with the rest of the query rather than a hand-concatenated SQL string. The problem is that EF Core doesn’t know what CHECKSUM is. There’s no EF.Functions.Checksum().

Fortunately EF has a well-defined extension point for exactly this: user-defined function mapping, which can be wired up from OnModelCreating.

1. Stub methods as translation targets

First, declare methods that EF can recognize inside a query. These never actually run — they exist only for EF to reflect over — so their bodies just throw:

public static class SqlServerFunctions
{
    public static int Checksum(int value) =>
        throw new NotSupportedException("This method is for use in EF Core queries only.");

    public static int Abs(int value) =>
        throw new NotSupportedException("This method is for use in EF Core queries only.");

    public static int AbsChecksum(int value) =>
        throw new NotSupportedException("This method is for use in EF Core queries only.");

    public static int AbsChecksum(string? value) =>
        throw new NotSupportedException("This method is for use in EF Core queries only.");
}

2. Register functions

There are two kinds of mapping. For functions that correspond one-to-one to a SQL function, HasName plus IsBuiltIn() is enough.

modelBuilder.HasDbFunction(() => SqlServerFunctions.Checksum(default))
    .HasName("CHECKSUM").IsBuiltIn();

modelBuilder.HasDbFunction(() => SqlServerFunctions.Abs(default))
    .HasName("ABS").IsBuiltIn();

For our new shorthand composite AbsChecksum, we supply a translation that builds the SQL expression tree:

modelBuilder.HasDbFunction(() => SqlServerFunctions.AbsChecksum(default(int)))
    .HasTranslation(TranslateAbsChecksum);

modelBuilder.HasDbFunction(() => SqlServerFunctions.AbsChecksum(default(string)))
    .HasTranslation(TranslateAbsChecksum);   // both overloads share one translation

private static SqlExpression TranslateAbsChecksum(IReadOnlyList<SqlExpression>  args)
{
    var intTypeMapping = new IntTypeMapping("int");

    // CHECKSUM(arg)
    var checksum = new SqlFunctionExpression(
        "CHECKSUM",
        args,
        nullable: false,
        argumentsPropagateNullability: args.Select(_ => false),
        typeof(int),
        intTypeMapping);

    // ABS( CHECKSUM(arg) )
    return new SqlFunctionExpression(
        "ABS",
        new[] { checksum },
        nullable: false,
        argumentsPropagateNullability: new[] { false },
        typeof(int),
        intTypeMapping);
}

SqlFunctionExpression is the building block; nesting the CHECKSUM call inside the ABS call’s arguments produces ABS(CHECKSUM(arg)).

Using it

With that in place, the “every Nth” filter is just another LINQ predicate:

query.Where(c => SqlServerFunctions.AbsChecksum(c.SomeColumn) % denominator == remainder);

…and EF emits exactly the SQL we wanted:

WHERE ABS(CHECKSUM([c].[SomeColumn])) % @denominator = @remainder

Wrapping up

It feels like there ought to be an easier way to do this, and maybe I missed a cleaner solution. But I don’t mind, because it turned into an excuse to explore some cool corners of SQL and EF.

CHECKSUM is a neat little function, especially in how it handles a variety of input types. But the part that really struck me was on the EF side. As a mere user of the library, I can declare builtin SQL functions, describing database features that the ORM authors haven’t gotten around to covering — and have them slot right into ordinary LINQ.

So even if there’s a shorter path I overlooked, I came away with a clean, reusable filter and a bit more appreciation for the tools. I hope it saves you some time, too.