context database.sqlQuery<type>() with dynamic parameter list

Question

i have a problem with an sqlQuery/Exec. My Stored Procedure returns a list of entitys. But i need to append the parameterlist dynamicly.

        using (MyContext db = new MyContext ()) 
        {
            var queryParam = new Dictionary<String, String>();
            var arg = String.Empty;
            var argList = new List<ObjectParameter>();
            var sql = String.Empty;

            foreach(var line in param.Split(','))
            {    
                var obj = line.Split(':');
                if (obj.Length < 2)
                {
                    continue;
                }

                if (obj[1].Equals(String.Empty))
                {
                    continue;
                }

                // arg = arg + "@" + obj[0] + "=" + "'" + obj[1] + "' ";
                argList.Add(new ObjectParameter(obj[0], obj[1]));
            }

            object[] prm = argList.ToArray();

            // Stored procedure ausführen und ergebniss erhalten.
            var list = db.Database.SqlQuery<Dokument>("EXEC dbo.spDokumente", prm).ToList();
            return AppServerHelper.AppResponse(list);
        }

On runtime the Application returns that the object type does not match ne internal providers. I dont have a clue to solve this problem!

Sujeewa · Accepted Answer · 2014-11-03 14:51:36Z

1

Try using SqlParameter instead of ObjectParameter. Follow the sample code below:

public List<MyCustomObject> SelectLoad(DateTime parameter1, DateTime parameter2)
{          
       return Execute("MyStoredProcedure @Parameter1, @Parameter2",
           new object[]{new SqlParameter("@Parameter1", parameter1),
           new SqlParameter("Parameter2",parameter2)});
}

private List<MyCustomObject> Execute(string query, object[] parameters)
{
    var output = new List<MyCustomObject>();
    using (var dbContext = new MyEntityFrameworkDataContext())
    {
    output = dbContext.Database.SqlQuery<MyCustomObject>(query, parameters).ToList();
    }
    return output;        
}

You can create the parameters object array dynamically within the first method.

answered Nov 3, 2014 at 14:51

Sujeewa

7118 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

context database.sqlQuery<type>() with dynamic parameter list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related