Most relational databases are a modern marvel, with many features waiting for you to use. However, for .NET developers, you often need to discover methods to get data into and out of our database instances that work for our use cases. In this post, you’ll explore Microsoft SQL Server’s (MSSQL) Cursor and how to use it with the Entity Framework Core APIs.

What’s an SQL Server Cursor?

Anyone managing a database will ultimately need to perform complex data manipulation outside the realm of simple Update statements. Cursors allow you to create a complete set of results within the scope of a database transaction. Then, you can iterate this result set performing read operations and, in some circumstances, updates. Regarding MSSQL, you also have cursor operations that allow us to move forward and backward in absolute and relative steps.

Typically, to create a Cursor, you need to take the following steps:

  1. Open a database transaction.
  2. Create a cursor definition using the Declare <name> Cursor construct.
  3. Open the cursor to start the iteration process.
  4. Use the FETCH keyword to get the next row of interest.
  5. Once complete, CLOSE and DEALLOCATE the cursor.

You’ll see an explicit example of a cursor further in the article. To read more about MSSQL Cursors, I recommend reading the documentation.

Using Entity Framework Core With Cursors

I’ve taken the liberty of generating a database table of 10,000 Person rows for this post.

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

The DbContext implementation is straightforward, but I’ll include it here for completeness. To make the code below work, you’ll need references to Entity Framework Core and Bogus.

using Bogus;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApp3;

public class Database : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // sql server connection string template
        optionsBuilder.UseSqlServer(@"Server=localhost,11433;Database=Curses;User=sa;Password=Pass123!;Encrypt=false");
    }

    public static async Task InitializeDatasetAsync()
    {
        await using var db = new Database();

        if (await db.People.AnyAsync())
        {
            // already initialized database
            Console.WriteLine("Database is already initialized");
            return;
        }

        var faker = new Faker<Person>();
        faker.RuleFor(m => m.Name, f => f.Person.FullName);

        var stored = 0;
        var chunkSize = 1_000;
        var totalCount = 10_000;
        foreach (var chunk in faker.GenerateLazy(totalCount).Chunk(chunkSize))
        {
            stored += chunkSize;
            Console.WriteLine($"Saving {stored} of {totalCount}");
            await db.People.AddRangeAsync(chunk);
            await db.SaveChangesAsync();
            db.ChangeTracker.Clear();
        }
        
        Console.WriteLine("Complete");
    }
}

Now that you know what our DbContext implementation looks like, you’ll need to look at the code required to iterate over a cursor.

using ConsoleApp3;
using Microsoft.EntityFrameworkCore;

// wait for data to load
await Database.InitializeDatasetAsync();

// we need a transaction
await using var db = new Database();
await using var transaction = db.Database.CurrentTransaction ?? 
                              await db.Database.BeginTransactionAsync();

var sqlCursor = 
    @"
    Use Curses -- My Database
    DECLARE people_cursor CURSOR  FOR 
    Select * 
    from People
    where People.Name like 'A%';
    
    Open people_cursor;
    ";

// the cursor is being declared in the scope of transaction
await db.Database.ExecuteSqlRawAsync(sqlCursor);
var count = 0;

while (true)
{
    var people =
        await db.People.FromSqlRaw("FETCH NEXT FROM people_cursor;")
            .AsNoTracking()
            .ToListAsync();

    // reached the end
    if (people.Count == 0) {
        break;
    }

    count += people.Count;
    Console.WriteLine($"Retrieved {count} from database.");
}

await db.Database.ExecuteSqlRawAsync(
    @"CLOSE people_cursor;  
      DEALLOCATE people_cursor;"
);

await transaction.CommitAsync();

The first step in the preceding code is to start a database transaction. You can use EF Core’s Database.CurrentTransaction property or the Database.BeginTransactionAsync method. You must have an open transaction and manage it ourselves. When the transaction ends, so does the existence of our defined cursor.

await using var transaction = db.Database.CurrentTransaction ?? 
                              await db.Database.BeginTransactionAsync();

Next, you’ll need to define the cursor and its result set. You’ll also take this time to open the cursor.

var sqlCursor = 
    @"
    Use Curses -- My Database
    DECLARE people_cursor CURSOR  FOR 
    Select * 
    from People
    where People.Name like 'A%';
    
    Open people_cursor;
    ";

// the cursor is being declared in the scope of the transaction
await db.Database.ExecuteSqlRawAsync(sqlCursor);

Once the cursor exists within your transaction, you can start to iterate over the result set. Since the result set is based on the Person type, you can use the DbSet on the Database instance.

var people =
    await db.People.FromSqlRaw("FETCH NEXT FROM people_cursor;")
        .AsNoTracking()
        .ToListAsync();

The next point is essential, Cursors fetch a single row at a time, but the FromSqlRaw method always returns a collection. The EF Core API expects you to use the ToListAsync method or another collection iterator method.

Once you’ve retrieved all the rows, you’ll want to close the cursor and deallocate its resources at the database instance.

await db.Database.ExecuteSqlRawAsync(
    @"CLOSE people_cursor;  
      DEALLOCATE people_cursor;"
);

There you have it, you’ve successfully used a SQL Cursor to retrieve data from SQL Server.

Should I Use Cursors?

Hold up! Before you switch all your queries to use cursors, you should consider the advantages and disadvantages of the approach.

Advantages of MSSQL Cursors

There are a few reasons to use Cursors.

  1. Cursors can provide deterministic result sets isolated from other SQL operations.
  2. Cursors allow for updates to rows as well as reads.
  3. Advanced positioning methods like Absolute and Relative allow you to skip around a defined result set more logically.

Disadvantages of MSSQL Cursors

There are a few reasons you may not want to use a Cursor when retrieving data. Here are just a few that I could imagine might make them unfeasible for some developers.

  1. Each row fetch is a network call, which may be efficient for memory usage but inefficient for network I/O utilization.
  2. Cursors require resources at the database that you could use for other operations.
  3. Transaction management can be a pain in the butt, and EF Core typically will manage transactions for you transparently.
  4. One row at a time can be slower than batching rows.
  5. SQL is limited to an approved subset of operations.

Conclusion

While there are undoubtedly many ways to retrieve data from your MSSQL instance, cursors present advantages over typical paging approaches. However, as mentioned in the previous section, you also have some disadvantages. You’ve seen how to use the EF Core API to work with Cursors and retrieve data. I want to thank Twitter user suchoss for the idea of this post, and I hope you found it insightful and helpful.