Otis, I am back! I have had no issues for such a long time, but you knew I would be back.

I am trying to generate SQL like this:

select left(borrowername,1), count(*)
from Claim
group by left(borrowername,1)
order by left(borrowername,1)

The code I use to do this (listed below) causes sql with two problems.

First, lets look at the order by. The SQL actually generated is

order by borrowername

which is not right and causes issues with the group by.

Second the group by. The SQL actually generated is

select left(borrowername,@p1), count(*)
from Claim
group by left(borrowername,@p2)

The problem is that I now have not grouped by the column I am selecting, because the parameter is a different name (even though it has the same value).

So, I need the order by to use the actual expression that is generated and I need the group by to use the same parameter as the field that I assigned to it.

        ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(ClaimFields.BorrowerName, 0);
        fields[0].ExpressionToApply = SqlFunctionFactory.Left(ClaimFields.BorrowerName, 1);
        fields.DefineField(ClaimFields.ClaimId, 1, AggregateFunction.CountRow);

        ISortExpression sortExpression=new SortExpression( new SortClause(fields[0],SortOperator.Ascending));
        IGroupByCollection groupBy = new GroupByCollection();
        groupBy.Add(fields[0]);

        DataTable dynList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynList, 0, sortExpression, GetClaimFilter(), null, false, groupBy, null, 0, 0);

_The call to SqlFunctionFactory above is from my add-on code in the 3rd party section, simply ends up with DbFunctionCall("LEFT", new object[] { ClaimFields.BorrowerName, 1 }); _

If you use an alias for the first field, it will be used in the ORDER BY clause:

fields[0].ExpressionToApply = SqlFunctionFactory.Left(ClaimFields.BorrowerName, 1, "The Borrower");

And for the group by part, I think it is unavoidable. Unless maybe if you try to implement your own IExpression to output the needed text without the use of parameters for the "1" part.

And for the group by part, I think it is unavoidable.

Ugh....Is there no way to use the same parameter in more than one place? Really, that is the root of the problem I guess. I dont want to do a custom IExpression and such...

Ugh....really need some support for things like this....

I guess I will have to do what we do when LLBLGen will not output the SQL we need - we make views....I had hoped to not need one..

WayneBrantley wrote:

And for the group by part, I think it is unavoidable.

Ugh....Is there no way to use the same parameter in more than one place? Really, that is the root of the problem I guess. I dont want to do a custom IExpression and such...

That's indeed the problem. My example:

SELECT LEFT(CompanyName, 1) as firstchar, count(*)
FROM Customers
GROUP BY LEFT(CompanyName, 1)
ORDER BY firstchar ASC

test code:

[Test]
public void GroupByOnFunctionCallResultTest()
{
    ResultsetFields fields = new ResultsetFields(2);
    fields.DefineField(CustomerFields.CompanyName.SetExpression(new DbFunctionCall("LEFT", new object[] { CustomerFields.CompanyName, 1 })), 0, "FirstChar");
    fields.DefineField(CustomerFields.CustomerId.SetAggregateFunction(AggregateFunction.CountRow), 1);
    DataTable results = new DataTable();
    SortExpression sorter = new SortExpression(new SortClause(fields[0], SortOperator.Ascending));
    GroupByCollection groupBy = new GroupByCollection();
    groupBy.Add(fields[0]);
    TypedListDAO dao = new TypedListDAO();
    Assert.IsTrue(dao.GetMultiAsDataTable(fields, results, 0, sorter, null, null, true, groupBy, null, 0, 0));
}

indeed results in a query like:

Generated Sql query: 
    Query: SELECT LEFT([Northwind].[dbo].[Customers].[CompanyName], @LO11) AS [FirstChar], COUNT(*) AS [CustomerId] FROM [Northwind].[dbo].[Customers]  GROUP BY LEFT([Northwind].[dbo].[Customers].[CompanyName], @LO12) ORDER BY FirstChar ASC
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @LO12 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

which of course gives the exception: TestCase 'Unittests.TestLibrary.SqlServerTests.SelfServicing.NorthwindFetchTests.GroupByOnFunctionCallResultTest' failed: System.Data.SqlClient.SqlException : Column 'Northwind.dbo.Customers.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The thing is that the groupby collection does its own query snippet creation and it doesn't know any details about the rest of the query. So it produces a parameter, as it has to, and that will be a unique new parameter.

The ToQueryText method in GroupByCollection is virtual, you can modify it if you want to, the thing is that it's hard to get this right as you don't have the info you need as that's not part of that object.

You can hack it in though. The Expression object in the ExpressionToUse property on the field does contain the parameters of the expression, IF its ToQueryText has been called before. Calling ToQueryText again (as GroupByCollection.ToQueryText does) will remove them from the expression again.

So what's best I think is this: Derive a class from DbFunctionCall. Override ToQueryText in there. Now, add a boolean flag to it which is initial false. If ToQueryText is called, and the flag is false, call the base' method, store the base' ToQueryText result in a string var and also copy over the parameters to a collection and set the flag to true. If the flag is already true when ToQueryText is called, simply clear the base' Parameter collection and copy over the parameters you saved to the base' Parameters collection and simply return the string you saved from the first ToQueryText call.

Ugh....really need some support for things like this....

I guess I will have to do what we do when LLBLGen will not output the SQL we need - we make views....I had hoped to not need one..

Well, it's a tradeoff: the emited string + accompanying parameters, can it be cached safely ? Or is it unsafe because re-using a parameter won't be accepted by the db?

There's a problem though: the Expression object doesn't know when a new query is generated and when it's simply re-used in the same query. So re-using an expression object in another query will then also simply return the text and parameters of the previous query but that's wrong, as it has to re-emit the query text. This can be determined though, with an architectural change, as the dbspecific generator knows scope information or can know this. This is the reason it's not currently build in.

You have a valid point, and I'll investigate if we can add snippet caching for this to the various elements (predicates etc.) so they re-use parameters and things like this are possible, in v2.1

So what's best I think is this: Derive a class from DbFunctionCall. Override ToQueryText in there. Now, add a boolean flag to it which is initial false. If ToQueryText is called, and the flag is false, call the base' method, store the base' ToQueryText result in a string var and also copy over the parameters to a collection and set the flag to true. If the flag is already true when ToQueryText is called, simply clear the base' Parameter collection and copy over the parameters you saved to the base' Parameters collection and simply return the string you saved from the first ToQueryText call.

For 'off the top of your head' implementation, you were very close. Problem was that if you copied the parameter collection and then re-assigned it, it caused a duplicate parameter to be added to the parameters collection of the actual command. Turns out what really needs to happen, is after the query is generated, the parameter collection needed to be cleared!

Also, the derrived class does not have access to the _databaseParameters variable and the public property is read only! So, I copied and pasted the entire source for DbFunctionCall and made the following change to the class (other than renaming it to MyDbFunctionCall).

    public List<System.Data.IDataParameter> DatabaseParameters
    {
        get { return _databaseParameters; }
        set { _databaseParameters=value; }  //added this line
    }

Now, my derrived class can do what it needs to do and here is the complete code. Until you get full snippet caching working in 2.1, can you add the above set code on the parameters property in the shipping code? Or make the _databaseParameters a protected variable?

public class CachedDbFunctionCall : MyDbFunctionCall
{
    private bool hasToQueryTextBeenCalled = false;
    private string cachedQueryText;

    public CachedDbFunctionCall(string functionName, object[] parameters)
        : this(string.Empty, string.Empty, functionName, parameters)
    {
    }


    public CachedDbFunctionCall(string schemaName, string functionName, object[] parameters)
        : this(string.Empty, schemaName, functionName, parameters)
    {
    }


    public CachedDbFunctionCall(string catalogName, string schemaName, string functionName, object[] parameters)
        : base(catalogName, schemaName, functionName, parameters)
    { 
    }

    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if (!hasToQueryTextBeenCalled)
        {
            cachedQueryText = base.ToQueryText(ref uniqueMarker, inHavingClause);
            hasToQueryTextBeenCalled = true;
        }
        else
        {
            this.DatabaseParameters = new List<IDataParameter>(); 
        }
        return cachedQueryText;
    }
}

The parameters collection is a collection, so couldn't you just call base.Properties.Clear() ?

Uh, yeah! Woops, kind of got distracted there when trying to figure out how to get it to work.

That all works good for now!

Question - the only reason to NOT use this cached version everywhere would be?

• Is it for problems in the case when the expression was used in multiple places (across multiple function calls, etc)?

WayneBrantley wrote:

Uh, yeah! Woops, kind of got distracted there when trying to figure out how to get it to work.

That all works good for now!

Question - the only reason to NOT use this cached version everywhere would be?

• Is it for problems in the case when the expression was used in multiple places (across multiple function calls, etc)?

Some people re-use expression and filter objects in between queries. So if you throw away the fields object and the expression object after one query, there's no problem. If you're re-using it in another query, it is a problem as it will then again simply emit the same string, which then is obviously wrong.

That's what I thought. So, this final code will allow this to be used everywhere in place of a regular DbFunctionCall - and if you decide you need the caching, you can just turn it on.

Thanks for your help/guidance on this!

Edit I have updated my add-on that generates many common SQL Function calls to use this new class. I will upload to you for replacement.

public class CachedDbFunctionCall : DbFunctionCall
{
    private bool hasToQueryTextBeenCalled = false;
    private string cachedQueryText;

    public CachedDbFunctionCall(string functionName, object[] parameters)
        : this(string.Empty, string.Empty, functionName, parameters)
    {
    }


    /// <summary>
    /// Initializes a new instance of the <see cref="CachedDbFunctionCall"/> class.
    /// </summary>
    /// <param name="schemaName">Name of the schema.</param>
    /// <param name="functionName">Name of the function.</param>
    /// <param name="parameters">The parameters for the function to call. Can be null (which means: no parameters)</param>
    public CachedDbFunctionCall(string schemaName, string functionName, object[] parameters)
        : this(string.Empty, schemaName, functionName, parameters)
    {
    }


    /// <summary>
    /// Initializes a new instance of the <see cref="CachedDbFunctionCall"/> class.
    /// </summary>
    /// <param name="catalogName">Name of the catalog.</param>
    /// <param name="schemaName">Name of the schema.</param>
    /// <param name="functionName">Name of the function.</param>
    /// <param name="parameters">The parameters for the function to call. Can be null (which means: no parameters)</param>
    public CachedDbFunctionCall(string catalogName, string schemaName, string functionName, object[] parameters)
        : base(catalogName, schemaName, functionName, parameters)
    { }


    private bool _cacheQueryText;
    public bool CacheQueryText
    {
        get
        {
            return _cacheQueryText;
        }
        set
        {
            _cacheQueryText = value;
        }
    }

    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if (!hasToQueryTextBeenCalled)
        {
            cachedQueryText = base.ToQueryText(ref uniqueMarker, inHavingClause);
            hasToQueryTextBeenCalled = _cacheQueryText;
        }
        else
        {
            this.DatabaseParameters.Clear(); // = new List<IDataParameter>(); // paramCopy;
        }
        return cachedQueryText;
    }
}

Thanks Wayne. I've received the updated file, I'll upload it today.

Wayne, I've to decide not to add this. The reasons are mainly: 1) a lot of the databases supported use anonymous parameter passing. This means that if twice a parameter is referenced but there's just 1 parameter, it won't work. This thus requires that generic code checks into db specific code if this feature should be enabled. 2) to determine if a predicate or expression or other element which emits query fragments is in a NEW query, it requires complex code to make this work properly. This is because the design of the query system is setup in such a way that the individual elements don't really know of one another as they don't need that info. However now they do need to know. The main issue is the fact that the same code paths are used for subqueries and normal queries, this means that a fragment in a subquery is of course in the same scope but a new query isn't. this is doable in adapter, because it creates a new DQE instance for every query, however in selfservicing there are scenario's where this isn't working, as a Dao creates a DQE object per dao instance. So one could get the situation where a typedlist dao is used to fetch multiple typedlists with the same filter. This thus goes wrong.

Ok. The above solution does solve the problem perfectly for me, so as long as that continues to work in 2.1, then I am good with it.

Thanks. BTW, I keep looking for 2.1...I am guessing any day now?

BTW, I keep looking for 2.1...I am guessing any day now?

Very close... a few days I think http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9448&StartAtMessage=0&#56398