Using File Tables with Entity Framework as well as cascading functionality

Question

SQL Server FileTable was first introduced in 2012, yet it is not supported by Entity Framework (either .NET Core or full .NET Framework). Using SQL Server FileTable or FileStream allow faster file uploads and downloads.

I want to use FileTable with my .NET Core application where I must create a relationship of this FileTable with another simple table.

To do that, I need to have type hierarchyid in C# as well as enabling file stream support at SQL Server instance level. Entity Framework doesn't seem to provide a support for creating database with FileTable support when it creates database during updating migrations.

Answer 1

Earlier I asked the question and found no answer when I wanted to use SQL File Tables with my .Net Core application using code first approach. Well, now that I have resolved the issue, I would like to share it. I am not going with each and every detail, however, this is defined on Microsoft's website in detail.

First of all, you need to enable File Stream support at SQL Server Instance level. Right click on the MSSQLSERVER service in SQL Server Configuration Manager (my SQL Server instance name was MSSQLSERVER) and then choose properties. In the FileStream tab, enable the first two check boxes ("Enable Filestream for transact-SQL access & Enable Filestream for file I/O access"). Then restart the service.

In SQL Server Management Studio (SSMS), right click at the top node (named after your computer name mostly) and click properties. Go to the Advanced tab and change FILESTREAM Access Level as per your desire (whether transact-SQL or Full access).

Thereafter, you are supposed to create the Database by following query:

CREATE DATABASE xyz
ON PRIMARY 
(NAME = FS,
    FILENAME = 'D:\Database\xyzDB.mdf'),
FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FStream, 
    FILENAME = 'D:\Database\xyzFs')
LOG ON 
(NAME = FILESDBLog,   
    FILENAME = 'D:\Database\xyzDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'xyz')
GO

Here, D:\Database is my directory where I am storing the database files including files that will be stored in SQL File Table. xyz is name of the database suffixing DB.mdf, Fs & DBLog.ldf and prefixing N thereafter. I enabled NON_TRANSACTED_ACCESS; but you can disable if you don't want such access. Note that, directory must exist before you run this query.

Now that you have created the database, you can go ahead and run migration from your .Net Application with the same database name in your Connection String.

You will also require a SQL functions to support your operations, I created that using MigrationBuilder class:

migrationBuilder.Sql("CREATE FUNCTION dbo.HierarchyIdToString (@Id hierarchyid) RETURNS varchar(max) with schemabinding AS BEGIN RETURN CONVERT(varchar(max),CONVERT(varbinary(max),@Id,1),1); END");

AND

migrationBuilder.SQL("CREATE FUNCTION StringToHierarchyId (@Id varchar(max)) "+
"RETURNS hierarchyid WITH SCHEMABINDING AS "+
"BEGIN "+
"RETURN CONVERT(hierarchyid,CONVERT(VARBINARY(MAX),@Id,1),1) "+
"END");

Later, I will use this function and explain its role along the way.

Now, create the File Table that will store your files. You can of course create as many file tables as you want for different type of files.

migrationBuilder.Sql("CREATE TABLE DocumentStore AS FILETABLE WITH (FileTable_Directory = 'DocumentStore', FileTable_Collate_Filename = database_default);");

My File Table name is DocumentStore.

You will also require a Stored procedure to get the File Table Root Path (where it stores files) in order to access these files at file system level, in case you want to access files in NON-TRANSACTED way. Below is the code:

migrationBuilder.Sql("CREATE PROCEDURE GetFileTableRootPath @TableName VARCHAR(100), @Path VARCHAR(1000) OUTPUT AS BEGIN SET @Path = (SELECT FILETABLEROOTPATH(@TableName)) END");

Note that FILETABLEROOTPATH('TableName') is the built-in function that I used within the stored procedure. I knew how to call stored procedures in .Net, so I just wrapped the function in Stored Procedure.

I created another stored procedure to get the Path_Locator of any file stored in the file table. Path_Locator is the primary key of a File Table which I would later require to enter as a reference to this file in another table. Code is:

migrationBuilder.Sql("CREATE PROCEDURE GetFileTableRootPath @TableName VARCHAR(100), @Path VARCHAR(1000) OUTPUT AS BEGIN SET @Path = (SELECT FILETABLEROOTPATH(@TableName)) END");

I also created a table using simple .Net Model Class named Documents.cs including the normal attributes (repeated though as they are available in File Table as well) including an attribute to reference file in File Table. Since File Table has PK named Path_Locator with type of HIERARCHYID, I created the varchar(max) field in the Documents table and will store the Path_Locator in this column after converting into SQL VARCHAR from SQL HIERARCHYID data type. This table is part of my domain classes and will have relationship as a table would normally have.

Now that I have created the supporting tables, I also need to implement CASCADE DELETE functionality which I can do by using SQL Triggers. First Trigger on the File Table as:

migrationBuilder.Sql(
                "CREATE TRIGGER dbo.CascadeDelete ON DocumentStore "+
                "AFTER DELETE NOT FOR REPLICATION " +
                "AS "+
                "BEGIN "+
                "SET NOCOUNT ON "+
                "DECLARE @Id varchar(max); "+
                "DECLARE @Table Table (MyHierarchy hierarchyid); "+
                "INSERT INTO @Table SELECT deleted.path_locator from deleted; "+
                "WHILE ((SELECT COUNT(*) FROM @Table) > 0) "+
                "BEGIN "+
                "select @Id = dbo.HierarchyIdToString((SELECT TOP 1 * from @Table)); "+
                "DELETE FROM Documents WHERE HierarchyInString = @Id; "+
                "DELETE FROM @Table where MyHierarchy = dbo.StringToHierarchyId(@Id); "+
                "END END"
            );

And the second trigger would work with the table using migrations named as Documents to reflect the File Deletes in File Table (synchronizing the Database):

migrationBuilder.Sql(
                "CREATE TRIGGER dbo.CascadeDeleteDocuments ON Documents AFTER DELETE NOT FOR REPLICATION AS BEGIN SET NOCOUNT ON DECLARE @Id hierarchyid; DECLARE @Table Table (MyHierarchyInString varchar(max)); INSERT INTO @Table SELECT deleted.HierarchyInString from deleted; WHILE ((SELECT COUNT(*) FROM @Table) > 0) BEGIN select @Id = dbo.StringToHierarchyId((SELECT TOP 1 * from @Table)); DELETE FROM DocumentStore WHERE path_locator = @Id; DELETE FROM @Table where MyHierarchyInString = dbo.HierarchyIdToString(@Id); END END");

To get these triggers work with, I needed to convert the HIERARCHYID with VARCHAR(MAX) and vice versa, for which I used SQL Scalar functions to convert back and forth.

Now, to insert files, I store into windows file system at location retrieved from GETPATHLOCATOR stored procedure and the file is stored automatically to my File Table. Simultaneously, I also add a record to my other table created from C# model class i.e. Documents maintaining both tables.

In future, I would attempt to Create Database with File Stream support, enable File Stream support at SQL Server Instance Level using some code from within the application to avoid this out of the code process.