Using LLBLGEN Adapter 5.8.6 I have this code

queryFactory.Create().Select(
                             queryFactory.ImosClosedVoyage.Select(ImosClosedVoyageFields.ClientsOwnId).Any((ImosClosedVoyageFields.ClientsOwnId == deserializedVoyage.voyageNo)
                                 .And(ImosClosedVoyageFields.ClientsVesselCode == deserializedVoyage.vesselCode)))

Which translates to this SQL (I added the carriage returns):

exec sp_executesql N'SELECT TOP(@p8) CASE WHEN CASE WHEN  EXISTS (
SELECT [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] AS [ClientsOwnId] FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE] 
WHERE ( ( ( [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] = @p1 AND [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_vessel_cd] = @p2)))) 
THEN 1 ELSE 0 END=1 THEN @p4 ELSE @p6 END AS [LLBLV_1] FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE]',
N'@p1 varchar(20),@p2 varchar(20),@p4 bit,@p6 bit,@p8 bigint',@p1='22558',@p2='HA1234',@p4=1,@p6=0,@p8=1

Nothing too special, no inheritance is involved. This query would be fine, except when the table has no rows, it is completely empty, and then it returns null when I would expect 1 or 0.

Have I done something wrong creating the QuerySpec ?

Thanks,

Scott

No, it's sadly how SQL works. As the table is empty, the whole case when clause won't run, the select will simply return null as there are no rows to execute this on. If I execute the same query on a table with rows, it will return 1 or 0. e.g.:

SELECT TOP(1) 
    CASE WHEN 
        CASE WHEN  
            EXISTS (
                SELECT [dbo].[Orders].[orderid] AS [ClientsOwnId] 
                FROM [dbo].[Orders] 
                WHERE ( ( ( [dbo].[Orders].[orderid] = 1 
                    AND [dbo].[Orders].[CustomerId] = 'Foo')))
            ) 
        THEN 1 
        ELSE 0 
        END=1 
    THEN 1 
    ELSE 0 
    END AS [LLBLV_1] 
FROM 
[dbo].[Orders]

will return 0 but (super is empty)

SELECT TOP(1) 
    CASE WHEN 
        CASE WHEN  
            EXISTS (
                SELECT [dbo].Super.[id] AS [ClientsOwnId] 
                FROM [dbo].[Super] 
                WHERE ( ( ( [dbo].[Super].[id] = 1 
                    AND [dbo].[super].[Name] = 'Foo')))
            ) 
        THEN 1 
        ELSE 0 
        END=1 
    THEN 1 
    ELSE 0 
    END AS [LLBLV_1] 
FROM 
[dbo].[super]

does not, it will return no rows.

To get around this, do this:

var qf = new QueryFactory();
var q = qf.Super.Select(SuperFields.Id, SuperFields.Name);
var result = adapter.FetchScalar<bool?>(qf.Create().Select(q.Any((SuperFields.Id == 1).And(SuperFields.Name == "Foo"))));

where you specify bool? as the type to return (so a nullable bool)

The difference here with linq is that 'Any()' here isn't the function that's executed (in Linq we check for null and return false), here the 'Any' is part of a projection so it depends on the rows returned by that projection.

Right, I guess I thought it could just drop the FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE] at the end as that would work in all cases (at least in SQL Server)

exec sp_executesql N'SELECT TOP(@p8) CASE WHEN CASE WHEN  EXISTS (SELECT [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] AS [ClientsOwnId] FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE] WHERE ( ( ( [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] = @p1 AND [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_vessel_cd] = @p2)))) THEN 1 ELSE 0 END=1 THEN @p4 ELSE @p6 END AS [LLBLV_1]',N'@p1 varchar(20),@p2 varchar(20),@p4 bit,@p6 bit,@p8 bigint',@p1='22558',@p2='HA1234',@p4=1,@p6=0,@p8=1

However, I suppose the implementation makes that difficult.

yowl wrote:

Right, I guess I thought it could just drop the FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE] at the end as that would work in all cases (at least in SQL Server)

exec sp_executesql N'SELECT TOP(@p8) CASE WHEN CASE WHEN  EXISTS (SELECT [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] AS [ClientsOwnId] FROM [dbo].[HSE_IMOS_CLOSED_VOYAGE] WHERE ( ( ( [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_own_id] = @p1 AND [dbo].[HSE_IMOS_CLOSED_VOYAGE].[clients_vessel_cd] = @p2)))) THEN 1 ELSE 0 END=1 THEN @p4 ELSE @p6 END AS [LLBLV_1]',N'@p1 varchar(20),@p2 varchar(20),@p4 bit,@p6 bit,@p8 bigint',@p1='22558',@p2='HA1234',@p4=1,@p6=0,@p8=1

However, I suppose the implementation makes that difficult.

I wouldn't do that, I'd specify a bool?as the type of scalar to fetch and check the HasValue on the returned value

Ok thanks. I see the extra FROM was already discussed at https://www.llblgen.com/tinyforum/Thread/24898/1.

One last thing, you mentioned Linq, is Exists possible with Linq? For me

var q = (from icv in meta.ImosClosedVoyage
  where icv.ClientsOwnId == deserializedVoyage.voyageNo && icv.ClientsVesselCode == deserializedVoyage.vesselCode
  select icv).Any();

generates a

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: 'Encountered a boolean expression which can't be handled as there's no mapping found to wrap this expression into a construct which is allowed in the projection of the SELECT statement'

I can't see the difference between mine and https://www.llblgen.com/tinyforum/Thread/24007/1 (which is an old thread admittedly).

Linq .Any() works as intended and as shown in the old thread.

Please note that in your code, .Any() will be executed at once and q will hold the boolean value. Most probably you have used q as a construct.

I see of course. Is there any way that an "Any" IQueryable can be passed to another method without it being interpreted as a construct?

More precisely

        public bool FetchExists<T>(IQueryable<T> q, string methodName = "caller not supplied")
        {
            using (var tsw = new TransactionScopeWrapperBase(methodName))
            {
                ((LLBLGenProProvider2)q.Provider).AdapterToUse = tsw.DataAccessAdapter;
                var found = (((ILLBLGenProQuery)q).Any());
                tsw.Complete();
                return found;
            }
        }

While this code works, it does not utilise EXISTS. Can it be rewritten to do so?

yowl wrote:

More precisely

        public bool FetchExists<T>(IQueryable<T> q, string methodName = "caller not supplied")
        {
            using (var tsw = new TransactionScopeWrapperBase(methodName))
            {
                ((LLBLGenProProvider2)q.Provider).AdapterToUse = tsw.DataAccessAdapter;
                var found = (((ILLBLGenProQuery)q).Any());
                tsw.Complete();
                return found;
            }
        }

While this code works, it does not utilise EXISTS. Can it be rewritten to do so?

That code doesn't compile here, as it runs into an issue with .Any() being called no an ILLBLGenProQuery object which isn't an IQueryable.

Why not do var found = q.Any(); ?

Thank you! I was using Anyfrom IEnumerable in my code, not the one from IQueryable with the correct Any it is all good.