In your example: http://www.llblgen.com/TinyForum/Threads.aspx?ForumID=10

Will the function call results be cached also, like adding ToCacheResultset() call:

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = (from o in metaData.Order
        where o.CustomerId == "CHOPS"
        select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) ).ToCacheResultset(300);

I the above example, it would produce SQL like:

select OrderId, dbo.CalculateOrderTotal(OrderId, true) from Orders where CustomerID="CHOPS"

Let's say in I already knew the OrderId value. So, what I really want to run is on SQL is:

select dbo.CalculateOrderTotal(@p1, true) 

Is there a way to do that? Some sort of syntax or dummy table or something such that a pointless query against a table is not done?

I am currently achieving this through a DbFunctionCall.GetScalar() method. But I would like to use the linq, because I would like it to integrate with the caching. (or an easy way to have the caching integrate to the DbFunctionCall. GetScalar(cacheTime:300) )

Otis I already shared copies (6 or 8 months ago) of the code I have to write to make all this happen for a 'use case example' of how to support functions directly in LLBLGen...and it works fine, but now I want these function calls cached easily...

The link you posted above is not a thread. And, if possible, post the other link about the DBFunctionCall.GetScalar() code.

How are you doing that already? and What LLBLGen version are you using?

Sorry, pasted wrong link: http://www.llblgen.com/documentation/4.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm#ExampleCustomMappings

LLBLGen 4.1....I have been doing this since 2.6.

DBFunctionCall.GetScalar() - I have previously sent Otis all code, but...

    public static object GetScalar(IExpression expressionToApply, ITransaction transaction)
    {
      ResultsetFields resultsetFields = new ResultsetFields(1);
      resultsetFields.DefineField((IEntityFieldCore) new EntityField("AFld", expressionToApply), 0);
      return new TypedListDAO().GetScalar((IEntityFields) resultsetFields, transaction, (IPredicate) null, (IRelationCollection) null, (IGroupByCollection) null);
    }

Where IExpression is:

      return (IExpression) new CachedDbFunctionCall("dbo", "ActualNameOfFunction", new object[3]
      {
        (object) param1,
        (object) param2,
        (object) param3
      });

And CachedDbFunctionCall derives from DbFunctionCall, just adds caching of the query text.

Lots more background info here daelmo: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10619

A single value returning query (like the fetch scalar is) isn't a resultset and therefore isn't cacheable. You'll see that when you write the linq query with FirstOrDefault, it's no longer an IQueryable but a single value. The framework isn't caching single values, only resultsets

FirstOrDefault is not cached? I do want to cache that result just like any other and reading this thread, I thought that was cached now? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21846

No, single/scalar results are not cached, only sets. So your scalar is read from the DB every time. This is by design as there's no way to specify the cache directives on a scalar query (as the query is executed immediately, it's not an IEnumerable/IQueryable).

I am really confused now...seems like the thread says it will work (after your fix last year) and seems like you are saying it will not work now. So, can you clear it up for me?

You say 'scalar' is not cached, but the link I posted above is from where you 'fixed' the code as it was not caching 'FirstOrDefault()' linq queries.

So, if I do this:

var customer=(from cust in metadata.Customer where cust.CustomerId=3 select cust).CacheResultset(10).FirstOrDefault();

Is that cached? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21846 says it was not cached and now is?

I think it is just as important to cache that as it is a 'list'. Are you saying I have to do this: var customer=(from cust in metadata.Customer where cust.CustomerId=3 select cust).CacheResultset(10).ToList();

..sorry...just really confused at this point...

After your post, I'm confused too! So I'll check it out

[Test]
public void FirstOrDefaultCacheTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                where c.CustomerId == "ALFKI"
                select c;
        q = q.CacheResultset(2);

        var q1 = q.FirstOrDefault();
        var q2 = q.FirstOrDefault();

        var q3 = q.Any();
        var q4 = q.Any();
    }
}

q2 and q4 are not fetching from the DB, q1 and q3 do. So you were right, the query is cached. Thinking about it, it's logical as the data is fetched using sets in Linq. So it can be made possible to do so.

So with linq you can make your query cachable, add a function mapping to a custom function mapping class and pass that to the linqmetadata to make sure you can execute the function.

Ok, that makes me feel better.

Now back to the original question post, is there a way to call the functions without involving a table that is unused anyway?

Given this:

select OrderId, dbo.CalculateOrderTotal(OrderId, true) from Orders where CustomerID="CHOPS"

If I already knew the OrderId, I would really only need to execute this:

select dbo.CalculateOrderTotal(@p1, true)

Hate having to write this:

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = (from o in metaData.Order
        where o.OrderId == 100
        select new { OrderTotal = NorthwindFunctions.CalculateOrderTotal(100, true) ).ToCacheResultset(300);

Which would generate:

select dbo.CalculateOrderTotal(100, true) from Orders where OrderId=100

which of course has a useless where clause, causes an index seek on SQL, etc.

Wondering if there was some linq syntax or extension that we could use that would let it know to not use the table...

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = (from o in metaData.Order
        where o.OrderId == 100
        select new { OrderTotal = NorthwindFunctions.CalculateOrderTotal(100, true) ).FunctionCallOnly().ToCacheResultset(300);

If I could do the above (or something similar), I would not have to write all the code around DBFunctionCall.GetScalar() and could use Linq and caching and such...

The function mapping takes care of the scalar, so you can simply not specify the where clause and the query will then simply contain 'FROM Orders', which is OK. We can't do function call only selects ('SELECT <something>' without a FROM), there has to be a from clause. This isn't a problem, it doesn't have an effect on the function call, as the function called retrieves its own data.

Leaving the where clause results in a result set that is equal to the size of the from table! Very inefficient.

I am just trying to get an optimal query output to sql. I realize that due to Linq, I have to have a from...but was wondering if I could supply some extension that would tell your code not to output a from!

WayneBrantley wrote:

Leaving the where clause results in a result set that is equal to the size of the from table! Very inefficient.

Then don't specify it, it has no use in the query anyway.

I am just trying to get an optimal query output to sql. I realize that due to Linq, I have to have a from...but was wondering if I could supply some extension that would tell your code not to output a from!

The framework will output a from clause, it's not due to linq, but due to the design of the framework's DQE engines. They all emit a from clause in normal queries. I do understand what you want, but the engines can't do that, they do everything they can to emit a from clause in the first place.

btw: the 'call a function' feature is on the TODO list already but requires more than a runtime change, and didn't make it to the v4.2 feature list. Perhaps in the future.