Before going any further I want to ensure 100% credit to Bob's wonderful keen insight on FileTable, FileStream and SQL Server.
The issue at hand:
Oddly enough I had a warning when altering my FileTable in SQL Server 2012 today. Normally it is a simple process of two commands:
Unable to modify table. Table contains at least one fulltext index.Ok so when all else fails use T-SQL
DROP FULLTEXT INDEX ON MYDB..MyFileTable GO DROP FULLTEXT CATALOG MyFileTableFullTextCatalog GO
Ran the commands and got the same error. This happened because I shared an Index pre-made by SQL Server when I built the FileTable which Bob explains in more detail in his post.
 |
| stream_id unique index. |
The Reason:
This is to get around a defect with Entity Framework not having compatibility with HeiarchyId as a SQL Data Type. The EF team stated in their blog that EF6 probably won't have support for this data type either. EF will support a read only view though, say something like this:
USE [DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[MyFileTable] AS ---- SELECT MyFT.stream_id, --Transform and omit path locator and path locator parent. FileTableRootPath() + MyFT.file_stream.GetFileNamespacePath() AS FileNamespacePath, MyFT.name, -- Omit these two fields to strings --MyFT.path_locator, --MyFT.parent_path_locator, -- Remaining normal fields. MyFT.file_type, MyFT.cached_file_size, MyFT.creation_time, MyFT.last_write_time, MyFT.last_access_time, MyFT.is_directory, MyFT.is_offline, MyFT.is_hidden, MyFT.is_readonly, MyFT.is_archive, MyFT.is_system, MyFT.is_temporary FROM MyFileTableNameInDbo AS MyFT ---- GO
For the moment this will work. I will expand on this after I give it more thought. Perhaps it would be better to use a AFTER UPDATE Trigger to a real USER_TABLE with just the stream_id (PK), the path_locator as a string via FileNamespacePath in the script above. Finally I will include the name of the file. This way I can use foreign key constraints (which views do not support).
This new "real" table can only be updated by this trigger. The downside is that the EF will not support the additional features offered by FileTable (yet!)
