Using SQL filetables with entity framework

Recently I had a problem on a project where we needed to securely store documents. It was the first time for the customer to do such a thing. Telling them this was not so hard, I convinced them to use SQL file tables to store these documents securely in an SQL server database. After validating the SQL server database version used, I let the DBA’s create the file stream storage group and file, changing the database and server properties and created the file table. So far, no issues arose and we were ready for development.

At that point we encountered a serious problem. The application development standard prevented us from using direct SQL statements, and all database access had to be done using .NET Entity Framework (EF). The result was not as expected, it looked as if EF did not import the file table definition correctly. Looking at it in more detail we found that EF didn’t recognise the HIERARCHYID column type. Looking for hours on the msdn blogs, we found several tweaks to force EF to handle the file table meta correctly into the model schema. However, we still couldn’t insert data into the table without writing SQL statement ourselves.

What did I do to solve this? In fact the statement to use only ANSI standard SQL was not that bad, so we left that part as the standard and decided to create an SQL layer between the .NET and the table that managed all the non-standard ANSI code. In other words, preventing the technical specification (non ANSI sql part) from showing to the ‘outside’ world. That solved the whole problem. For EF all that was left was to follow the standard implementation of EF. Finally, everybody was happy and in no time thousands of documents where written.

Please find the complete insight of my solution below.

Solution

1. Create the file table

   
   CREATE TABLE [dbo].[Document] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [db_FS]
   WITH
   (
   FILETABLE_DIRECTORY = N'dboDocumentTable', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS
   )
   

    

   

2. Create a view on top of it hiding the hierachyid

   
   CREATE VIEW [dbo].[vDocument]
   AS
   SELECT [stream_id]
         ,[file_stream]
         ,[name]
         ,CONVERT(VARCHAR(4000),[path_locator]) AS [path_locator]
         ,CONVERT(VARCHAR(4000),[parent_path_locator]) AS [parent_path_locator]
         ,[file_type]
         ,[cached_file_size]
         ,[creation_time]
         ,[last_write_time]
         ,[last_access_time]
         ,[is_directory]
         ,[is_offline]
         ,[is_hidden]
         ,[is_readonly]
         ,[is_archive]
         ,[is_system]
         ,[is_temporary]
     FROM [dbo].[Document]
     ;
   

   Depending on the directory structure you use, the varchar(4000) can be to small, but change it when needed to a longer field.

   

3. Create another join table to link the document to

   
   CREATE TABLE [dbo].[LinkedDocuments](
   [ID] [INT] IDENTITY(1,1) NOT NULL,
   [OtherEntityID] [INT] NOT NULL,
   [DocumentID] [VARCHAR](4000) NULL,
   [InsertDT] [DATETIME] NOT NULL DEFAULT (GETDATE()),
   [UpdateDT] [DATETIME] NULL,
   [InsertedBy] [VARCHAR](255) NOT NULL DEFAULT (SUSER_SNAME()),
   [UpdatedBy] [VARCHAR](255) NULL,
   PRIMARY KEY CLUSTERED
   (
   [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
   ) ON [PRIMARY]

4. Now create the 3 stored procedure for adding, update and removing documents on the file table. First addDocument stored procedure. This needs some parameters:
   • • relativePath: path starting from the tables root path where you want to store the new file
     • Name:name of the file with extension
     • Stream:the real data stream of the document to store
     • Path_locator: this is optional, when you know it, you can pass it here. This must be a variable representation of the path_locator (hierachryid). For the outside world remember? Hint:convert(varchar(4000), path_locator)

   The procedure will create the complete directory structure for you when it’s not present yet. Then adding the document stream to the file location, and returns the character representation of the path_locator. (this I the unique identifier of the stream)

   Ex. /45134823255421.163030830980382.1528000315/

   what is the same as hierarchyid
   0xFCA4278A369913CFE5113258908A3EF96C4D3566E0

   You write this value to the LinkedDocuments table in the documentID field.

   
   CREATE PROC [dbo].[AddDocument]
   (@relativePath varchar(max)=null
   ,@name varchar(512)=null
   ,@stream VARBINARY(MAX)=NULL
   ,@path_locator VARCHAR(4000)=null OUTPUT
   )
   AS 
   BEGIN
   SET NOCOUNT ON 
   
   DECLARE @path_Locator_for_file HIERARCHYID; SET @path_Locator_for_file=NULL;
   
   BEGIN TRY
   BEGIN TRAN adddocument
          DECLARE @directories TABLE(ID int IDENTITY(1,1), Name varchar(MAX),Parent varchar(MAX),Path_Locator HIERARCHYID, seq int);
                 
          -- test input
          IF COALESCE(@name,'') ='' 
                 THROW 50001, 'no input name given', 1; 
          
          -- test/create directory
          IF @relativePath IS NOT NULL
          BEGIN         
   
                 DECLARE @rows INT;
                 ;WITH directories(Name, Pname, seq) AS 
                 (SELECT @relativePath AS Name
                       , CASE WHEN LEFT(@relativePath,1)='\' THEN SUBSTRING(@relativePath,2,LEN(@relativePath)-1) ELSE @relativePath END AS PName
                          , 0 AS seq 
            UNION ALL 
                  SELECT CASE WHEN CHARINDEX('\',REVERSE(PName)) > 0 THEN RIGHT(PName,CHARINDEX('\',REVERSE(PName))-1) ELSE PName END AS Name
                       , CASE WHEN CHARINDEX('\',REVERSE(PName)) > 0 THEN LEFT(PName,LEN(PName)-CHARINDEX('\',REVERSE(PName))) ELSE '' END AS PName
                          , seq+1 AS seq 
            FROM directories recurs
                 WHERE PName>''
                 )
                 ,dirs AS
                 (
                 SELECT Name, PName, LAG(Name) OVER(ORDER BY seq DESC) AS Parent, ROW_NUMBER() OVER(ORDER BY seq desc) AS Seq
                 FROM directories 
                 WHERE seq > 0 AND name > ''
                 )
                 INSERT INTO @directories(Name, Parent, Path_Locator, seq)
                 SELECT Name
                       ,Parent
                          ,null
                       ,seq
                 FROM dirs
                 ;
                 SET @rows=@@ROWCOUNT;
                 
                 DECLARE @loop INT; SET @loop=1;
                 DECLARE @lname VARCHAR(MAX);
                 DECLARE @lparent VARCHAR(MAX);
                 DECLARE @lPath_Locator HIERARCHYID;
                 DECLARE @lParent_Path_Locator HIERARCHYID;
                 DECLARE @lnew_Path_Locator HIERARCHYID;
   
                 WHILE @loop <= @rows BEGIN SELECT @lPath_Locator=Path_Locator, @lname=Name, @lparent=Parent, @lParent_Path_Locator=null FROM @directories WHERE ID=@loop; IF (@lPath_Locator) IS NULL BEGIN IF @lparent IS NOT NULL BEGIN SET @lParent_Path_Locator = (SELECT path_locator FROM @directories WHERE id=@loop-1); SET @lPath_Locator=(SELECT path_locator FROM dbo.Document WHERE name=@lName AND parent_path_locator=@lParent_Path_Locator); IF @lPath_Locator IS NULL BEGIN SELECT @lPath_Locator = @lParent_Path_Locator.ToString() + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'; INSERT INTO dbo.Document(name, is_directory, path_locator) VALUES( @lName, 1, @lPath_Locator ); End END ELSE BEGIN SET @lPath_Locator=(SELECT path_locator FROM dbo.Document WHERE name=@lName); IF @lPath_Locator IS NULL BEGIN INSERT INTO dbo.Document(name, is_directory) VALUES( @lName, 1 ); SET @lPath_Locator = (SELECT path_locator FROM dbo.Document WHERE name=@lName); End END UPDATE @directories SET Path_Locator=@lPath_Locator WHERE ID=@loop; END SET @loop=@loop+1; end SET @path_Locator_for_file=@lPath_Locator; SELECT @path_Locator_for_file = @lPath_Locator.ToString() + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'; -- create file in subdir INSERT INTO dbo.Document (name, file_stream, path_locator) SELECT @name, @stream, @path_Locator_for_file; END ELSE BEGIN -- create file IN ROOT INSERT INTO dbo.Document (name, file_stream) SELECT @name, @stream; SELECT @path_Locator_for_file=path_locator FROM dbo.document WHERE name=@name; END SET @path_locator=CONVERT(VARCHAR(max),@path_Locator_for_file); COMMIT TRAN adddocument; END TRY BEGIN CATCH IF @@TRANCOUNT>0
                 ROLLBACK TRAN adddocument;
       THROW;
   
   END CATCH
   
   END
   

5. Create the UpdDocument procedure:

   
   CREATE PROC [dbo].[UpdDocument]
   (@relativePath varchar(max)=null
   ,@name varchar(4000)=null
   ,@stream VARBINARY(MAX)=NULL
   ,@path_locator VARCHAR(512)=null
   )
   AS 
   BEGIN
   DECLARE @path_locator_hid HIERARCHYID;
   
   BEGIN TRY
   BEGIN TRAN upddocument
                 
          -- test input
          IF @path_locator IS null AND (COALESCE(@relativePath,'')='' OR COALESCE(@name,'')='')
                 THROW 50001, 'no input given', 1; 
      
          IF coalesce(@relativePath,'') > ''
          BEGIN
           SET @relativePath=LTRIM(RTRIM(@relativePath));
                 IF LEFT(@relativePath,1)='\'
                        SET @relativePath=SUBSTRING(@relativePath, 2, LEN(@relativePath)-1);
                 IF RIGHT(@relativePath,1)='\'
                        SET @relativePath=LEFT(@relativePath, LEN(@relativePath)-1);
       END 
                 
          -- find pathlocator for doc
          IF @path_Locator IS NULL
       begin
                 SELECT @path_locator_hid=getPathLocator(FileTableRootPath('dbo.document')+'\'+@relativePath+'\'+@name);
       END  
          ELSE 
          BEGIN
                 SET @path_locator_hid=CONVERT(HIERARCHYID,@path_locator);
       end
          
          -- update doc
          UPDATE dbo.Document
             SET file_stream=@stream
       WHERE path_locator=@path_locator_hid;
   
          IF @@ROWCOUNT=0 
                 THROW 50002, 'document not found', 1;
   
          COMMIT TRAN updocument;
   END TRY
   BEGIN CATCH
   
          IF @@TRANCOUNT>0
                 ROLLBACK TRAN upddocument;
       THROW;
   
   END CATCH
   
   END
   

   Input parameters are the same as in the addDocument procedure. In this case the document must exists otherwise it returns an error.
   Only the data stream is replaced the meta data left the same.

6. The last procedure is the deletion of a document in delDocument
   Do not forget to remove our link in the linkedDocument table when you remove a document. (This must be done in the application.)

   
   CREATE PROC [dbo].[DelDocument]
   (@relativePath varchar(MAX)=NULL
   ,@name varchar(512)=NULL
   ,@path_locator VARCHAR(4000)=NULL 
   )
   AS 
   BEGIN
   
   SET NOCOUNT ON
   
   DECLARE @path_locator_hid HIERARCHYID; 
   
   BEGIN TRY
   BEGIN TRAN deldocument
   
          -- test input
          IF @path_locator IS null AND (COALESCE(@relativePath,'')='' OR COALESCE(@name,'')='')
                 THROW 50001, 'no input given', 1; 
      
          IF coalesce(@relativePath,'') > ''
          BEGIN
           SET @relativePath=LTRIM(RTRIM(@relativePath));
                 IF LEFT(@relativePath,1)='\'
                        SET @relativePath=SUBSTRING(@relativePath, 2, LEN(@relativePath)-1);
                 IF RIGHT(@relativePath,1)='\'
                        SET @relativePath=LEFT(@relativePath, LEN(@relativePath)-1);
       END 
                 
          -- delete doc
          IF @path_Locator IS NULL
       begin
                 SELECT @path_locator_hid=getPathLocator(FileTableRootPath('dbo.document')+'\'+@relativePath+'\'+@name);
       END    
          ELSE 
          BEGIN
                 SET @path_locator_hid=CONVERT(HIERARCHYID,@path_locator);
       end
   
          DELETE FROM [dbo].[Document] 
          WHERE path_locator=@path_locator_hid;
   
          IF @@ROWCOUNT=0 
                 THROW 50002, 'document not found', 1;
   
          COMMIT TRAN deldocument;
   END TRY
   BEGIN CATCH
   
          IF @@TRANCOUNT>0
                 ROLLBACK TRAN deldocument;
       THROW;
   
   END CATCH
   
   END
   

   That’s all you have to do. Now you can just calling the procedures AddDocument,DelDocument and UpdDocument to manage your files. If you need to search for a file and/or link the file table, just using the vDocument view (joined or not).

Conclusion

this is just a little work around to use file tables combined with EF. It’s not perfect, and can be optimised at some point, but for the moment it works for me.