0

I want to understand how can data be retrieved efficiently in EF Core if I have defined a secondary index (non-clustered) for my table.

I want to be able to do efficient lookups of single elements based on the secondary index:

public class Entity
{
   [Key]
   public long Id{get;set;}
   [Index(IsClustered=false,IsUnique=false)]
   public string SecondaryId{get;set;}
}

public class MyContext:DbContext{
   public DbSet<Entity> Entities{get;set;}
}

public void Find(long primaryIndex,string SecondaryId,MyContext context)
{
   var normalSearch=context.Entities.Find(primaryIndex);
   var secondaryIndexSearch=context.Entites.Find(SecondaryId); //does it know to look at the `SecondaryId` column and detect that its a secondary index ?
}
Dale K
  • 25,246
  • 15
  • 42
  • 71
Bercovici Adrian
  • 8,794
  • 17
  • 73
  • 152
  • The `.Find()` method **only** works with the **primary key** of your entity. It cannot be extended to cover secondary indexes – marc_s Jun 04 '20 at 04:47
  • But then if you query with `Linq` an `IEQueryable` with a predicate being `x=>x.SecondaryId` , will it search it based on the secondary index ? Will it deduce it is a secondary index and perform the query faster? – Bercovici Adrian Jun 04 '20 at 08:02
  • If EF can find an index, and if the SQL Server cost-based optimizer sees a benefit in using it - then **yes** that secondary index will be used. – marc_s Jun 04 '20 at 08:36
  • 1
    For any other searches you use the regular LINQ `Where`, `First`, `Any` etc. You nan do that for PK as well. The only additional functionality by `Find` is that it searches the local cache, thus locates `Added` *but tot yet committed) entities, while all other methods go to database. – Ivan Stoev Jun 04 '20 at 10:04
  • Ok i said `Find` improperly.What i wanted to say is : will EF leverage secondary indexes implicitly when evaluating Linq expressions if there are any ? Or do i have to manually tell it somehow ? – Bercovici Adrian Jun 04 '20 at 12:49

0 Answers0