Telerik Forums / Data Access Forum / General Discussions /

SQL FileTable mapping with DataAccess.Fluent

Data Access
Resources Buy Try

Thread is closed for posting

17 posts, 0 answers

RIT

21 posts

Member since:
Apr 2015

Posted 28 Sep 2015 Link to this post

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

21 posts

Member since:
Apr 2015

Posted 30 Sep 2015 Link to this post

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
Admin

190 posts

Posted 01 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
RIT

21 posts

Member since:
Apr 2015

Posted 01 Oct 2015 Link to this post

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

21 posts

Member since:
Apr 2015

Posted 06 Oct 2015 in reply to Boris Georgiev Link to this post

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
Admin

190 posts

Posted 06 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
RIT

21 posts

Member since:
Apr 2015

Posted 06 Oct 2015 in reply to Boris Georgiev Link to this post

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

21 posts

Member since:
Apr 2015

Posted 06 Oct 2015 in reply to Boris Georgiev Link to this post

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
Admin

190 posts

Posted 09 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
RIT

21 posts

Member since:
Apr 2015

Posted 19 Oct 2015 in reply to Boris Georgiev Link to this post

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
Admin

590 posts

Posted 21 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
RIT

21 posts

Member since:
Apr 2015

Posted 22 Oct 2015 in reply to Thomas Link to this post

Hi Thomas

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

Regards,

Florian
Igor

1 posts

Member since:
Oct 2008

Posted 25 Oct 2015 Link to this post

Hi,

Any info about this issue?

Kind regards,

Igor
Thomas
Admin

590 posts

Posted 26 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Thomas
Admin

590 posts

Posted 26 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
RIT

21 posts

Member since:
Apr 2015

Posted 27 Oct 2015 in reply to Thomas Link to this post

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
Admin

323 posts

Posted 30 Oct 2015 Link to this post

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

Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.

SQL FileTable mapping with DataAccess.Fluent

RIT

RIT

Boris Georgiev

RIT

RIT

Boris Georgiev

RIT

RIT

Boris Georgiev

RIT

Thomas

RIT

Igor

Thomas

Thomas

RIT

Viktor Zhivkov