SQL FileTable mapping with DataAccess.Fluent

[RIT]

I want to map a SQL FileTable with DataAccess. This works in the designer but the fluent notation doesn't work. I cannot map the Id property (path_locator) as IsIdentity because SqlHierarchyId cannot be defined as IsIdentity.
Is there a known workaround?

 configuration.HasProperty(x => x.Id).IsIdentity<GenericPropertyConfiguration>().WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("path_locator").IsNotNullable(); // this is not allowed

 If I omit the IsIdentity, Data Access looks for a voa_keygen table...

 

Greetings,

Florian

 

[RIT]

Can you please give me a hint on this. It is a showstopper in our company, as I assumed FileTable mappings are possible with Telerik.DataAccess.

Basically I need the example given in the post http://www.telerik.com/forums/error-while-trying-to-map-sql2012-filetable

in a DataAccess.Fluent version.

Thanks a lot!

[Boris Georgiev]

Hello Florian,

To map a HierarchyId column type as Identity Column with fluent mapping you should only specify the column type for the property as 'hierarchyId'. 

For example the mapping for this class:

public class Employee
{
    public SqlHierarchyId Id { get; set; }
    public string Name { get; set; }
}

Should look like this:

MappingConfiguration<Employee> configuration = new MappingConfiguration<Employee>();
configuration.MapType(x => new { Id = x.Id, Name = x.Name }).ToTable("EmployeeWithHierarchyID");
configuration.HasProperty<Employee>(x => x.Id).HasColumnType("hierarchyid").IsIdentity();
configuration.HasProperty(x => x.Name).ToColumn("Name");

I hope that helps.

Regards,
Boris Georgiev
Telerik

 

[RIT]

Hi Boris

Thanks for your answer. I'm sorry, but your method doesn't seem to work. Have you actually tried this?

(I have tried it with Microsoft.SqlServer.Types 10 and 12 version).

I receive a compilation error:

Error   CS1662:
Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type Reber.Document  C:\Daten\Source\zh\meddb-dms\Reber.Document\DbMetadataSource.cs 75
Error   CS0029:
Cannot implicitly convert type 'Microsoft.SqlServer.Types.SqlHierarchyId' to 'System.Collections.Generic.IList<byte>'
C:\Daten\Source\zh\meddb-dms\Reber.Document\DbMetadataSource.cs 75

Here is my complete mapping (please focus on the Id property, maybe the rest has to be reviewed):

public MappingConfiguration<FileStore> GetFileStoreMappingConfiguration()
{
    MappingConfiguration<FileStore> configuration = this.GetFileStoreClassConfiguration();
    this.PrepareFileStorePropertyConfigurations(configuration);
    this.PrepareFileStoreAssociationConfigurations(configuration);

    return configuration;
}

public MappingConfiguration<FileStore> GetFileStoreClassConfiguration()
{
    MappingConfiguration<FileStore> configuration = new MappingConfiguration<FileStore>();
    configuration.MapType().WithConcurencyControl(OptimisticConcurrencyControlStrategy.Changed).ToTable("FileStore");
    return configuration;
}

public void PrepareFileStorePropertyConfigurations(MappingConfiguration<FileStore> configuration)
{
    configuration.HasProperty<FileStore>(x => x.Id).HasColumnType("hierarchyid").IsIdentity();
    //configuration.HasProperty(x => x.Id).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("path_locator").IsNotNullable().HasColumnType("hierarchyid");
    configuration.HasProperty(x => x.StreamId).IsIdentity().WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("stream_id").IsNotNullable().HasColumnType("Guid");
    configuration.HasProperty(x => x.FileStream).WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("file_stream").IsNullable().HasColumnType("varbinary");
    configuration.HasProperty(x => x.IsDirectory).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_directory").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsArchive).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_archive").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsHidden).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_hidden").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsOffline).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_offline").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsReadonly).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_readonly").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsSystem).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_system").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.IsTemporary).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("is_temporary").IsNotNullable().HasColumnType("bit").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.FileType).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("file_type").IsNullable().HasColumnType("nvarchar").HasLength(255);
    configuration.HasProperty(x => x.Name).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("name").IsNotNullable().HasColumnType("nvarchar").HasLength(255);
    configuration.HasProperty(x => x.PathLocator).AsTransient();//.WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("path_locator").IsNotNullable().HasColumnType("hierarchyid");
    configuration.HasProperty(x => x.ParentPathLocator).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("parent_path_locator").IsNullable().HasColumnType("hierarchyid");
    configuration.HasProperty(x => x.CachedFileSize).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("cached_file_size").IsNullable().HasColumnType("bigint");
    configuration.HasProperty(x => x.CreationTime).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("creation_time").IsNotNullable().HasColumnType("datetimeoffset");
    configuration.HasProperty(x => x.LastAccessTime).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("last_access_time").IsNullable().HasColumnType("datetimeoffset");
    configuration.HasProperty(x => x.Parent).AsTransient();
    configuration.HasProperty(x => x.LastWriteTime).WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("last_write_time").IsNullable().HasColumnType("datetimeoffset");
}

public partial class FileStore : IEntityWithTypedId<SqlHierarchyId>
{
    public virtual SqlHierarchyId Id { get; set; }

    public virtual byte[] FileStream { get; set; }

    public virtual string Name { get; set; }

    public virtual SqlHierarchyId PathLocator { get; set; }

    public virtual SqlHierarchyId ParentPathLocator { get; set; }

    public virtual string FileType { get; set; }

    public virtual long? CachedFileSize { get; set; }

    public virtual DateTimeOffset CreationTime { get; set; }

    public virtual DateTimeOffset LastWriteTime { get; set; }

    public virtual DateTimeOffset? LastAccessTime { get; set; }

    public virtual bool IsDirectory { get; set; }

    public virtual bool IsOffline { get; set; }

    public virtual bool IsHidden { get; set; }

    public virtual bool IsReadonly { get; set; }

    public virtual bool IsArchive { get; set; }

    public virtual bool IsSystem { get; set; }

    public virtual bool IsTemporary { get; set; }

    public virtual FileStore Parent { get; set; }

    public virtual IList<FileStore> FileStores { get; set; }

    public Guid StreamId { get; set; }
}

[RIT]

Hi Boris

I'm still waiting for a working solution. Meanwhile I have found some kind of workaround, but im not sure if this method has some sideeffects:

public partial class FileStore : IEntityWithTypedId<SqlHierarchyId>
{
    private SqlHierarchyId id;
    public virtual SqlHierarchyId Id
    {
        get
        {
            return this.id;
        }
        set
        {
            this.id = value;
        }
    }

    public virtual BinaryStream FileStream { get; set; }

    // more properties...
}

Mapping:

 

configuration
    .HasPrimitiveMember("id", "Id")
    .IsIdentity()
    .WithDataAccessKind(DataAccessKind.ReadOnly)
    .IsNotNullable()
    .ToColumn("path_locator")
    .HasColumnType("hierarchyid");

configuration
    .HasProperty(x => x.FileStream)
    .WithDataAccessKind(DataAccessKind.ReadWrite)
    .ToColumn("file_stream")
    .IsNullable();

This works at least for read-operations. But when I try to insert a file, I ran into a new problem:

A SQL-FileTable has a check-constraint for the "file_stream" column (varbinary(max). I must not be null: you must insert at least 0x.

Using your BinaryStream type for the "FileStream" property in my "FileStore" class above, this will not work. Data Access seems to insert the row first and then writes the stream -> this is not allowed by the FileTable (and I cannot remove the constraint as it is by design).

I'm using this method to insert a new file:

developer-guide-domain-model-managing-advanced-working-with-streams​

 

using (var db = new Reber.Document.Db())
{
    var file = new FileStore();
    file.Id = db.FileStores.First(x => x.IsDirectory).Id.GetDescendant(SqlHierarchyId.Null, SqlHierarchyId.Null);
    file.Name = "test.txt";
    var attachedFile = db.AttachCopy(file);
    db.FlushChanges(); // exception here
    using (FileStream stream = File.OpenRead(@"C:\temp\AllTags.docx"))
    {
        byte[] buffer = new byte[1024];
        int bytesRead = 0;
        while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) > 0)
        {
            attachedFile.FileStream.Write(buffer, 0, bytesRead);
        }
    }

    db.SaveChanges();
}

Exception details:

Die MedDB.Test.TestDataAccess.CanUseDocumentDataContext-Testmethode hat eine Ausnahme ausgelöst:

Telerik.OpenAccess.Exceptions.DataStoreException: Insert of '2059671172-/9095764594888.222387776954401.2966721089/1/' failed: Telerik.OpenAccess.RT.sql.SQLException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen. ---> System.Data.SqlClient.SqlException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen.

   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   bei System.Data.SqlClient.SqlDataReader.get_MetaData()

   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

   bei OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)

   bei Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

   bei System.Data.Common.DbCommand.ExecuteReader()

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)

   --- Ende der internen Ausnahmestapelüberwachung ---

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)

   bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)

   bei OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl)
INSERT INTO [FileStore] ([name]) VALUES (?)
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen. ---> System.Data.SqlClient.SqlException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen.

   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   bei System.Data.SqlClient.SqlDataReader.get_MetaData()

   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

   bei OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)

   bei Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

   bei System.Data.Common.DbCommand.ExecuteReader()

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)

   --- Ende der internen Ausnahmestapelüberwachung ---

   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)

   bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)

   bei OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl) ---> Telerik.OpenAccess.RT.sql.SQLException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen. ---> System.Data.SqlClient.SqlException: Der Vorgang hat einen Fehler bei einer CHECK-Einschränkung für eine 'FileTable' verursacht. Ein Dateieintrag darf keinen NULL-Wert für den der Zeile zugeordneten Datenstrom enthalten. Fügen Sie Dateidaten ein, oder verwenden Sie '0x', um eine Datei mit der Länge 0 einzufügen.
    bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, ref Boolean dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   bei System.Data.SqlClient.SqlDataReader.get_MetaData()
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, ref Task task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, ref Task task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   bei OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)
   bei Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.ExecuteReader()
   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()
   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)
 --- Ende der inneren Ausnahmestapelüberwachung ---
    bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)
   bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)
   bei OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl)
 --- Ende der inneren Ausnahmestapelüberwachung ---
    bei OpenAccessRuntime.ExceptionWrapper.Throw()
   bei OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)
   bei OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.flushRetainState()
   bei OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.flush(Boolean retainState)
   bei OpenAccessRuntime.EnlistableObjectScope.FlushChanges(Boolean releaseMemory)
   bei Telerik.OpenAccess.OpenAccessContextBase.FlushChanges(Boolean releaseMemory)
   bei Telerik.OpenAccess.OpenAccessContextBase.FlushChanges()
   bei MedDB.Test.TestDataAccess.CanUseDocumentDataContext() in TestDataAccess.cs: line 102.

Please help me with this. A byte[] is not an option (only if I can do some kind of lazy-loading for this property).

Greetings,

Florian

 

[Boris Georgiev]

Hello Florian,

You are on the right track and you had found the right solution for the issue with the byte array. BinaryStream type is a special type that exposes a stream around a database binary field and it is provided by Telerik Data Access for working with varbinary(max) columns.

From the third code snippet which you had sent, it looks like you are trying to insert the row with the FlushChanges() method in the database and if the file_stream column is nullable it should insert a null value in the column. From the first and the second code snippet, it looks like your mapping is also right. Could you sent us the sample application and the database script so we could try to reproduce the issue in our test environment?

I am looking forward to hearing from you.

Regards,
Boris Georgiev
Telerik

 

[RIT]

Hi Boris

The file_stream column is nullable indeed, but has a check-constraint by design (see the Microsoft FileTable documentation). So you can only insert null if you try to create a directory. Basically I'm trying to map a FileTable (with a predefined schema) in DataAccess. I'm going to make you a sample and upload it, but to give you an idea you could just create a filetable like this and map it in DataAccess:

(here is some documentation)

 

-- CONFIGURE FIREWALL https://msdn.microsoft.com/en-us/library/dd283098.aspx
-- Allow TCP inbound, 139 and 445
--   adapt directories, database etc below.

-- enable Filestream on instance
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-- add filestream group to existing database
ALTER DATABASE MyRepository
ADD FILEGROUP FileStoreFileGroup CONTAINS FILESTREAM
GO

-- the following statement only works if nobody is connected!
ALTER DATABASE MyRepository
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyRepository' );
GO

ALTER DATABASE MyRepository
ADD FILE ( NAME = 'FileStore', FILENAME = 'C:\FileStore' )
TO FILEGROUP FileStoreFileGroup;
GO

-- Check the Filestream Options
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

USE MyRepository
IF OBJECT_ID('dbo.FileStore', 'U') IS NOT NULL
  DROP TABLE dbo.FileStore
GO

-- add filetabe: user will access the file in share \\computername\db-instance (mssqlserver)\MyRepository\Documents
-- i.e. \\server-xy\mssqlserver\MyRepository\Documents
CREATE TABLE FileStore AS FileTable
WITH (FileTable_Directory = 'Documents');
GO

SELECT DB_NAME ( database_id ), directory_name
    FROM sys.database_filestream_options;
GO

--View all objects for all filetables, unsorted
SELECT * FROM sys.filetable_system_defined_objects;
GO

--View sorted list with friendly names
SELECT OBJECT_NAME(parent_object_id) AS 'FileTable', OBJECT_NAME(object_id) AS 'System-defined Object'
    FROM sys.filetable_system_defined_objects
    ORDER BY FileTable, 'System-defined Object';
GO

Now try to map this created table in DataAccess.

Greetings,

Florian

[RIT]

Here is a complete sample. The sql script is similar to the one posted above. Please note that you need SQL-Server 2012 or above with enabled filestream-support on the instance-level. There are some screenshots in the sql-folder (in german, sorry ;)).

Reber.Document-Sample

 

[Boris Georgiev]

Hi Florian,

Thank you for the provided sample. We were able to successfully reproduce the issue in our test environment but we will need more time to investigate what is causing it. We will do our best to answer you as soon as possible.

Thank you for your understanding.

Regards,
Boris Georgiev
Telerik

 

[RIT]

Hi Boris,

Thanks for your investigations. Could you tell me about the status on this issue?

Basically I just need the following:

- non nullable BinaryStream-column support: If you insert/flush entities with a non-nullable BinaryStream-column (aka FileStream-column on SQL-Server), then the framework should insert 0x as a placeholder.

- proper support for SqlHierarchyId: I could do the workaround, but the querying isn't very comfortable and associations nearly impossible to do...

Regards,

Florian

[Thomas]

Hi Florian,

I took over this ticket but need a bit more time for investigation. I will be able to continue working on this on monday, hoping that this is not to late for you.

Regards,
Thomas
Telerik

 

[RIT]

Hi Thomas

Thanks for helping me with this. I really appreciate a clean solution as my workarounds are dirty ;)

Regards,

Florian

[Igor]

Hi,

Any info about this issue?

Kind regards,

Igor

[Thomas]

Hello Florian/Igor,


at the moment it looks like a problem in our implementation. There is a tiny chance I get it working tomorrow
by specifying a converter, but I'm not very optimistic on this. I will investigate this further and let you know about the outcome.

Regards,
Thomas
Telerik

 

[Thomas]

Hello Florian/Igor,


looks like a new converter won't do it. Basic file stream usage seems ok, but file tables with their constraints aren't handled adequately. I will need to file a bug entry for this!

The only workaround that is worth mentioning is to use the ADO API to insert a FileTable object:

private static void InsertDocumentStore(Guid id, string title, byte[] content, bool isReadonly = false)
        {
            using (var connection = DatabaseSpecificTestBase.DataContext.Connection)
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"INSERT INTO [DocumentStore]
([stream_id], [file_stream], [name], [path_locator], [creation_time], [last_write_time], [last_access_time],
 [is_directory], [is_offline], [is_hidden], [is_readonly], [is_archive], [is_system], [is_temporary])
VALUES (@id, @content, @title, DEFAULT, GETDATE(), GETDATE(), GETDATE(), 0, 0, 0, @is_readonly, 0, 0, 0)";
                    StreamTestBase.AddParameter(command, "@id", DbType.Guid, id);
                    StreamTestBase.AddParameter(command, "@title", DbType.String, title);
                    StreamTestBase.AddParameter(command, "@content", DbType.Binary, content);
                    StreamTestBase.AddParameter(command, "@is_readonly", DbType.Boolean, isReadonly);

                    command.ExecuteNonQuery();
                }
            }
        }

The code is copied from a test suite and will need adaption (uses Guid for id ...). I copied it here to illustrate the principal idea: Add a row into the database with ADO, so that that stream instance is correctly set up.

Thanks for reporting this issue with us, hope we can finally fix it in a future version.

Regards,
Thomas
Telerik

 

[RIT]

Hi Thomas,

Thanks for your workaround. I am still going to wait for the bugfix-release. Do you have any clues about the roadmap on this?

Best regards,

Florian

[Viktor Zhivkov]

Hi Florian,

I am sorry, but I cannot give you any timeline when the required fix will be available.
My suggestion is to try the work around and if it works potentially grow it into a Stored Procedure. 
If you are unable to resolve the issue with the provided suggestion do not hesitate to contact us.

Regards,
Viktor Zhivkov
Telerik