Scenario C in This Microsoft Doc describes how temp tables scoped to a connection can be replaced with Memory-Optimized Tables. The scheme uses a Filter Security Policy which calls a function to determine if @@spid matches the SpidFilter column in the Memory-Optimized table.
Will this work with .NET connection pooling? I would expect @@spid will return the same number as a connection is re-used over and over again. .NET clears the session scoped temp tables by calling sp_reset_connection, but that will not clear Memory-Optimized tables, or change @@spid. Maybe sys.dm_exec_sessions's session_id could be added to make it work in a connection pooling environment?
With the help of Microsoft Support, I was able to get the necessary details about ASP.NET Connection Pooling to answer this concern. It is true that ASP.NET threads will share the same SPID, but never at the same time. A thread gets assigned a connection only after the connection is no longer being used by the previous thread. Connection Pooling does not reduce the number of connections needed, it only reduces the number of times connections need to be opened and closed.
This is good documentation on Connection Pooling, although it does not make that distinction. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling
Notice that Scenario C has special note: "Replace the CREATE TABLE #tempSessionC statements in your code with DELETE FROM dbo.soSessionC, to ensure a session is not exposed to table contents inserted by a previous session with the same session_id." – i-one
Since only one thread will be using the connection at a time, this is sufficient. If the table is not also deleted after being used, it will continue consuming memory (especially precious in Azure) until another thread happens to use a connection with the same SPID.