FromSql and SqlParameter issue when calling by 2 ways the query

[mailivore]

Hi,
Since the SQL LIKE is not already available, with linq queries,I tried to use the FromSql function but it leads to some problems when I want to paginate my results.

Steps to reproduce

var emprunteurs = contextDoti.Emprunteur.FromSql(@"select distinct Emprunteur.*
                                                    from credit.Emprunteur
                                                        left join credit.Pret on Pret.EmprunteurId = Emprunteur.EmprunteurId
                                                    where (NumeroCompte is NULL and @NumeroCompte = '' OR NumeroCompte like '%' + @NumeroCompte + '%')
                                                            and (NumeroSiren is NULL and @NumeroSiren = '' OR NumeroSiren like '%' + @NumeroSiren + '%')
                                                            and (DesignationSociale is NULL and @Designation = '' or DesignationSociale like '%' + @Designation + '%')",
                                                            new SqlParameter("@NumeroCompte", System.Data.SqlDbType.NVarChar) { Value = numeroCompte ?? "" },
                                                            new SqlParameter("@NumeroSiren", System.Data.SqlDbType.NVarChar) { Value = numeroRcs ?? "" },
                                                            new SqlParameter("@Designation", System.Data.SqlDbType.NVarChar) { Value = designation ?? "" });

return new
            {
                Resultats = emprunteurs.Skip((page - 1) * nombreAffiche).Take(nombreAffiche).ToArray(),
                Total = emprunteurs.Count()
            };

The issue

It seems that the query is executed two times with the same parameters which leads to the following error.

Exception message: The SqlParameter element is already contained in another SqlParameterCollection (originally in french: L'élément SqlParameter est déjà contenu dans un autre SqlParameterCollection).
Stack trace:    à System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value)
   à System.Data.SqlClient.SqlParameterCollection.Add(Object value)
   à Microsoft.EntityFrameworkCore.Storage.Internal.DynamicRelationalParameter.AddDbParameter(DbCommand command, Object value)
   à Microsoft.EntityFrameworkCore.Storage.Internal.CompositeRelationalParameter.AddDbParameter(DbCommand command, Object value)
   à Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   à Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   à Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   à Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
   à Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable`1 valueBuffers)
   à lambda_method(Closure , QueryContext )
   à Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)
   à Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   à Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   à GestionCredit.Controllers.EmprunteurController.Get(String numeroCompte, String numeroPersonne, String numeroRcs, String designation, Int32 page, Int32 nombreAffiche) dans C:\Dev\Gestion Credit\GestionCredit\src\GestionCredit\Controllers\EmprunteurController.cs:ligne 75
   à lambda_method(Closure , Object , Object[] )
   à Microsoft.AspNetCore.Mvc.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   à Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()

Things tried

I tried to use linq to entity with the contains() method instead of SQL LIKE and it is working but it has worse performance and not the wanted behaviour.
This makes me think that the problem is related to the FromSql function.
Here is the code.

 var emprunteurs3 = contextDoti.Emprunteur.Where(x => ((numeroRcs??"") == "" || x.NumeroSiren.Contains(numeroRcs)) &&
                                                                ((numeroPersonne??"") == "" || x.NumeroPersonne.ToString().Contains(numeroPersonne)) &&
                                                                ((numeroCompte??"") == "" || x.Prets.Any(y => y.NumeroCompte.ToString().Contains(numeroCompte))));

return new
            {
                Resultats = emprunteurs.Skip((page - 1) * nombreAffiche).Take(nombreAffiche).ToArray(),
                Total = emprunteurs.Count()
            };

Further technical details

EF Core version: 1.0.0
Operating system: Win 7 X64
Visual Studio version: VS2015 update 3

[igoventura]

You tried to use Linq to SQL?

var emprunteurs3 = from emp in contextDoti.Emprunteur
                   where ((numeroRcs??"") == "" || emp.NumeroSiren.Contains(numeroRcs)) &&
                   ((numeroPersonne??"") == "" || emp.NumeroPersonne.ToString().Contains(numeroPersonne)) &&
                   ((numeroCompte??"") == "" || emp.Prets.Any(y => y.NumeroCompte.ToString().Contains(numeroCompte))))
                   select new Emprunteur { property1 = emp.property1, property2 = emp.property2 };

I aways use this and works fine.

Otherwise you can use some SqlCommand like this:

using(var contextDoti = new YourContextClass())
{
    var conn = contextDoti.Database.GetDbConnection();
    conn.Open();

    var command = (SqlCommand)conn.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "Your query goes here";
    command.Parameters.Add(new SqlParameter() { DbType = DbType.TheTypeOfTheParameter, ParameterName = "@ParameterName", Value = valueOfParameter });
    // You can use the sintax above for each parameter you have

    SqlDataAdapter da = new SqlDataAdapter();
    DataSet ds = new DataSet();
    da.SelectCommand = command;
    da.Fill(ds);
    if(conn.State == ConnectionState.Open)
        conn.Close();
}

This will return a DataSet with the data.

And to transform the DataSet on a Entity you can use the code bellow:

    List<Emprunteur> items = ds.Tables[0].AsEnumerable().Select(row => 
    new Emprunteur
        {
            PropertyName = row.Field<int>("PropertyName"),
            PropertyName2 = row.Field<string>("PropertyName2")
            // You can use the sintax above for each property
        }).ToList();

Remeber: That is only a way to do what you want. We will apply the correction on the EFCore.

[mailivore]

I think that you didn't understand my problem.
Yes with a pure linq query it would work but it doesn't allow me to use the SQL LIKE.

The problem is that I query two times the "emprunteurs" BdSet which leads to a duplicate parameters error.

return new
            {
                Resultats = emprunteurs.Skip((page - 1) * nombreAffiche).Take(nombreAffiche).ToArray(), //First call
                Total = emprunteurs.Count() //Second call
            };

I have the solution to declare an "emprunteurs2" the same way that the "emprunteurs" was and call the count() on the "emprunteur2".
It would work but it enforces me to duplicate my code.

Anyway, I think that it should be possible to call two times the FromSql function and the parameters error is just an unintended bug.