Entity Framework 4.1 Raw SQL

Question

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.

tcode · Accepted Answer · 2012-09-13 13:18:17Z

8

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.

answered Sep 13, 2012 at 13:18

tcode

5,10519 gold badges70 silver badges129 bronze badges

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

Comments

endyourif · Accepted Answer · 2012-09-13 11:24:02Z

0

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.

answered Sep 13, 2012 at 11:24

endyourif

2,20220 silver badges33 bronze badges

1 Comment

tcode Over a year ago

Sorry, this is not the solution

Collectives™ on Stack Overflow

Entity Framework 4.1 Raw SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related