Code | SQL Query | Programming

Google Cloud Spanner Provider For Entity Framework Core

We’re very excited to announce the general availability of the Google Cloud Spanner provider for Entity Framework Core, which allows your Entity Framework Core applications to take advantage of Cloud Spanner‘s scale, strong consistency, and up to 99.999% availability. In this post, we’ll cover how to get started with the provider and highlight the supported features.

Set Up the Project

The Cloud Spanner provider is compatible with Microsoft.EntityFrameworkCore 3.1. After you have set up Entity Framework Core, add the Cloud Spanner provider to the project. You can also do this by editing your csproj file as follows:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>
  ...
  <ItemGroup>
    <PackageReference Include="Google.Cloud.EntityFrameworkCore.Spanner" Version="1.0.0" />
  </ItemGroup>
</Project>

Set Up Cloud Spanner

Before you begin using Cloud Spanner:

  1. Follow the Set Up guide to configure a Cloud Project, authentication and authorization.
  2. Then create a Cloud Spanner instance and database following the Quickstart using the Cloud Console.

Setup a new database

If you don’t have an existing database, you may use the following example (also available on GitHub) to create a new model, populate it with data, then query the database. See Migrating an Existing Database below if you have an existing database.

Data model

We will use the following data model, created using the Cloud Console for simplicity.

CREATE TABLE Singers (
  SingerId  STRING(36) NOT NULL,
  FirstName STRING(200),
  LastName  STRING(200) NOT NULL,
  FullName  STRING(400) NOT NULL AS (COALESCE(FirstName || ' ', '') || LastName) STORED,
) PRIMARY KEY (SingerId);
CREATE INDEX Idx_Singers_FullName ON Singers (FullName);
CREATE TABLE Albums (
  AlbumId     STRING(36) NOT NULL,
  Title       STRING(100) NOT NULL,
  SingerId    STRING(36) NOT NULL,
  CONSTRAINT FK_Albums_Singers FOREIGN KEY (SingerId) REFERENCES Singers (SingerId),
) PRIMARY KEY (AlbumId);
CREATE TABLE Tracks (
  AlbumId         STRING(36) NOT NULL,
  TrackId         INT64 NOT NULL,
  Title           STRING(200) NOT NULL,
) PRIMARY KEY (AlbumId, TrackId), INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

Create a model

Data is accessed as a model in Entity Framework Core, which contains the entities, the context representing the database context and configuration for the entities.

In this example model, we have three Entities, representing a Singer, an Album and a Track.

public class Singer
{
    public Singer()
    {
        Albums = new HashSet<Album>();
    }
    public Guid SingerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    // FullName is generated automatically from FirstName and LastName.
    public string FullName { get; set; }
    public virtual ICollection<Album> Albums { get; set; }
}
public class Album
{
    public Album()
    {
        Tracks = new HashSet<Track>();
    }
    public Guid AlbumId { get; set; }
    public string Title { get; set; }
    public virtual Singer Singer { get; set; }
    public virtual ICollection<Track> Tracks { get; set; }
}
public class Track
{
    public Track()
    {
    }
    public Guid AlbumId { get; set; }
    public long TrackId { get; set; }
    public string Title { get; set; }
    public virtual Album Album { get; set; }
}

On configuring the model, we use two different approaches to defining relationships between entities:

  1. Album references Singer using a foreign key constraint, by including a Singer in the Album entity. This ensures that each Album references an existing Singer record, and that a Singer cannot be deleted without also deleting all Albums of that Singer.
  2. Track references Album by being interleaved in the parent entity Album, and is configured through OnModelCreating() with a call to InterleaveInParent(). This ensures that all Track records are stored physically together with the parent Album, which makes accessing them together more efficient.
public class MusicDbContext : DbContext
{
    private readonly string _connectionString;
    public MusicDbContext(string connectionString)
    {
        _connectionString = connectionString;
    }
    public MusicDbContext(string connectionString, DbContextOptions<MusicDbContext> options)
        : base(options)
    {
        _connectionString = connectionString;
    }
    public virtual DbSet<Singer> Singers { get; set; }
    public virtual DbSet<Album> Albums { get; set; }
    public virtual DbSet<Track> Tracks { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        // Configure Entity Framework to use a Cloud Spanner database.
        => options.UseSpanner(_connectionString);
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Album>(entity =>
        {
            entity.HasKey(entity => new { entity.AlbumId });
        });
        modelBuilder.Entity<Singer>(entity =>
        {
            // FullName is generated by Cloud Spanner on each add or update.
            entity.Property(e => e.FullName).ValueGeneratedOnAddOrUpdate();
        });
        modelBuilder.Entity<Track>(entity =>
        {
            // Track is INTERLEAVED IN PARENT Album.
            entity
                .InterleaveInParent(typeof(Album), OnDelete.Cascade)
                .HasKey(entity => new { entity.AlbumId, entity.TrackId });
        });
    }
}

Insert data

Data can be inserted into the database by first creating an instance of the database context, adding the new entities to the DbSet defined in the model, and finally saving the changes on the context.

The provided connection string must be in the format of Data Source=projects/<my-project>/instances/<my-instance>/databases/<my-database>.

string connectionString = "Data Source=projects/my-project/instances/my-instance/databases/my-database";
// Create a DbContext that uses our sample Spanner database.
using var context = new MusicDbContext(connectionString);
var singer = new Singer
{
    SingerId = Guid.NewGuid(),
    FirstName = "Bob",
    LastName = "Allison",
};
context.Singers.Add(singer);
var album = new Album
{
    AlbumId = Guid.NewGuid(),
    Title = "Let's Go",
    Singer = singer,
};
context.Albums.Add(album);
var track = new Track
{
    Album = album,
    TrackId = 1L,
    Title = "Go, Go, Go",
};
context.Tracks.Add(track);
// This saves all the above changes in one transaction.
Console.WriteLine("Writing Singer, Album and Track to the database…");
var count = await context.SaveChangesAsync();
Console.WriteLine($"{count} records written to the database\n");

Query data

You may query for a single entity as follows:

Console.WriteLine("Querying singers...");
var singers = await context.Singers
    .Where(s => s.FullName == "Bob Allison")
    .ToListAsync();
Console.WriteLine($"Found {singers.Count} singer(s) with full name {singers.First().LastName}");

You can also use LINQ to query the data as follows:

Console.WriteLine("Querying singers with LINQ...");
var singersStartingWithBo = context.Singers
    .Where(s => s.FullName.StartsWith("Bo"))
    .OrderBy(s => s.LastName)
    .AsAsyncEnumerable();
Console.WriteLine("Singers with a name starting with 'Bo':");
await foreach (var singer in singersStartingWithBo)
{
    Console.WriteLine($"{singer.FullName}");
}

Migrate an existing database

The Cloud Spanner Entity Framework Core provider supports database migrations. Follow this example to generate the data model using Migrations with the data model being the source of truth. You can also let Entity Framework Core generate code from an existing database using Reverse Engineering. Take a look at Managing Schemas for further details.

Features

Transaction support

By default the provider applies all changes in a single call to SaveChanges in a transaction. If you want to group multiple SaveChanges in a single transaction, you can manually control the read/write transactions following this example.

If you need to execute multiple consistent reads and no write operations, it is preferable to use a read-only transaction as shown in this example.

Entity Framework Core feature support

Entity Framework Core supports concurrency handling using concurrency tokens, and this example shows how to use this feature with the Cloud Spanner provider.

Cloud Spanner feature support

Besides interleaved tables mentioned above, the provider also supports the following Cloud Spanner features.

Commit timestamps

Commit timestamp columns can be configured during model creation using the UpdateCommitTimestamp annotation as shown in the sample DbContext. The commit timestamps can be read after an insert and/or an update, based on the configured annotation, as shown in this example.

Mutations

Depending on the transaction type, the provider automatically chooses between mutations and DML for executing updates.

An application can also manually configure a DbContext to only use mutations or only use DML statements for all updates. This example shows how to use mutations for all updates. However, note the following caveats when choosing these options:

  1. Using only Mutations will speed up the execution of large batches of inserts/updates/deletes, but it also doesn’t allow a transaction to read its own writes during a manual transaction.
  2. Using only DML will reduce the execution speed of large batches of inserts/updates/deletes that are executed as implicit transactions.

Query Hints

Cloud Spanner supports various statement hints and table hints, which can be configured in the provider by using a Command Interceptor. This example shows how to configure a command interceptor in the DbContext to set a table hint.

Stale reads

Cloud Spanner provides two read types. By default all read-only transactions will default to performing strong reads. You can opt into performing a stale read when querying data by using an explicit timestamp bound as shown in this example.

Generated columns

Cloud Spanner supports generated columns, which can be configured in the provider using the ValueGeneratedOnAddOrUpdate annotation in the model. This example shows how a generated column can be read after an entity is saved.

Limitations

The provider has some limitations on generating values for primary keys due to Cloud Spanner not supporting sequences, identity columns, or other value generators in the database that will generate a unique value that could be used as a primary key value. The best option is to use a client side Guid generator for a primary key if your table does not contain a natural primary key.

Getting involved

The Cloud Spanner Entity Framework Core provider is an open-source project on GitHub and we welcome contributions in the form of feedback or pull requests.

We would like to thank Knut Olav Løite and Lalji Kanjareeya for their work on this integration, and Ben Wulfe for their earlier work on the project.

By: Shanika Kuruppu (Software Engineer)
Source: Google Cloud Blog

Total
0
Shares
Leave a Reply
Previous Article
Google Cloud | Anthos

Deploy Anthos On GKE With Terraform Part 3: Enabling Cloud Resources Provisioning

Next Article
Data

Model Training As A CI/CD System: Part I

Related Posts