kohera-logo-regular.svg

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
    )
    

    Create the file table

  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.

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

  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
      
    • 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.

    • 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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...