I have two issues, I think both are related:

First issue:

Given a TSQL UDF that returns a BIT and doing a standard function mapping to a function like:

      public static int SomeTestFunction(int someId)
        {
            return 0;
        }

Then calling that function in linq works, it returns a 1 or 0. However, the rest of LLBLGen maps bit to boolean so I tried:

      public static bool SomeTestFunction(int someId)
        {
            return false;
        }

It crashes.
The reason is when it is an INT the DQE emits a query like

[db].[dbo].SomeTestFunction(bla)

When it is a boolean mapped function, the DQE emits a query like

CASE WHEN [db].[dbo].SomeTestFunction(bla) THEN 1 ELSE 0 END 

So, when doing function mappings, is there a way we can make this work, or do I have to keep returning INT values and then comparing them to 1 or 0? This has actually a pain point for me for a while; I just worked around it; but finally decided I should report it to see if it could be fixed

Crash data (doubt you need) An exception was caught during the execution of a retrieval query: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'. Incorrect syntax near 'LPLA_1'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, QueryParameters parameters) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source)

Second issue:

Given the same TSQL UDF that returns a BIT and doing a standard function mapping to a function like:

      public static int SomeTestFunction(int someId)
        {
            return 0;
        }

I then execute the following linq:

var data = (from table in metaData.Table
                  where table.pKey=12
                  select MyCustomFunctionMapping.SomeTestFunction(table.pKey)).FirstOrDefault()

That works just fine...the issue is sometimes when writing the above since I know I am getting one row and table.pKey will equal 12, I may write...

var data = (from table in metaData.Table
                  where table.pKey=12
                  select MyCustomFunctionMapping.SomeTestFunction(12)).FirstOrDefault()

This will not map to the TSQL function at all and simply returns the result of the extension defined above (a zero). In the case of extensions that are function mappings it should always result in TSQL code calling it, even if the only values being passed are constants!

(of course I do not even need that table or anything, but hey, we have already hashed this out and it is on the 'feature' list: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=22702 )

So, at the end of the day for both scenarios, the DQE needs to do different things based on a regular extension being called vs a linq function mapping extension being called.

Thoughts?

4.1.14.225 OrmSupport 4.1.14.110 DQE Sql Server

Regarding the first issue, there is a bool function example in the docs (FullTextSearch). Could you please provide the FunctionMappingStore method?

Additionally, for your first issue:

You are telling LLBLGen that your function returns a bool, but in the real world your function returns a bit or an int, whatever. To make sure, LLBLGen wraps the call in a CASE THEN ELSE END statement. Then the problem is that the structure of the CASE is something like

CASE WHEN <condition> THEN <value>

but dbo.MyFunction is not a <condition> per-se, it's a value. To make the function to work, turn it into a condition. This is a workaround for your 1st issue:

public class NorthwindFunctions
{
    public static bool IsOrderShippedAsBool(int orderId)
    {
        return false;
    }
}

public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "IsOrderShippedAsBool", 1,
                            "IsOrderShipped({0}) = 1", "Northwind", "dbo"));
    }
}

HTH

WayneBrantley wrote:

So, at the end of the day for both scenarios, the DQE needs to do different things based on a regular extension being called vs a linq function mapping extension being called.

For the second issue, I think this will end in a debate as this has been mentioned before. (i.e.: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20999&StartAtMessage=0&#118370 ). Linq tries to understand your function and check whether a function should be called on DB or in-memory. Similar to String.ToUpper(), if 'string' is a local variable you don't need the DB function, if 'string' is a entity field then you need the DB function. Your custom function mapping is an extension of that, is not that different, the only difference is that you wrote it.

daelmo, I have now read the other thread and you are right, tough debate. Thank you for the workaround and your response.

Ok, now I have located what appears to be a bug with this!

Given this:

public class NorthwindFunctions
{
    public static bool IsOrderShippedAsBool(int orderId)
    {
        return false;
    }
}

public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "IsOrderShippedAsBool", 1,
                            "IsOrderShipped({0}) = 1", "Northwind", "dbo"));
    }
}

if you do linq like this:

var data = (from table in metaData.Orders
                 where table.pKey=12
                 select  NorthwindFunctionMappings.IsOrderShippedAsBool(table.pKey)).FirstOrDefault()

It works find and the DQE emits a CASE statement, just like you said. However, if you do linq like this:

public class MyData { public bool? IsShipped {get;set;} }

var data = (from table in metaData.Orders
                 where table.pKey=12
                 select new MyData {
                      IsShipped=NorthwindFunctionMappings.IsOrderShippedAsBool(table.pKey)
                 }
                 ).FirstOrDefault()

The DQE will not use a case statement and instead output the call as you coded it in the mapping like:

[Northwind].[dbo].IsOrderShipped([LPA_L2].[pKey], GetDate()) = 1 AS [IsShipped]

For now, I fixed it in my current code, by not having a bool?, but instead having bool.... So, if you are projecting into a nullable boolean column, it leaves off the case statement resulting in bad SQL.

Can't reproduce what you are saying. See this:

Mappings

public class NorthwindFunctions
{
    public static bool IsOrderShippedAsBool(int orderId)
    {
        return false;
    }
}

public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "IsOrderShippedAsBool", 1,
                            "IsOrderShipped({0}) = 1", "Northwind", "dbo"));
    }
}

Test

public class MyData
{
    public int OrderId { get; set; }
    public bool IsShipped { get; set; }
}

[TestMethod]
public void FunctionMappingAsIntWithDiscreteParamUsingLinq2()
{
    using (var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        metaData.CustomFunctionMappings = new NorthwindFunctionMappings();

        var q = (from o in metaData.Order
                    select new MyData
                    {
                        OrderId = o.OrderId,
                        IsShipped = NorthwindFunctions.IsOrderShippedAsBool(o.OrderId)
                    })
                .FirstOrDefault();
    }
}

Generated SQL

SELECT TOP(@p2) [LPLA_1].[OrderID] AS [OrderId],
                CASE
                  WHEN [Northwind].[dbo].IsOrderShipped([LPLA_1].[OrderID]) = 1 THEN 1
                  ELSE 0
                END             AS [IsShipped]
FROM   [Northwind].[dbo].[Orders] [LPLA_1] 

daelmo, Your MyData class has to have a nullable boolean field:

public class MyData
{
    public int OrderId { get; set; }
    public bool? IsShipped { get; set; }
}

Try that it will reproduce!

WayneBrantley wrote:

Try that it will reproduce!

Reproduced! Looking further into it...

Fixed.

I've checked for other occurrences of the same mistake, and corrected them too.

Ok, thanks....I moved to the non-null boolean so, I am not going to install/use your fix at this point. I will just wait until I move to 4.2, but at least this little bug is squashed for future uses and others.

Thanks again.