.NET Core

Dapper Using Lists

Last week’s post on ASP.NET Core with Dapper covered the very basics of using Dapper in an ASP.NET Core project. This week we will be changing up the sample code from last week to show using Dapper to deal with lists instead of just a single object at a time.

This post is going to be pretty short, but I thought it was important to point out that Dapper can execute a single query multiple times by passing it an IEnumerable.

The Sample

The following sample connects to the database, deletes all the existing contacts, seeds the contact table with 3 contacts (this is our list), and then selects all the contacts back out (this comes out as an IEnumerable).

using (var connection = 
           new SqlConnection(_configuration
                             .GetConnectionString("DefaultConnection")))
{
    connection.Open();

    connection.Execute("DELETE Contacts");

    var seedContacts = new List<Contact>
                       {
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           },
                           new Contact
                           {
                               Name = "Teddy Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           },
                           new Contact
                           {
                               Name = "Kate Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           }
                       };

    connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                               Subregion, Email) 
                         VALUES (@Name, @Address, @City, 
                                 @Subregion, @Email)",
                       seedContacts);
    
    var allContacts = connection
                      .Query<Contact>(@"SELECT Id, Name, Address, City, 
                                               Subregion, Email 
                                        FROM Contacts");
}

As expected the end result contains three contact records.

Wrapping Up

Hopefully, this quick little post helps fill in one of the gaps of last week’s post. Make sure and check out the Dapper GitHub page in addition to features I have covered in these two posts it also supports a lot of other functionality such as stored procedures, multiple result sets, etc. The code in its final state can be found here.

Dapper Using Lists Read More »

ASP.NET Core with Dapper

Entity Framework Core has been a the heart of a lot of the post I have done recently. I thought it would be a good change of pace to try out one of the alternatives to Entity Framework Core and give Dapper a try.

I tried to find a good example of the difference between micro ORMs like Dapper and full ORMs like Entity Framework Core, but all the ones I found ended up pushing one option over the other.  I’m not looking to say one is better than the other, but to add a new tool to my toolbelt.

Basically, the differences seem to come down to micro ORMs provide significantly fewer features than a full ORM but come with a higher level of performance. Which is the right choice is going to vary by project.

Sample Project

We will be using a new Razor Pages application as the starting point for the sample application. It currently doesn’t have any database access setup. The code for the starting point of the sample can be found here.

I’m going to be using an existing database created for the SQL Server example in my Entity Framework sample project. Below is a Contacts table generated from that sample project in case you want to manually generate the table.

CREATE TABLE [dbo].[Contacts] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (MAX) NULL,
    [Address]    NVARCHAR (MAX) NULL,
    [City]       NVARCHAR (MAX) NULL,
    [Subregion]  NVARCHAR (MAX) NULL,
    [PostalCode] NVARCHAR (MAX) NULL,
    [Phone]      NVARCHAR (MAX) NULL,
    [Email]      NVARCHAR (MAX) NULL,
    [Timestamp]  ROWVERSION     NULL
);

Add Dapper Nuget Package

Using your favorite way to add a NuGet package add a reference to the Dapper package. I’m going to do it using Visual Studio by right-clicking on the project and selecting Manage NuGet Packages.

On the next screen, from the Browse tab use the Search Box to search for Dapper. Select the Dapper package and click Install.

Add Connection String Configuration

We are going to store the database connection string in the appsettings.json file. Open the file and add a ConnectionString section with a DefaultConnection that contains the database connection string. This exact setup isn’t required I’m just following the way configuration is set up for Entity Framework base projects. The following is my full configuration file with the new section at the top.

{
  "ConnectionStrings": {
    "DefaultConnection": "Your connection string"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Contact Model

Add a Contact class in a Models folder which will be used to represent our data. The following is the full model class I will be using in this example.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Test Setup

To keep things simple I will not be dealing with any UI other than as a way to run the Dapper related code. As such we will be using the Page Modle of the Index page to run the sample code. This is in no way an example of the best practice setup and is only done to demo Dapper in the simplest setup possible.

The following is the full page model with the configuration for the application being injected via the constructor. All the changes passed this point will be in the OnGet function.

public class IndexModel : PageModel
{
    private readonly IConfiguration _configuration;

    public IndexModel(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public void OnGet()
    {

    }
}

Using Dapper

Dapper provides a number of extension methods off of the IDbConnection interface. For this example, we will use these extensions to check to see if a specific contact exists, if it doesn’t then insert it, and finally select the contact back out of the database.

The first step is to open a connection. In this example, we are using SQL Server, but Dapper doesn’t really care and will work with any ADO.NET provider. The following code opens a SQL connection using the connection string from appsettings.json.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
    connection.Open();
}

Next, we are going to query the Contacts table for the ID of a specific contact.

if (connection.QueryFirstOrDefault<int?>(@"SELECT Id 
                                           FROM Contacts 
                                           WHERE Name = @Name",
                                         new {Name = "Charlie Plumber"}) == null)
{
}

As you can see from the above you there is nothing special about the SQL being written. For this query, we are using an anonymous type to pass the query a name filter which will be translated to a SqlParameter which ensures the query won’t all things like SQL injection. Note that the parameter name in the SQL string must match the name of the corresponding property on the object being passed to the query.

This next example is the contact insert if the contact wasn’t found in the previous query.

connection.Execute(@"INSERT INTO Contacts (Name, Address, City, Subregion, Email)
                     VALUES (@Name, @Address, @City, @Subregion, @Email)",
                   new Contact
                   {
                       Name = "Charlie Plumber",
                       Address = "123 Main St",
                       City = "Nashville",
                       Subregion = "TN",
                       Email = "[email protected]"
                   });

Again the SQL is exactly what you would expect, which is one of the great parts about Dapper if you are familiar with SQL.

This last example is a combination of all the previous examples with a select to get out the contact details of the contact in question.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
    connection.Open();

    if (connection
           .QueryFirstOrDefault<int?>(@"SELECT Id 
                                        FROM Contacts 
                                        WHERE Name = @Name",
                                      new {Name = "Charlie Plumber"}) == null)
    {
        connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                                   Subregion, Email) 
                             VALUES (@Name, @Address, @City, 
                                     @Subregion, @Email)",
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           });
    }

    var charile = 
      connection.QueryFirstOrDefault<Contact>(@"SELECT Id, Name, Address, 
                                                       City, Subregion, Email 
                                                FROM Contacts 
                                                WHERE Name = @Name",
                                              new {Name = "Charlie Plumber"});
}

Wrapping Up

This first round of playing with Dapper was fun. I write a lot of SQL in my day job so it was kind of nice seeing the explicit SQL vs the magic of a full ORM. I can tell you from doing this sample I would miss Entity Framework’s database creation and migration capabilities.

The code in its final state can be found here.

ASP.NET Core with Dapper Read More »

Entity Framework Core: Client-side Evaluation

My post a few weeks ago on Entity Framework Core: String Filter Tips may have come across as too harsh on client-side evaluation based on what I saw on Reddit. My point wasn’t that you shouldn’t use client-side evaluation when you need too, but that you should be making a choice when to use client-side evaluation and it is very easy to miss when a query is running client-side.

TimeRemove did a great job explaining ways to detect and deal with client-side evaluation which this post will review. The sample application is the same one used in  Entity Framework Core: String Filter Tips as defined in the Sample Application section.

Sample Client-side Query

The following is the query we will be using to trigger client-side evaluation.

var contacts = 
 await _context
       .Contacts
       .Where(c => String.Compare(c.FirstName, "D", StringComparison.Ordinal) > 0)
       .ToListAsync();

No Changes

Running this query with no change to the application will actually give you the following warning in the Debug Window.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression ‘where (Compare([c].FirstName, “D”, Ordinal) > 0)’ could not be translated and will be evaluated locally.

I’m not sure how long this warning has been there, but this is the first time I have noticed it. Good reminder to make sure and check the debug window more often.

Throw Exception for Client-side Evaluation

Making the following change to the DbContext will cause Entity Framework Core to throw an exception when it does client-side evaluation instead of just showing a warning. In this example, we are overriding the OnConfiguring function of our DbContext and changing to throw an exception if the application is built in debug mode.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
#if DEBUG
    optionsBuilder.ConfigureWarnings(w => 
          w.Throw(RelationalEventId.QueryClientEvaluationWarning));
#endif

    base.OnConfiguring(optionsBuilder);
}

Now running the sample query results in the following exception.

InvalidOperationException: Error generated for warning ‘Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression ‘where (Compare([c].FirstName, “D”, Ordinal) > 0)’ could not be translated and will be evaluated locally.’. This exception can be suppressed or logged by passing event ID ‘RelationalEventId.QueryClientEvaluationWarning’ to the ‘ConfigureWarnings’ method in ‘DbContext.OnConfiguring’ or ‘AddDbContext’.

There are times when a client-side evaluation is what you want. How do you deal with that need and leave the exception enabled so you get a notification when a client-side evaluation is happening? To accomplish this you will need to split your query into multiple parts running the server evaluated parts and then in a different statement running the client evaluated parts. The following is our sample query broken up so that no exception is thrown.

//Server evaluation
var contacts = await _context.Contacts.ToListAsync();

//Client evaluation
contacts = 
    contacts
        .Where(c => String.Compare(c.FirstName, "D", StringComparison.Ordinal) > 0)
        .ToList();

Wrapping Up

If I introduced any confusion I hope this post helps clear it up. Just to be 100% clear client-side evaluation isn’t bad, but I feel it is important for it to be a choice that the developer has made. I hope the above will give you the tools to be clear on when you are choosing client-side evaluation.

Thanks again to TimeRemove for the clarifications to my original post.

Entity Framework Core: Client-side Evaluation Read More »

Entity Framework Core: SQL Server Concurrency Checks

Over the last couple of weeks, I have covered concurrency checks in SQLite and Postgres. This week I’m expanding my Entity Framework sample repo to include a SQL Server based example project in order to demo concurrency checks. for SQL Server. The repo with the SQL Server project added, but before the concurrency checks can be found here.

Note that all the changes in this post will be made under the SqlServer folder.

Context Changes and Data Migration

SQL Server has a built-in column type of rowversion which is automatically generated with a unique binary number when a row is inserted or updated. In order to utilize the rowversion column type via Entity Framework, we need to add a new column to our model with the Timestamp attribute like the following from the sample code in the Contact model.

[Timestamp] 
public byte[] Timestamp { get; set; }

Next, from a command prompt in the same directory as your project file using the following .NET CLI command to generate a migration for the above change.

dotnet ef migrations add ContactTimestamp --context ContactsDbContext

Then, use the following command to apply the migration to your database.

dotnet ef database update --context ContactsDbContext

Testing it out

For a quick test add a ConcurrencyTest razor page under the Contacts directory. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

public void OnGet()
{
    var context1 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlServer("yourConnectionString")
                                         .Options);
    var contactFromContext1 = context1.Contacts
                              .FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
                              {
                                  Name = "Test"
                              };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var context2 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlServer("yourConnectionString")
                                         .Options);
    var contactFromContext2 = context2.Contacts
                              .FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is https://localhost:44324/Contacts/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

Of the three providers, Postgres seems to have the simplest implementation, not that concurrency is going to be how you choose your database provider. I don’t plan on adding any more providers at the moment, but if you are using with that hasn’t be covered leave a comment and I will look at adding another example.

The code in its final state can be found here.

Entity Framework Core: SQL Server Concurrency Checks Read More »

Entity Framework Core: Postgres Concurrency Checks

Last week’s post on SQLite Concurrency Checks used this repo which contains examples of using Entity Framework Core with SQLite and Postgres. This post is going to tackle concurrency checks using Postgres to keep the projects in the repo with the same level of functionality. You can grab the sample code before any change here. This whole post will only be touch files found in the Postgres folder/project.

Context Changes and Data Migration

Unlike SQLite, Postgres has better built-in support for concurrency checks. If you read the official docs on Optimistic Concurrency and Concurrency Tokens you will find that all tables have an implicit/hidden system column call xmin which holds the ID of the latest updating transaction which means it gets changed automatically every time a row is changed.

The Postgres Entity Framework Core provide contains an extension that makes it very simple to use the xmin column as a concurrency token. In the ContactDbContext add the following to the OnModelCreating function to enable concurrency checking on the specified entity, in this case, a Contact.

modelBuilder.Entity<Contact>().ForNpgsqlUseXminAsConcurrencyToken();

Next, from a command prompt in the same directory as your project file using the following .NET CLI command to generate a migration for the above change. This migration is a bit strange since the column technically already exists, but the migration seemed to be needed.

dotnet ef migrations add Concurrency --context ContactsDbContext

Then, use the following command to apply the migration to your database.

dotnet ef database update --context ContactsDbContext

Testing it out

For a quick and dirty test, I added a ConcurrencyTest razor page under the Contacts directory. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

public void OnGet()
{
    var context1 = 
        new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                              .UseNpgsql("yourConnectionString")
                              .Options);
    var contactFromContext1 = context1.Contacts
                                      .FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
                              {
                                  Name = "Test"
                              };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var context2 = 
        new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                              .UseNpgsql("yourConnectionString")
                              .Options);
    var contactFromContext2 = context2.Contacts
                                      .FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is https://localhost:44324/Contacts/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

This process was much simpler using Postgres than SQLite. Not that the SQLite version was hard just not as simple of a path.

The code in its final state can be found here.

Entity Framework Core: Postgres Concurrency Checks Read More »

Entity Framework Core: SQLite Concurrency Checks

Most of the work I have done with SQLite has been on single-user systems, but recently I had to work on a SQLite project that was going to have a handful of concurrent users and a subset of the user activities would need to deal with concurrency issues. In the past, in a situation like this, I have been using SQL Server and use the rowversion or timestamp column type which places a unique value on the row on any updates or inserts.

There is a page in the official docs on Concurrency Tokens, but for me, it wasn’t super helpful. Thankfully after some searching, I came across the GitHub issue In ASP.Net Core 2.x with Entity Framework Core, Concurrency Control not working with SQLite which had a solid sample as one of the replies. This post is going to walk through an example implementation of that sample. The starting point of the code can be found in this GitHub repo.

Sample Background

The sample project being used is a simple web application to manage a contact list. The repo contains an implementation using Postgres and one using Sqlite. This whole post will only be touch files found in the Sqlite folder/project.

Model Changes and Data Migration

SQLite doesn’t have the concept of a timestamp column, but this solution is going to emulate one. To do this we are going to change the Contact model found in the Models folder. We are going to add a Timestamp property with a Timestamp data annotation. The following is the full model class with the new property at the bottom.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    [Timestamp] public byte[] Timestamp { get; set; }
}

Next, let’s create a new migration with the change to the model. I’m using the .NET CLI so from a command prompt in the project directory run the following command.

dotnet ef migrations add ContactTimestamp --context ContactsDbContext

In the Migrations directory, open newly created migration. It should be named something like *_ContactTimestamp.cs.  In the Up function, we are going to add a couple of triggers to the new Timestamp column. These triggers are going to assign a random blob to the timestamp column when a row is inserted or updated which is how we are simulating the function of SQL Server’s Timestamp data type. The following is the full Up function with the added triggers.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<byte[]>(
        name: "Timestamp",
        table: "Contacts",
        rowVersion: true,
        nullable: true);

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnUpdate
        AFTER UPDATE ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnInsert
        AFTER INSERT ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");
}

To apply the migration to the database you can use the following command.

dotnet ef database update --context ContactsDbContext

Testing it out

Now for a quick and dirty test, we are going to add a ConcurrencyTest function to the existing ContactsController. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

[Route("ConcurrencyTest")]
public void ConcurrencyTest()
{
    var context1 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);
    var context2 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);

    var contactFromContext1 = context1.Contacts.FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
        {
            Name = "Test"
        };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var contactFromContext2 = context2.Contacts.FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is http://localhost:1842/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

While the information wasn’t the easiest in the world to locate, as you can see Entity Framework Core using SQLite has good support for concurrency control. The above is just one option for its implementation. I hope this saves you all so time.

The code in its final state can be found here.

Entity Framework Core: SQLite Concurrency Checks Read More »

Entity Framework Core: String Filter Tips

I have been working on an application that is backed by Entity Framework Core using SQLite and I have hit a couple of things that were not super clear to me at first when dealing with string filters. This post is going to cover setting up a sample application and demoing a couple of things to keep in mind when working with string in filters.

Sample Application

I’m using the Razor Pages template with individual auth as the base for this sample since it comes with Entity Framework Core already set up and ready to go. Using a command prompt in the directory you want the sample project created run the following command.

dotnet new razor --auth Individual

Open the resulting project in your editor of choice and add a Models folder. As usual, I’m going to be using a contact as my example so inside the Models folder create a Contact class matching the following. The override on ToString is just to make it easy to see the results when debugging.

public class Contact
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }

    public override string ToString()
    {
        return $"{Id} - {LastName}, {FirstName}";
    }
}

Next, open the ApplicationDbContext found in the Data folder and add the following DbSet property to expose our contacts.

public DbSet<Contact> Contacts { get; set; }

Add the following OnModelCreating function to the ApplicationDbContext which will create some test data for us.

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Contact>()
           .HasData(new Contact
                    {
                        Id = 1,
                        FirstName = "Bob",
                        LastName = "Smith",
                        Address = "123 Main Street",
                        City = "Nashville",
                        State = "TN",
                        Zip = "35970"
                    },
                    new Contact
                    {
                        Id = 2,
                        FirstName = "Sam",
                        LastName = "Smith",
                        Address = "1 Sun Lane",
                        City = "Knoxville",
                        State = "TN",
                        Zip = "48909"
                    },
                    new Contact
                    {
                        Id = 3,
                        FirstName = "Clark",
                        LastName = "Swift",
                        Address = "750 10th Street",
                        City = "Chattanooga",
                        State = "TN",
                        Zip = "91590"
                    }
                   );

    base.OnModelCreating(builder);
}

Back in the command prompt run the following command in the same directory as the csproj file to create a migration for our new contact model.

dotnet ef migrations add Contacts

Finally, run the following command to apply the migration to your database.

dotnet ef database update

Data Execution

For this example, I don’t really care to display the results in a UI so I am using the OnGetAsync of the Index page to run my queries. The following is my full index page model with a query that returns all the contacts. The rest of the post will just be showing the LINQ statements to query the database and not the full page model.

public class IndexModel : PageModel
{
    private readonly ApplicationDbContext _context;

    public IndexModel(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IActionResult> OnGetAsync()
    {
        var contacts = await _context.Contacts.ToListAsync();

        return Page();
    }
}

The above results in all the of the contacts seeded being return.

1 - Smith, Bob
2 - Smith, Sam
3 - Swit, Clark

Like Queries

As part of the Entity Framework Core 2.0 release EF.Functions.Like was added which allows usages of wildcards that were not possible using string function translation that was previously the only option.  The following query is an example of using like.

_context.Contacts
        .Where(c => EF.Functions.Like(c.LastName, "S_i%"))
        .ToListAsync();

While this would have been possible before I would imagine the query would have been nasty and involved some level of client-side evaluation. The result of the query is the same as above.

Greater Than/Less Than

Using String.Compare or value.CompareTo will allow you to do greater than or less than comparison on strings. For example String.Compare(value) > 0 give you a greater than and less than zero would be for less than. For example, here is a string comparison query along with the SQL that is generated.

_context.Contacts
        .Where(c => String.Compare(c.FirstName, "D") > 0 )
        .ToListAsync();

SELECT [c].[Id], [c].[Address], [c].[City], [c].[FirstName], [c].[LastName], [c].[State], [c].[Zip]
FROM [Contacts] AS [c]
WHERE [c].[FirstName] > N'D'

It is important to not try and use any of the overloads of String.Compare or you will end up with client-side evaluation of your query. The following is a query that uses one of the overloads and the SQL that is generated.

_context.Contacts
        .Where(c => String.Compare(c.FirstName, "D", StringComparison.Ordinal) > 0)
        .ToListAsync();

SELECT [c].[Id], [c].[Address], [c].[City], [c].[FirstName], [c].[LastName], [c].[State], [c].[Zip]
FROM [Contacts] AS [c]

Notice that the first query has a Where clause and the second one doesn’t. This means the second query will pull all the records to the client and then apply the filter.  While this is fine to a small amount of data please be careful with queries that are evaluated client-side as they can cause performance issues.

Both of the above queries return the following result.

2 - Smith, Sam

Wrapping Up

This is one of those posts that will be a reminder for me as much as anything. I do hope that is save you some time of clears up a bit how some of the way that Entity Framework Core handles strings.

Entity Framework Core: String Filter Tips Read More »

Migration from ASP.NET Core 2.1 to 2.2

On December 4th .NET Core 2.2 including ASP.NET Core 2.2 and Entity Framework 2.2. In this post, I will be taking one of the projects used in the ASP.NET Basics series and converting it from ASP.NET 2.1.x to the new 2.2 version of ASP.NET Core. This will all be based on the official 2.2 migration guide.

The code before any changes can be found here. In the sample solution, this guide will be working with the Contacts project only.

Installation

Head over to the .NET download page and download the new version of the .NET Core SDK for version 2.2 which is available for Window, Linux and Mac.

After installation is done run the following command if you want to verify the SDK is installed.

dotnet --list-sdks

You should see 2.2.100 listed. If you are like me you might also see a few preview versions that would be good to uninstall.

If you are using Visual Studio make sure you are on at least version 15.9. If not updates can be downloaded from here.

Project File Changes

Right-click on the project and select Edit projectName.csproj.

Change the TargetFramework to netcoreapp2.2.

Before:
<TargetFramework>netcoreapp2.1</TargetFramework>
After:
<TargetFramework>netcoreapp2.2</TargetFramework>

Update any Microsoft packages with a version to 2.2.x the following is an example.

Before:
<PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.1.1" PrivateAssets="All" />

After:
<PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.0" PrivateAssets="All" />

If you want to use the new IIS in-process hosting model you also need to add the following line to a property group.

<AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>

The following is my full csproj for reference.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    <AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>
    <UserSecretsId>aspnet-Contacts-cd2c7b27-e79c-43c7-b3ef-1ecb04374b70</UserSecretsId>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.0" PrivateAssets="All" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="4.0.1" />
  </ItemGroup>

</Project>

Startup Changes

In Startup.cs update the compatibility version to enable the new 2.2 features.

Before:
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

After:
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

Wrapping Up

As with the migration to 2.1 the move to 2.2 is really easy to do. Make sure you check out the official migration guide for more details that may have not been covered by this project.

The code in its final state can be found here.

Migration from ASP.NET Core 2.1 to 2.2 Read More »

Visual Studio Missing Scaffolding for ASP.NET Core

This morning I set out to try the Scaffold Identity option that was added as part of the ASP.NET 2.1 release. The docs for this feature is really good so I didn’t think I would have any issue, but I was wrong.

Sample Application

To start I used the .NET CLI to create a new application using the following command.

dotnet new razor

I then opened the project in Visual Studio. Then following the docs I right-clicked on the project and expanded the Add option, but the New Scaffolded item is missing.

Sample Application 2

Since the project created using the CLI didn’t have the option I thought I would try creating a new project in Visual Studio. From in Visual Studio using File > New > Project.

Under Visual C# > Web select ASP.NET Core Web Application and click OK.

Select Web Application and then click OK.

After the creation process finishes I right-clicked on the project and expanded the Add option and the New Scaffolded item is there.

What’s the difference?

After trying a lot of different things I finally got to the point of looking at the csproj files for both projects. Here is the csproj from the Visual Studio create project.

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.1.2" PrivateAssets="All" />
  </ItemGroup>
</Project>

And the csproj from the CLI created project.

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>


  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0-preview3-35497" PrivateAssets="All" />
  </ItemGroup>
</Project>

Notice that the CLI created project is targeting .NET Core 2.2 which is in preview at the time of this writing. Not surprisingly Visual Studio has some limits on what can be done using preview bits.

Wrapping Up

I can’t believe that I let having preview bits installed cause me issues again. I’m guessing that most people aren’t installing the previews so hopefully, this isn’t an issue most of you will have to deal with.

If you do want to use the CLI to create an application targeting a specific version of .NET Core it can be done using a global.json file. Check out Controlling .NET Core’s SDK Version for more information.

Visual Studio Missing Scaffolding for ASP.NET Core Read More »

Azure DevOps Project

After last week’s post on Azure Pipelines: Release to Azure App Service I came across a much easier way to get started using Azure DevOps and Azure App Servies. This post is going to walk through this process which is started from the Azure Portal side instead of Azure DevOps.

The names here are going to be a bit confusing. When I say Azure DevOps I am talking about the rebrand of Visual Studio Team Services which includes services for boards, repos, pipeline, etc. When I say Azure DevOps Project, or just DevOps Project, I am referring to the project type this post is going to be using from the Azure Portal side.

Create DevOps Project

From the Azure Portal click the Create a resource button.

For me, DevOps Project was in the list of popular items. If you don’t see it list you can use the search at the top to find it. Click on the DevOps Project to start the process.

On the next page, you have options to start a new application with a lot of different languages or to deploy an existing application. For this example, we are going to select .NET and click the Next button. This screen is a great example of how Microsoft is working hard to support more than just .NET.

For .NET the next choice is ASP.NET or ASP.NET Core. No surprise I’m sure that we are going to go with ASP.NET Core. There is also an option for adding a database, but we aren’t going to use it for this example. Click Next to continue.

The next step is to select which Azure Service the new application should run on. We are going to use a Linux Web App to match what last week’s sample was running on. Click Next to continue.

There are quite a few settings on this next screen, but they are all pretty clear. The first group of settings is for the Azure DevOps project and you can either use an existing account or the process will create one for you. A Project name is required.

The next group of settings is for the Azure App Service that will be created. Subscription should default in if you only have one. Web app name is going to control the URL Azure provides as well as play in the naming of the resources that are created. Click Done to start the creation process.

Deployment

After clicking down above the deployment of all the need resources will start. This process takes awhile. The portal will redirect you to a status page similar to the following while deployment is in progress. It took a little over 4 minutes for mine to complete.

When the deployment is complete click the Go to resource button.

Results

The Overview page for the project gives a great summary of the whole CI/CD pipeline that was created with links to the associated Azure DevOps pages to manage each step. The Azure resources section will have the URL you can use to access the running application.

The resulting application should look similar to the following.

Wrapping Up

This process is a much easier way to get started if you are going all in with Azure. If you read last week’s post you know there is a lot that goes into creating something close to this setup manually and even then it was missing the nice overview provided by this setup.

Azure DevOps Project Read More »