Starting to use LLBLGen, I went through the euphoric stage of making simple updates and inserts, you start to face turning one-liners like:

'where getdate() between dt1 and dt2 and customerid not in (select cid from blacklist)'

... into LLBLGEN .NET code. It seems that this is hardly possible and today I simply don't bother doing this with the generated classes no more, going immediately for bypassing the generated data layer with raw SQL or a direct call to some stored proc.

I see the reasoning of the academic SQL hater, who finds happiness in the exersize of mapping each and every part of the SQL language into some object form with a strong datatype for each and every operand, expression and subexpression... However I am not a SQL hater, and neither do I have time for this, as real customers are waiting for my code. Soooooo, looking for solutions that are simple and effective I come to this suggestion:

Why not have, apart for the already included predicate classes, the possibility to specify a where (order by, ...) clause directly. If the generated SQL would always use predictable table aliases, for example in a Customer.GetMultiOrder() always "Customer SRC" and "Order DEST" or so, adding it would make the classes so much more versatile.

The best thing is... Nobody would actually HAVE to use it! Some people hate SQL enough to spend a sunny afternoon trying to find the 24 lines of code that will do the job in a 100% politically correct object oriented way, having tons of fun doing it, while I would have tons of fun getting a good product finished right on time using exactly the same data layer!

Henk

The reason why SQL isn't used is that you now avoid typo's in the query to process. Another way is that you can build the query using objects, in a routine for example, without string concatenation and compares.

Though I understand your point. In the second half of 2005, a textbased query engine is planned which basicly takes your query text and converts it into predicate objects and other objects. This for the people who just want to use strings.

If you want to specify a where clause in text, you can do so. All you have to do is implement IPredicate, or better: derive your class from the abstract 'Predicate' in the ORMSupportClasses, and give it a property (and constructor) which accepts your query text. Then it should simply output this text when ToQueryText is called. It then ends up in the query

First: thx for the super quick reply!

About the typo's in SQL strings: I use SqlServer2000, and really, each and every typo ("select * form" is my favorite) does get intercepted the day I program it... So I'm not worried about that. And errors like "getdate() between dt1 and dt1" instead of "and dt2" can be made in both direct string and object oriented approach. So why not go for strings? Binding variables without using the "@" syntax seems the only main advantage of oo.

Anyway: YES I am considering following that suggestion right away, in fact many people should (new release rumored??? :-)). Like a savety valve beats blowing the engine, a pure string Predicate class to bypass part of the data layer beats having to bypass the data layer completely.

There is, however, one issue. I would have to assume, when going to get the Orders of a Customer with GetMultiOrder(), that there will be one table named "Order" in the main "from" clause... I can imagine that now, or in some future realease of LLBLGen, the query will start like "select data_src.* from order data_src, order predicate_handler" or so, in which case I would be really screwed!

For the sake of... whatever, this is not going to happen, right??? ;-)

Thx again,

Henk Poell

hjpoell wrote:

First: thx for the super quick reply!

About the typo's in SQL strings: I use SqlServer2000, and really, each and every typo ("select * form" is my favorite) does get intercepted the day I program it... So I'm not worried about that. And errors like "getdate() between dt1 and dt1" instead of "and dt2" can be made in both direct string and object oriented approach. So why not go for strings? Binding variables without using the "@" syntax seems the only main advantage of oo.

Try writing a generic search form with a string based approach, it's not that easy . But strings can be easier to write, true, the OO code sometimes gets a bit verbose.

With strings it's though a problem, which language is used? No, not SQL, as it is not usable in a lot of the entity oriented commands.

Anyway: YES I am considering following that suggestion right away, in fact many people should (new release rumored??? :-)). Like a savety valve beats blowing the engine, a pure string Predicate class to bypass part of the data layer beats having to bypass the data layer completely.

Using hard strings is not recommended, I'd take the extra time to write the predicates, instead of pushing hardcoded SQL to the db.

There is, however, one issue. I would have to assume, when going to get the Orders of a Customer with GetMultiOrder(), that there will be one table named "Order" in the main "from" clause... I can imagine that now, or in some future realease of LLBLGen, the query will start like "select data_src.* from order data_src, order predicate_handler" or so, in which case I would be really screwed!

For the sake of... whatever, this is not going to happen, right??? ;-)

You already can do that Entity aliassing is already part of the runtime since 1.0.2004.1 and for example used in complex queries and typed lists with multiple times the same entity.

Well... There sure are more ways leading to Rome here. Who knows I will resolve a problem using oo predicates once. For now I'm happy that including the piece of code you suggested (see below, yes VB was choosen) in my project really solves my needs:

Public Class FreePredicate
    Inherits SD.LLBLGen.Pro.ORMSupportClasses.Predicate

    Private s As String

    Public Sub New(ByRef sNew)
        s = sNew
    End Sub

    Public Overloads Overrides Function ToQueryText(ByRef uniqueMarker As Integer) As String
        Return s
    End Function

    Public Overloads Overrides Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
        Return s
    End Function
End Class

.... as it allows me to quickly program "dirty yet very readable things" like:

Dim filter As IPredicateExpression = New PredicateExpression
filter.Add(New FreePredicate("getdate() between isnull(StartDatum,'19000101') and isnull(EindDatum,'20690101')"))
repli.DataSource = department.GetMultiMedewerker(True, filter)
repli.DataBind()

... of course I hear, in the background, a choir chanting "blasphemy, blasphemy" but hey I know it will work for the next 63 years, and came to an age where I really don't care anymore if anyone else can't sleep well because of it. Thanks, Otis!!! We'll get te project done in time!

Henk

Blasphemy!

The reason I set it up as objects is the extensibility of the system. As you can see, you can add your own predicate classes to get things done in the filtering system without a problem. You can even generate parameters if you want.

Though I'd avoid using code like this as much as possible though.