I'm looking for a way to convert Pic.PathName() and GET_FILESTREAM_TRANSACTION_CONTEXT() to provide a link for downloading in ASP.NET Core.
I have seen SqlFileStream in .NET Framework, but it does not work in .NET Core.
I don't think there's a reasonable way to get file handle access to filestream blobs from .NET core. Techincally on Windows you could get the file handle through P/Invoke and the ODBC driver, but you probably would never want to do that.
Because you can very easily get streaming access to the filestream blob through SQL Server. There's a slight performance advantage to using the handle-based access, but probably not enough to matter. If you really need that, you're probably better off using FileTables, and just accessing the file using a UNC path.
Anyway, filestream blobs are accessable as varbinary(max) columns on the table, and .NET SqlClient supports streaming blobs. See SqlClient Streaming Support for info and examples. If it's a large file you'll want to stream the result directly to the client.