EF Core 6 - Prevent querying on a specific column

Question

Context

EF Core 6 with some columns in the database encrypted with Always Encrypted

Issue

Those encrypted columns cannot be queried. In some cases, EF will throw exceptions; others are not. But in principal, we do not want to do the query on these encrypted columns as the result is not correct. But, sometimes the developers do it by mistake since they forget about these encrypted fields...

Wish

Inspect the query (before EF sends it to database server), and throw exception if any of the encrypted columns presents in the query. Is there any way to achieve this?

I would recommend https://learn.microsoft.com/en-us/ef/core/modeling/table-splitting to move encrypted fields to a different instance. Allowing you more control over when they are queried. — Jeremy Lakeman, Sep 14 '22 at 03:19

score 1 · Answer 1 · answered Sep 14 '22 at 05:14

If you want to exclude certain columns from EF Core you can mark the properties representing those columns with [NotMapped] or using the fluent API in the OnModelCreating method. This will tell EF Core not to query those columns when it retrieves entities from the database.

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    [NotMapped]
    public String SecretData{ get; set; }
}

ref https://learn.microsoft.com/en-us/ef/core/modeling/entity-properties?tabs=fluent-api%2Cwithout-nrt

Another option would be to make use of Data Transfer Objects (DTO's) to exclude the encrypted columns.

public class BlogDTO
{
    public int BlogId { get; set; }
    public string Url { get; set; }

}

Then using EF Core select the non encrypted columns and map to the DTO.

var blogDto = context.Blogs
                        .Where(x => x.Id == blogId)
                        .Select(x => new BlogDTO {
                            BlogId = x.BlogId1,
                            Url = x.Url 
                            // etc, don't include the SecretData column
                        });

ref Exclude certain column from Entity Framework select statment

If using `[NotMapped]`, the column will not be created in the database which is not my intention. — kal, Sep 14 '22 at 06:43
That makes sense. How do you currently create / update the database? Are you using migrations? — Brett Manners, Sep 14 '22 at 07:23

score 0 · Answer 2 · answered Sep 15 '22 at 02:07

I would use shadow property

Shadow properties are the properties that are not defined in your .NET entity class directly; instead, you configure it for the particular entity type in the entity data model. They can be configured in the OnModelCreating() method of the context class.

Here is a full article on how to use it

https://www.entityframeworktutorial.net/efcore/shadow-property.aspx

By doing this, you will be able to prevent the call of this property since it doesn't exist in the .Net class

EF Core 6 - Prevent querying on a specific column

2 Answers2