I recently read a blog post from Dustin Moris Gorski titled “The type system is a programmer’s best friend”, and he makes a lot of great points. Typically when dealing with data storage mechanisms, we model our objects using primitive types like int, string, double, etc. These primitives play well with database providers but can lead you to make errors when working with data. As Dustin says, “Good types can prevent bugs”. For example, you might typically represent an EmailAddress property with a string, but an email is a complex type with structure. Wouldn’t it be better to deal with the logical concept?

Well, if you’re on the side that wants to take this approach and an Entity Framework Core user, you will want to read this post. First, we’ll explore a simple database model and then discuss the querying caveat you’ll have to overcome.

The Domain Model

Let’s start with our domain model, which in this example, will have a Person with an identifier and an EmailAddress. We’ll also be using Entity Framework Core as our database access library. However, unlike a typically modeled relationship, the EmailAddress will not be another table; instead, we will map it to a primitive type of string. Let’s take a look at the model.

public class Person
{
    public int Id { get; set; }
    public EmailAddress EmailAddress { get; set; } 
        = new("");
}

public record EmailAddress(string Value)
{
    public string Domain => Value[Value.IndexOf('@')..];
    public string Username => Value[..Value.IndexOf('@')];
}

You’ll note that the EmailAddress model has domain-specific properties that can help you in the realm of .NET but do not apply to the database storage of our data. Let’s see how we can map this complex type to a text column in our SQLite database.

public class Database : DbContext
{
    public DbSet<Person> People => Set<Person>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlite("Data Source=database.db")
            .LogTo(Console.WriteLine)
        ;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .Property(m => m.EmailAddress)
            .HasConversion<EmailAddressConversion>();
    }
}

public class EmailAddressConversion 
    : ValueConverter<EmailAddress, string>
{
    public EmailAddressConversion()
        : base(
            emailAddress => emailAddress.Value,
            value => new EmailAddress(value)
        )
    { }
}

You can map any .NET type to a database column using EF Core value converters. To do so, you first need to implement a class that implements ValueConverter<T>, with the first type argument being the C# type and the second being the target column type. In the case of this sample, we are converting to string from an EmailAddress and vice versa. To register a converter, you’ll need to use the OnModelCreating method on your DbContext to register the converter for the property using the HasConversion method.

Once you’ve set up your mappings, it’s as simple as writing the C# you know and love.

var person = new Person
{
    EmailAddress = new("khalid+abuhakmeh@example.com")
};

database.People.Add(person);
database.SaveChanges();

Looking at the EF Core migrations, we can see how EF Core has mapped our property EmailAddress to an SQLiteTEXT column.

b.Property<string>("EmailAddress")
    .IsRequired()
    .HasColumnType("TEXT");

As you may know already, storing and retrieving data is only half the story. What about queries?

Querying complex types

Your first instinct might be to write the following LINQ Query to act on the EmailAddress value in the database.

var result = database
    .People
    .Where(p => p.EmailAddress.Value.EndsWith("@example.com"))
    .OrderByDescending(x => x.Id)
    .First();

Sadly, the translation from LINQ to SQL will lead to an exception.

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Person>()
.Where(p => p.EmailAddress.Value != null && "@example.com" != null && p.EmailAddress.Value.
EndsWith("@example.com"))' could not be translated. Either rewrite the query in a form that can
be translated or switch to client evaluation explicitly by inserting a call to 'AsEnumerable',
'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The exception makes sense. EF Core doesn’t know what EmailAddress.Value is in the context of the database schema. We only know of a column named EmailAddress. So how do we get EF Core to do what we want without complicating our OnModelCreating method with complex translations? Well, I have a trick for you!

Let’s update our EmailAddress object to include a cast operator from EmailAddress to string.

public record EmailAddress(string Value)
{
    public string Domain => Value[Value.IndexOf('@')..];
    public string Username => Value[..Value.IndexOf('@')];
    
    // THE IMPORTANT PART
    public static implicit operator string(EmailAddress d) => d.Value;
}

We can now update our LINQ statement with a cast and “trick” EF Core into translating the query correctly.

var result = database
    .People
    // Remember to cast your logical entity to the primitive
    .Where(p => ((string)p.EmailAddress).EndsWith("@example.com"))
    .OrderByDescending(x => x.Id)
    .First();

Running our query results in the following generated SQL.

SELECT "p"."Id", "p"."EmailAddress"
FROM "People" AS "p"
WHERE CAST("p"."EmailAddress" AS TEXT) LIKE '%@example.com'
ORDER BY "p"."Id" DESC
LIMIT 1

So how did that work? It is straightforward once you realize EF Core translates our C# using expressions into SQL. In this case, we’re leveraging the C# cast operator to take advantage of the EF Core translations for string methods like EndsWith. Since our logical entity maps directly to a column type, this translation works!

Conclusion

As you read in Dustin’s blog post, you can use domain objects to reduce the potential errors that may result from using primitive types. If you like that approach, you’ve seen that it doesn’t take a lot of EF Core configuration to make that happen in your codebase. Of course, the biggest hurdle will be crafting queries. Still, using the implicit cast operator, we can trick EF Core query translation into producing a SQL query that works against the schema. While we could write translations specific to each model and the SQL, we would like to generate, most logical entities will always map to a .NET/database primitive. EF Core already knows these primitives and has a library of translations for commonly-used .NET methods.

I hope you enjoyed this blog post, and if you haven’t already, please read Dustin’s blog post. It makes some great arguments.