If you have ever dealt dates/times in an environment that crosses time zones, you know who difficult it can be to handle all scenarios properly. This situation isn’t made any better by .NET’s somewhat limited representation of date and time values through the one DateTime class. For example, how to I represent a date in .NET when I don’t care about the time. There is no type that represents a Date on it’s own. That’s why the Noda Time library was created, billing itself as a better date and time API for .NET.

Noda Time is an alternative date and time API for .NET. It helps you to think about your data more clearly, and express operations on that data more precisely.

An example using NodaTime

In my app, I needed to model an Event that occurs on a particular date. It might be initially tempting to store the date of the event as a DateTime in UTC, but that’s not necessarily accurate unless the event happens to be held at the Royal Observatory Greenwich. I don’t want to deal with time at all, I’m only interested in the date the event is being held.

NodaTime provides a LocalDate type that is perfect for this scenario so I declared a LocalDate property named Date on my Event class.

public class Event
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public LocalDate Date {get; set;}
}

Using Entity Framework

This app was using Entity Framework Core and there was a DbSet for the Event class.

public class EventContext : DbContext
{
    public EventContext(DbContextOptions<EventContext> options) : base(options)
    {
    }

    public DbSet<Event> Events { get; set; }
}

This is where I ran into my first problem. Attempting to run the app, I was greeted with a friendly InvalidOperationException:

InvalidOperationException: The property ‘Event.Date’ could not be mapped, because it is of type ‘LocalDate’ which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the ‘[NotMapped]’ attribute or by using ‘EntityTypeBuilder.Ignore’ in ‘OnModelCreating’.

This first problem was actually easy enough to solve using a ValueConverter. By adding the following OnModelCreating code to my EventContext, I was able to tell Entity Framework Core to store the Date property as a DateTime with the Kind set to DateTimeKind.Unspecified. This has the effect of avoiding any unwanted shifts in the date time based on the local time of the running process.

public class EventContext : DbContext
{
    public EventContext(DbContextOptions<EventContext> options) : base(options)
    {
    }

    public DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        var localDateConverter = 
            new ValueConverter<LocalDate, DateTime>(v =>  
                v.ToDateTimeUnspecified(), 
                v => LocalDate.FromDateTime(v));

        modelBuilder.Entity<Event>()
            .Property(e => e.Date)
            .HasConversion(localDateConverter);
    }
}

With that small change, my application now worked as expected. The value conversions all happen behind the scenes so I can just use the Event entity and deal strictly with the LocalDate type.

But what about queries

I actually had this application running in a test environment for a week before I noticed a serious problem in the log files.

In my app, I was executing a simple query to retrieve the list of events for a particular date.

var queryDate = new LocalDate(2019, 3, 25);
Events = await context.Events.Where(e => e.Date == queryDate).ToListAsync();

In the app’s log file, I noticed the following warning:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression ‘where ([e].Date == __queryDate_0)’ could not be translated and will be evaluated locally.

Uh oh, that sounds bad. I did a little more investigation and confirmed that the query was in fact executing SQL without a WHERE clause.

SELECT [e].[Id], [e].[Date], [e].[Description], [e].[Name]
FROM [Events] AS [e]

So my app was retrieving EVERY ROW from Events table, then applying the where filter in the .NET process. That’s really not what I intended to do and would most certainly cause me some performance troubles when I get to production.

So, the first thing I did was modified my EF Core configuration to throw an error when a client side evaluation like this occurs. I don’t want this kind of thing accidently creeping in to this app again. Over in Startup.ConfigureServices, I added the following option to ConfigureWarnings.

services.AddDbContext<EventContext>(options =>
        options.UseSqlServer(myConnectionString)
        .ConfigureWarnings(warnings => 
            warnings.Throw(RelationalEventId.QueryClientEvaluationWarning)));

Throwing an error by default is the correct behavior here and this is actually something that will be fixed in Entity Framework Core 3.0. The default behavior in EF Core 3 will be to throw an error any time a LINQ expression results in client side evaluation. You will then have the option to allow those client side evaluations.

Fixing the query

Now that I had the app throwing an error for this query, I needed to find a way for EF Core to properly translate my simple e.Date == queryDate expression to SQL. After carefully re-reading the EF Core documentation related for value converters, I noticed a bullet point under Limitations:

Use of value conversions may impact the ability of EF Core to translate expressions to SQL. A warning will be logged for such cases. Removal of these limitations is being considered for a future release.

Well that just plain sucks. It turns out that when you use a value converter for a property, Entity Framework Core just gives up trying to convert any LINQ expression that references that property. The only solution I found was to query for my entities using SQL.

var queryDate = new LocalDate(2019, 3, 25);
Events = await context.Events.
    FromSql(@"SELECT [e].[Id], [e].[Date], [e].[Description], [e].[Name]
FROM[Events] AS[e]
WHERE [e].[Date] = {0}", queryDate.ToDateTimeUnspecified()).ToListAsync();

Wrapping it up

NodaTime is a fantastic date and time library for .NET and you should definitely consider using it in your app. Unfortunately, Entity Framework Core has some serious limitations when it comes to using value converters so you will need to be careful. I almost got myself into some problems with it. While there are work-arounds, writing custom SQL for any query that references a NodaTime type is less than ideal. Hopefully those will be addressed in Entity Framework Core 3.

This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.

In today’s post, we explore a pattern to prevent multiple users (or processes) from accidentally overwriting each other’s change. Given our current implementation for updating the Aircraft record, there is potential for data loss if there are multiple active sessions are attempting to update the same Aircraft record at the same time. In the example shown below, Bob accidentally overwrites Jane’s changes without even knowing that Jane made changes to the same Aircraft record

The pattern we will use here is Optimistic Offline Lock, which is often also referred to as Optimistic Concurrency Control.

Modifying the Database and Entities

To implement this approach, we will use a rowversion column in SQL Server. Essentially, this is a column that automatically version stamps a row in a table. Any time a row is modified, the rowversion column will is automatically incremented for that row. We will start by adding the column to our Aircraft table.

ALTER TABLE Aircraft ADD RowVer rowversion

Next, we add a RowVer property to the Aircraft table. The property is a byte array. When we read the RowVer column from the database, we will get an array of 8 bytes.

public class Aircraft 
{
    public int Id { get; set; }
    public string Manufacturer {get; set;}
    public string Model {get; set;}
    public string RegistrationNumber {get; set;}
    public int FirstClassCapacity {get; set;}
    public int RegularClassCapacity {get; set;}
    public int CrewCapacity {get; set;}
    public DateTime ManufactureDate {get; set; }
    public int NumberOfEngines {get; set;}
    public int EmptyWeight {get; set;}
    public int MaxTakeoffWeight {get; set;}
    public byte[] RowVer { get; set; }
}

Finally, we will modify the query used to load Aircraft entities so it returns the RowVer column. We don’t need to change any of the Dapper code here.

public async Task<Aircraft> Get(int id)
{ 
    Aircraft aircraft;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var query = @"
SELECT 
Id
,Manufacturer
,Model
,RegistrationNumber
,FirstClassCapacity
,RegularClassCapacity
,CrewCapacity
,ManufactureDate
,NumberOfEngines
,EmptyWeight
,MaxTakeoffWeight
,RowVer
FROM Aircraft WHERE Id = @Id";
        aircraft = await connection.QuerySingleAsync<Aircraft>(query, new {Id = id});
    }
    return aircraft;
}

Adding the Concurrency Checks

Now that we have the row version loaded in to our model, we need to add the checks to ensure that one user doesn’t accidentally overwrite another users changes. To do this, we simply need to add the RowVer to the WHERE clause on the UPDATE statement. By adding this constraint to the WHERE clause, we we ensure that the updates will only be applied if the RowVer has not changed since this user originally loaded the Aircraft entity.

public async Task<IActionResult> Put(int id, [FromBody] Aircraft model)
{
    if (id != model.Id) 
    {
        return BadRequest();
    }

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var query = @"
UPDATE Aircraft 
SET  Manufacturer = @Manufacturer
  ,Model = @Model
  ,RegistrationNumber = @RegistrationNumber 
  ,FirstClassCapacity = @FirstClassCapacity
  ,RegularClassCapacity = @RegularClassCapacity
  ,CrewCapacity = @CrewCapacity
  ,ManufactureDate = @ManufactureDate
  ,NumberOfEngines = @NumberOfEngines
  ,EmptyWeight = @EmptyWeight
  ,MaxTakeoffWeight = @MaxTakeoffWeight
WHERE Id = @Id
      AND RowVer = @RowVer";
      
      await connection.ExecuteAsync(query, model);
    }

    return Ok();
}

So, the WHERE clause stops the update from happening, but how do we know if the update was applied successfully? We need to let the user know that the update was not applied due to a concurrency conflict. To do that, we add OUTPUT inserted.RowVer to the UPDATE statement. The effect of this is that the query will return the new value for the RowVer column if the update was applied. If not, it will return null.

public async Task<IActionResult> Put(int id, [FromBody] Aircraft model)
{
    byte[] rowVersion;
    if (id != model.Id) 
    {
        return BadRequest();
    }

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var query = @"
UPDATE Aircraft 
SET  Manufacturer = @Manufacturer
  ,Model = @Model
  ,RegistrationNumber = @RegistrationNumber 
  ,FirstClassCapacity = @FirstClassCapacity
  ,RegularClassCapacity = @RegularClassCapacity
  ,CrewCapacity = @CrewCapacity
  ,ManufactureDate = @ManufactureDate
  ,NumberOfEngines = @NumberOfEngines
  ,EmptyWeight = @EmptyWeight
  ,MaxTakeoffWeight = @MaxTakeoffWeight
  OUTPUT inserted.RowVer
WHERE Id = @Id
      AND RowVer = @RowVer";
        rowVersion = await connection.ExecuteScalarAsync<byte[]>(query, model);
    }

    if (rowVersion == null) {
        throw new DBConcurrencyException("The entity you were trying to edit has changed. Reload the entity and try again."); 
    }
    return Ok(rowVersion);
}

Instead of calling ExecuteAsync, we call ExecuteScalarAsync<byte[]>. Then we can check if the returned value is null and raise a DBConcurrencyException if it is null. If it is not null, we can return the new RowVer value.

Wrapping it up

Using SQL Server’s rowversion column type makes it easy to implement optimistic concurrency checks in a .NET app that uses Dapper.

If you are building as REST api, you should really use the ETag header to represent the current RowVer for your entity. You can read more about this pattern here.

This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.

In today’s post, we explore a more complex scenario that involves executing multiple write operations. In order to ensure consistency at the database level, these operations should all succeed / fail together as a single transaction. In this example, we will be inserting a new ScheduledFlight entity along with an associated set of Flight entities.

As a quick reminder, a Flight represents a particular occurrence of a ScheduledFlight on a particular day. That is, it has a reference to the ScheduledFlight along with some properties indicating the scheduled arrival and departure times.

public class Flight 
{
    public int Id {get; set;}
    public int ScheduledFlightId {get; set;}
    public ScheduledFlight ScheduledFlight { get; set;}
    public DateTime Day {get; set;}
    public DateTime ScheduledDeparture {get; set;}
    public DateTime ScheduledArrival {get; set;}
}

public class ScheduledFlight 
{
    public int Id {get; set;}
    public string FlightNumber {get; set;}

    public int DepartureAirportId {get; set;}
    public Airport DepartureAirport {get; set;}
    public int DepartureHour {get; set;}
    public int DepartureMinute {get; set;}

    public int ArrivalAirportId {get; set;}
    public Airport ArrivalAirport {get; set;}        
    public int ArrivalHour {get; set;}
    public int ArrivalMinute {get; set;}

    public bool IsSundayFlight {get; set;}
    public bool IsMondayFlight {get; set;}
    // Some other properties
}

Inserting the ScheduledFlight

Inserting the ScheduledFlight and retrieving the database generated id is easy enough. We can use the same approach we used in the previous blog post.

// POST api/scheduledflight
[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int newScheduledFlightId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
        newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model);
    }
    return Ok(newScheduledFlightId);
}

According to the bosses at Air Paquette, whenever we create a new ScheduledFlight entity, we also want to generate the Flight entities for the next 12 months of that ScheduledFlight. We can add a method to the ScheduledFlight class to generate the flight entities.

NOTE: Let’s just ignore the obvious bugs related to timezones and to flights that take off and land on a different day.

public IEnumerable<Flight> GenerateFlights(DateTime startDate, DateTime endDate)
{
    var flights = new List<Flight>();
    var currentDate = startDate;

    while (currentDate <= endDate)
    {
        if (IsOnDayOfWeek(currentDate.DayOfWeek))
        {
            var departureTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, DepartureHour, DepartureMinute, 0);
            var arrivalTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, ArrivalHour, ArrivalMinute, 0);
            var flight = new Flight
            {
                ScheduledFlightId = Id,
                ScheduledDeparture = departureTime,
                ScheduledArrival = arrivalTime,
                Day = currentDate.Date
            };
            flights.Add(flight);
        }
        currentDate = currentDate.AddDays(1);
    }
    return flights;
}
public bool IsOnDayOfWeek(DayOfWeek dayOfWeek)
{
    return     (dayOfWeek == DayOfWeek.Sunday && IsSundayFlight)
            || (dayOfWeek == DayOfWeek.Monday && IsMondayFlight)
            || (dayOfWeek == DayOfWeek.Tuesday && IsTuesdayFlight)
            || (dayOfWeek == DayOfWeek.Wednesday && IsWednesdayFlight)
            || (dayOfWeek == DayOfWeek.Thursday && IsThursdayFlight)
            || (dayOfWeek == DayOfWeek.Friday && IsFridayFlight)
            || (dayOfWeek == DayOfWeek.Saturday && IsSaturdayFlight);
}

Now in the controller, we can add some logic to call the GenerateFlight method and then insert those Flight entities using Dapper.

// POST api/scheduledflight
[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int newScheduledFlightId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
        newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model);

        model.Id = newScheduledFlightId;
        var flights = model.GenerateFlights(DateTime.Now, DateTime.Now.AddMonths(12));

    var insertFlightsSql = @"INSERT INTO [dbo].[Flight]
    ([ScheduledFlightId]
    ,[Day]
    ,[ScheduledDeparture]
    ,[ActualDeparture]
    ,[ScheduledArrival]
    ,[ActualArrival])
VALUES
    (@ScheduledFlightId
    ,@Day
    ,@ScheduledDeparture
    ,@ActualDeparture
    ,@ScheduledArrival
    ,@ActualArrival)";

        await connection.ExecuteAsync(insertFlightsSql, flights);

    }
    return Ok(newScheduledFlightId);
}

Note that we passed in an IEnumerable<Flight> as the second argument to the ExecuteAsync method. This is a handy shortcut in Dapper for executing a query multiple times. Instead of writing a loop and calling ExecuteAsync for each flight entity, we can pass in a list of flights and Dapper will execute the query once for each item in the list.

Explicitly managing a transaction

So far, we have code that first inserts a ScheduledFlight, next generates a set of Flight entities and finally inserting all of those Flight entities. That’s the happy path, but what happens if something goes wrong along the way. Typically when we execute a set of related write operations (inserts, updates and deletes), we want those operations to all succeed or fail together. In the database world, we have transactions to help us with this.

The nice thing about using Dapper is that it uses standard .NET database connections and transactions. There is no need to re-invent the wheel here, we can simply use the transaction patterns that have been around in .NET since for nearly 2 decades now.

After opening the connection, we call connection.BeginTransaction() to start a new transaction. Whenever we call ExecuteAsync (or any other Dapper extension method), we need to pass in that transaction. At the end of all that work, we call transaction.Commit(). Finally, we wrap the logic in a try / catch block. If any exception is raised, we call transaction.Rollback() to ensure that none of those write operations are committed to the database.

[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int? newScheduledFlightId = null;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var transaction = connection.BeginTransaction();

        try
        {
            var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
            newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model, transaction);

            model.Id = newScheduledFlightId.Value;
            var flights = model.GenerateFlights(DateTime.Now, DateTime.Now.AddMonths(12));

            var insertFlightsSql = @"INSERT INTO [dbo].[Flight]
    ([ScheduledFlightId]
    ,[Day]
    ,[ScheduledDeparture]
    ,[ActualDeparture]
    ,[ScheduledArrival]
    ,[ActualArrival])
VALUES
    (@ScheduledFlightId
    ,@Day
    ,@ScheduledDeparture
    ,@ActualDeparture
    ,@ScheduledArrival
    ,@ActualArrival)";

            await connection.ExecuteAsync(insertFlightsSql, flights, transaction);
            transaction.Commit();
        }
        catch (Exception ex)
        { 
            //Log the exception (ex)
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // Handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                // Log the exception ex2
            }
            return StatusCode(500);
        }
    }
    return Ok(newScheduledFlightId);
}

Managing database transactions in .NET is a deep but well understood topic. We covered the basic pattern above and showed how Dapper can easily participate in a transaction. To learn more about managing database transactions in .NET, check out these docs:

• SqlConnection.BeginTransaction
• Transactions in ADO.NET

Wrapping it up

Using transactions with Dapper is fairly straight forward process. We just need to tell Dapper what transaction to use when executing queries. Now that we know how to use transactions, we can look at some more advanced scenarios like adding concurrency checks to update operations to ensure users aren’t overwriting each other’s changes.

This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.

In today’s post, we explore how easy it is to perform basic Insert, Update and Delete operations using the same Aircraft entity that we used in the first post in this series. Basically, instead of using Dapper’s QueryAsync extension method that we used to retrieve data, we will use the ExecuteAsync method.

As a quick reminder, here is the Aircraft class:

public class Aircraft 
{
    public int Id { get; set; }

    public string Manufacturer {get; set;}

    public string Model {get; set;}

    public string RegistrationNumber {get; set;}

    public int FirstClassCapacity {get; set;}

    public int RegularClassCapacity {get; set;}

    public int CrewCapacity {get; set;}

    public DateTime ManufactureDate {get; set; }

    public int NumberOfEngines {get; set;}
}

NOTE: In these examples, I am ignoring some important aspects like validation. I want to focus specifically on the Dapper bits here but validation is really important. In a real-world scenario, you should be validating any data that is passed in to the server. I recommend using Fluent Validation.

Insert

Inserting a single new record is really easy. All we need to do is write an INSERT statement with parameters for each column that we want to set.

[HttpPost()]
public async Task<IActionResult> Post([FromBody] Aircraft model)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
INSERT INTO Aircraft 
(Manufacturer
,Model
,RegistrationNumber
,FirstClassCapacity
,RegularClassCapacity
,CrewCapacity
,ManufactureDate
,NumberOfEngines
,EmptyWeight
,MaxTakeoffWeight)
VALUES (@Manufacturer
,@Model
,@RegistrationNumber
,@FirstClassCapacity
,@RegularClassCapacity
,@CrewCapacity
,@ManufactureDate
,@NumberOfEngines
,@EmptyWeight
,@MaxTakeoffWeight)";
        await connection.ExecuteAsync(sqlStatement, model);
    }
    return Ok();
}

The version of the ExecuteAsync method we used here accepts two parameters: a string containing the SQL statement to execute and an object containing the parameter values to bind to the statement. In this case, it is an instance of the Aircraft class which has properties with names matching the parameters defined in the INSERT statement.

Our Aircraft table’s Id column is an auto-incremented identity column. That means the primary key is generated by the database when the row is inserted. We will likely need to pass that value back to whoever called the API so they know how to retrieve the newly inserted Aircraft.

An easy way to get the generated Id is to add SELECT CAST(SCOPE_IDENTITY() as int) after the INSERT statement. The SCOPE_IDENTITY() function returns the last identity value that was generated in any table in the current session and current scope.

Now, since the SQL statement we are executing will be returning a single value (the generated id), we need to call ExecuteScalarAsync<int>. The ExecuteScalarAsync method executes a SQL statement that returns a single value whereas the ExecuteAsync method executes a SQL statement that does not return a value.

[HttpPost()]
public async Task<IActionResult> Post([FromBody] Aircraft model)
{
    int newAircraftId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
INSERT INTO Aircraft 
(Manufacturer
,Model
,RegistrationNumber
,FirstClassCapacity
,RegularClassCapacity
,CrewCapacity
,ManufactureDate
,NumberOfEngines
,EmptyWeight
,MaxTakeoffWeight)
VALUES (@Manufacturer
,@Model
,@RegistrationNumber
,@FirstClassCapacity
,@RegularClassCapacity
,@CrewCapacity
,@ManufactureDate
,@NumberOfEngines
,@EmptyWeight
,@MaxTakeoffWeight);

SELECT CAST(SCOPE_IDENTITY() as int)";
        newAircraftId = await connection.ExecuteScalarAsync<int>(sqlStatement, model);
    }
    return Ok(newAircraftId);
}

Update

Updating an existing entity is similar to inserting. All we need is a SQL statement containing an UPDATE statement that sets the appropriate columns. We also want to make sure we include a WHERE clause limiting the update only to the row with the specified Id.

Again, the parameters in the SQL statement match the names of the properties in our Aircraft class. All we need to do is call the ExecuteAsync method passing in the SQL statement and the Aircraft entity.

// PUT api/aircraft/id
[HttpPut("{id}")]
public async Task<IActionResult> Put(int id, [FromBody] Aircraft model)
{
    if (id != model.Id) 
    {
        return BadRequest();
    }

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
UPDATE Aircraft 
SET  Manufacturer = @Manufacturer
,Model = @Model
,RegistrationNumber = @RegistrationNumber 
,FirstClassCapacity = @FirstClassCapacity
,RegularClassCapacity = @RegularClassCapacity
,CrewCapacity = @CrewCapacity
,ManufactureDate = @ManufactureDate
,NumberOfEngines = @NumberOfEngines
,EmptyWeight = @EmptyWeight
,MaxTakeoffWeight = @MaxTakeoffWeight
WHERE Id = @Id";
        await connection.ExecuteAsync(sqlStatement, model);
    }
    return Ok();
}

Delete

Deleting an entity is the easiest of the three operations since it only requires a single parameter: the unique Id to identify the entity being deleted. The SQL statement is a simple DELETE with a WHERE clause on the Id column. To execute the delete, call the ExecuteAsync method passing in the SQL statement and an anonymous object containing the Id to delete.

// DELETE api/aircraft/id
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = "DELETE Aircraft WHERE Id = @Id";
        await connection.ExecuteAsync(sqlStatement, new {Id = id});
    }
    return Ok();
}

I really appreciate how simple delete is using Dapper. When using Entity Framework, delete requires you to first fetch the existing entity, then delete it. That requires 2 round trips to the database while the approach we used here only requires a single round trip.

Wrapping it up

Basic insert, update and delete operations are easy to implement using Dapper. Real world scenarios are often a little more complex and we will dig into some of those scenarios in future posts:

• Bulk inserts, updates and deletes
• Managing transactions
• Optimistic concurrency checks

This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.

In today’s post, we explore paging through large result sets. Paging is a common technique that is used when dealing with large results sets. Typically, it is not useful for an application to request millions of records at a time because there is no efficient way to deal with all those records in memory all at once. This is especially true when rendering data on a grid in a user interface. The screen can only display a limited number of records at a time so it is generally a bad use of system resources to hold everything in memory when only a small subset of those records can be displayed at any given time.

Source: AppStack Bootstrap Template

Modern versions of SQL Server support the OFFSET / FETCH clause to implement query paging.

In continuing with our airline theme, consider a Flight entity. A Flight represents a particular occurrence of a ScheduledFlight on a particular day. That is, it has a reference to the ScheduledFlight along with some properties indicating the scheduled arrival and departure times.

public class Flight 
{
    public int Id {get; set;}
    public int ScheduledFlightId {get; set;}
    public ScheduledFlight ScheduledFlight { get; set;}
    public DateTime Day {get; set;}
    public DateTime ScheduledDeparture {get; set;}
    public DateTime ScheduledArrival {get; set;}
}

public class ScheduledFlight 
{
    public int Id {get; set;}
    public string FlightNumber {get; set;}

    public int DepartureAirportId {get; set;}
    public Airport DepartureAirport {get; set;}
    public int DepartureHour {get; set;}
    public int DepartureMinute {get; set;}

    public int ArrivalAirportId {get; set;}
    public Airport ArrivalAirport {get; set;}        
    public int ArrivalHour {get; set;}
    public int ArrivalMinute {get; set;}

    public bool IsSundayFlight {get; set;}
    public bool IsMondayFlight {get; set;}
    // Some other properties
}

Writing the query

As we learned in a previous post, we can load the Flight entity along with it’s related ScheduledFlight entity using a technique called multi-mapping.

In this case, loading all the flights to or from a particular airport, we would use the following query.

SELECT f.*, sf.*
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode

But this query could yield more results than we want to deal with at any given time. Using OFFSET/FETCH, we can ask for only a block of results at a time.

SELECT f.*, sf.*
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode
ORDER BY f.Day, sf.FlightNumber
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY

Note that an ORDER BY clause is required when using OFFSET/FETCH.

Executing the Query

//GET api/flights
[HttpGet]
public async Task<IEnumerable<Flight>> Get(string airportCode, int page=1, int pageSize=10)
{
    IEnumerable<Flight> results;

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var query = @"
SELECT f.*, sf.*
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode
ORDER BY f.Day, sf.FlightNumber
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
";

        results = await connection.QueryAsync<Flight, ScheduledFlight, Flight>(query,
          (f, sf) =>
              {
                  f.ScheduledFlight = sf;
                  return f;
              },
            new { AirportCode = airportCode,
                              Offset = (page - 1) * pageSize,
                              PageSize = pageSize }
            );        
    }

    return results;
}

Here we calculate the offset by based on the page and pageSize arguments that were passed in. This allows the caller of the API to request a particular number of rows and the starting point.

One step further

When dealing with paged result sets, it can be useful for the caller of the API to also know the total number of records. Without the total number of records, it would be difficult to know how many records are remaining which in turn makes it difficult to render a paging control, a progress bar or a scroll bar (depending on the use case).

A technique I like to use here is to have my API return a PagedResults<T> class that contains the list of items for the current page along with the total count.

public class PagedResults<T>
{
    public IEnumerable<T> Items { get; set; }
    public int TotalCount { get; set; }
}

To populate this using Dapper, we can add a second result set to the query. That second result set will simply be a count of all the records. Note that the same WHERE clause is used in both queries.

SELECT f.*, sf.*
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode
ORDER BY f.Day, sf.FlightNumber
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

SELECT COUNT(*)
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode

Now in our code that executes the query, we will the QueryMultipleAsync method to execute both SQL statements in a single round trip.

//GET api/flights
[HttpGet]
public async Task<PagedResults<Flight>> Get(string airportCode, int page=1, int pageSize=10)
{
    var results = new PagedResults<Flight>();

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var query = @"
SELECT f.*, sf.*
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode
ORDER BY f.Day, sf.FlightNumber
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

SELECT COUNT(*)
FROM Flight f
INNER JOIN ScheduledFlight sf ON f.ScheduledFlightId = sf.Id
INNER JOIN Airport a ON sf.ArrivalAirportId = a.Id
INNER JOIN Airport d ON sf.DepartureAirportId = d.Id
WHERE a.Code = @AirportCode OR d.Code = @AirportCode
";

        using (var multi = await connection.QueryMultipleAsync(query,
                    new { AirportCode = airportCode,
                          Offset = (page - 1) * pageSize,
                          PageSize = pageSize }))
        {
            results.Items = multi.Read<Flight, ScheduledFlight, Flight>((f, sf) =>
                {
                    f.ScheduledFlight = sf;
                    return f;
                }).ToList();

            results.TotalCount = multi.ReadFirst<int>();
        }
    }

    return results;
}

Wrapping it up

Paged result sets is an important technique when dealing with large amounts of data. When using a full ORM like Entity Framework, this is implemented easily using LINQ’s Skip and Take methods. It’s so easy in fact that it can look a little like magic. In reality, it is actually very simple to write your own queries to support paged result sets and execute those queries using Dapper.

So you’re working an existing (brown-field) ASP.NET MVC application. The application’s views are rendered server-side using Razor. Everything is working great and life is good. Suddenly, someone asks for a bit of additional functionality that will require some client side logic. Okay, no big deal. We do this all the time. The question though, is what framework/JavaScript library you will use to implement that client side functionality.

The default MVC project templates already include jQuery, so you might use that. You’ll probably end up writing a lot of code if you go down that path. Chances are, you will want to use a JavaScript framework that offers two-way data binding between the elements in the DOM to a your model data.

I seems that for many people, Knockout.js is the default library to use in these scenarios. I won’t get into the specifics but I think that Knockout is a little dated and that there are better options these days. If you want to dig into some of the issues with Knockout, you can read Simon Timms’ rant on the subject.

Vue.js

My fellow ASP.NET Monster James Chambers recently strongly recommended I take a look at Vue.js. I had been meaning to give Vue a try for some time now and I finally had a chance to use it on a recent project. Let me tell you…I love it.

I love it for a whole bunch of reasons. The documentation is great! It is super easy to get drop in to your existing project and it doesn’t get in the way. For what I needed to do, it just did the job and allowed me to get on with my day. It is also designed to be “incrementally adoptable”, which means you can start out with just using the core view layer, then start pulling in other things like routing and state management if/when you need them.

A simple example

I won’t go into great detail about how to use Vue. If you want a full tutorial, head on over to the Vue docs. What I want to show here is just how simple it is to drop Vue into an existing ASP.NET MVC project and add a bit of client side functionality.

The simplest example I can think of is a set of cascading dropdowns. Let’s consider a form where a user is asked to enter their Country / Province. When the Country is selected, we would expect the Province dropdown to only display the valid Provinces/States for the selected Country. That probably involves a call to an HTTP endpoint that will return the list of valid values.

public class ProvinceLookupController : Controller
{
    public ActionResult Index(string countryCode)
    {
        var provinces = ProvinceLookupService.GetProvinces(countryCode);
        return Json(provinces, JsonRequestBehavior.AllowGet);
    }
}

Including Vue in your Razor (cshtml) view

The easiest way to include Vue on a particular Razor view is to link to the vue.js file from a CDN. You can add the following Script tag to your scripts section.

@section scripts  {
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.22/dist/vue.js"></script>
}

Be sure to check the docs to make sure you are referencing the latest version of Vue.

Binding data to the our View

Now that you have included the core Vue library, you can start using Vue to bind DOM elements to model data.

Start by defining a Vue object in JavaScript. You can add this in a new <script> tag in your scripts section.

@section scripts  {
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.22/dist/vue.js"></script>

    <script type="text/javascript">
        var app = new Vue({
            el: '#vueApp',
            data: {
                selectedCountryCode: null,
                countries: [
                    { code: 'ca', name: 'Canada' },
                    { code: 'us', name: 'United States' }
                ]            
            }
        });
    </script>
}

This Vue object targets the DOM element with id vueApp and contains some simple data. The currently selected country code and the list of countries.

Now, back in the HTML part of your csthml, wrap the form in a div that has an id="vueApp".

<div id="vueApp">
    <!-- your form -->
</div>

Next, bind the <select> element to the data in your Vue object. In Vue, data binding is done using a combination of custom attributes that start with v- and the double curly bracket (aka. Mustache) syntax for text.

<div class="form-group">
    @Html.LabelFor(model => model.CountryCode, new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        <select id="CountryCode" name="CountryCode" class="form-control" 
                v-model="selectedCountryCode">
            <option v-for="country in countries" v-bind:value="country.code">
                {{ country.name }}
            </option>
        </select>
    </div>
</div>

Now, when you run the app, you should see a dropdown containing Canada and United States.

Adding functionality

Next, you will want to add some client side logic to get the list of valid provinces from the server whenever the selected country changes.

First, add an empty provinces array and a selectedProvinceCode property to the Vue object’s data.

Next, add a method called countryChanged to the Vue object. This method will call the ProvinceLookup action method on the server, passing in the selectedCountryCode as a parameter. Assign the response data to the provinces array.

var app = new Vue({
    el: '#vueApp',
    data: {
        selectedCountryCode: null,
        countries: [
            { code: 'ca', name: 'Canada' },
            { code: 'us', name: 'United States' }
        ],
        selectedProvinceCode: null,
        provinces: []
    },
    methods: {
        countryChanged: function () {
            $.getJSON('@Url.Action("Index", "ProvinceLookup")?countryCode=' + this.selectedCountryCode, function (data) {
                this.provinces = data;
            }.bind(this));
        }
    }
});

Here I used jQuery to make the call to the server. In the Vue community, Axios is a popular library for making HTTP requests.

Back in the HTML, bind the change event from the country select element to the countryChanged method using the v-on:change attribute.

<select id="CountryCode" name="CountryCode" class="form-control" 
        v-model="selectedCountryCode" v-on:change="countryChanged">
    <option v-for="country in countries" v-bind:value="country.code">
        {{country.name}}
    </option>
</select>

Now you can add a select element for the provinces.

<div class="form-group">
    @Html.LabelFor(model => model.ProvinceCode, new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        <select id="ProvinceCode" name="ProvinceCode" class="form-control"
                v-model="selectedProvinceCode">
            <option v-for="province in provinces" v-bind:value="province.Code">
                {{province.Name}}
            </option>
        </select>
    </div>
</div>

Voila! You now have a working set of cascading dropdowns.

One last thing

You might want to disable the provinces dropdown whenever a request is being made to get the list of provinces for the selected country. You can do this by adding an isProvincesLoading property to the Vue object’s data, then setting that property in the countryChanged method.

var app = new Vue({
    el: '#vueApp',
    data: {
        selectedCountryCode: null,
        countries: [
            { code: 'ca', name: 'Canada' },
            { code: 'us', name: 'United States' }
        ],
        selectedProvinceCode: null,
        provinces: [],
        isProvincesLoading: false
    },
    methods: {
        countryChanged: function () {
            this.isProvincesLoading = true;
            $.getJSON('@Url.Action("Index", "ProvinceLookup")?countryCode=' + this.selectedCountryCode, function (data) {
                this.provinces = data;
                this.isProvincesLoading = false;
            }.bind(this));
        }
    }
});

In your HTML, bind the disabled attribute to the isProvincesLoading property.

<div class="form-group">
<select id="ProvinceCode" name="ProvinceCode" class="form-control"
        v-model="selectedProvinceCode"
        v-bind:disabled="isProvincesLoading">
    <option v-for="province in provinces" v-bind:value="province.Code">
        {{province.Name}}
    </option>
</select>

Putting it all together

Here is the entire cshtml file.

@{
    ViewBag.Title = "Location Settings";
}
@model Mvc5VueJsExample.Models.LocationSettingsModel

<h2>@ViewBag.Title.</h2>
<h3>@ViewBag.Message</h3>

<div id="vueApp">
    @using (Html.BeginForm("LocationSettings", "Home", FormMethod.Post, new { @class = "form" }))
    {
        <div class="form-group">
            @Html.LabelFor(model => model.CountryCode, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                <select id="CountryCode" name="CountryCode" class="form-control" 
                        v-model="selectedCountryCode" v-on:change="countryChanged">
                    <option v-for="country in countries" v-bind:value="country.code">
                        {{ country.name}}
                    </option>
                </select>
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.ProvinceCode, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                <select id="ProvinceCode" name="ProvinceCode" class="form-control"
                        v-model="selectedProvinceCode"
                        v-bind:disabled="isProvincesLoading">
                    <option v-for="province in provinces" v-bind:value="province.Code">
                        {{province.Name}}
                    </option>
                </select>
            </div>
        </div>
        <button class="btn btn-primary" type="submit">Save</button>
    }

</div>
@section scripts  {
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.22/dist/vue.js"></script>

    <script type="text/javascript">
        var app = new Vue({
            el: '#vueApp',
            data: {
                selectedCountryCode: null,
                countries: [
                    { code: 'ca', name: 'Canada' },
                    { code: 'us', name: 'United States' }
                ],
                selectedProvinceCode: null,
                provinces: [],
                isProvincesLoading: false
            },
            methods: {
                countryChanged: function () {
                    this.isProvincesLoading = true;
                    $.getJSON('@Url.Action("Index", "ProvinceLookup")?countryCode=' + this.selectedCountryCode, function (data) {
                        this.provinces = data;
                        this.isProvincesLoading = false;
                    }.bind(this));
                }
            }
        });
    </script>
}

Wrapping it up

I hope this gives you a taste for how easy it is to work with Vue. My current thinking is that Vue should be the default choice for client side frameworks in existing ASP.NET MVC apps.

NOTE: This example uses ASP.NET MVC 5 to illustrate that Vue can be used with brownfield applications. It would be just as easy, if not easier, to use Vue in an ASP.NET Core project.