DbSet<T>.Find by alternate key or predicate

[BalassaMarton]

It would be nice to have a version of DbSet<T>.Find that works with an alternate key or predicate.

  // Find by alternate key
  string barcode = "123456789";
  var product = db.Products.Find(p => p.Barcode, barcode);
  // Find by predicate
  var keyboards = db.Products.Find(p => p.Category == "Keyboard");

The proposed signatures:

  public TEntity Find<TKey>(Expression<Func<TEntity, TKey>> keySelector, TKey value);
  public TEntity Find(Expression<Func<TEntity, bool>> predicate);

Functionally, this can be achieved with extension methods, by first searching the local set, but it would perform better if there was an identity map for alt. keys and Find would search using that.

[ajcvickers]

@BalassaMarton This has been discussed several times in the past--for example, see #10303. Find has two advantages over executing a query:

• It is a simple API that takes a key value and nothing more
• It looks in the DbContext cache first and only does a query to the database if needed

Any time we look at expanding the Find API we always come to the conclusion that it would be better to just write a query. That is, it doesn't retain the first advantage of Find.

As for the second advantage, #1948 covers how this could be added to normal LINQ queries. Taking these two things together, the conclusion has been that we should implement #1948 instead.

[BalassaMarton]

@ajcvickers This is something that I've come across in an actual ETL project. Take this very simple example where we have a flat input with ProjectCode, ProjectName, ProjectStatus; and an EF Core target with Project entity that has long primary key and Code alternate key.
Let me first show you what doesn't work:

foreach (var sproj in source) {
    var proj = db.Projects.FirstOrDefault(p => p.Code == sproj.ProjectCode) ?? new Project { Code = sproj.ProjectCode };
    proj.Status = sproj.ProjectStatus;
    db.Projects.Update(proj);
}

When we hit the same project code a second time, FirstOrDefault will still return null since we haven't called SaveChanges yet. We could call SaveChanges after every update, but that would severely violate our nice unit of work pattern or whatever. The proposed overload to Find is precisely what you wrote: "It looks in the DbContext cache first and only does a query to the database if needed". That is what I need, look at the local set first, just like the original Find.

[ajcvickers]

@BalassaMarton We discussed this in triage and came to the conclusion that once #7391 (as referenced from #10303) is implemented, then it will be possible to easily to efficient lookup by key or alternate key in the state manager, which will allow different types of Find to be implemented as extension methods.

[BalassaMarton]

Sounds great, thank you.

[Neme12]

@ajcvickers The advantage of Find over queries is that it ignores global query filters. If we want to use queries to find a specific entity by ID, we need call IgnoreQueryFilters() on the query.

[BalassaMarton]

It does? That would be a huge issue in a multi-tenant environment.

[Neme12]

Sorry I was wrong, I thought they were only used for queries.