Using LLBL Gen Pro 5.11.2

-Target Language C# / Target Platform .NET 8 -Template group: SelfServicing - Selected preset: SD.Presets.SelfServicing.General.Netstandard

Defined SQL FileTable as follows:

• ALTER DATABASE [DataBaseName] ADD FILEGROUP [DealData] CONTAINS FILESTREAM

• ALTER DATABASE [DataBaseName] ADD FILE (NAME = DF_Deal, FILENAME = 'D:\FileData\StreamPath\StreamPath_FILEDATA') TO FILEGROUP [DealData]

• CREATE TABLE [ast].[DealDocument] AS FILETABLE FILESTREAM_ON [DealData] WITH ( FileTable_Directory = 'Database_Deal', FileTable_Collate_Filename = Latin1_General_CI_AS );

var metaData = _transArgs != null ? new LinqMetaData(_transArgs.Transaction) : new LinqMetaData();

var docCount = metaData.DealDocument.Count();

var documentFound1 = metaData.DealDocument.Where
    (x => x.StreamId == streamIdValue).FirstOrDefault();

var documentFound2 = (from c in metaData.DealDocument
                     where c.Name == documentName
                     select c).SingleOrDefault();

var documentFound = metaData.DealDocument.Where
    (x => x.Name.Equals(documentName)).FirstOrDefault();

The code for the variable docCount, will work just fine and it will return the total count of FileTable documents. But for the next lines of code, for variables documentFound1, documentFound2 or documentFound, it won't work and it will return the following error:

An exception was caught during the execution of a retrieval query: Internal connection fatal error.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Query generated looks correct:

Query: SELECT TOP(@p2) [LPA_L1].[cached_file_size] AS [CachedFileSize], [LPA_L1].[creation_time] AS [CreationTime], [LPA_L1].[file_stream] AS [FileStream], [LPA_L1].[file_type] AS [FileType], [LPA_L1].[is_archive] AS [IsArchive], [LPA_L1].[is_directory] AS [IsDirectory], [LPA_L1].[is_hidden] AS [IsHidden], [LPA_L1].[is_offline] AS [IsOffline], [LPA_L1].[is_readonly] AS [IsReadonly], [LPA_L1].[is_system] AS [IsSystem], [LPA_L1].[is_temporary] AS [IsTemporary], [LPA_L1].[last_access_time] AS [LastAccessTime], [LPA_L1].[last_write_time] AS [LastWriteTime], [LPA_L1].[name] AS [Name], [LPA_L1].[parent_path_locator] AS [ParentPathLocator], [LPA_L1].[path_locator] AS [PathLocator], [LPA_L1].[stream_id] AS [StreamId] FROM [USEA_VISIONAST].[ast].[DealDocument] [LPA_L1] WHERE ( ( ( [LPA_L1].[name] = @p3))) Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p3 : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: "10814_2024829.csv".

Hoping it can be figured out.

Please provide the inner exception and stack trace.

Also please make sure the entity model and the relational database are in Sync, and that your solution is using the latest generated code.

"Internal connection fatal error" isn't an error message coming from our runtime, so it sounds like it's from the ADO.NET provider

I am adding some of the information I am able to collect when the error occurred. The Relational Model Data was set in Sync, and entities were removed and added again when pointing to the new Database with the new FileStream configuration.

When executing:

var metaData = _transArgs != null ? new LinqMetaData(_transArgs.Transaction) : new LinqMetaData();
var docCount = metaData.DealDocument.Count();

var documentFound = metaData.DealDocument.Where
    (x => x.Name.Equals(documentName)).FirstOrDefault();

The docCount returns correctly the number of documents, but when attempting to execute the Lambda query with a Where clause by Name, it will throw the exception.

Initially, it was only assigned one FILEGROUP at the moment of the database creation, which no error was generated. The error is happening when FILEGROUP is added after Database is created.

This is the scrip when database is generated along with the Default FILEGROUP, which it was not generating errors.

CREATE DATABASE [USEA_DBTEST_DEV]
ON PRIMARY
    (NAME = N'USEA_DBTEST_DEV_DATA',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_DEV.mdf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB),
FILEGROUP FileStreamData CONTAINS FILESTREAM
(NAME = DBTEST_DEV_DOCS, FILENAME = 'D:\FileData\USEA_DBTEST_DEV\USEA_DBTEST_DEV_FILEDATA')
LOG ON
    ( NAME = N'USEA_DBTEST_DEV_Log',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_DEV.ldf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
GO

New Database scripts, which now we are getting the error.

CREATE DATABASE [USEA_DBTEST]
ON PRIMARY
    (NAME = N'USEA_DBTEST_DATA',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST.mdf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
LOG ON
    ( NAME = N'USEA_DBTEST_Log',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_Log.ldf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
GO

GO
ALTER DATABASE [USEA_DBTEST] ADD FILEGROUP [DealData] CONTAINS FILESTREAM

ALTER DATABASE [USEA_DBTEST] ADD FILE (NAME = DF_Deal, FILENAME = 'D:\FileData\USEA_DBTEST\USEA_DBTEST_FILEDATA') TO FILEGROUP [DealData]
GO

USE [USEA_DBTEST]
GO
CREATE TABLE [ast].[DealDocument] 
AS FILETABLE FILESTREAM_ON [DealData]
WITH 
(
    FileTable_Directory = 'USEA_DBTEST_Deal',
    FileTable_Collate_Filename = Latin1_General_CI_AS
);

InnerException.StackTrace

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ThrowUnsupportedCollationEncountered(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.GetCodePage(SqlCollation collation, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TryCommonProcessMetaData(TdsParserStateObject stateObj, _SqlMetaData col)
   at System.Data.SqlClient.TdsParser.TryProcessMetaData(Int32 cColumns, TdsParserStateObject stateObj, _SqlMetaDataSet& metaData)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.<>c__DisplayClass12_0.<Execute>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

ex.SerializationStackTraceString

  at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String& failureErrorText)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityFactory entityFactoryToUse, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.PerformGetMulti(QueryParameters parameters)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteEntityProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at USEA.DA.Dealer.Access.Type.DealDocument.GetDocumentByName(String documentName, Boolean includeFile) in C:\Development\VisionAST\USE AST Service Solution\USEADA\Dealer\Access\Type\DealDocument.cs:line 83
   at USEA.Control.Operations.DealerOperation.GetDealDocument(String fileName, Boolean includeFile) in C:\Development\VisionAST\USE AST Service Solution\USEACTRL\Operations\DealerOperation.cs:line 46
   at USEA.Control.Dealer.SetDealDocumentsToProcess(IList`1 filesNames, ASTDealProcess& astDealProcess) in C:\Development\VisionAST\USE AST Service Solution\USEACTRL\Dealer.cs:line 150
   at USEA.Control.Dealer.GetDealDocumentsToProcess(ASTDealProcess& astDealProcess) in C:\Development\VisionAST\USE AST Service Solution\USEACTRL\Dealer.cs:line 40
   at USEA.ASTSvc.Controllers.DealController.ProcessDeals(DMSProcess model) in C:\Development\VisionAST\USE AST Service Solution\USEAASTSVC\Controllers\DealController.cs:line 47

It clearly shows it crashes inside the .NET SqlClient. I don't know what causes this. The query should be something like:

SELECT TOP 1 <DealDocument fields> FROM DealDocument WHERE name==@documentName

So it could be something related to how the sql server is configured, e.g. that it dies in a deadlock when scanning the whole table on that where clause, or that it can't load an index because the file is inaccessible...

The Query generated is correct and executes without any issues at the SQL Server Database. Only differences are as follows:

In this SQL Script, the database is created and the FILEGROUP is added as only main group for the entire Database. For this FileStream definition, there was no problems executing any LLBL queries towards the FileTable, even using complex queries through LLBL Gen PRO 5.11

CREATE DATABASE [USEA_DBTEST_DEV]
ON PRIMARY
    (NAME = N'USEA_DBTEST_DEV_DATA',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_DEV.mdf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB),
FILEGROUP FileStreamData CONTAINS FILESTREAM
(NAME = DBTEST_DEV_DOCS, FILENAME = 'D:\FileData\USEA_DBTEST_DEV\USEA_DBTEST_DEV_FILEDATA')
LOG ON
    ( NAME = N'USEA_DBTEST_DEV_Log',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_DEV.ldf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
GO

With this new SQL Server Database definition stablished at the same SQL Server as the one above, the FILESTREAM is defined in separate groups, with the purpose to allow the files to be located not just in one main location but in a distributed or dedicated storage if needed. Also able to add more File Groups if needed, and assigning FileTables to those specific groups with different UNC Path or Local Path.

CREATE DATABASE [USEA_DBTEST]
ON PRIMARY
    (NAME = N'USEA_DBTEST_DATA',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST.mdf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
LOG ON
    ( NAME = N'USEA_DBTEST_Log',
      FILENAME = N'F:\data\usea\mssql\data\USEA_DBTEST_Log.ldf',
          SIZE = 500000KB,
          MAXSIZE = UNLIMITED,
          FILEGROWTH = 51200KB)
GO

GO
ALTER DATABASE [USEA_DBTEST] ADD FILEGROUP [DealData] CONTAINS FILESTREAM

ALTER DATABASE [USEA_DBTEST] ADD FILE (NAME = DF_Deal, FILENAME = 'D:\FileData\USEA_DBTEST\USEA_DBTEST_FILEDATA') TO FILEGROUP [DealData]
GO

USE [USEA_DBTEST]
GO
CREATE TABLE [ast].[DealDocument] 
AS FILETABLE FILESTREAM_ON [DealData]
WITH 
(
    FileTable_Directory = 'USEA_DBTEST_Deal',
    FileTable_Collate_Filename = Latin1_General_CI_AS
);

When executing DealDocument.Count() it works without issues, even at the new FILESTREAM Group definition, but when attempting to query with specific WHERE clause or any type of filtering metaData.DealDocument.Where(x => x.Name.Equals(documentName)).FirstOrDefault(); then it will give the error.

var metaData = _transArgs != null ? new LinqMetaData(_transArgs.Transaction) : new LinqMetaData();
var docCount = metaData.DealDocument.Count();

var documentFound = metaData.DealDocument.Where
    (x => x.Name.Equals(documentName)).FirstOrDefault();

Query generated when error occurred:

SELECT TOP(@p2) [LPA_L1].[cached_file_size] AS [CachedFileSize], [LPA_L1].[creation_time] AS [CreationTime], [LPA_L1].[file_stream] AS [FileStream], [LPA_L1].[file_type] AS [FileType], [LPA_L1].[is_archive] AS [IsArchive], [LPA_L1].[is_directory] AS [IsDirectory], [LPA_L1].[is_hidden] AS [IsHidden], [LPA_L1].[is_offline] AS [IsOffline], [LPA_L1].[is_readonly] AS [IsReadonly], [LPA_L1].[is_system] AS [IsSystem], [LPA_L1].[is_temporary] AS [IsTemporary], [LPA_L1].[last_access_time] AS [LastAccessTime], [LPA_L1].[last_write_time] AS [LastWriteTime], [LPA_L1].[name] AS [Name], [LPA_L1].[parent_path_locator] AS [ParentPathLocator], [LPA_L1].[path_locator] AS [PathLocator], [LPA_L1].[stream_id] AS [StreamId] FROM [USEA_VISIONAST].[ast].[DealDocument] [LPA_L1] WHERE ( ( ( [LPA_L1].[name] = @p3))) Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p3 : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: "10814_2024829.csv".

Could you please try using the Microsoft.Data.SqlClient instead of the System.Data variant?

You will need to register the DbProviderFactory through the RuntimeConfiguration. Documentation

I have identified the issue.

I didn't notice but when creating the FileTable, the FileTable_Collate_Filename should be always set as database_default Now, any query with where clause will work without problems.