public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Yvette",
LastName = "Wendelson",
// SpannerDate is specifically designed to map a DATE column in a Cloud Spanner
// database to a property of an entity. DateTime properties will by default be
// mapped to TIMESTAMP columns.
BirthDate = new SpannerDate(1980, 10, 17),
};
await context.Singers.AddAsync(singer);
await context.SaveChangesAsync();
// Commonly used properties and methods of SpannerDate are mapped to the equivalent Cloud Spanner functions.
var singersBornIn1980 = await context.Singers
.Where(s => s.BirthDate.GetValueOrDefault().Year == 1980)
.OrderBy(s => singer.LastName)
.ToListAsync();
foreach (var s in singersBornIn1980)
{
Console.WriteLine($"Born in 1980: {s.FullName} ({s.BirthDate})");
}
}
private static async Task Setup(SpannerSampleDbContext context)
{
await context.Singers.AddRangeAsync(
new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Alice",
LastName = "Henderson",
BirthDate = new SpannerDate(1983, 10, 19),
},
new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Peter",
LastName = "Allison",
BirthDate = new SpannerDate(2000, 5, 2),
},
new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Mike",
LastName = "Nicholson",
BirthDate = new SpannerDate(1976, 8, 31),
});
await context.SaveChangesAsync();
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
// Create a concert.
var(singer, venue) = await GetSingerAndVenueAsync(connectionString);
var concert = new Concert
{
SingerId = singer.SingerId,
VenueCode = venue.Code,
// TIMESTAMP columns are mapped to DateTime by default. Cloud Spanner stores all TIMESTAMP values in UTC.
// If a TIMESTAMP value is set in local time, the value will be converted to UTC before it is written to
// Cloud Spanner.
StartTime = new DateTime(2021, 2, 1, 19, 30, 0, DateTimeKind.Utc),
Title = "Theodore in Concert Hall",
};
await context.Concerts.AddAsync(concert);
await context.SaveChangesAsync();
// Commonly used methods and properties of DateTime are mapped to the equivalent Cloud Spanner functions and can be used in queries.
var concertsInFeb2021 = await context.Concerts
.Where(c => c.StartTime.Month == 2 && c.StartTime.Year == 2021)
.OrderBy(c => c.StartTime)
.ToListAsync();
foreach (var c in concertsInFeb2021)
{
Console.WriteLine($"February concert: {c.Title}, starts at {c.StartTime}");
}
}
private static async Task <(Singer, Venue)> GetSingerAndVenueAsync(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
using var transaction = await context.Database.BeginTransactionAsync();
var venue = await context.Venues.FindAsync("CON");
if (venue == null)
{
venue = new Venue
{
Code = "CON",
Name = "Concert Hall",
Active = true,
};
await context.Venues.AddAsync(venue);
}
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Theodore",
LastName = "Walterson",
};
await context.Singers.AddAsync(singer);
await context.SaveChangesAsync();
await transaction.CommitAsync();
return(singer, venue);
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
await Setup(context);
var singersBornBefore2000 = context.Singers
.Where(s => s.BirthDate.GetValueOrDefault().Year < 2000)
.OrderBy(s => s.BirthDate)
.AsAsyncEnumerable();
Console.WriteLine("Singers born before 2000:");
await foreach (var singer in singersBornBefore2000)
{
Console.WriteLine($"{singer.FullName}, born at {singer.BirthDate}");
}
var singersStartingWithAl = context.Singers
.Where(s => s.FullName.StartsWith("Al"))
.OrderBy(s => s.LastName)
.AsAsyncEnumerable();
Console.WriteLine("Singers with a name starting with 'Al':");
await foreach (var singer in singersStartingWithAl)
{
Console.WriteLine($"{singer.FullName}");
}
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
// Singer has a generated column FullName that is the combination of the
// FirstName and LastName. The value is automatically computed by Cloud Spanner.
// Setting it manually client side has no effect.
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Alice",
LastName = "Jameson"
};
await context.Singers.AddAsync(singer);
await context.SaveChangesAsync();
// Entity Framework will automatically fetch the computed value for FullName
// from Cloud Spanner after it has been written.
Console.WriteLine($"Added singer with full name {singer.FullName}");
// Updating the last name of the singer will also update the full name.
singer.LastName = "Jameson - Cooper";
await context.SaveChangesAsync();
Console.WriteLine($"Updated singer's full name to {singer.FullName}");
}
private static async Task Setup(SpannerSampleDbContext context)
{
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Alice",
LastName = "Henderson",
BirthDate = new SpannerDate(1983, 10, 19),
};
await context.Singers.AddAsync(singer);
await context.Albums.AddRangeAsync(new Album
{
AlbumId = Guid.NewGuid(),
SingerId = singer.SingerId,
Title = "Henderson's first",
},
new Album
{
AlbumId = Guid.NewGuid(),
SingerId = singer.SingerId,
Title = "Henderson's second",
},
new Album
{
AlbumId = Guid.NewGuid(),
SingerId = singer.SingerId,
Title = "Henderson's third",
});
await context.SaveChangesAsync();
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
// Start a read-only transaction with strong timestamp bound (i.e. read everything that has been committed up until now).
// This transaction will be assigned a read-timestamp at the first read that it executes and all
// following read operations will also use the same read timestamp. Any changes that are made after
// this read timestamp will not be visible to the transaction.
// NOTE: Although read-only transaction cannot be committed or rollbacked, they still need to be disposed.
using var transaction = await context.Database.BeginReadOnlyTransactionAsync(TimestampBound.Strong);
// Search for a singer with a new id. This will establish a read timestamp for the read-only transaction.
var singerId = Guid.NewGuid();
var count = await context.Singers
.Where(s => s.SingerId == singerId)
.CountAsync();
Console.WriteLine($"Searching for singer with id {singerId} yielded {count} result(s)");
// Create a new database context and insert a singer with the given id. This singer will not be visible
// to the read-only transaction.
using (var writeContext = new SpannerSampleDbContext(connectionString))
{
await writeContext.Singers.AddAsync(new Singer
{
SingerId = singerId,
FirstName = "Alice",
LastName = "Goldberg",
});
await writeContext.SaveChangesAsync();
}
// The count should not have changed, as the read-only transaction will continue to use
// the read timestamp assigned during the first read.
count = await context.Singers
.Where(s => s.SingerId == singerId)
.CountAsync();
Console.WriteLine($"Searching for singer with id {singerId} yielded {count} result(s)");
// Now 'commit' the read-only transaction. This will close the transaction and allow us to start
// a new one on the context.
await transaction.CommitAsync();
// Start a new read-only transaction. TimestampBound.Strong is default so we don't have to specify it.
using var newTransaction = await context.Database.BeginReadOnlyTransactionAsync();
count = await context.Singers
.Where(s => s.SingerId == singerId)
.CountAsync();
Console.WriteLine($"Searching for singer with id {singerId} yielded {count} result(s)");
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Brian",
LastName = "Truman",
};
await context.Singers.AddAsync(singer);
var album = new Album
{
AlbumId = Guid.NewGuid(),
SingerId = singer.SingerId,
Title = "Potatoes",
};
await context.Albums.AddAsync(album);
// Tracks are interleaved in Albums. This relationship is treated the same as FOREIGN KEYS in
// Entity Framework Core, which means that we can traverse the relationship both ways, and any
// Track that references an Album that has is associated with the database context will also be
// associated with the context.
album.Tracks.Add(new Track
{
AlbumId = album.AlbumId,
TrackId = 1L,
Title = "They are good",
});
album.Tracks.Add(new Track
{
AlbumId = album.AlbumId,
TrackId = 2L,
Title = "Some find them delicious",
});
// This will save 1 singer, 1 album and 2 tracks.
var updateCount = await context.SaveChangesAsync();
Console.WriteLine($"Saved {updateCount} rows");
// We can traverse the relationship from Track to Album.
foreach (var track in album.Tracks)
{
Console.WriteLine($"'{track.Title}' is on album '{track.Album.Title}'");
}
// Tracks are defined as `INTERLEAVE IN PARENT Albums ON DELETE CASCADE`. Deleting an
// album will therefore automatically also delete its tracks.
context.Albums.Remove(album);
var deletedCount = await context.SaveChangesAsync();
Console.WriteLine($"Deleted {deletedCount} albums and tracks");
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
// Start a read/write transaction that will be used with the database context.
using var transaction = await context.Database.BeginTransactionAsync();
// Create a new Singer, add it to the context and save the changes.
// These changes have not yet been committed to the database and are
// therefore not readable for other processes.
var singerId = Guid.NewGuid();
await context.Singers.AddAsync(new Singer
{
SingerId = singerId,
FirstName = "Bernhard",
LastName = "Bennet"
});
var count = await context.SaveChangesAsync();
Console.WriteLine($"Added {count} singer in a transaction.");
// Now try to read the singer in a different context which will use a different transaction.
// This will return null, as pending changes from other transactions are not visible.
using var contextWithoutTransaction = new SpannerSampleDbContext(connectionString);
var exists = await contextWithoutTransaction.Singers
.FromSqlInterpolated($"SELECT * FROM Singers WHERE SingerId={singerId}")
.FirstOrDefaultAsync();
Console.WriteLine($"Can read singer outside of transaction: {exists != null}");
// Now try to read the same using the context with the transaction. This will return true as
// a transaction can read its own writes. The Cloud Spanner Entity Framework Core provider
// uses DML by default for updates that are executed in manual transactions in order to support
// the read-your-writes feature.
exists = await context.Singers
.FromSqlInterpolated($"SELECT * FROM Singers WHERE SingerId={singerId}")
.FirstOrDefaultAsync();
Console.WriteLine($"Can read singer inside transaction: {exists != null}");
// Commit the transaction. The singer is now also readable in a context without the transaction.
await transaction.CommitAsync();
exists = await contextWithoutTransaction.Singers
.FromSqlInterpolated($"SELECT * FROM Singers WHERE SingerId={singerId}")
.FirstOrDefaultAsync();
Console.WriteLine($"Can read singer after commit: {exists != null}");
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
var(singer, album) = await GetSingerAndAlbumAsync(context);
// A track has two array columns: Lyrics and LyricsLanguages. The length of both arrays
// should be equal, as the LyricsLanguages indicate the language of the corresponding Lyrics.
var track1 = new Track
{
AlbumId = album.AlbumId,
TrackId = 1,
Title = "Whenever",
Lyrics = new List <string> {
"Lyrics 1", "Lyrics 2"
},
LyricsLanguages = new List <string> {
"EN", "DE"
},
};
var track2 = new Track
{
AlbumId = album.AlbumId,
TrackId = 2,
Title = "Wherever",
// Array elements may be null, regardless whether the column itself is defined as NULL/NOT NULL.
Lyrics = new List <string> {
null, "Lyrics 2"
},
LyricsLanguages = new List <string> {
"EN", "DE"
},
};
var track3 = new Track
{
AlbumId = album.AlbumId,
TrackId = 3,
Title = "Probably",
// ARRAY columns may also be null.
Lyrics = null,
LyricsLanguages = null,
};
await context.Tracks.AddRangeAsync(track1, track2, track3);
var count = await context.SaveChangesAsync();
Console.WriteLine($"Added {count} tracks.");
// TODO: Add sample for querying using array functions.
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
await Setup(context);
var singers = context.Singers
.OrderBy(s => s.BirthDate)
.AsAsyncEnumerable();
Console.WriteLine("Found singers:");
await foreach (var singer in singers)
{
Console.WriteLine($"{singer.FullName}, born at {singer.BirthDate}");
}
}
private static async Task <(Concert, Track)> GetConcertAndTrackAsync(SpannerSampleDbContext context)
{
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Alice",
LastName = "Jameson",
};
await context.Singers.AddAsync(singer);
var album = new Album
{
AlbumId = Guid.NewGuid(),
Title = "Rainforest",
SingerId = singer.SingerId,
};
await context.Albums.AddAsync(album);
var track = new Track
{
AlbumId = album.AlbumId,
TrackId = 1,
Title = "Butterflies",
};
await context.Tracks.AddAsync(track);
if (await context.Venues.FindAsync("CON") == null)
{
await context.Venues.AddAsync(new Venue
{
Code = "CON",
Name = "Concert Hall",
Active = true,
});
}
var concert = new Concert
{
VenueCode = "CON",
SingerId = singer.SingerId,
StartTime = new DateTime(2021, 1, 27, 18, 0, 0, DateTimeKind.Utc),
Title = "Alice Jameson - LIVE in Concert Hall",
};
await context.AddAsync(concert);
await context.SaveChangesAsync();
return(concert, track);
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
await Setup(context);
var query = from album in context.Albums
join singer in context.Singers
on album.SingerId equals singer.SingerId
select new { singer, album };
Console.WriteLine("Singers and albums:");
await foreach (var row in query.AsAsyncEnumerable())
{
Console.WriteLine($"Singer {row.singer.FullName} produced album {row.album.Title}");
}
}
public static async Task Run(string connectionString)
{
using var context = new SpannerSampleDbContext(connectionString);
// Create a new Singer, add it to the context and save the changes.
await context.Singers.AddAsync(new Singer
{
// Cloud Spanner does not support server side generation of Guid values,
// so it must always be generated by the client.
SingerId = Guid.NewGuid(),
FirstName = "Jamie",
LastName = "Yngvason"
});
var count = await context.SaveChangesAsync();
// SaveChangesAsync returns the total number of rows that was inserted/updated/deleted.
Console.WriteLine($"Added {count} singer.");
}
public static async Task Run(string connectionString)
{
var startTime = new DateTime(2021, 1, 27, 19, 0, 0, DateTimeKind.Utc);
using var context = new SpannerSampleDbContext(connectionString);
(Concert concert, Track track) = await GetConcertAndTrackAsync(context);
// Create a new performance and save it.
// This will automatically fill the CreatedAt property with the commit timestamp of the transaction.
var performance = new Performance
{
VenueCode = concert.VenueCode,
SingerId = concert.SingerId,
ConcertStartTime = concert.StartTime,
AlbumId = track.AlbumId,
TrackId = track.TrackId,
StartTime = startTime,
Rating = 7.5,
};
await context.Performances.AddAsync(performance);
var count = await context.SaveChangesAsync();
Console.WriteLine($"Saved {count} performance");
Console.WriteLine($"Performance was created at {performance.CreatedAt}");
// Last updated is only filled when the entity is updated (not when it is inserted).
var lastUpdated = performance.LastUpdatedAt == null ? "<never>" : performance.LastUpdatedAt.ToString();
Console.WriteLine($"Performance was last updated at {lastUpdated}");
// Update the performance. This will also fill the LastUpdatedAt property.
performance.Rating = 8.5;
count = await context.SaveChangesAsync();
Console.WriteLine($"Updated {count} performance");
Console.WriteLine($"Performance was created at {performance.CreatedAt}");
Console.WriteLine($"Performance was updated at {performance.LastUpdatedAt}");
}
private static async Task <(Singer, Album)> GetSingerAndAlbumAsync(SpannerSampleDbContext context)
{
var singer = new Singer
{
SingerId = Guid.NewGuid(),
FirstName = "Hannah",
LastName = "Polansky"
};
await context.Singers.AddAsync(singer);
var album = new Album
{
AlbumId = Guid.NewGuid(),
SingerId = singer.SingerId,
Title = "Somewhere",
};
await context.Albums.AddAsync(album);
await context.SaveChangesAsync();
return(singer, album);
}
