int? count=null;
            bool exists=false;
            using (var context=new DataAccessAdapter(connectionString))
            {
                var qf = new QueryFactory();

                var q1 = qf.Create().Select(InvoiceSummaryFields.Sn);//sn is long type
                var countv = context.FetchScalar<int?>(qf.Create().Select(q1.CountRow()));//this is fine
                count = countv;
            }
            using (var context = new DataAccessAdapter(connectionString))
            {
                var qf = new QueryFactory();
                var q = qf.Create().Select(qf.InvoiceSummary.Where(InvoiceSummaryFields.FreeForUseQuantity > 5).Any());//FreeForUseQuantity is int type
                if (count != null && count > 0)
                {
                    **var existv = context.FetchScalar<bool>(q);//error**
                    exists = existv;
                }
            }

the error message is below

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Dynamic SQL Error SQL error code = -804 Data type unknown. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error SQL error code = -804 Data type unknown ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error SQL error code = -804 Data type unknown at FirebirdSql.Data.Client.Native.FesDatabase.ProcessStatusVector(IntPtr[] statusVector) at FirebirdSql.Data.Client.Native.FesStatement.Prepare(String commandText) at FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) --- End of inner exception stack trace --- at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.<>c__DisplayClass12_0.<Execute>b__0() at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func1 toExecute) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) --- End of inner exception stack trace --- at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQueryAsProjection(IDataAccessAdapter adapter, DynamicQuery query, IGeneralDataProjector projector, Boolean requireDBNullPostProcessing) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery(IDataAccessAdapter adapter, DynamicQuery query) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchScalar[TValue](IDataAccessAdapter adapter, DynamicQuery query) at webApi.Form1.CheckForBookeInvNumber5Sec_Elapsed(Object sender, ElapsedEventArgs e) in C:\Users\alonso_c.PARTNER\Documents\job\source code\webApiForPosAp\webApi\webApi\Form1.cs:line 334

Please enable DQE tracing to see the SQL query generated (https://www.llblgen.com/Documentation/5.3/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#conventions )

Though it might seem the types returned by firebird embedded doesn't match what it supports. I'll ask Jiri (dev of firebird client) what it can be.

Yeah. The SQL is needed. Looks like the generated SQL is something that Firebird does not support. In this case I would guess it's parameter in where clause that's not bound to some value/column, hence the type isn't inferred by engine.

Otis wrote:

Please enable DQE tracing to see the SQL query generated (https://www.llblgen.com/Documentation/5.3/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#conventions )

Though it might seem the types returned by firebird embedded doesn't match what it supports. I'll ask Jiri (dev of firebird client) what it can be.

the generated sql is

FirebirdSql.Data.FirebirdClient Information: 0 : Command: SELECT FIRST 1 CASE WHEN CASE WHEN EXISTS (SELECT "InvoiceSummary"."FreeForUseQuantity" FROM "InvoiceSummary" WHERE ( ( "InvoiceSummary"."FreeForUseQuantity" > @p1))) THEN 1 ELSE 0 END=1 THEN @p3 ELSE @p5 END AS "LLBLV_1" FROM "InvoiceSummary" Parameters: Name:@p1 Type:Integer Used Value:5 Name:@p3 Type:Boolean Used Value:True Name:@p5 Type:Boolean Used Value:False

here is the creat sql file contecnt

-- -------[ Tables ]-----------------------------------------------------------------------------------------------

CREATE TABLE "BussinssInfo"
(
    "Branchno" VARCHAR(20) NOT NULL, 
    "Businessid" VARCHAR(10) NOT NULL, 
    "Pid" VARCHAR(20) NOT NULL, 
    "Pname" VARCHAR(100) NOT NULL, 
    "Posno" VARCHAR(20) NOT NULL, 
    "QrCode" VARCHAR(50) NOT NULL, 
    "Rid" VARCHAR(50) NOT NULL, 
    "Sn" BIGINT NOT NULL 
);

CREATE TABLE "InvoiceNumber"
(
    "LastTradeState" VARCHAR(5), 
    "LastUpdateTime" DATE NOT NULL, 
    "Period" VARCHAR(5) NOT NULL, 
    "TrackPlusNumber" VARCHAR(50) NOT NULL 
);

CREATE TABLE "InvoiceSummary"
(
    "Period" VARCHAR(50) NOT NULL, 
    "Sn" BIGINT NOT NULL, 
    "BeginNo" VARCHAR(8) NOT NULL, 
    "EndNo" VARCHAR(50) NOT NULL, 
    "Status" VARCHAR(8) NOT NULL, 
    "Track" VARCHAR(50) NOT NULL, 
    "FreeForUseQuantity" INTEGER NOT NULL 
);
-- -------[ Sequences ]--------------------------------------------------------------------------------------------

CREATE GENERATOR "serailGeneratorOne";

CREATE GENERATOR "serialGeneratorTwo";

-- ###############################################################################################################
-- Create statements for Primary key constraints, Foreign key constraints, Unique constraints and Default Values
-- ###############################################################################################################
-- ----------------------------------------------------------------------------------------------------------------
-- Catalog 'Default'
-- ----------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------
-- Primary Key constraints for schema 'Default'
-- ----------------------------------------------------------------------------------------------------------------

ALTER TABLE "BussinssInfo"
    ADD CONSTRAINT "PK_7bcf36448879a519984b1edfc5a" PRIMARY KEY
    ( 
        "Sn" 
    );

ALTER TABLE "InvoiceNumber"
    ADD CONSTRAINT "PK_9fd686a4be2a5201652830f9887" PRIMARY KEY
    ( 
        "TrackPlusNumber" 
    );

ALTER TABLE "InvoiceSummary"
    ADD CONSTRAINT "PK_c3e1e794d9fbcac0a492ba8eaa0" PRIMARY KEY
    ( 
        "Sn" 
    );

Yep. The generated SQL needs some small tweaking. This piece "THEN @p3 ELSE @p5 END" has parameters without types (Firebird needs to be able to infer types from the SQL and or metadata, it's not using any data from client). Hence it should rather be something like "THEN CAST(@p3 AS xxx) ELSE CAST(@p5 as xxx) END". That will make Firebird happy.

by the way, i found the walkaround method is to use LinqMetaData to query is fine!

cincura.net wrote:

Yep. The generated SQL needs some small tweaking. This piece "THEN @p3 ELSE @p5 END" has parameters without types (Firebird needs to be able to infer types from the SQL and or metadata, it's not using any data from client). Hence it should rather be something like "THEN CAST(@p3 AS xxx) ELSE CAST(@p5 as xxx) END". That will make Firebird happy.

This is a little strange, though it somehow does sound familiar too, as the parameters do have types, it doesn't infer the types from the parameters coming in? casting them is a bit problematic to do automatically I think, as the case statement is generated using a pattern. I have to test whether a cast to int added to the pattern (It's the IIF function call mapping) works always in this situation.

In linq they are cast, hence I think the reason why it works in the linq provider, which does contain the cast. The query is a bit convoluted due to the Any() helper method. Typical query could be to simply fetch the ID with an exists predicate, which would result in 0 or 1 rows, and check if there's a row returned, if not, it doesn't exist.

We'll fix this in the next release (v5.4), as it requires more work than a quick fix, as the pattern (the IIF pattern) is the one used by the Functions.IIF() function in queryspec, and the Any() call simply uses that to build the query. But IIF() of course can return any type as the result, so additional code is needed to fix this.

Correct. The command is processed before parameters are taken into account. Thus types from parameters are not used at that stage.

Ok, good to know The parameters are coming from the correction to have a predicate in the projection (to make it a boolean expression that works in a projection of a sql query). I think we can safely wrap those in casts. Will be corrected in next minor version (5.4)

is there any suggesstion to deal with it? using linq or replace Any() with Functions.IIF()?

alonsoChen wrote:

is there any suggesstion to deal with it? using linq or replace Any() with Functions.IIF()?

Any() uses an EXISTS query, which can be built with extension methods, the problem is that it results in a boolean predicate, but that's not usable in a projection in a SELECT statement, it requires real values (even if that's a boolean). So the boolean predicate is converted to 1 or 0 using an internal IIF mapping, which is the outer CASE statement you're seeing, so the predicate results in 1 or 0.

I haven't tried, but you could do: var q = f.Create().Select(qf.InvoiceSummary.Where(InvoiceSummaryFields.FreeForUseQuantity > 5).Any().AsBooleanValue());

which pre-formats the predicate produced by 'any' as a case WHEN <predicate> THEN 1 ELSE 0, but with hardcoded 1 and 0 values, so no parameters.

Again, not tested, but I think this would avoid the problem with the parameters.

Otis wrote:

I haven't tried, but you could do: var q = f.Create().Select(qf.InvoiceSummary.Where(InvoiceSummaryFields.FreeForUseQuantity > 5).Any().AsBooleanValue());

tested and work.Tks

Implemented in v5.4