Hi all,

I need help selling LLBLGen to my fellow coders!

I love LLBLGen and would to create a brand new app and database using it, instead of hammering it into our existing app, but thats life! I've been using it for a good few months, so I think I know it pretty well. Anyway, a couple of other coders here (new to .NET) are starting to have to use it - 99% of the time on report-esque pages so they need to use a lot of predicate logic and the like.

So, when they come accross all this predicate, relation, and resultset stuff going on, they immediately shout "whats wrong with good old SQL statements?" - they know exactly what SQL they want to write, but have trouble translating this into LLBLGen code.

I'm trying to explain that its a great save on the standard CRUD logic that we'd have to do, but these guys aren't doing any "CUD", just "R" - and mostly complex listing!

Any thoughts?

Hey - about a "query builder" that feeds off generated code and generates the predicate logic for you....cool feature!!

Well obviously it's a lot easier to write "select * from users where name = 'alfred'", but that's only one smart part of the whole data access picture. The biggest problem with native ADO.NET is the lack of type safety. It's also very difficult to manually build a set of objects that related to each other the way LLBLGen does.

And to tell you the truth, once you spend some time with LLBLGen, writing predicates and relations is actually easier than writing complicated joins in SQL. LBLLGen WILL chop off about a third of your development time. It produces cleaner code on the surface and that code ALWAYS works.... Try writing a 200 word SQL statement and tell me if it works the first time. I doubt it will.

I'm sure someone else here can do a better job of convincing you, but that's what does it for me.

By the way, the SQL-statement-to-RelationPredicateBucket idea sounds pretty cool, but it's probably more difficult to parse the string than you think.

As Alex hinted, using strongly-typed, object-oriented representations of the query will result in code that is more maintanable and potentially easier to follow. Yes, it's much more verbose, but remember that the cost of writing the additional code upfront will result in much less work down the road in reading, understanding, and modifying the query.

That being said, using an object-oriented model to model set-based behavior (such as that used for reporting-eque functionality) isn't always the best approach; there's a lot of overhead that feels unnecessary. You do get the maintainability of the OO code, but at the expense of a lot of upfront work. My opinion is that O/R mapping, especially as Frans has implemented it, shines most when the environment is abstracted and where one is truly working with entities and objects, not when reporting and summarizing on large sets of data. It's called "Object / Relational Mapping" technologies for a reason.

I will say, though, that there is something to be said for consistency. As long as there is no serious impact on performance, keeping all of your data structures in LLBLGen makes for a very simple and easy to maintain data access strategy. So, modeling your reporting data structures on LLBLGen constructs makes sense from that standpoint. The only place where I would seriously question the wisdom of consolidating the data access logic for consistency's sake would be when there is a real need to do server-side processing - complex stored procedures, for example. But how about LLBLGen hosted in the SQL CLR? Yum...

Jeff...

jeffreygg wrote:

The only place where I would seriously question the wisdom of consolidating the data access logic for consistency's sake would be when there is a real need to do server-side processing - complex stored procedures, for example. But how about LLBLGen hosted in the SQL CLR? Yum... Jeff...

I had been thinking about this too--maybe in the future sometime. Doesn't look like Yukon will allow a DAL of any sort to run in process. But that would be very cool--it also addresses the concerns a lot of DBAs have about security, for example if you could have some kind of entity broker running on the database machine that you could configure to support token authentication (and other authentication methods) to further beef up the security model in your application I think that would be very powerful. Not to mention the benefits of having your DAL code executing on the same server(s) as your data...

Thanks all.

I agree with all statements. I've done more that just reporting with LLBLGen, so for me the benefits are hugely obvious, and I also personally feel now that I can write a predicate just as easily as a complex SQL statement.

I think there are 2 factors that are making it less obious why LLBLGen is so useful, aside from the obvious facts that these coders are new to LLBLGen:

1. The database is not greatly designed. Its old, and we wouldn't keep it as it is if we had a chance to change it. So as a result it doesnt map as well as it should to entities.

2. The queries are reasonably complex - and usually involve a lot of "where ID in" and unions...

Anyway, its intersting what you say about the moving more processing down to the database, and taking advantage of greater set of functionality, especially when yukon hits us. If a lot of views/procs/batch jobs were doen on slq server then the advantages of LLBL begin to lessen (does llblgen support parametized procs?)....can't wait to see what the future hold for llblgen though!

jookyone wrote:

jeffreygg wrote:

The only place where I would seriously question the wisdom of consolidating the data access logic for consistency's sake would be when there is a real need to do server-side processing - complex stored procedures, for example. But how about LLBLGen hosted in the SQL CLR? Yum... Jeff...

<snip>... Doesn't look like Yukon will allow a DAL of any sort to run in process.

I'm not so sure about this...I haven't done any real testing yet, but it appears as if you have Fusion support in the SQL host process. If the BCL is made available to it as well, including remoting support, etc, then I think you could host a complete DAL from within SQL process. Combine this with the direct, in-band SQL calls ADO.NET is providing and you have the makings of a very fast, in-proc, all-inclusive DAL.

MattWoberts wrote:

1. The database is not greatly designed. Its old, and we wouldn't keep it as it is if we had a chance to change it. So as a result it doesnt map as well as it should to entities.

Difficult indeed. However, I would say that the level of reusability afforded by encapsulating the queries into objects still makes for a strong reason for using the framework. Assuming you've developed your standard predicate expressions in a way that makes them reusable, this should be a strong sell - no more copy/pasting SQL statements...

<snip> Anyway, its intersting what you say about the moving more processing down to the database, and taking advantage of greater set of functionality, especially when yukon hits us. If a lot of views/procs/batch jobs were doen on slq server then the advantages of LLBL begin to lessen (does llblgen support parametized procs?)....can't wait to see what the future hold for llblgen though!

I'm not quite sure how the "standard" hook works for using .NET code as I haven't tested it yet. My assumption is that you would create a standard stored procedure and make calls to .NET types from within the stored proc.

What I hope is the case is that you don't have to treat code hosted by SQL any differently than "regular" code. In this way, I would prefer to use remoting to call into my DAL sitting in-process on the SQL server.

Either way, though, I think that you could successfully model a parameterized proc in code as it's just basically a function call.

Jeff...

jeffreygg wrote:

I will say, though, that there is something to be said for consistency. As long as there is no serious impact on performance, keeping all of your data structures in LLBLGen makes for a very simple and easy to maintain data access strategy. So, modeling your reporting data structures on LLBLGen constructs makes sense from that standpoint. The only place where I would seriously question the wisdom of consolidating the data access logic for consistency's sake would be when there is a real need to do server-side processing - complex stored procedures, for example. But how about LLBLGen hosted in the SQL CLR? Yum...

Yesterday I had an MVP meeting and with some of my fellow MVP's I discussed this. It turned out as I already expected: the CLR is 'hosted' but not 'fully integrated'. This means that I can't run a generated assembly inside the database process. I can only write procedures and functions in C#/VB.NET, but that's very fragmented. (and full of restrictions). Missed chance for MS.