SQL FileTable mapping with DataAccess.Fluent

17 posts, 0 answers
  1. RIT
    RIT avatar
    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

     

  2. RIT
    RIT avatar
    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!

  3. DevCraft banner
  4. Boris Georgiev
    Admin
    Boris Georgiev avatar
    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.
  5. RIT
    RIT avatar
    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; }
    }

  6. RIT
    RIT avatar
    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

     

  7. Boris Georgiev
    Admin
    Boris Georgiev avatar
    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.
  8. RIT
    RIT avatar
    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)

     

    -- 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

  9. RIT
    RIT avatar
    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

     

  10. Boris Georgiev
    Admin
    Boris Georgiev avatar
    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.
  11. RIT
    RIT avatar
    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

  12. Thomas
    Admin
    Thomas avatar
    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.
  13. RIT
    RIT avatar
    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

  14. Igor
    Igor avatar
    1 posts
    Member since:
    Oct 2008

    Posted 25 Oct 2015 Link to this post

    Hi,

    Any info about this issue?

    Kind regards,

    Igor

  15. Thomas
    Admin
    Thomas avatar
    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.
  16. Thomas
    Admin
    Thomas avatar
    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.
  17. RIT
    RIT avatar
    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

  18. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 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.
Back to Top
DevCraft banner