Click here to Skip to main content
15,867,686 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. :)

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