Hi Guys,

I need to return a set of results that are randomly ordered. I have seen this thread below:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1746

but to be honest can't get my head around what Otis has suggested. Can anyone help?

Cheers

Gareth

That thread was not one of my bests... I made several mistakes in one thread...

(the codebase is so big I don't remember every construct from my bare head anymore, and I should have looked it up before suggesting something).

The Order By clauses are generated in the DQE, in a loop. The loop constructs a fieldname and appends the operator. The thought I had was, (before checking it) that ISortClause could produce that fieldname without a problem. However that is not the case, ISortClause only contains data which is used by the database specific generator object to produce a name, and in the case of SqlServer, it always produces [table].[fieldname] or similar names, so that's not it.

However, what CAN be done, and it's quite simple, is this. As the OrderBy routine looks like this:

/// <summary>
/// Appends an ORDER BY clause to the query specified.
/// </summary>
/// <param name="queryText">query text currently being build</param>
/// <param name="sortClauses">sort clauses collection</param>
/// <param name="creator">Creator for sqlserver specific sql</param>
private static void AppendOrderByClause(StringBuilder queryText, ISortExpression sortClauses, IDbSpecificCreator creator)
{
    // append ORDER BY plus the sort clauses
    queryText.Append(" ORDER BY ");
    for(int i = 0; i < sortClauses.Count; i++)
    {
        ISortClause sortClauseToUse = sortClauses[i];
        if(i > 0)
        {
            queryText.Append(",");
        }
        string fieldName = sortClauseToUse.FieldToSortCore.Alias;
        if((sortClauseToUse.FieldToSortCore.ExpressionToApply==null) && (sortClauseToUse.FieldToSortCore.AggregateFunctionToApply==AggregateFunction.None))
        {
            fieldName = creator.CreateFieldName(sortClauseToUse.PersistenceInfo, sortClauseToUse.FieldToSortCore.Alias, sortClauseToUse.ObjectAlias, false);
        }

        queryText.AppendFormat("{0} {1}", fieldName, creator.ConvertSortOperator(sortClauseToUse.SortOperatorToUse));
    }
}

you see that the fieldName is the alias specified in the field if an expression and/or aggregate function is specified with the field (because the expression is in the select list etc.)

So to mislead this routine, you could try: (haven't tested this, but looking at this routine THIS TIME it should work )

Below is for selfservicing, if you're using adapter, let me know.

// create empty field object, doesn't matter IEntityField newIDField = new EntityField(); newIDField.Alias = "NEWID()"; newIDField.ExpressionToApply = new Expression();

ISortExpression sorter = new SortExpression(new SortClause(newIDField, SortOperator.Ascending));

As ExpressionToApply is not null, it will pick the Alias for the fieldname and therefore should result in ORDER BY NEWID() ASC

I'm glad this got picked up, I'd not had the chance to try from your previous post.

FYI, I'm using Adapter. What changes are needed for it?

Thanks!

Not much, you need to set the Fieldname as well, and you have to pass null (nothing) as well to the SortClause constructor. That would be it, I think. Again, I haven't tested it myself, so perhaps some issue pops up with the PersistenceInfoFactory, as you pass in an empty field. If that's the case, use teh EntityFieldFactory to create an existing field (doesn't matter of which entity) and change the alias for that object.

Thanks, I will have a look.

Yes I'm using Adapter, and the above fails:

 Line 449:          for (int i = 0; i < sortClauses.Count; i++)
Line 450:           {
Line 451:               sortClauses[i].PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)sortClauses[i].FieldToSortCore);
Line 452:           }
Line 453:       }

Any ideas.

Cheers

Gareth

adapter: (this worked: , it creates a random field, pick one, doesn't matter)

IEntityField2 newIDField = EntityFieldFactory.Create(CustomerFieldIndex.Country);
newIDField.Name = "NEWID";
newIDField.Alias = "NEWID()";
newIDField.ExpressionToApply = new Expression();

ISortExpression sorter = new SortExpression(new SortClause(newIDField, null, SortOperator.Ascending));

Yep, that works in my stuff. Wonderful!

Now, if you'll excuse me, I'm out to find an even more obscure requirement to be created in LLBLGen!

hehe You must be singing the whole trip to work every day, or not?

As Otis suggested, I tried the following code:

Otis wrote:

// create empty field object, doesn't matter IEntityField newIDField = new EntityField(); newIDField.Alias = "NEWID()"; newIDField.ExpressionToApply = new Expression();

ISortExpression sorter = new SortExpression(new SortClause(newIDField, SortOperator.Ascending));

SQL Profilers shows that:

 exec sp_executesql N'SELECT DISTINCT TOP 1 [dbo].[Quotes].[QuoteID] AS [QuoteId],[dbo].[Quotes].[QuoteName] AS [QuoteName],[dbo].[Quotes].[Quote] AS [Quote],[dbo].[Quotes].[QuoteAuthor] AS [QuoteAuthor],[dbo].[Quotes].[QuoteCatagoryID] AS [QuoteCatagoryId],[dbo].[Quotes].[QuoteIsDeleted] AS [QuoteIsDeleted],[dbo].[Quotes].[CreateDate] AS [CreateDate],[dbo].[Quotes].[UpdateDate] AS [UpdateDate] FROM [dbo].[Quotes] 
WHERE ( [dbo].[Quotes].[QuoteIsDeleted] = @QuoteIsDeleted1) ORDER BY NEWID() ASC', N'@QuoteIsDeleted1 bit', @QuoteIsDeleted1 = 0

[b]ERROR[/b]: Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

If i remove DISTINCT from the sql query, it works. How to remove DISTINCT from GetMulti() function? Any alternative to fetch random records?

Thanks. Developer

And if you set the Name property too?

Otis wrote:

And if you set the Name property too?

Same error message, as it generates same SQL using DINSTICT.

Regards, Developer

Developer wrote:

Otis wrote:

And if you set the Name property too?

Same error message, as it generates same SQL using DINSTICT.

hmmm, well on adapter it also generates the DISTINCT, so I'm a bit puzzled why it bugs in selfservicing. I'll do some testing.

The selfservicing templates always pass 'false' for allowDuplicates, even if there are no relations passed in. Adapter tests if there are relations present and if not, it doesn't set allowDuplicates to false, but passes in true.

I'll file this as a bug for the selfservicing templates. I don't think I've time to fix this tomorrow, so expect a fix next week (monday or so).

A new set of sharedtemplates is available which should fix this. Remember: the NEWID trick doesn't work if you pass in relations in your fetch, it only works with filters solely on the entity set you're also fetching (so no relations, otherwise DISTINCT is emitted)

Using adapter and ForceName, we tried the code above and get the following error:

        /// <summary>
        /// Handles the catalog name inside the passed in FieldPersistenceInfo object. Uses the setting set in catalogNameUsageSetting.
        /// </summary>
        /// <param name="persistenceInfo">Field persistence info object which catalog name should be altered or not.    </param>
        private void HandleCatalogName(IFieldPersistenceInfo persistenceInfo)
        {
            switch(_catalogNameUsageSetting)
            {
                case CatalogNameUsage.Default:
                    // do nothing
                    return;
                case CatalogNameUsage.ForceName:
                    // write the name specified as the catalog name
                    persistenceInfo.SourceCatalogName = _catalogNameToUse;
                    break;
                case CatalogNameUsage.Clear:
                    persistenceInfo.SourceCatalogName = String.Empty;
                    break;
            }
        }

System.NullReference on persistenceInfo.SourceCatalogName = _catalogNameToUse;

For testing purposes, We added a null test and exit the method, which seems to work fine.
Any advice? Thanks!

The problem is in the routine: DataAccessAdapter.GetFieldPersistenceInfo(). This is a protected routine in the generated code. What you can do to work around this is:

Create a derived class from DataAccessAdapter, then override GetFieldPersistenceInfo and in that override copy the code generated in the DataAccessAdapter.GetFieldPersistenceInfo() however add some logic:

/// <summary>
/// Retrieves the persistence info for the field passed in. 
/// </summary>
/// <param name="field">Field which fieldpersistence info has to be retrieved</param>
/// <returns>the requested persistence information</returns>
protected override IFieldPersistenceInfo GetFieldPersistenceInfo(IEntityField2 field)
{
    IFieldPersistenceInfo persistenceInfo = PersistenceInfoFactory.GetFieldPersistenceInfo(field.ContainingObjectName, field.Name);
    if(persistenceInfo!=null)
    {
        return base.GetFieldPersistenceInfo(field);
    }

    return null;
}

This will then be called by the InsertPersistenceInfoObject() methods which will insert the persistence info. When a field is found, it will work as before. If it isn't, which is the case in the NEWID case (as that fieldname isn't there) it simply returns null.  :) 

This worked perfectly.
Thanks Frans