Efcore: 'HasConversion' functions not getting called when querying for specific field

[maheshchauhan-terem]

File a bug

'HasConversion' functions not getting called when querying for a specific fields using Where or FirstOrDefault.

Code

MyContext.cs
```C#
modelBuilder.Entity().Property(p => p.FirstName).HasConversion(
val => Encrypt(val),
val => Decrypt(val));

Now in Controller, if I do this:
```C#
await context.User.ToListAsync()

then I am getting all fields of User including FirstName (decrypted) and on debugging I can see its going inside 'Decrypt' function as specified in value conversion.

But if I do this:
```C#
await context.User.FirstOrDefaultAsync(x => x.FirstName == "Test")

OR
```C#
context.User.Where(x => x.FirstName == "Test")

then its returning null and not even going inside "Decrypt" function.

Include provider and version information

EF Core version:
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET Core 3.1
Operating system: macOS
IDE: JetBrains Rider

[ajcvickers]

@maheshchauhan-terem I am not able to reproduce this, at least on SQL Server--see my code below. @roji This doesn't seem like it could be PostgresSQL-specific, right?

```C#
public class Program
{
public static async Task Main()
{
using (var context = new SomeDbContext())
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

        context.Add(new User {FirstName = "Arthur"});

        await context.SaveChangesAsync();
    }

    using (var context = new SomeDbContext())
    {
        var user = await context.User.FirstOrDefaultAsync(x => x.FirstName == "Arthur");
        Console.WriteLine(user?.FirstName);
    }
}

}

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

  public string FirstName { get; set; }

}

public class SomeDbContext : DbContext
{
private static readonly ILoggerFactory
Logger = LoggerFactory.Create(x => x.AddConsole()); //.SetMinimumLevel(LogLevel.Debug));

public DbSet<User> User { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        => optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlite("DataSource = test.db");

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<User>().Property(p => p.FirstName).HasConversion(
          val => Encrypt(val),
          val => Decrypt(val));
  }

  private string Encrypt(string val) 
      => val + "_encrypted";

  private string Decrypt(string val)
      => val.Substring(0, val.Length - "_encrypted".Length);

}

The logs are:

home/ajcvickers/.dotnet/dotnet /home/ajcvickers/AllTogetherNow/ThreeOne/bin/Debug/netcoreapp3.1/ThreeOne.dll
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.8 initialized 'SomeDbContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA journal_mode = 'wal';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "User" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_User" PRIMARY KEY AUTOINCREMENT,
"FirstName" TEXT NULL
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[@p0='Arthur_encrypted' (Size = 16)], CommandType='Text', CommandTimeout='30']
INSERT INTO "User" ("FirstName")
VALUES (@p0);
SELECT "Id"
FROM "User"
WHERE changes() = 1 AND "rowid" = last_insert_rowid();
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.8 initialized 'SomeDbContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "u"."Id", "u"."FirstName"
FROM "User" AS "u"
WHERE "u"."FirstName" = 'Arthur_encrypted'
LIMIT 1
Arthur
```

[smitpatel]

Few points

• When generating query, we will go inside Encrypt function only to encrypt "Test" so that it can be compared server side.
• If there are no matching records on the server with "Test" then results would be null and empty list respectively.
• If there are no result materialized then Decrypt is never called

[maheshchauhan-terem]

There is another point which I should have mentioned is the User entity definition is this:

```C#
public class User {
public int Id { get; set; }

    [Column(TypeName = "bytea")]
    public string FirstName { get; set; }

}
```

As you can see the FirstName is of type string but its column type is bytea as its storing the encrypted bytes. But as I mentioned if I query on User using Id the results contains the decrypted FirstName means its going into conversion properly but not working when I query directly using FirstName

[smitpatel]

@maheshchauhan-terem - We will need a repro which demonstrate the issue. As @ajcvickers posted above, we are not able to reproduce based on what information provided.

[maheshchauhan-terem]

Hi @smitpatel and @ajcvickers I don't know if the issue is that but I am using postgres "pgcrypto" extension installed at the database level to encrypt and decrypt the field value. To reproduce it, you need to do below:

1. Create a local postgres db named "test"
2. Using pgAdmin or other tool run below queries on the created db.

```C#
CREATE EXTENSION IF NOT EXISTS pgcrypto;

```C#
CREATE OR REPLACE FUNCTION public.encryptTest(t text, k text) RETURNS bytea AS $function$
BEGIN
   RETURN pgp_sym_encrypt(t, k);
END;
$function$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.decryptTest(t bytea, k text) RETURNS text AS $function$
BEGIN
   RETURN pgp_sym_decrypt(t, k);
END;
$function$ LANGUAGE plpgsql;

1. Once the first two steps done then run the below application code: (I created a basic console app)

```C#
public class Program
{
public static async Task Main()
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseNpgsql("host=localhost;port=5432;userid=postgres;password=password;database=test;");
using (var context = new SomeDbContext(optionsBuilder.Options))
{
await context.Database.EnsureCreatedAsync();

            context.Add(new User {FirstName = "Arthur"});

            await context.SaveChangesAsync();
        }

        using (var context = new SomeDbContext(optionsBuilder.Options))
        {
            var user = await context.User.FirstOrDefaultAsync(x => x.FirstName == "Arthur");
            Console.WriteLine("user name : " +  user?.FirstName);

            var userTest = context.User.ToList().FirstOrDefault(x => x.FirstName == "Arthur");
            Console.WriteLine("userTest name : " +  userTest?.FirstName);
        }
    }
}

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

    [Column(TypeName = "bytea")]
    public string FirstName { get; set; }
}

public class SomeDbContext : DbContext
{

    public DbSet<User> User { get; set; }

    public SomeDbContext(DbContextOptions options) : base(options)
    {

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        => optionsBuilder
            .EnableSensitiveDataLogging()
            .UseNpgsql("host=localhost;port=5432;userid=postgres;password=password;database=test;");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().Property(p => p.FirstName).HasConversion(
            val => Encrypt(val),
            val => Decrypt(val));
    }

    private byte[] Encrypt(string text)
    {
        Console.WriteLine("Encrypt");
        using (var dbContext = new SomeDbContext(GetOptionBuilder().Options))
        using (var command = dbContext.Database.GetDbConnection().CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "encryptTest"; // Function name in postgres
            command.Parameters.Add(
                new Npgsql.NpgsqlParameter("t", NpgsqlTypes.NpgsqlDbType.Text) {Value = text});
            var publicKey = "Salted__I]4¥sô¬©]ígòi%e·õW…ðB";
            command.Parameters.Add(
                new Npgsql.NpgsqlParameter("k", NpgsqlTypes.NpgsqlDbType.Text) {Value = publicKey});
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }

            var encrypted = (byte[]) command.ExecuteScalar();
            return encrypted;
        }
    }

    private string Decrypt(byte[] cipher)
    {
        Console.WriteLine("Decrypt");
        using (var dbContext = new SomeDbContext(GetOptionBuilder().Options))
        using (var command = dbContext.Database.GetDbConnection().CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "decryptTest"; // Function name in postgres
            command.Parameters.Add(
                new Npgsql.NpgsqlParameter("t", NpgsqlTypes.NpgsqlDbType.Bytea) { Value = cipher });
            var privateKey = "Salted__I]4¥sô¬©]ígòi%e·õW…ðB";
            command.Parameters.Add(
                new Npgsql.NpgsqlParameter("k", NpgsqlTypes.NpgsqlDbType.Text) { Value = privateKey });
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }

            var decrypted = (string)command.ExecuteScalar();
            return decrypted;
        }
    }

    private DbContextOptionsBuilder<SomeDbContext> GetOptionBuilder()
    {
        var optionsBuilder = new DbContextOptionsBuilder<SomeDbContext>();
        optionsBuilder.UseNpgsql("host=localhost;port=5432;userid=postgres;password=password;database=test;");
        return optionsBuilder;
    }
}

```

When you will run it creates a record in User table properly and upon querying it doesn't return a result if we query directly using FirstName but it returns correct result if we do context.User.ToList().FirstOrDefault(x => x.FirstName = "Arthur")

[ajcvickers]

/cc @roji

[roji]

@maheshchauhan-terem the pgp_sym_encrypt function will return a different value each time you use it: try running SELECT pgp_sym_encrypt('key', 'clear') several times, and you will see a different value each time. This means that you're inserting one value into the database, but later when you're trying to compare to it when selecting, you're comparing against a different value. If you put ToList() before the filter, the predicate is evaluated client-side and comparison happens on cleartext instead, which is why it works.

It seems like you may want to do hashing instead. Hashing (SHA1, MD5...) will always produce the same output given the same inputs (and salt), so you can use it to encrypt passwords and later check that the cleartext corresponds to the hashed output in the database. However, carefully consider whether you want to send the cleartext over the network - usually the point of encryption is to avoid that. A proper, secure solution would be to perform all hashing/encryption on the client (using a .NET implementation), and send hashed/encrypted outputs to the database.

Finally, carefully consider the performance implications of contacting the database from inside a value converter. This means that you are executing a database roundtrip for each and every property you need to encrypt or decrypt - this is usually a pretty bad idea.

[maheshchauhan-terem]

Thanks @roji for explaining this and recommending the solution. Will need to revisit the implementation. 😬