you need to add EF support for hierarchyid
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.
7 answers
Sort by: Most helpful
-
-
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}
-
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)
-
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:
note. some of the rows are directories, and some are files. the file table is tree, built using the hierarchy id support:
to insert a row in the file table, you need to know which directory row it belongs to.
-
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