EntityException occurred in EntityFramework.SqlServer.dll

Question

My Controller code

   ParaEntities db = new ParaEntities();
    public List<Client> GetAllClients()
    {
        return db.Client.ToList();
    }

Please click this link to see the error message

It is weird that when I am first time to click the button to get all client information then it responses 500. In the second time, I click the button to get all client, which is success.

its seems a connection string problem.have you retrieved any other records using this context? — umer, Jun 08 '16 at 06:17
Yes, I am using this context many times. As I said, it does not work at first time. After first time it works successfully. I think it is some problems in EF issue, such as **Lazy Loading** or connection string problem like what you said — Gaoxin Huang, Jun 08 '16 at 22:02

score 1 · Answer 1 · answered Jun 08 '16 at 05:32

1

You should assign variable and display the data in View. Please change the syntax as i write below.

ParaEntities db = new ParaEntities();
public List<Client> GetAllClients()
{
    var getData= db.Client.ToList();
       if(getData==null)
           {
               return null;
            }
    return getData;
}

answered Jun 08 '16 at 05:32

Prerak Acharya

124
6

The code is using in WebAPI, so not need to return View. And I have tried your code. some issue occurred – Gaoxin Huang Jun 08 '16 at 05:40
But the thing is if Null data return from your entity then it cause an error. so i write to store data in variable. – Prerak Acharya Jun 08 '16 at 09:19
Yes. I have tried your code. it does not work. As I said, it does not work at first time. After first time it works successfully. I think it is some problems in EF issue. – Gaoxin Huang Jun 08 '16 at 22:04

score 0 · Answer 2 · answered Jun 08 '16 at 05:53

0

Please try this

public List<Client> GetAllClients() { ParaEntities db = new ParaEntities(); return db.Client.ToList(); }

answered Jun 08 '16 at 05:53

arunraj770

767
1
10
29

Yes probably its an issue in entity framework. I think only after first button click those dbcontext or something like initialization is happening, thats why on second click that values from db are loaded. please check it when the dbcontext is initalized and where – arunraj770 Jun 09 '16 at 05:44
Please put "var data = db.Client.ToList();" before return and check whether "data" is empty on first click – arunraj770 Jun 09 '16 at 05:46
I change current database to a new database server. It works successfully. Is it possible for the issue is form the stability of database server? – Gaoxin Huang Jun 10 '16 at 05:07

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

This error points to a connection problem rather then code issue. Check that the connectionstring is valid and that the user specified in the connectionstring has access to the database. If you're running the application on IIS then make sure that the applicationpool user has access to the database. Here is another SO issue were they solved this error.

If you want to store the db context as a local variable in your controller class then I suggest you to instantiate it inside of the controllers constructor. Then you make sure that every time a instance of the controller is created then a new db context is created as well. Lets say your controller namned ClientController

private ParaEntities db;
public ClientController()
{
    this.db = new ParaEntities();
}

public List<Client> GetAllClients()
{
   return db.Client.ToList();
}

Another approach is to wrap your db context in a using statment inside of your method. In that case you make sure that the method is using a fresh context when being called upon and that the context is being disposed when the operation is completed.

public List<Client> GetAllClients()
{
    using(ParaEntities db = new ParaEntities())
    {
       return db.Client.ToList();
    }
}

PS: both examples violates the dependency inversion principle (hard coupling to the db context) but thats for another day

edited May 23 '17 at 12:15

Community

1
1

answered Jun 08 '16 at 06:10

Marcus Höglund

16,172
11
47
69

Hi Marcus, the case is for WebAPI, so I can not dispose db context in this case. However, I have tried your code. It caused another issue. BTW: As I said, it does not work at first time. After first time it works successfully. – Gaoxin Huang Jun 08 '16 at 22:11
@GaoxinHuang by the example you have showed, you can dispose it. What error message did you get when you stored it in the method? Either way I've updated the answer with a new example on how to create your local db context in the controller. – Marcus Höglund Jun 09 '16 at 06:17
I change current database to a new database server. It works successfully. Is it possible for the issue is form the stability of database server? – Gaoxin Huang Jun 10 '16 at 05:08
Ok. The initial error message indicates that there was an issue with the connection to the database. It could be, as you point out, the database server but as it works for you on the second call it hard to tell. Have you tryed to instantiate the db context in the controller constructor? – Marcus Höglund Jun 10 '16 at 05:15
It is so weird that I try to connect with database by SSMS. same issue happened, when I firstly connect it and it is failure. Then, I tried more time to connect with database. which it is successfully. – Gaoxin Huang Jun 10 '16 at 08:41
Could you please provide your connectionstring? I have an idea.. don't forget to rename username and password etc – Marcus Höglund Jun 10 '16 at 09:15
Sorry, I can not provide my connectionstring. It is private in our company. If possible, can you describe your idea? – Gaoxin Huang Jun 12 '16 at 07:11
@GaoxinHuang ok, Its a long shot. but if you have "Integrated Security=True;" in your connectionstring, Try and replace it with - "persist security info=True;user id=youruser;password=yourpasword;" – Marcus Höglund Jun 13 '16 at 06:58
I am using **persist security info=True**. Could you please tell me any differences between with Integrated Security and persist security info? – Gaoxin Huang Jun 13 '16 at 21:42
In addition, which one is normal using in enterprise projects? – Gaoxin Huang Jun 13 '16 at 21:44
You can read about it here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb97cafd-d3aa-450a-ad33-f8e450c8a7b3/persist-security-info?forum=sqldataaccess. The default for production environments should be persist security info=False – Marcus Höglund Jun 14 '16 at 05:22
Here is a good explaination of the difference http://stackoverflow.com/questions/2009976/differance-between-persist-security-info-and-integrated-security – Marcus Höglund Jun 14 '16 at 05:23

EntityException occurred in EntityFramework.SqlServer.dll

3 Answers3