Am încheiat recent un proiect având interacțiunea cu o bază de date SQL Server prin Entity Framework (EF) 6.3, elemente care nu puteau fi supuse modificării. Nu că ar fi ceva rău, nu că as avea ceva de obiectat, dar este important de menționat pentru restul povestirii.

Ilustrație – DALL-E2

Pe lângă legătura standard dintre entități și tabele (sau view-uri) din baza de date, am fost nevoit să folosesc și interogări construite, folosind, Database.SqlQuery<TSmuff>(), pe baza unor funcții SQL ce returnează date tabelare, dar cu parametri ceva mai… deosebiți – TVP, adică Table Valued Parameters.

Acest fapt necesită crearea explicită a unor instanțe de DbParameter; nu pot fi transmise pur și simplu valorile, fapt ce-ar lăsa-n sarcina EF crearea instanțelor de DbParameter atunci când este momentul. Și, ca o restricție suplimentară: Database.SqlQuery<>() acceptă drept argumente ori doar valori, ori doar instanțe de DbParameter; nu poți să le combini.

Rezultatul funcției e citit ca o colecție de obiecte bine-definite, iar modul general de lucru este:

– se obține un IQueryable<TSmuff> din context (DbContext), folosind o metodă care se ocupă cu formularea interogării, compunerea parametrilor (ca DbParameter) etc.;
– pe baza acelui IQueryable<TSmuff> se adaugă (eventual) clauze suplimentare folosind construcții LINQ;
– la un moment dat se materializează acel IQueryable<TSmuff> într-o colecție; spre exemplu, într-o List<TSmuff>;

public DbRawSqlQuery DoStuff(List<Guid> ids)
{
 	string queryText = "SELECT * FROM dbo.DoStuff(@ids)";
	SqlParameter pIds = CreateTVPSQLParameter( ids );
	return Database.SqlQuery<Smuff>( queryText, pIds);
}

Dar, uneori, pornind de la același IQueryable<TSmuff> de bază poate fi compusă și o interograre secundară (spre exemplu una pentru determinarea numărului total de înregistrări, în vederea paginării) și aici apare prima problemă: o excepție cu mesajul The SqlParameter is already contained by another SqlParameterCollection.

Sursa acestei probleme este că odată ce este materializată și executată prima comandă (căci materializarea unui IQueryable se face, în cele din urmă prin formarea și executarea unei instanțe de DbCommand) lista de parametri atașați acelei comenzi nu este golită, deci acei parametri știu că sunt încă de mamă și de tată.

Odată ce este re-materializat același IQueryable sau altul derivat din același IQueryable de bază, sunt refolosite exact aceleași instanțe de DbParameter care au fost transmise [explicit] la început, dar, cum acestea știu ca sunt deja folosite într-un DbCommand, vor ridica excepția de mai sus atunci când se va încerca atașarea lor la noul DbCommand ce va trebui executat.

Cam ce se-ntâmplă atunci când încă ții de fusta lui mamițu, dar vrei și bărbat în același timp: în loc să-l călărești în week-end, te duci la mami-acasă și după aia te miri de ce-ți face vânt (asta în caz că are ceva minte).

Problema nu e nemaiîntâlnită. De căutați mesajul, veți găsi tot felul de articole sau raportări chiar pe pagina proiectului de pe Github, dar și pe forumuri de consiliere emoțională. Sfatul, în general, este apelarea command.Parameters.Clear() după ce DbCommand-ul inițial a fost folosit, soluție care mi se pare că ridică totuși două probleme:

– prima este: atunci când folosești EF, când mai exact să efectuezi operațiunea;
– a doua este: atunci când folosim EF știm și eliminăm toți parametrii sigur că nu intervenim mai mult decât trebuie?

Legat de întrebarea când?, soluția este folosirea unui interceptor (IDbCommandInterceptor), prin intermediul căruia pot fi manipulate instanțele de DbCommand create și executate de EF. Sunt trei categorii de comenzi (non-query – cele care nu produc nicio valoare de ieșire, reader – cele care produc un set de înregistrări, scalar – cele care produc o singură valoare) și, pentru fiecare, două momente de timp în care se poate interveni: înainte de execuție (executing) și după execuție (executed). Deci, în total, șase metode de implementat:

public class ClearDbParamsInterceptor : IDbCommandInterceptor
{
  public void NonQueryExecuted( DbCommand command, 
	DbCommandInterceptionContext<int> interceptionContext )
  {
	return;	 
  }

  public void NonQueryExecuting( DbCommand command, 
	DbCommandInterceptionContext<int> interceptionContext )
  {
	return;	 
  }

  public void ReaderExecuted( DbCommand command, 
	DbCommandInterceptionContext<DbDataReader> interceptionContext )
  {
	return;
  }

  public void ReaderExecuting( DbCommand command, 
	DbCommandInterceptionContext<DbDataReader> interceptionContext )
  {
	return;
  }

  public void ScalarExecuted( DbCommand command, 
	DbCommandInterceptionContext<object> interceptionContext )
  {
	return;
  }

  public void ScalarExecuting( DbCommand command, 
	DbCommandInterceptionContext<object> interceptionContext )
  {
	return;
  }
}

Cât despre întrebarea cum?, am preferat să clonez parametrii pe care i-am considerat corespunzători situațiilor care fac probleme. Oricum nu pot fi clonați fără discernământ, deoarece sunt și situații în care-ar însemna să-mi tai craca de sub picioare. Spre exemplu, parametrii de ieșire, ce preiau parametrii de tip OUT din procedurile stocate; de i-aș clona pe aceștia, nu aș mai putea să obțin valoarea din procedura stocată.

În cazul meu, condițiile pentru clonare ar fi:

– operez înainte de executarea comenzii (executing);
– comanda să aibă doar parametri de tip SqlParameter și cel puțin unul de tip SqlDbType.Structured (dacă nu este îndeplinită, nici măcar nu inspectez lista de parametri mai departe);
– parametrul să nu fie de ieșire (direcția să nu fie ParameterDirection.Output).

private void ReplaceParameters( DbCommand command )
{
	if ( command.Parameters == null 
		|| command.Parameters.Count == 0 )
		return;

	List<DbParameter> clonedParameters =
		new List<DbParameter>();

	foreach ( DbParameter p in command.Parameters )
		clonedParameters.Add( p );

	if ( !ShouldClone( clonedParameters ) )
		return;
		
	for ( var i = 0; i < clonedParameters.Count; i++ )
	{
		SqlParameter oldParam = 
			( SqlParameter ) clonedParameters [ i ];
		
		if ( oldParam.Direction 
			== System.Data.ParameterDirection.Output )
		{
			clonedParameters [ i ] = oldParam;
			continue;
		}

		SqlParameter newParam = 
			new SqlParameter( oldParam.ParameterName, 
				oldParam.SqlDbType );
				
		newParam.TypeName = oldParam.TypeName;
		newParam.Direction = oldParam.Direction;
		newParam.Value = oldParam.Value;
		
		clonedParameters [ i ] = newParam;
	}

	command.Parameters.Clear();
	command.Parameters.AddRange( clonedParameters.ToArray() );
}

private bool ShouldClone( List<DbParameter> parameters )
{
	return parameters.All( p => p is SqlParameter )
		&& parameters.Any( p => ( ( SqlParameter ) p ).SqlDbType 
			== System.Data.SqlDbType.Structured );
}

Metoda ReplaceParameters() de mai sus este definită-n interceptor și apelată-n metodele NonQueryExecuting(), ReaderExecuting() și ScalarExecuting(). Apoi interceptorul astfel construit este înregistrat în constructorul contextului:

DbInterception.Add( new ClearDbParamsInterceptor() );