Table-Valued Parameters in EF Core

Ab SQL Server 20008 können benutzerdefinierte Tabellentypen folgendermassen erstellt werden:

CREATE TYPE dbo.list_of_int AS TABLE (Id INT)

Es können auch Typen mit mehreren Spalten definiert werden, meistens wird jedoch nur eine Möglichkeit benötigt, eine Liste von Zahlen oder Strings an eine Stored Procedure zu übergeben.

Eine Stored Procedure könnte z.B. folgendermassen aussehen:

CREATE PROCEDURE GetSomeUsers
  @IdList list_of_int READONLY
AS
BEGIN
  SELECT * FROM [User] WHERE UserId IN (SELECT Id FROM @IdList)
END

Aufruf aus C#:

int[] list = new[] { 1, 2, 3, 4, 5 };
ctx.User.FromSql($"EXEC GetSomeUsers {CreateListParameter("@p2", "dbo.int_list", list)}");

Es wird angenommen, dass User bereits als DbSet definiert wurde. dbo.int_list ist der Name des benutzerdefinierten Tabellentyps. Die Methode CreateListParameter ist folgendermassen definiert:

private static SqlParameter CreateListParameter(string paramName, string typeName, IEnumerable ids)
{
  SqlParameter param = new SqlParameter(paramName, SqlDbType.Structured);
  param.TypeName = typeName;
  var records = new List();
  param.Value = records;
  foreach (int id in ids)
  {
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));
    record.SetInt32(0, id);
    records.Add(record);
  }
  return param;
}

Hinweis: Bis und mit EF Core 2.1 hatte die FromSql Methode einen Bug, dass SqlParameter Objekte in einem FormattableString nach einem bestimmten Schema benannt werden mussten. Ein Parameter der z.B. an erster Stelle verwendet wurde, musste @p0 heissen, einer an zweiter Stelle @p1 usw. Hat man sich nicht daran gehalten, wurde eine Ausnahme mit der Fehlermeldung "Must declare the scalar variable @p0" geworfen.