Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Linq Function Mapping issues
 

Pages: 1
Bugs & Issues
Linq Function Mapping issues
Page:1/1 

  Print all messages in this thread  
Poster Message
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 27-May-2014 16:54:17.  
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:
Code:

     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:

Code:

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


It crashes.
The reason is when it is an INT the DQE emits a query like
Code:
[db].[dbo].SomeTestFunction(bla)

When it is a boolean mapped function, the DQE emits a query like
Code:
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 Wink


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(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 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](IQueryable`1 source)




Second issue:

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

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


I then execute the following linq:

Code:

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...
Code:

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=22702Laugh )

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
SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14498 posts
# Posted on: 28-May-2014 07:40:14.  
Regarding the first issue, there is a bool function example in the docs (FullTextSearch).
Could you please provide the FunctionMappingStore method?


  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# Posted on: 28-May-2014 08:31:04.  
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
Code:
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:
Code:
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 Wink
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# Posted on: 28-May-2014 08:45:35.  
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.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 28-May-2014 14:57:17.  
daelmo,
    I have now read the other thread and you are right, tough debate. Thank you for the workaround and your response.
SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 29-May-2014 15:52:37.  
Ok, now I have located what appears to be a bug with this!

Given this:
Code:

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:

Code:

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:

Code:

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

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


SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# Posted on: 30-May-2014 08:08:26.  
Can't reproduce what you are saying. See this:

Mappings
Code:
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
Code:
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
Code:
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]
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 30-May-2014 15:31:12.  
daelmo,
Your MyData class has to have a nullable boolean field:

Code:

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


Try that it will reproduce!


SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# Posted on: 31-May-2014 08:04:15.  
WayneBrantley wrote:
Try that it will reproduce!

Reproduced! Looking further into it...
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 02-Jun-2014 10:49:16. Goto attachments  
Fixed.

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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 02-Jun-2014 14:48:31.  
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.
SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.