Common exceptions and fixes in Entity Framework and SQL Server

When starting to work with Entity Framework and SQL Server, you often run into the same errors. Sometimes these errors are caused by missing permissions for the database connection, and sometimes it's caused the way that the database is accessed. In this article, we will go through some of the most common errors and some of the possible ways these errors could be fixed.

Prerequisites for debugging

To debug the following errors and find the root course of each problem, there is a set of required tools we need to install first. The first one is, of course, a proper logging framework so that the full error, including any inner exceptions, is revealed. We have an example of how to log to elmah.io from Entity Framework. The next is Microsoft SQL Server Management Studio, which can be used to inspect the setup of the SQL Server and the permissions of the users of the databases. The most common mistake is a malformed connection string. The correct format is as the following: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

System.Data.SqlClient.SqlException: Login failed for user 'myUsername'

At first glance, this seems like it would cover a quite simple error: That the user credentials passed were not valid. This would seem like it could only be a problem with the connection string like a misspelling of the username or password, and that's one of the possible causes of the problem. It could also be a problem in the SQL Server like that your user does not have permission to access the database or that the server does not allow SQL Server authentication. The local version of SQL Server has the Server authentication set to Windows Authentication mode by default, which does not enable the possibility to connect without Integrated Security.

The measures one can take to fix the problem are the following:

1. Check that the User Id and Password in the connection string are correct.
2. Ensure that Integrated Security is set to False in the connection string.
3. Check that the user is present in Security>Logins in the SQL Server connection in Microsoft SQL Server Management Studio.
4. Try resetting the password of the user.
5. Ensure that SQL Server authentication is enabled for the server in Server Properties>Security>Server authentication and then select SQL Server and Windows Authentication mode.

System.Data.SqlClient.SqlException: Cannot open database "myDB" requested by the login. The login failed.

The error states that there could not be an established connection to a specific database on the server. This could mean that the specified database in the connection string does not exist or that there was a typo in the connection string.

Possible solutions to the problem could be:

1. Check that there are no typos in the database field in the connection string.
2. If your project is code-first, ensure that you've called myContext.Database.EnsureCreated() before using your context.
3. If your project is database-first, check the server if the database is present on the server using Microsoft SQL Server Management Studio.

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

This error states that it was not possible to connect to the specified server. The error often comes together with a long wait time because it wants to make sure that it does not just have a slow connection. A typo could cause the error in the connection string or that the server is configured wrong.

These are some of the ways to troubleshoot the problem:

1. Check that the server field in the connection string is written correctly.
2. Ensure that remote connection is allowed in Microsoft SQL Server Management Studio in Server Properties>Connection>Remote server connections.
3. When installing Microsoft SQL Server Management Studio, you also get a program called SQL Server Configuration Manager. This can only be used on the computer that the server is running. In SQL Network Configuration>Protocols for SERVERNAME TCP/IP needs to be enabled for it to be accessed.

Microsoft.EntityFrameworkCore.DbUpdateException

This error covers a lot of different errors. What they all have in common is that Entity Framework failed to save the changes to the database. It is essential to inspect the inner exceptions. It is often just a wrapper around all the System.Data.SqlClient.SqlException's that occur when the connection is established.
Since the ways to fix these errors are unique to each case, we will list some common causes and how to avoid and fix these errors in general terms.

There have been changes to the models that Entity Framework uses in your project
Entity Framework creates all the tables in a database by itself, but when the models in a project start to change, there is a clash between what Entity Framework expects and what's actually on the SQL Server. A way to solve this is by using the Migration feature from Entity Framework when you make changes to your models.

The same SQL Server is used for many projects
When a lot of projects use the same database by accident or intentionally, there can be clashes in the naming of tables. This would mean that one project's Entity Framework connection would think that its table was made, while the scheme is the one of another project. This can be avoided by using unique database names for different projects.

Duplicate of entries with the same primary key
If multiple entries with the same primary key are inserted into a table, then there is a conflict in the database. This can sometimes be solved by doing context.SaveChanges() only once per update and sometimes be avoided by explicitly making foreign keys in one-to-many relations instead of letting Entity Framework handle it.

elmah.io: Error logging and Uptime Monitoring for your web apps

This blog post is brought to you by elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify you when something is wrong or dig through hundreds of megabytes of log files spread across servers. With elmah.io, we store all of your log messages, notify you through popular channels like email, Slack, and Microsoft Teams, and help you fix errors fast.

See how we can help you monitor your website for crashes Monitor your website