question

sblb-6958 avatar image
0 Votes"
sblb-6958 asked sblb-6958 commented

Use FileTable / .NET Core application class model

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.

dotnet-csharpdotnet-aspnet-core-generaldotnet-entity-framework-core
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

you need to add EF support for hierarchyid

https://github.com/efcore/EFCore.SqlServer.HierarchyId



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sblb-6958 avatar image
0 Votes"
sblb-6958 answered

Thanks to your schema.

So I would like to know if I can have your expert advice about the description of the schema that I propose.

I don't know if I was clear in my explanations but the File Table has PK named Path_Locator with type of HIERARCHYID and I propose to create the varchar(max) field in the Document.cs table and will store the Path_Locator in this column after converting into SQL varchar(max) from SQL HIERARCHYID data type already defined in File Table.

So, I'm not sure to create the field in Document.cs as you propose :

 public HierarchyId Id { get; set; }
 public string Name {get;set}


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered sblb-6958 edited

The stream_id (a guid) would be a more compact key to file table row. The hierarchyid can be converted to a string or byte array (varmax) but it can be large (directory path plus filename)


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So how identify stream_id in document.cs class?
I propose the schéma in first post and I would like to know if I can find the procedure to apply it. I have searched several times in Google and I did not find anything. This means that I am using the wrong scheme

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

the stream_id is a guid (unique_identifier). here is the file table schema:

https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetable-schema?view=sql-server-ver15

note. some of the rows are directories, and some are files. the file table is tree, built using the hierarchy id support:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/tutorial-using-the-hierarchyid-data-type?view=sql-server-ver15

to insert a row in the file table, you need to know which directory row it belongs to.

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sblb-6958 avatar image
0 Votes"
sblb-6958 answered sblb-6958 edited

Hi,

I've implemented process Server-Side Processing with JQuery DataTable. It's really impressive the difference in performance between client side and server side processing.

1- I've synchronize the File Table and Simple Class FileDescription.cs (thanks to @AgaveJoe) directly in SSMS with script below.

  INSERT INTO [dbo].[FileDescriptions] ([Name], [Description], [CreatedTimestamp], [UpdatedTimestamp], [ContentType])
  SELECT w.name,
    w.[file_type],
    GETDATE(),
    GETDATE(),
   CASE 
   WHEN w.[file_type] IN ('jpg', 'png', 'gif', 'tif') THEN 'image/' +  w.[file_type]
   WHEN w.[file_type] IN ('pdf') THEN 'applicaiton/' + w.[file_type]
   ELSE 'application/octet-stream'
   END
  FROM   [dbo].[wSerie] as w
   LEFT JOIN [dbo].[FileDescriptions] as f ON w.name = f.Name
  WHERE f.Name IS NULL
   AND [is_archive] = 1 

2- I've implemented FileController

 [HttpPost]
         public IActionResult GetCustomers()
         {
             try
             {
                 var draw = Request.Form["draw"].FirstOrDefault();
                 var start = Request.Form["start"].FirstOrDefault();
                 var length = Request.Form["length"].FirstOrDefault();
                 var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
                 var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
                 var searchValue = Request.Form["search[value]"].FirstOrDefault();
                 int pageSize = length != null ? Convert.ToInt32(length) : 0;
                 int skip = start != null ? Convert.ToInt32(start) : 0;
                 int recordsTotal = 0;
                 var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer);
                 if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
                 {
                     customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);
                 }
                 if (!string.IsNullOrEmpty(searchValue))
                 {
                     customerData = customerData.Where(m => m.Name.Contains(searchValue)
                                                 || m.Description.Contains(searchValue)
                                                 || m.ContentType.Contains(searchValue));
                                                  
                 }
                 recordsTotal = customerData.Count();
                 var data = customerData.Skip(skip).Take(pageSize).ToList();
                 var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
                 return Ok(jsonData);
             }
             catch (Exception ex)
             {
                 throw;
             }
        }



So Actually, I've no link in FileDescription class with FileTable to open the File directly in column Name?

Questions :
- I've tried to implemented HierarchyId in FileDescription class but I don't know How I can Synchronise with this kind of the type field?
- How can I make sure that the File Table and FileDescription class are automatically synchronized? Because files can later be added to the File Table

Thanks in advance to your support


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered sblb-6958 commented

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://docs.microsoft.com/en-us/sql/relational-databases/blob/work-with-directories-and-paths-in-filetables?view=sql-server-ver16

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The interest of the additional table is that I can easily implement jquery datatable on server side to improve the performance.
I guess there is another method.

Can you help me to implement a trigger on the file table?

0 Votes 0 ·

Hi
you can use the stream_id as a link

I tried to put stream_id in Id column of table FileDrescription but I can not acces to the file.

Can you give me the good way to do that?

0 Votes 0 ·
sblb-6958 avatar image
0 Votes"
sblb-6958 answered sblb-6958 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.