CODEWITHSUNDEEP
sqlentity-framework-4.1
tcode
tcode

Reputation: 5105

Entity Framework 4.1 Raw SQL

I am developing an ASP.Net MVC 3 application using Entity Framework 4.1. For a particular complex query that I need to execute I have decided to write a raw SQL query and pass it to the built in dbSet.SqlQuery method.

I have a Service method like below where I assign the SQL query to a string variable called query. As the query is passed two parameters, I have parameterized these to prevent SQL Injection. 

public IList<User> GetAvailableLocums(int shiftID, int shiftDateID)
{
           var query ="Select .... where t1 = @p0 and t2 = @p1";

           ObjectParameter _shiftID = new ObjectParameter("p0", shiftID);
           ObjectParameter _shiftDateID = new ObjectParameter("p1", shiftDateID);

           object[] parameters = new object[] { _shiftID, _shiftDateID };


           return _UoW.User.GetWithRawSql(query, parameters).ToList();
 }

I then pass the query and the parameters to a method in my repository which executes the query for me.

public IEnumerable<TEntity> GetWithRawSql(string query, params object[] parameters)
{
            return dbSet.SqlQuery(query, parameters).ToList();
}

I know the query is correct as I have tested it in SQL Server Management Studio, however, I currently get the following error when I try to run this code

No mapping exists from object type System.Data.Objects.ObjectParameter to a known managed provider native type

Does anyone have any suggestions as to how I can fix this?

Thanks for your help.

Upvotes: 1

Views: 2194

Answers (2)

tcode
tcode

Reputation: 5105

Folks

The problem was that I was using ObjectParameter to create my Parameters. I instead changed this to SqlParameter and it worked fine. See below.

Change from this

ObjectParameter _shiftID = new ObjectParameter("p0", shiftID);

To this

SqlParameter _shiftID = new SqlParameter("p0", shiftID);

And it worked. Hope this helps someone else.

Upvotes: 8

endyourif
endyourif

Reputation: 2202

From a quick Google search, it looks like your close. I think you are missing setting the return type for your SQL query:

return dbSet.SqlQuery<TEntity>(query, parameters).ToList();

This just tells Entity Framework how to map it.

Upvotes: 0

Related Questions