Hi there,

I implemented the solution shown here:

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

but I think it's a bit awkward and I'd like to know if there's any progress on 0-parameter Function Mappings and/or default Function Mapping for random order.

Regards, André

Hi Andé,

Not workaround for 0-params function mappings. You will have to work with the workaround mentioned, or use our normal LLBLGen API.

function mappings can deal with 0 parameters, so I don't really see what you're referring to when you say '0-parameter function mappings'. The workaround specified is actually the mechanism to order on a 'random' number in sqlserver. It's not that random either btw, as newid()'s results don't necessarily have a high entrophy

Thanks for your answer.

My goal was not really to elaborate on the randomness of newid, but if my approach on fetching random rows from the database was right, given the fact that I need them in random order from the database, as the underlying table has many entries. For my needs the limited entrophy of newid() is not an issue. I just don't know another method to get random rows from a table.

When I try to define the above solution using a parameter-less function mapping I don't get the same results. The query is different too, missing any ORDER clause at all.

Extension definition

public static class GuidExtensions {
    public static Guid Random(Guid whatever) {
        throw new NotImplementedException();
    }

    public static Guid Random() {
        return Guid.NewGuid();
    }
}

Code:

public void TestFilter() {
    using (var adapter = new DataAccessAdapter()) {
    var metaData = new LinqMetaData(adapter){
                                                CustomFunctionMappings = new FunctionMappingStore()
                                            };

        metaData.CustomFunctionMappings.Add(new FunctionMapping(typeof(GuidExtensions), "Random", 0, "newid()"));

        var query = from t in metaData.ContentItem
                        orderby GuidExtensions.Random()
                        select t;

        foreach (var i in query) {
        Console.WriteLine("Guid: "+ i.Guid);
    }

        metaData.CustomFunctionMappings.Add(new FunctionMapping(typeof(GuidExtensions), "Random", 1, "dbo.fn_newID({0})"));

        query = from t in metaData.ContentItem
                                orderby GuidExtensions.Random(t.Guid)
                                select t;

        foreach (var i in query) {
            Console.WriteLine("Guid: " + i.Guid);
        }
  }         
}

The first linq query creates the following sql query:

SELECT DISTINCT [LPA_L1].[id] AS [F3_0], [LPA_L1].[guid] AS [F3_1], [LPA_L1].[ID_Rating] AS [F3_2], [LPA_L1].[created] AS [F3_3], [LPA_L1].[creator] AS [F3_4], [LPA_L1].[modified] AS [F3_5], [LPA_L1].[modifier] AS [F3_6], [LPA_L1].[saleAmount] AS [F3_7], [LPA_L1].[viewCount] AS [F3_8], [LPA_L1].[salesCount] AS [F3_9], [LPA_L1].[orientation] AS [F3_10], [LPA_L1].[validFrom] AS [F3_11], [LPA_L1].[validTo] AS [F3_12], [LPA_L1].[active] AS [F3_13], [LPA_L2].[idContentItem] AS [F11_14], [LPA_L2].[minWidth] AS [F11_15], [LPA_L2].[maxWidth] AS [F11_16], [LPA_L2].[minHeight] AS [F11_17], [LPA_L2].[maxHeight] AS [F11_18], [LPA_L2].[fitMethod] AS [F11_19], [LPA_L2].[allowImageUpload] AS [F11_20], [LPA_L2].[numberPages] AS [F11_21] FROM ( [dbContent].[dbo].[tblContentItem] [LPA_L1]  LEFT JOIN [dbContent].[dbo].[tblTemplate] [LPA_L2]  ON  [LPA_L1].[id]=[LPA_L2].[idContentItem])

The second creates the following query:

SELECT [LPA_L1].[id] AS [F3_0], [LPA_L1].[guid] AS [F3_1], [LPA_L1].[ID_Rating] AS [F3_2], [LPA_L1].[created] AS [F3_3], [LPA_L1].[creator] AS [F3_4], [LPA_L1].[modified] AS [F3_5], [LPA_L1].[modifier] AS [F3_6], [LPA_L1].[saleAmount] AS [F3_7], [LPA_L1].[viewCount] AS [F3_8], [LPA_L1].[salesCount] AS [F3_9], [LPA_L1].[orientation] AS [F3_10], [LPA_L1].[validFrom] AS [F3_11], [LPA_L1].[validTo] AS [F3_12], [LPA_L1].[active] AS [F3_13], [LPA_L2].[idContentItem] AS [F11_14], [LPA_L2].[minWidth] AS [F11_15], [LPA_L2].[maxWidth] AS [F11_16], [LPA_L2].[minHeight] AS [F11_17], [LPA_L2].[maxHeight] AS [F11_18], [LPA_L2].[fitMethod] AS [F11_19], [LPA_L2].[allowImageUpload] AS [F11_20], [LPA_L2].[numberPages] AS [F11_21] FROM ( [dbContent].[dbo].[tblContentItem] [LPA_L1]  LEFT JOIN [dbContent].[dbo].[tblTemplate] [LPA_L2]  ON  [LPA_L1].[id]=[LPA_L2].[idContentItem]) ORDER BY dbo.fn_newID([LPA_L1].[guid]) ASC

Maybe I didn't quite understand the definition process and usage of function mappings.

Regards, André

Reproduced, looking into it.

The first one is not ending up in the query because it's seen as a local method call. try adding throw new NotImplementedException(); instead of the return new guid, in random() and you'll see that it is called during execution.

The problem is as follows: linq queries can contain in-memory calls and calls which are translated to DB constructs. It might be tempting to say: 'but I have a mapping defined, so that should be converted first!', but that's actually almost impossible to do, as in-memory constructs can take any shape or form, making it very hard to parse them. So instead, a visitor is used to determine if a tree branch contains a reference to an element which is used inside the db, like a table field (it actually determines this by checking whether a piece of the tree refers to another part of the tree through a parameter). If so, it will not be converted to an in-memory lambda (by simply compiling the whole expression tree branch and executing it).

As very complex mixtures of code can be constructed in a linq query, it's impossible to determine whether your call to a method is really a method which should be converted to a db construct or that it should be converted to an in-memory call. So in short your Random() call will never make it to the visitor which converts method calls to functions if they have a mapping, as it has been converted before that.

So it's not really solvable the way you want it to be unfortunately, it requires the workaround with the db function.

Thanks a lot, Frans, that made it a lot clearer to me. Much appreciated.

Regards, André