[Solved] EF SQL query Performance on comma-separated string Ids

by


If you store Ids as comma separated string – you always have TABLE/INDEX scan. If your table is small it can be enough.

With SecondaryTable table which stores Ids associated with main table there a lot of other plans:

  1. You can leave as is and trust or not DB Engine optimiser
query = query.Where(x => x.SecondaryTable.Any(s => s.Id == value));
  1. If pair (MainId, Id) is unique. The following query should definitely hit index
var query = 
   from m in query
   from s in m.SecondaryTable.Where(s => s.Id == value)
   select s;
  1. If pair (MainId, Id) is NOT unique.
var secondary = db.SecondaryTable.Where(s => s.Id == value);
var mainIds = secondary.Select(s => new { s.MainId }).Distinct();

query = 
   from m in query
   from s in mainIds.Where(s => s.MainId == m.Id)
   select m;

Anyway, better to test and check execution plan.

0

solved EF SQL query Performance on comma-separated string Ids