In the previous post I added the EntityFramework nugets to my Web API project and created a Sqlite db using the EntityFramework tools. Here I’m going to add in a Get and Post to my API and interface with the database.

First I need to add the database context to my services in the API so I have it readily available. There are two approaches to this. One is to add the db context to the services and the other is to add a db context factory to the services. You shouldn’t do both but I’ve found the latter is much more useful than the former.

The problem with adding a db context to the services is that only one thread can access the db context at a time. This causes a lot of headaches as you will likely get multiple exceptions as the API tries to access the db context as different calls are made to the API from multiple clients. To this end, in my Program.cs for the API, when adding services, I’ll add the following:

builder.Services.AddDbContextFactory<MessageDbContext>();

If we consider the use case of a chat app, I’ll need a Post for the clients to send messages to and a Get for the clients to get any messages that have come in. We’re taking the Minimal API approach here so we won’t be using Controllers or anything fancy, just simple, simple, simple.

After the app has been built with builder.Build() and any other configuration has been applied to the app, I’ll add a Post with app.MapPost

app.MapPost("/sendMessage", async (IDbContextFactory<MessageDbContext> dbFactory, IHubContext<ChatterChatHub> hub, WriteToChatMessage chatMessage) =>
{
    var db = dbFactory.CreateDbContext();
    try
    {
        var message = new Message
        {
            Username = chatMessage.alias,
            JsonMessage = JsonSerializer.Serialize(chatMessage),
        };
        db.Add(message);
        await db.SaveChangesAsync();
        chatMessage = chatMessage with { messageId = message.Id };

        //For SignalR send to the hub
        await hub.Clients.All.SendAsync("ReceiveMessage", message);

        return chatMessage;
    }
    finally
    {
        await db.DisposeAsync();
    }
});

For trying to be simple, that’s a lot. Let’s break it down. First, the “/sendMessage” is the route that clients will call for posting messages. It just defines the end point. Next is the lambda for what to execute. The signature for the lambda relies pretty heavily on dependency injection. Ignoring the hub stuff related to SignalR, for now, there is the db context factory and the chat message.

The API will first try to resolve any parameters from the services. Since I added a DbContextFactory earlier in the post, it will find that and pass it in as a parameter. We’ll also add a SignalR hub in a future post to the services so that will be available. Finally, all we’re missing is the WriteToChatMessage so we should expect that the client will pass in that object on the post in the body.

In the method, I need an instance of the db and this is where the db context factory comes in handy. I’m wrapping it in a try/finally to make sure it’s disposed. As far as the data in the table, to be honest I got lazy.

I could have simply created new properties in my class and when I used code first from the EntityFramework tools it would have added all those in. But I decided to just store a json representation of the message in the database. In general, if you’re using relational database this is a bad approach.

The truth is it’s easy to get bad data in there and makes it easy to break normalization and normal forms. This isn’t even how I would do it if using a no-sql db like MongoDB or RavenDB. But for the purposes of learning about the fundamentals of EntityFramework and Web API, it works for now.

After we’ve created an instance of the database we create a message to store. We then add it to the db and save all the changes. This will set the id on our message and we need to return it to any clients with the id value. Since WriteToChatMessage is a record we can’t change the property directly as records are immutable. I can get a copy of the record with properties set to new values, however, using the with keyword.

chatMessage = chatMessage with { messageId = message.Id };

Finally (ignore the hub stuff again), I return the message and dispose of the database context. Simple and straight-forward.

Now I’ll explain the get method for getting messages.

app.MapGet("/getMessages/{lastId}", async (IDbContextFactory<MessageDbContext> dbFactory, int lastId, [FromQuery(Name = "skip")] int skip = 0, [FromQuery(Name = "take")] int take = 0) =>
{
    if (take > 10)
        throw new ArgumentException("take may not be greater than 10");

    var db = dbFactory.CreateDbContext();
    try
    {
        var chatMessages = new List<WriteToChatMessage>();

        //if they are new then they won't know where to start so just give them the last message
        if (lastId <= 0)
        {
            var lastMessage = db.Messages.OrderBy(x => x.Id).LastOrDefault();
            if (lastMessage != null)
            {
                var chatMessage = JsonSerializer.Deserialize<WriteToChatMessage>(lastMessage.JsonMessage);
                chatMessage = chatMessage with { messageId = lastMessage.Id };
                chatMessages.Add(chatMessage);
                return chatMessages;
            }
        }

        if (take <= 0)
            take = 10;

        var messages = db.Messages.Where(x => x.Id > lastId).OrderBy(x => x.Id).Skip(skip).Take(take);
        foreach (var message in messages)
        {
            var chatMessage = JsonSerializer.Deserialize<WriteToChatMessage>(message.JsonMessage);
            chatMessage = chatMessage with { messageId = message.Id };
            chatMessages.Add(chatMessage);
        }

        return chatMessages;
    }
    finally
    {
        await db.DisposeAsync();
    }
});

As with before the route to the method is defined in the first parameter, “/getMessages/{lastId}”. So I expect that lastId will be on the url that is called on my endpoint.

Next is the lambda signature. The db context factor will be injected via DI and since we defined {lastId} in the route, the back-end knows to take that value from the path. What gets interesting is the skip/take parameters. In situations like this it’s easy for an API to get overwhelmed with a client calls for a ton of data. We need to be able for the client to manage calling with a skip/take. Minimal API gets a bit fussy if you put more than one parameter on the get url so we’ll take the values for the skip/take as query parameters.

In the client, the url to call our get will look like:

var url = $"{baseUrl}/getMessages/{lastMessageId}?skip={skip}&take={take}";

Query parameters are parameters after the ?. They are defined explicitly in the lambda signature with the FromQuery attribute. I’ve also defined defaults for these. It’s a good idea to expect that not all clients will define query parameters. In the very first line of the lambda I want to make sure the request is never for more than 10 chats at a time.

After that I create the db context. If last id is <= 0 then the client is likely a newly connected client since it doesn’t know where to start. To give them a baseline to start with I just return the last message added to the database. Then in future requests, they’ll know where they started from and will have a last id.

When getting the messages from the database I take advantage of the Skip/Take methods EntityFramework has provided. But I need take to be some value so I set it to 10 if it’s <= 0.

Then I get the messages from the database where the id is > the last id passed, order by the id and do the skip/take. In practice, when doing this I order by id as database documentation generally says there is no promise that the objects returned will be in id order. Since I’m doing a skip/take I want to make sure that it will work okay.

The rest is building the messages like in the Post, returning the list and disposing of the database.

If you’d like, you can define the API as the start up project and post and get data via the Swagger page.

This is a nice way to test the code to make sure everything works as expected.

And that’s it. I’ve added a get and a post to my API to allow for clients to send messages and receive messages. Up next I’ll add a new chat handler to the project that will call the post to send the messages and call the get to update messages.

Thanks,
Brian