Entity Framework is a popular database connection choice for .NET developers. It’s fairly simple to use but, what if I told you that we could create a connection framework on top of ASP.NET that would allow us to get total control of the SQL that we write? The cherry on top is that it will take about as much code as it takes to configure Entity Framework.
In this article, I’ll show you how to connect an ASP.NET minimal API to a local SQLite database Entity Framework. We will still map tables into classes allowing us to interact with our data in C#. I’ll include every line of code you need, so let’s get started.
The data
First, we have to do is set up a database. I decided to use SQLite since I’ve never had an excuse to before. Make sure that you have SQLite installed and that you’ve connected to a database. (You could also connect to a MySQL, SQL Server or Postgres database using the following method too, but this article will focus on SQLite.)
With our database, we’ll track student information and grades. First, connect to SQLite and create a database, we’ll call it Students.db, then create a students table and a grades table.
sqlite> CREATE TABLE students (
...> id INTEGER PRIMARY KEY,
...> name TEXT,
...> school TEXT
...> );
sqlite> CREATE TABLE grades (
...> id INTEGER PRIMARY KEY,
...> scored INTEGER,
...> out_of INTEGER,
...> student_id INTEGER,
...> FOREIGN KEY (student_id) REFERENCES students(id)
...> );
Drop these two table definitions into ChatGPT and have it script you out some sample data for both of the tables. This isn’t necessary, but it will make your API more interesting to work with once we’re done. Once you’ve done that, we’re ready to get started.
The connection
Instead of using Entity Framework, we’ll remove that layer of abstraction to use ADO.NET. These are the libraries that EF uses in its implementation. Microsoft was kind enough to wrap them up in a NuGet package for us. In the root of your project, run the following command:
dotnet add package Microsoft.Data.Sqlite
With that installed, we can work on getting our application connected to the database. First, grab your connection string. Each database provider has their own format for these, but I’ll trust that you can find that and configure it on your own. Sqlite’s format is below:
"Data Source=path/to/database_file.db"
The best way to give your application access to this is through a configuration object that you supply through dependency injection. I wrote an article about how to get that set up if you need help. Or you can decide to be a lawless cowboy and hard-code it… I’m not your mother.
It’s finally time to write some code. In order to interact with our database, we need a class and a matching interface that will provide the connection to the rest of our application. We will call it, somewhat unimaginatively, DatabaseConnectionProvider.
// DatabaseConnectionProvider.cs
using Microsoft.Data.Sqlite;
namespace Students.Repository.SQL;
public class DatabaseConnectionProvider : IDatabaseConnectionProvider
{
private readonly string _connectionString;
public DatabaseConnectionProvider(IConfiguration config)
{
// Don't you hard code it, cowboy...
_connectionString = config["ConnectionString"]!;
if (string.IsNullOrEmpty(_connectionString))
{
throw new InvalidOperationException("Connection string not found.");
}
}
}
Since we’ll be injecting this provider into other classes later, we’ll create an interface and include a preview of the first method that we will create!
// IDatabaseConnectionProvider.cs
namespace Students.Abstractions;
public interface IDatabaseConnectionProvider
{
List<T> GetRecords<T>();
}
Now back in our provider class, create the database connection inside this GetRecords method.
// DatabaseConnectionProvider.cs
// after DatabaseConnectionProvider(IConfigration)
public List<T> GetRecords()
{
using (var connection = new SqliteConnection(_connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM students;";
var reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["name"].ToString())
}
connection.Close();
return new List<T>();
}
}
The SqliteConnection class is provided to us by that NuGet package that we installed earlier. This gives us the building blocks of database interaction: connection, issuing commands, and reading the results.
This isn’t our final GetRecords implementation, but it will work and you can test that (if you seeded some sample values into your database). When you run it, the method creates Sqlite database connection and issues a SELECT * command to return all the students. We get back a DataReader, also provided by the NuGet package from earlier, that we can use to read the "name" column of each row before disposing the connection.
We’re connected! Take a moment to celebrate.
The models
Now that we can get the data out of the database, we have a bit of grunt work to do in order to be able to work with it. For each of our database tables that creates a record we want to use in our code, we need to create a model in C# using a class.
In this small sample project, we just need two.
// Student.cs
public class Student
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string School { get; set; } = string.Empty;
}
// Grade.cs
public class Grade
{
public int Id { get; set; }
public int Scored { get; set; }
public int OutOf { get; set; }
public int StudentId { get; set; }
}
One of these models represents a single row in a table. Right now, they are sad and empty. Okay, maybe not sad, but definitely empty. But, now we have a problem. How do we get a DataReader to spit out these models that we just created?
Once we get into our read loop, we could access all of the columns by name and manually assign them to the model properties. That would work, but we would have to rename our method to GetStudentRecords, since attempting to query for grades would throw an exception when looking for the "name" or the "school" columns.
Well, then we could create a new GetGradeRecords, but then, of course, we would be rewriting all of the logic inside our original method except for where we construct and return a model.
Instead, we need to offload the parsing responsibility from our DatabaseConnectionProvider. Instead of making our provider responsible for reading data, we can make each model responsible for knowing how to construct itself by giving each one Parse method.
// In Student.cs
public string School { get; set; } = string.Empty;
public Student Parse(IDataReader reader)
{
Id = reader.ParseInt("id");
Name = reader.ParseString("name");
School = reader.ParseString("school");
return this;
}
}
// In Grade.cs
public int StudentId { get; set; }
public Grade Parse(IDataReader reader)
{
Id = reader.ParseInt("id");
Scored = reader.ParseInt("scored");
OutOf = reader.ParseInt("out_of");
StudentId = reader.ParseInt("student_id");
return this;
}
}
If you’re typing along, you probably noticed that ParseInt and ParseString are red. In order to clean up the syntax a bit, I added a couple static extensions methods on the DataReader class. You can add those in a new file.
using System.Data;
namespace Students.Repository.SQL;
public static class ReaderExtensions()
{
public static int ParseInt(this IDataReader reader, string columnName)
{
return reader.GetInt32(reader.GetOrdinal(columnName));
}
public static string ParseString(this IDataReader reader, string columnName)
{
return reader.GetString(reader.GetOrdinal(columnName));
}
}
Now all we have to do is let the DatabaseConnectionProvider know that our models have this parse method. We will do this with generics, but we are also going to need to define an interface to expose this method generically…
// ISqlDataParser.cs
using System.Data;
namespace Students.Abstractions;
public interface ISqlDataParser<T>
{
T Parse(IDataReader reader);
}
… and make sure that our models are inheriting it.
// Student.cs
public class Student : ISqlDataParser<Student>
// Grade.cs
public class Grade : ISqlDataParser<Grade>
Now, let’s update our GetRecords declaration to finish making our Parse methods available.
// In DatabaseConnectionProvider
// Replace public List<T> GetRecords()
public List<T> GetRecords() where T : ISqlDataParser<T>, new()
What this says, is that we can only call GetRecords<T> if the type that we insert for T implements the ISqlDataParser interface and has a parameter-less constructor.
Now that we can safely access Parse we can use it in GetRecords<T>.
// In DatabaseConnectionProvider.cs
// In GetRecords<T>
// Replace everything after: var reader = command.ExecuteReader();
var returnList = new List<T>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
returnList.Add(new T().Parse(reader));
}
}
connection.Close();
return returnList;
}
}
The query
Right now, we’re in an interesting state. The GetRecords method is able to parse theoretically infinite data types, but it never will. Why? Because we’ve hard-coded a query into the command object that we’re sending to the database.
Similar to what we did with the Parse method, we need to take away the DatabaseConnectionProvider‘s responsibility to choose the query it should run. That means that we’re going to have to move the query up into a parameter, but, we run into a problem there.
If only add a string parameter to the method, but then we don’t have access to the Command object.
So what?
The Command object is where we add SqlParameters. The only option that we would have at this point would be to use string interpolation to add the values in manually. Forcing users of our provider to build queries this way is not only poor etiquette, it is also a potential security vulnerability. Microsoft has already (hopefully), done a lot of work to secure this Command object against SQL injection attacks. No matter the sanitation that we do, adding the parameters to the correct property on the Command object is the smartest way forward.
In order to keep our provider’s methods modular, we need to abstract this into a class that could be used in conjunction with any model that we create. It will wrap up our intended query with placeholders for parameter values, and a dictionary of our parameters. We’ll call it DataCallSettings (naming is hard).
// DataCallSettings.cs
namespace Students.Models;
public class DataCallSettings
{
public string SqlCommand { get; set; }
public Dictionary<string, object> Parameters { get; set; } = new();
public DataCallSettings(string command)
{
SqlCommand = command;
}
public void AddParameter(string name, object value)
{
Parameters[name] = value;
}
}
This class is now the only parameter to GetRecords<T>. This is a significantly better developer experience than passing in positional parameters. This is a simple implementation, but if we ever decided to extend it to include support for stored procedures, retry logic, transactions or caching, we can do that without breaking our existing codebase.
Take a look at the finished method with all of the changes that we made.
// In DatabaseConnectionProvider.cs
public List<T> GetRecords<T>(DataCallSettings dcs) where T : ISqlDataParser<T>, new()
{
using (var connection = new SqliteConnection(_connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = dcs.SqlCommand;
foreach (var key in dcs.Parameters.Keys)
{
command.Parameters.AddWithValue(key, dcs.Parameters[key]);
}
var returnList = new List<T>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
returnList.Add(new T().Parse(reader));
}
}
connection.Close();
return returnList;
}
}
As a bonus, I’ll also give you two lines of code that will let us get single records using all the hard work we did earlier.
public T GetRecord<T>(DataCallSettings dcs) where T : ISqlDataParser<T>, new()
=> GetRecords<T>(dcs).FirstOrDefault() ?? new();
The command
Sometimes you will want to issue a query to the database that won’t return anything. So, our database interface isn’t quite complete. We need to build a method that allows us to send database queries without creating an empty model at the end. It will look very similar to our GetRecords method, but we will use the .ExecuteNonQuery() method instead, and we’ll call it Execute. Creative… I know.
// In DatabaseConnectionProvider.cs
// After GetRecord<T>
public int Execute(DataCallSettings dcs)
{
using (var connection = new SqliteConnection(_connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = dcs.SqlCommand;
foreach (var key in dcs.Parameters.Keys)
{
command.Parameters.AddWithValue(key, dcs.Parameters[key]);
}
var result = command.ExecuteNonQuery();
connection.Close();
return result;
}
}
This is fine, but we’re duplicating some logic between GetRecords and Execute. Let’s lift it into a fancy new BuildCommand method.
// In DatabaseConnectionProvider.cs
// After Execute()
private SqliteCommand BuildCommand(DataCallSettings dcs, SqliteConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = dcs.SqlCommand;
foreach (var key in dcs.Parameters.Keys)
{
command.Parameters.AddWithValue(key, dcs.Parameters[key]);
}
return command;
}
And refactor both methods to use it.
// In DatabaseConnectionProvider.cs
// In GetRecords<T>
connection.Open();
var command = BuildCommand(dcs, connection);
var returnList = new List<T>();
// In Execute()
connection.Open();
var command = BuildCommand(dcs, connection);
var result = command.ExecuteNonQuery();
Ah, much better. But, we’re not done yet. Let’s add one more method that we will wind up using a lot in practice. When we perform an INSERT, the database will set the identity column, so let’s make sure that we get that back out.
// In DatabaseConnectionProvider.cs
// Below Execute()
public int ExecuteWithIdentity(DataCallSettings dcs)
{
if (!dcs.SqlCommand.StartsWith("INSERT"))
{
throw new InvalidOperationException();
}
using (var connection = new SqliteConnection(_connectionString))
{
connection.Open();
dcs.SqlCommand = $"{dcs.SqlCommand} SELECT last_insert_rowid();";
var command = BuildCommand(dcs, connection);
var identity = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
return identity;
}
}
And with that… drumroll please.
We’ve done it! We’ve created, by hand, an way to interact with a database that is extensible and easy to use.
The repository
Now that we’re done creating this database interface, I want to quickly walk through how you can use it. One design pattern that I use daily is the repository design pattern. This is a fancy (and admittedly somewhat confusing) name that we can give to a file that wraps up our data transfer logic, keeping our modules less coupled.
Adding a separate layer allows us to ignore the database’s implementation details when we finally want to send this data to a front end somewhere. So, let’s create a StudentsRepository.
// StudentsRepository.cs
using Students.Abstractions;
using Students.Models;
namespace Students.Repository.SQL;
public class StudentsRepository : IStudentsRepository
{
}
And here’s the interface, complete with the methods that we’re going to create.
using Students.Models;
namespace Students.Abstractions;
public interface IStudentsRepository
{
Student GetStudent(int id);
List<Student> GetStudents();
int SaveStudent(Student student);
bool DeleteStudent(int id);
}
Then, back in our repository, we will inject our DatabaseConnectionProvider. Make sure that it’s registered in your Program.cs file first though. Here’s an article that walks through DI in ASP.NET if you need some help with that.
Finishing the implementation now is as simple as calling the appropriate provider method with a DataCallSettings instance with a SQL query. Instead of walking through step by step, I’ll just include the whole file below, so that you can see how it will look.
// in StudentsRepository.cs
public class StudentsRepository : IStudentsRepository
{
private readonly IDatabaseConnectionProvider _provider;
StudentsRepository(IDatabaseConnectionProvider provider)
{
_provider = provider;
}
public Student GetStudent(int id)
{
var dcs = new DataCallSettings("SELECT * FROM students WHERE Id = @Id;");
dcs.AddParameter("Id", id);
return _provider.GetRecord<Student>(dcs);
}
public List<Student> GetStudents()
{
var dcs = new DataCallSettings("SELECT * FROM students;");
return _provider.GetRecords<Student>(dcs);
}
public int SaveStudent(Student student)
=> student.Id == 0 // new student
? InsertStudent(student)
: UpdateStudent(student);
private int InsertStudent(Student student)
{
var dcs = new DataCallSettings("INSERT INTO students (name, school) VALUES (@Name, @School);");
AddStudentParameters(dcs, student);
return _provider.ExecuteWithIdentity(dcs);
}
private int UpdateStudent(Student student)
{
var dcs = new DataCallSettings("UPDATE students SET name = @Name, school = @School WHERE id = @Id;");
dcs.AddParameter("Id", student.Id);
AddStudentParameters(dcs, student);
return _provider.Execute(dcs) > 0
? student.Id
: 0;
}
public bool DeleteStudent(int id)
{
var dcs = new DataCallSettings("DELETE FROM students WHERE id = @Id");
dcs.AddParameter("Id", id);
return _provider.Execute(dcs) > 0;
}
private void AddStudentParameters(DataCallSettings dcs, Student student)
{
if (student.IsNew) { dcs.AddParameter("Id", student.Id); }
dcs.AddParameter("Name", student.Name);
dcs.AddParameter("School", student.School);
}
}
The conclusion
Okay, I’ll admit that this more code than you probably need if you were using Entity Framework. But… it’s not that much more. I find myself far more comfortable working in an application that has this level of transparency and flexibility than one that does mapping magic. To me, this is an easy trade-off.
This implementation isn’t clever, it’s not complicated, and it’s easy to work with, even if it takes a little bit of getting used to. Over the past two years I’ve been programming, a significant percentage of bugs I deal with are caused by data being malformed. Maybe this is skill issues, but it has made me slightly paranoid about data handling in apps that I work on.
Using this pattern, it’s being handled completely by me. If there’s a problem, I know that it was something I wrote (read: something I can fix). I created the database, I wrote the SQL queries. Since I am responsible for this system, I want to know how it’s handling my data.
This isn’t to say that ORMs are bad. I might not be very good at working with them (see my reference to skill issues above). But, if you’ve never tried to use ADO.NET, I hope you feel that you have the tools to get started.
