Using Dynamic/Ad Hoc SQL with Entity Framework

Ad hoc SQL refers to dynamically generated SQL queries, as opposed to queries stored and run via a stored procedure.

Positives

Refactoring

Code written inside C# can be written inside their own repositories and can be maintained and refactored.

This is because the SQL query can be extracted and reused.

Project Variables

Project variables and Enums can be used in the code and do not have to be hard coded in formats that are impossible to understand without cross referencing.

This makes the code a lot more readable.

Negatives

SQL Injection

SQL injection is a threat that must be considered when writing raw SQL in C#.

It is a type of computer security attack that exploits weak database functionality and executes malicious SQL code. It is often considered to be one of the most critical web application vulnerabilities and is one of the most common types of injection attacks.

Stackhawk provides some great explanations and suggestions on how to prevent SQL injection, including using allow-lists and validating any inputs passed to the query.

IntelliSense

IntelliSense can be challenging (not impossible) to use outside of SQL Server Management Studio without installing unsupported third party libraries like QueryFirst.

How to Achieve

How to Execute the Dynamic SQL

The execution of raw SQL can be accomplished in Entity Framework with multiple approaches.

For queries with no return data such as inserts, updates or deletes, SQL logic can be written inside the ExecuteSqlCommand. This query will return the number of rows effected.

int numberOfRowsEffected = context.Database.ExecuteSqlCommand("select...");

Alternatively, where there is data being returned, the logic can be written inside an SqlQuery method (RawSql for Core) with a constructor specified as the return type. For example

List<string> listData = context.Database.SqlQuery<string>("select...").ToList();

Or if the return data will match the structure of an existing table entity, the Database instance can be replaced with the tables DbSet.

List<Records> listData = context.Records.SqlQuery("select...").ToList();

How to Store SQL Queries inside C#

The SQL logic can be hard coded inside the query method parameter, stored in string format externally, or stored in .SQL format.

Storing Queries in .SQL Format

The SQL file can be stored inside the project in its native format and converted to a string at runtime.

This also means that the SQL can be opened and edited from within SQL Server Management Studio. This is especially helpful because SQL is not supported in many IDE’s and therefore IntelliSense cannot be used easily.

string sqlQuery = File.ReadAllText(@"path/subpath/sqlQuery.sql");

Where special characters such as £ are being returned within the SQL, the encoding type must be specified.

This is because the default encoding format for the read text method is ASCII, which only supports limited characters. Therefore, the ASCII extension encoding scheme of Latin1 (iso-8859-1) needs to be defined

...ReadAllText(@"filePath", Encoding.GetEncoding("iso-8859-1"));

Storing Queries in String Format

Alternatively, when stored externally, one can either store the SQL in string verbatim literal format

string sqlQuery = @"
    SELECT * FROM ...
    WHERE ...
"

Or via a slightly more awkward, string query builder format

string sqlQuery = 
"SELECT * FROM ..."+
"WHERE ...";

The personal preference is to use string verbatim literal format because excessive quotes and catenations can be eliminated. In addition, the logic can be reused to and from SQL Server Management Studio with ease.

How to Pass Parameters with Dynamic SQL and Entity Framework

Parameters can be passed by escaping the SQL statement

List<Records> listData = context.Records.SqlQuery("select... " + param + " ...").ToList();

However, this requires you to implement security measures to prepare for threats such as SQL injection.

A better approach with regards to security, is to prefix parameters with an @ symbol within the SQL code and use instances of the SQL Parameter class to replace them with the real values.

This is safer because when using this approach, the database knows exactly what code to expect and therefore prohibits the parameters from being executable SQL logic. More can be read on this topic here.

List<Records> listData = context.Records.SqlQuery(
    "select...",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    ).ToList();

Using Stored Procedures with Entity Framework

A stored procedure is an efficient way to save repetitive code and execute it from a single command in SQL Server.

Stored procedures are pre-compiled so that they run as efficiently as possible.

They also provide security benefits since they don’t expose your database structure to every user at the application level.

Positives

Performance

When writing and executing the query as a stored procedure, the output is often faster when compared to dynamic SQL. This is due to the execution plan being pre-generated and the task being cached in server memory.

SQL Injection

Unlike dynamic/raw SQL, stored procedures are not vulnerable to SQL injection. This is because the server knows exactly what query to expect. Therefore, any parameters that are passed into the query will not be executable because they will not be part of the pre-generated plan.

IntelliSense

Writing the SQL as a stored procedure means that the logic can be edited inside SQL Sever Management Studio, thus allowing the developer to reap the benefits of IntelliSense.

Separation of Concerns

Stored procedures allow the business/application and database logic to be separated. This means that if there is a bug with the database logic, there is only one place to look.

Negatives

Less Versatile with Change

When there are changes made to the stored procedure, the performance benefit of caching is invalidated because, even the smallest changes such as white space or letter casing, can prevent the plan from being reused.

Maintainability

If the logic is duplicated across multiple queries, it will need to be repeated. This failure to adhere to the DRY (don’t repeat yourself) principle can result in hard to maintain code.

Additionally, when using many stored procedures in a project, with the inability to neatly organise/structure the procedures, the database layer can quickly become hard to understand and maintain.

How to Achieve

How to Execute the Stored Procedure

As like when executing dynamic SQL, a stored procedure with return data can be executed using Entity Framework,

The following code (where SPName is the stored procedure name) executes the SQL query and maps it to a list of C# models. This Records object type can be interchanged dependent on the return data.

List<Records> listData = context.Database.SqlQuery<Records>("SPName").ToList();

Again, like dynamic SQL, a stored procedure with no return data can be executing using the ExecuteSQLommand method with the number of rows effected returned.

int numberOfRowsEffected = context.Database.ExecuteSqlCommand("SPName");

How to Pass Parameters to the Stored Procedure

Passing parameters is very similar to what has been previously explained, with the only difference being that the parameter names need to be explicitly defined alongside the stored procedure name.

List<Records> listData = db.Records.SqlQuery("SPName @Param1 @Param2",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    ).ToList();

Conclusion

As explained, multiple approaches to combine SQL with C# and Entity Framework exist, but the appropriate use is dependent on the scenario. Factors could include the complexity, likelihood of change, or return data format of the SQL query.

In this article, we have discussed how SQL can be used with C# and Entity Framework.

However, although Entity Framework and LINQ can make life easier for a developer, alternatives are available such as the ADO.NET SQLClient, OleDb or Odbc Libraries.

If you have any other ideas or suggestions on using SQL with C#, please leave a comment below.

Icons made by Smashicons from www.flaticon.com