EntityFrameworkCore.Extensions.Sequences

EntityFrameworkCore extensions for operations on sequences.

This project is still in the making and highly experimental. Feel free to contribute via PRs.

Table of contents

• Targets
• Installation
• Supported providers
• Usage
  • Configure DbContext
  • Query sequences
  • Manage sequences
    • Create sequence
    • Alter/Update sequence
    • Drop sequence
• Limitations

Targets

• EF Core 3.1

Installation

Nuget packages

EntityFrameworkCore.Extensions.Sequences

EntityFrameworkCore.Extensions.Sequences.SqlServer

EntityFrameworkCore.Extensions.Sequences.PostgreSQL

Supported providers

• SqlServer
• PostgreSQL

Usage

Configure DbContext

optionsBuilder.UseSqlServer("connection", options => options.UseSequences());

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.UseSequences(this);
}

Query sequences

Retrieve functional IQueryable with these extensions

public static IQueryable<IDbSequence<long>> Sequences(this DbContext context)

public static IQueryable<IDbSequence<T>> Sequences<T>(this DbContext context)

Get all sequences in the database

using var context = new SampleDbContext()
{
  var sequences = context.Sequences()
                         .ToList();
}

Select specific data

var sequences = context.Sequences()
                       .Select(s => new
                       {
                         s.Name,
                         s.Schema,
                         s.CurrentValue
                       }).ToList();

The IDbSequence interface

public interface IDbSequence<T>
  where T : struct
{
  string Name { get; }

  string Type { get; }

  string Schema { get; }

  string Owner { get; }

  T CurrentValue { get; }

  T StartValue { get; }

  T MinValue { get; }

  T MaxValue { get; }

  T IncrementBy { get; }

  bool Cycle { get; }

  long? CacheSize { get; }
}

Manage sequences

All functionality can also be used asynchronously

Get next sequence value

var next = context.NextSequenceValue<long>("mysequence");

Within queries (current only for PostgreSQL)

var values = this.context.Sequences()
                         .Select(s => new
                         {
                           Name = s.Name,
                           NextValue = EF.Functions.NextValLong(s.Schema + "." + s.Name)
                         }).ToList();

Create sequence

bool success = context.CreateSequence(new DbSequence<long>
{
  Name = "mysequence"
});

Alter/Update sequence

bool success = context.UpdateSequence("mysequence", new DbSequenceUpdate<long>
{
  RestartWith = 1L
});

Drop sequence

bool success = context.DropSequence("mysequence");

Limitations

• IQueryables of type IDbSequence{T} can only be used for reading operations and should not be handed to things like bulk-extensions for CRUD.
• The DbFunctions extension EF.Functions.NextVal{Type}(sequence) cannot translate constant expressions for sequence names and currently only works for PostgreSQL
• DbContext.NextSequenceValue{T}(sequence) with PostgreSQL only works for types: long, decimal
• Sequences of type decimal can only work with whole numbers (1, not 1.5)