How can I properly implement a WebAPI web server with a sqlite database?

Question

So, I've been playing around with EF and Sqlite. I've managed to in my dev environment get them successfully able to read and write "Hello world" from and to a database. I'm using as I said Entity framework and the sqlite entity framework provider.

I'm reading that EF needs to be run in a single threaded environment. That entirely confuses me because to my understanding, web apps service multiple requests in parallel. How can a utility that needs to be single threaded possibly hope to be used in a web server?

If it matters, here is the console app I'm using to play around with the connection -- It also represents my current sum experience with EF

class Program
{
    static void Main(string[] args)
    {
        using (var context = new SessionContext("SessionTables.sqlite"))
        {
            var session = new Session()
            {
                SessionID = Guid.NewGuid().ToString("B"),
                Domain = "NA",
                Username = "Sidney",
                Start = DateTime.UtcNow,
            };
            context.Sessions.Add(session);

            var action = new UserAction()
            {
                ActionDescription = "Tested Entity Framework",
                OccurredAt = DateTime.UtcNow,
            };
            session.Actions = new List<UserAction>();
            session.Actions.Add(action);
            context.SaveChanges();
        }
    }
}

public class Session
{
    public string SessionID { get; set; }
    public string Domain { get; set; }
    public string Username { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public virtual ICollection<UserAction> Actions { get; set; }
}

public class UserAction
{
    public int UserActionID { get; set; }
    public string ActionDescription { get; set; }
    public DateTime OccurredAt { get; set; }
    public Guid SessionID { get; set; }
    public virtual Session Session { get; set; }
}

class SessionContext : DbContext
{
    public SessionContext(string filename)
        : base(new SQLiteConnection()
        {
            ConnectionString = new SQLiteConnectionStringBuilder()
            {
                DataSource = filename, ForeignKeys = true
            }
        .ConnectionString
        }, true)
    { /**/ }

    public DbSet<Session> Sessions { get; set; }
    public DbSet<UserAction> Actions { get; set; }
}

Your confusing threading with instances. That being said, you will have a max # of connections, so you will need to deal with that. — Trey
– Trey, Commented Mar 7, 2017 at 19:30
@Trey Do you mean to say I can have multiple instances of the DBContext object, as long as I don't interact with one instance in a cross threaded manner? — Sidney
– Sidney, Commented Mar 7, 2017 at 19:33
Cool name btw, mine too :-) anyhow read the answer below. He nailed it. — Trey
– Trey, Commented Mar 7, 2017 at 19:45
@Sidney, yes that's correct, just the general rule for any non-thread-safe object. That means: use a context per request (which an IoC container can help with), DON"T use a static context (people do that, you know). — Gert Arnold
– Gert Arnold, Commented Mar 7, 2017 at 19:45

NicoRiff · Accepted Answer · 2017-03-07 19:34:10Z

2

That should not be a problem. Microsoft.Data.Sqlite from NuGet contains a version of SQLite that has been compiled with SQLITE_THREADSAFE=1 (Serialized).

Despite from this, there not seems to be any problem with EF that would make one suppose that the application should be "single-threaded".

Read please this document explaining the only limitations that you would have working with SQLite. As the document says:

When using the SQLite provider, there are a number of limitations you should be aware of. Most of these are a result of limitations in the underlying SQLite database engine and are not specific to EF.

https://learn.microsoft.com/en-us/ef/core/providers/sqlite/limitations

answered Mar 7, 2017 at 19:34

NicoRiff

4,8933 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I properly implement a WebAPI web server with a sqlite database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related