Mapped function

Posts   
 
    
mohamed avatar
mohamed
User
Posts: 136
Joined: 10-Mar-2008
# Posted on: 23-Nov-2009 19:06:59   

I have to implement sql function in my linq query

the issue that stopped me is can't use the mapped function to variable (it works for field)


public class AMSFunctions
   {
      public AMSFunctions()
      {}

      public static DateTime GetTimeOnly(DateTime dateTime)
      {
         return DateTime.Now;
      }
      public static DateTime GetDateOnly(DateTime dateTime)
      {
         return DateTime.Now;
      }
   }

public class AMSFunctionMappings : FunctionMappingStore
   {
      public AMSFunctionMappings()
         : base()
      {
         this.Add(new FunctionMapping(typeof(AMSFunctions), "GetTimeOnly", 1,
                         "GetTimeOnly({0})", "AMS", "dbo"));

         this.Add(new FunctionMapping(typeof(AMSFunctions), "GetDateOnly", 1,
                            "GetDateOnly({0})", "AMS", "dbo"));
      }

   }




 public static bool IsPostedGroup(int groupID, DateTime trxDate)
      {
         var toReturn = true;
         var fn=new DataAccess.AMSFunctionMappings();
         var metaData = DataAccess.LinqMetaDataFactory.Create(fn);

         var ss = metaData.Transactions
                  .Where(t => t.TrxDate ==
                                   metaData.Transactions.Where(td => td.InvestorNo == groupID).Select(td => td.TrxDate).Max()
                         && t.TrxTime ==
                                   metaData.Transactions.Where(tt => tt.InvestorNo == groupID).Select(tt => tt.TrxTime).Max()
                         && t.InvestorNo == groupID
                         && AMSFunctions.GetDateOnly(t.TrxDate) >= AMSFunctions.GetDateOnly(trxDate)    //  <=  this is part can't call sql function
                        )
                  .Select(t => t);
        var transactionEc= ((ILLBLGenProQuery)ss).Execute<EntityCollection<TransactionsEntity>>();

        if (transactionEc.Count > 0)
            toReturn = true;
         else
            toReturn = false;

         return toReturn;
      }

Is this bug or wrong implement calling function...

VS 2008 SQL 2008 adapter mode

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 24-Nov-2009 06:16:23   

I guess that when you pass an in-memory variable, the expression tree evaluator treat the function like an in-memory function.

Anyway, what is your runtime library version? and What is the exact exception message and stack trace?

Also, How your db function looks like? Is that special? Coz LLBLGen comes with a serie of predefined function mappings. In this case you could use the "Date" property of the dates of both the field and the constant (the later is evaluated with the in-memory Date .net function):

&& t.TrxDate.Date >= trxDate.Date
David Elizondo | LLBLGen Support Team
mohamed avatar
mohamed
User
Posts: 136
Joined: 10-Mar-2008
# Posted on: 24-Nov-2009 11:01:06   

daelmo wrote:

I guess that when you pass an in-memory variable, the expression tree evaluator treat the function like an in-memory function.

Yes.. it treats in-memory variable with an in-memory function not SQL function

daelmo wrote:

Anyway, what is your runtime library version? and What is the exact exception message and stack trace?

SD.LLBLGen.Pro.LinqSupportClasses.NET35 file version 2.6.9.1008

there is no exception message it is use in-memory function " AMSFunctions.GetDateOnly" instead of sql function

daelmo wrote:

Also, How your db function looks like? Is that special? Coz LLBLGen comes with a serie of predefined function mappings. In this case you could use the "Date" property of the dates of both the field and the constant (the later is evaluated with the in-memory Date .net function):

&& t.TrxDate.Date >= trxDate.Date

yes. I know that but i have many sql function other than GetDateOnly its like simple example

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Nov-2009 13:56:15   

It's not executed in the DB as the variable isn't known in the db, it IS in memory. So the provider will determine that the method call (with the variable) is all in-memory and will compile it and run it as-is. It does this to be able to consume in-memory code inside a query, which would otherwise be impossible.

so to overcome this, make your methods which you use to map (the .NET implementations) actually do something useful inmemory, it will then work as intended.

Frans Bouma | Lead developer LLBLGen Pro
AlbertK
User
Posts: 44
Joined: 23-Dec-2009
# Posted on: 14-Dec-2010 18:01:57   

Otis wrote:

so to overcome this, make your methods which you use to map (the .NET implementations) actually do something useful inmemory, it will then work as intended.

Hello, I'm experiencing a very similar situation, but I don't understand the solution above. In other words, how do you pass a date value from code to a mapped database function such that it triggers a database call and not in-memory version? Could you provide an example?

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 14-Dec-2010 21:00:37   

1) Please don't reopen old threads, as it makes it awkward for us to track them simple_smile

2) The documentation on SQL/LINQ function mapping is here - have a read through and let us know if there is anything more specific we can help with.

Matt