Use FileTable / .NET Core application class model

Question

Hi,

I would like some advise about the schema that I would to implement to my .Net App.
I want to use FileTable with my .NET Core application where I must create a relationship of this FileTable with another simple table (e.g Document.cs)

Schema :
1 - Documents.cs include the normal attributes and including an attribute to reference file in File Table.
The File Table has PK named Path_Locator with type of HIERARCHYID this means I have to create the varchar(max) field in the Document table and will store the
Path_Locator in this column.
2 - Make a migration with migrationBuilder.Sql() to do the link between file table and the class Document.cs
3 - To accelarete the acces to the file (quanties of the > 60k) in Document.cs, I think to use Server-Side Processing with JQuery DataTable

First of all I would like to know if the schema is realistic.

For the point 1 / the class Document.cs is :

  public class Document
        {
           [Key]
            public int Id { get; set; }
            public string FileName { get; set; }
            public string Description { get; set; }
            public DateTime CreatedTimestamp { get; set; }
            public DateTime UpdatedTimestamp { get; set; }
            public string ContentType { get; set; }
            // Field to link varchar(max) with hierarchyId in File Table
            public string Name{ get; set; }
       }

how I can do the relationship between Name (varchar(max)) in Document.cs with File Table (Path_Locator, HierarchyId)? After that, I think I have to convert varchar(max) in HierarchyId.

For the point 2 / I don't know where to start.

Fort the point 3/ normally I will able to do that.

Thanks in advance to your support.

Answer 1

its not clear the point if your additional table because it has no additional information. but you can just use a trigger on the file table to insert rows in the document table. as I stated, you can use the stream_id as a link.

this shows how to get the unc paths:

https://learn.microsoft.com/en-us/sql/relational-databases/blob/work-with-directories-and-paths-in-filetables?view=sql-server-ver16

Answer 2

Hi,
you can use the stream_id as a link

I've to add in FileDescription class stream_id as follow :

public string steam_id {get; set;}

or in trigger I will link the column Name in FileDescription with stream_id.

Do you have aan example of Trigger that insert row from File Table to FileDescription class?

thanks in advance