Click here to Skip to main content
15,125,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How can i execute raw query in my API using entity frame work data model. I was able to retrieve data through Linq but now i want some specific data because of which i am executing a raw query.
When i run the below code i get an exception
"The operation cannot be completed because the DbContext has been disposed."
I do understand the exception n when i google it, its telling me to check the connection, may be there is some bracket misplaced, but i don't know where.
Also if this is the correct way to execute a raw query in c# MVC using entity framework, m i missing something?

What I have tried:

public HttpResponseMessage Get(int EmployeeId)
{
  var masterInfo = entities.Empmasters.where(e => e.EmployeeId == 
                                             EmployeeId).FirstOrDefault();

  var emppersonal = entities.EmpMaster.Where(p => p.EmployeeID == 
                                             masterInfo.EmployeeID).FirstOrDefault();

  int value = empersonal.Gender;
  var Gender = entities.LookUps.SqlQuery(" SELECT LookUpName FROM LookUp WHERE 
               LookUpType = 'GENDER' AND LookUpValue = @value ", new sqlParameter(" 
               @value" , value));


  if (masterInfo == null)
  {
     return Request.CreateErrorResponse(HttpStatusCode.NotFound, "No info");
  }
  else
  {
    return Request.CreateResponse(HttpStatusCode.OK, new {masterInfo, 
                                                          empPersonal
                                                          Gender = gender});
  }
Posted
Comments
F-ES Sitecore 2-Aug-18 4:30am
   
The error message is relatively clear, the problem is that you are using a dbcontext that has had it's dispose method called on it so if "entities" is your dbcontext then look at your other code to see if it could have been disposed between when it was created and when your function runs. In general it is a bad idea to have this kind of code anyway, if your HttpResponseMessage needs a db context it should create it itself, your code has assumptions that are causing it to act unexpectedly.
Mike V Baker 2-Aug-18 9:35am
   
I don't understand why the LookUps is being treated differently than the Empmasters or Empmaster.
int value = empersonal.Gender;
var temp = enitites.LookUps.Where(p => p.LookUpType=='GENDER' && p.LookUpValue==value);
var Gender = temp.LookupName;
Tausif Khan 3-Aug-18 8:37am
   
Yeah we could do this this way, which we did. But i wanted to get it through a raw query. There will we scenarios were i'll be executing raw query later in project. Please if you can help me in executing the same by a raw query? It will be of a great help.
Mike V Baker 3-Aug-18 13:20pm
   
Raw queries are open to SQL injection attacks so I try to avoid them like the plague. I usually create either a view or a stored procedure and import that into my EF data model.
In the past (long past) I used a .NET data access layer that didn't use EF and it had support raw queries. Let me think... that was 2008... I used articles by David C. Veeneman I believe.
public static DataSet GetDataSet(string sqlQuery)
{
DataSet dataSet = new DataSet();
SqlConnection conn = GetConnection();
SqlCommand cmd;
if (conn != null)
{
using (conn)
{
cmd = conn.CreateCommand();
cmd.CommandText = sqlQuery;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataSet);
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
// return dataset
return dataSet;
}
I don't know if you want to bring in another library. Could be your application already uses SqlDataAdapter so another lib reference might not be needed.
Richard Deeming 3-Aug-18 14:25pm
   
Except they're not open to SQLi if you use parameters, as the OP has done. :)
Mike V Baker 3-Aug-18 15:10pm
   
Oops. You are correct. Sorry, I was thinking of a different conversation.
Richard Deeming 3-Aug-18 14:28pm
   
It sounds like it could be lazy-loading - something is trying to load a navigation property from one of your entities after the context has been disposed.
Mike V Baker 3-Aug-18 15:22pm
   
@OP - Are you saying that the first two calls to the database work but the third one that uses the parameterized query doesn't work? If that's the case then I don't know why it would complain about the context being disposed. If it's failing on all three because you're running this outside the normal process then using a locally created dbcontext will be the answer.
I do have a question about the last part of the query - AND LookUpValue = @value - what type is LookUpValue, text? Perhaps it should be AND LookUpValue = '@value' ??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900