Net Core: Generic Repository Primary Id Key Performance in Entity Framework

Question

We are reviewing two different methods in generic repository patterns. Currently, want to map primary keys to Ids. The purpose of this is to map to the Generic Repository Interface which utilizes Id. Two solutions are provided below.

What are performance implications of .FindPrimaryKey().Properties. Does it cause a schema lock on database table in trying to find the primary key? Does it cause any application slowness?

How does it compare in performance vs Partial Class Method Solution 2? What option is better performance-wise?

Note: Architects demand the use of repository pattern at the workplace, so implementing it. Know there is debate surrounding this issue, but not my call.

Scaffolded Model Example:

namespace Datatest
{
    public partial class Property
    {
        public int Property { get; set; }
        public int DocumentId { get; set; }
        public string Address { get; set; }
    }
}

Sample Generic Base Repository for all tables:

    public T Get(int id)
    {
        return Table.Find(id);
    }
    public async Task<T> GetAsync(int id)
    {
        return await Table.FindAsync(id);
    }
    public T Single(Expression<Func<T, bool>> predicate)
    {
        return All.Single(predicate);
    }
    public async Task<T> SingleAsync(Expression<Func<T, bool>> predicate)
    {
        return await All.SingleAsync(predicate);
    }
    public T FirstOrDefault(int id)
    {
        return All.FirstOrDefault(CreateEqualityExpressionForId(id));
    }

Solution 1: FindPrimaryKey()

Generic Repository in C# Using Entity Framework

use EF FindPrimaryKey()

var idName = _context.Model.FindEntityType(typeof(TEntity))
        .FindPrimaryKey().Properties.Single().Name;

Solution 2: Partial classes Mapping

Net Core: Create Generic Repository Interface Id Mapping for All Tables Auto Code Generation

public partial class Property: IEntity
{
    [NotMapped]
    public int Id { get => PropertyId; set => PropertyId = value; }
}

Answer 1

Regarding the first approach (using EF Core metadata services):

First, EF Core is ORM (Object Relational Mapper), with most important here is Mapper.

Second, it uses the so called code based model, which means all the mappings are provided by code and not the actual database (even though the model is created by reverse engineering of an existing database).

In simple words, EF Core creates at runtime a memory data structure containing the information (metadata) about classes and properties, and their mappings to database tables, columns and relationships. All that information is based on pure code model - the entity classes, conventions, data annotations and fluent configuration.

All EF Core runtime behaviors are based on that metadata model. EF Core uses it internally when building queries, mapping the query results to objects, linking navigation properties, generating create/update/delete commands and their order of execution, updating temporary FK property values after getting the real autogenerated principal key values etc.

Hence the metadata model and discovering services (methods) use optimized data structures and are (has to be) quite efficient. And again, no database operations are involved.

So the first approach is quite efficient. The performance impact of obtaining the PK property name via metadata service is negligible compared to actual query building, execution and materialization.

Also the performance of the first approach is similar to EF Core Find method which you are using in another method. Note that when calling Find method you just pass the PK value(s) and not the properties. So the method implementation should somehow know how to build the Where expression, right? And what it does internally is very similar to the suggested snippet.

---

Regarding the second approach:

It's simply not comparable because it doesn't work. It's possible to use base class/interface, but only if the actual property name is mapped - like all classes have Id property, and it's mapped to different column name in the database tables using [Column] data annotation or HasColumnName fluent API.

In your example, the Id property is [NotMapped] (ignored). Which means EF Core cannot map to the table column. The fact that your are mapping it to another property via code (property getter/setter) doesn't matter. EF Core is not a (de)compiler, it can't see your code, hence cannot translate a LINQ query using such properties to SQL.

Which in EF Core 2.x leads to either client evaluation (very inefficient, reading to whole table and applying the filter in memory), or exception if client evaluation is configured to do so. And in EF Core 3.0+ it will always be an exception.

So in case you don't remove properties like PropertyId and map the property Id (which would be hard with "database first" models), the second "approach" should be avoided. And even if you can map the actual Id property, all you'll save would be a few milliseconds. And again, when using Find you don't bother about performance, why bother with methods that uses the same (or similar) approach.