Use FileTable / .NET Core application class model

sblb 1,166 Reputation points
2022-05-19T20:29:11.483+00:00

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.

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
689 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,077 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,097 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 53,426 Reputation points
    2022-05-19T21:46:42.017+00:00

    you need to add EF support for hierarchyid

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

    0 comments No comments

  2. sblb 1,166 Reputation points
    2022-05-20T11:24:26.477+00:00

    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}
    
    0 comments No comments

  3. Bruce (SqlWork.com) 53,426 Reputation points
    2022-05-20T14:46:34.647+00:00

    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)


  4. Bruce (SqlWork.com) 53,426 Reputation points
    2022-05-20T17:02:12.223+00:00

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

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

    0 comments No comments

  5. sblb 1,166 Reputation points
    2022-05-24T15:05:16.013+00:00

    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

    0 comments No comments