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 »
