EF SQL query Performance on comma-separated string Ids

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));

2. 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;

3. 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.

The first option performs a string comparison on a dynamically-generated string, so there is no possibility of an index improving query performance. It will be exceptionally slow.

The second option is performing a numeric comparison on an indexed primary key (assuming that Id is some numeric type and that it is the primary key). This is a much faster comparison for your processor to evaluate, and it is a simple comparison without generating dynamic data for said comparison.