Una funzionalità molto comune nelle applicazioni è quella di dover recuperare una lista di oggetti dati i loro id. Per esempio, potremmo dover creare un metodo che accetti una lista di id e restituisca le persone corrispondenti a quegli id. Il modo più semplice e veloce per eseguire questa operazione è usare il metodo LINQ Contains come mostrato nell'esempio.

People[] GetPeopleByIds(int[] ids){
  return _ctx.People.Where(x => ids.Contains(x.Id)).ToArray();
}

Questa query produce un codice SQL simile a questo.

SELECT * FROM People WHERE Id IN (1,2,4,7,10,5)

Sebbene perfettamente funzionante, EF Core scrive gli id direttamente nel codice SQL. Questo significa che se il metodo viene chiamato spesso con parametri sempre diversi, Sql Server genera un query plan per ogni query, perchè la stringa SQL cambia sempre. Il risultato è che la cache dei plan di Sql Server è piena di query praticamente identiche dove quello che cambia sono solo gli id e questo va a scapito delle performance della cache in quanto ci sono troppe entry e alcune potrebbero rubare il posto ad altre più importanti.

Per evitare di mal utilizzare la cache dei plan, possiamo fare in modo di riscrivere la query in modo da evitare l'utilizzo dei dati cablati. Per fare questo possiamo creare una stringa che contiene gli id separati da virgola e passarla a una funzione di Sql Server che la trasforma in una tabella che poi mettiamo in join con quella in cui dobbiamo cercare i dati.

Il primo passo consiste nel creare la funzione che altro non è che una Table-Valued Function. Il risultato della funzione è una tabella con un campo di nome value di tipo int.

ALTER Function [dbo].[Int_Split](@string varchar(max))
Returns
@Result Table (value int)
As
Begin
  declare @len int, @loc int = 1
  While @loc <= len(@string) 
  Begin
    Set @len = CHARINDEX(',', @string, @loc) - @loc
    If @Len < 0 Set @Len = len(@string)
    Insert Into @Result Values (SUBSTRING(@string,@loc,@len))
    Set @loc = @loc + @len + 1
  End
  Return
End

Una volta creata la funzione, dobbiamo mapparne il risultato con una classe C# come se fosse una tabella.

public class IntSplitResult
{
  public int Value { get; set; }
}

Ora possiamo mappare la classe IntSplitResult come view (specificando che non ha una chiave) e la funzione SQL con una funzione C# utilizzabile nelle nostre query LINQ.

public partial class PeopleContext
{
  ...

  partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<StringSplitResult>().HasNoKey();
  }

    [DbFunction(IsBuiltIn = false, Name = "INT_SPLIT")]
    private IQueryable<IntSplitResult> IntSplit(string source)
        => FromExpression(() => IntSplit(source));

    public IQueryable<int> AsSplit(IEnumerable<int> source)
        => IntSplit(string.Join(",", source.Select(x => Convert.ToString(x)))).Select(s => s.Value);
}

Il metodo AsSplit prende in input la lista di id e la trasforma in una stringa di numeri separati da virgola per poi passarla alla funzione IntSplit che agisce come proxy verso la funzione in Sql Server. A questo punto possiamo scrivere la nostra query che sfrutta il metodo AsSplit.

People[] GetPeopleByIds(int[] ids){
  return _ctx.People.Where(x => db.AsSplit(ids).Contains(x.Id)).ToArray();
}

Il codice SQL generato dalla query è il seguente (semplificato).

DECLARE @__source_1 nvarchar(4000) = N'1,2,3,4,5';

SELECT *
FROM People AS p
WHERE EXISTS (
    SELECT 1
    FROM Int_Split(@__source_1) AS [s]
    WHERE (s.Value = p.Id))