More real world LLBLGenPro code

I love working with LLBLGenPro. However sometimes I go through a process of adapting what I want to do into LLBL speak. I'm still pretty new to using the tool, so I figured I'd throw up some of the more useful chunks of code relating to real world problems just in case someone else finds them useful.

The Problem

You have a list of entities, lets call them "Categories". You're dynamically creating a total related to these categories, lets say "the number of images in each category". Finally, you want to filter your result set (using LLBLGenPro - not using a Datatable's select method or DefaultView property) to exclude Categories which don't have any images in it.

Sounds fairly easy, and it is! The trick is in the process of translating it from a simple business requirement into LLBL speak!

So lets do this in reverse. With all the code first, and then explaining the smaller chunks which I found more interesting. In my example above, I'm binding the results to a repeater, as it's used to generate a custom categories menu. My method returns a DataTable, which is a byproduct of defining a custom resultset - although having said that I'm sure there's many other ways you could achieve the same result (the base of the custom resultset code was taken from this post from Frans Bouma).

Here's the entire method I've ended up with (post testing and polishing): 


public static DataTable GenerateCategoryDataTable()
    bool showEmptyCategories = ConfigManager.GetConfigBool(ConfigManager.Setting.ShowEmptyCategories);

    // create a dynamic list with in-list scalar subqueries
    ResultsetFields fields = new ResultsetFields(4);

    IEntityField2 categoryCountField = new EntityField2("CategoryCount",
        new ScalarQueryExpression(ImageCategoryFields.CategoryId.SetAggregateFunction(AggregateFunction.Count),
(CategoryFields.CategoryId == ImageCategoryFields.CategoryId))); // define the fields in the select list, one for each slot. fields.DefineField(CategoryFields.CategoryId, 0); fields.DefineField(CategoryFields.Name, 1); fields.DefineField(CategoryFields.Description, 2); fields.DefineField(categoryCountField, 3); IPredicateExpression filter = new PredicateExpression(); if(!showEmptyCategories) { filter.Add(new FieldCompareValuePredicate(categoryCountField, null, ComparisonOperator.GreaterThan, 0)); } IRelationPredicateBucket bucket = new RelationPredicateBucket(filter); DataTable results = new DataTable(); using (DataAccessAdapter adapter = new DataAccessAdapter()) { adapter.FetchTypedList(fields, results, bucket, 0, new SortExpression(CategoryFields.Name | SortOperator.Ascending), true); } return results; }

Some notes about the method:

IEntityField2 categoryCountField = new EntityField2("CategoryCount",
new ScalarQueryExpression(ImageCategoryFields.CategoryId.SetAggregateFunction( AggregateFunction.Count), 
(CategoryFields.CategoryId == ImageCategoryFields.CategoryId)));

categoryCountField can be defined inside the fields.DefineField call if you wish, there's no need to define a variable. The only reason I did was because I re-used it later for my FieldCompareValuePredicate, so it made the code a lot more readable (imho) to define it that way.

There are also other ways to get totals values, some which are a bit cleaner than what I've pasted above - however the above code made sense in the context of where I was using it. In english, the scalar query is counting the number of times each CategoryFields.CategoryId field occurs inside the ImageCategory table (an image can contain multiple entries in this table indicating all the categories that it belongs to).

showEmptyCategories is simply an application configuration flag which determines whether categories containing no images show up in the list of categories. If you preferred, you could clean it up a bit and avoid sending through a bucket with an empty predicate expression through to the adapter.

As the results are being fetched into a DataTable, then you could easily call the "select" method to filter your results - however I wanted to throw my results against a databound control, so it wasn't an option for me. You could also set up a DefaultView in order to apply the filter, however I'm sure it's a lot more efficient to do the whole thing using LLBLGenPro.

So there we have it. A fairly simple but useful piece of code.


 Print | Posted on Sunday, October 07, 2007 7:20 PM | Filed Under [ ASP.NET Web Development C# SQL ]


No comments posted yet.

Post Comment

Website / Url

Your comment

Ensure the word in this box says 'orange':
Please add 5 and 4 and type the answer here:

Due to excessive comment spam, all comments are now being moderated. If you're a comment spammer then you're wasting your time here. Your comments will not be published - ever.

About me

My name is Ross Hawkins and I'm a developer, consultant, business owner and writer based in Auckland, New Zealand (pictured below!). My current work revolves around ASP.NET, C#, jQuery, Ajax, SQL Server, and a mix of other Microsoft development technologies.

I also have about 15 years of experience with IBM Lotus Notes/Domino and associated technologies. While Notes/Domino is no longer my primary focus I still like to dabble and keep my skills up to date.

I own and run 2 businesses - Hawkins Consulting Services, and Ignition Development.

Bethells Beach, located in sunny West Auckland, New Zealand


Subscribe to this feed


Popular Content

Troubleshooting WebResource.axd

The .NET 2.0 framework changed the way clientside JavaScript is delivered to the browser. Previously, ASP.NET 1.1 used the aspnet_client directory whereas now 2.0 uses WebResource.axd.

Published on October 8, 2006

Useful IIS Rewrite Rules

The IIS Rewrite Module is easy to use, however for some rules it can take a bit of trial and error to get your syntax correct. After using it for many projects across multiple customers, I thought it might be useful to put up a post containing a small collection of the useful rules I’ve come across, for your copying and pasting pleasure.

Published on January 14, 2014

jQuery Wildcard Selectors - some simple examples

I wrote about jQuery wildcard selector syntax briefly back in 2009, and since then that post has received a lot of views – way more than a post that brief should ever have seen..

Published on October 14, 2011

Microsoft AJAX Extensions: Sys.Debug is null or not an object

One of the breaking changes which was made with the 1.0 release of the Microsoft Ajax Extensions was the renaming of the 'Debug' class to 'Sys.Debug' for reasons of compatiability with other frameworks. Breaking changes like this can often be a source of frustration..

Published on May 22, 2007

Simple ASP.NET Character Counter

A textbox character counter is a pretty simple piece of functionality, and there's a lot of different ways to apply one to your application. The following method is nice and simple, and can be done using only clientside JavaScript if required, or combined with server side code in order to create a more dynamic effect

Published on December 4, 2006

Simple ASP.NET Character Counter - with Master Page Support

A quick update to my previous character counter article adding some changes for those using it with Master Pages.

Published on February 7th, 2009

Adding Tooltips to Gridview Headers

As the title says, this is a very simple but dynamic way of achieving tooltip text on a header column. It's not overly flash, but it's lightweight and quick to implement.

Published on April 15, 2007

SQL Server Web Report Viewer Issues on Windows 2008 Server/IIS7

A fix for another AXD related issue, this time with the SQL Server Web Report Viewer Control which was being served up via IIS7 on a Windows 2008 server.

Published on June 2, 2007
Updated on April 10, 2008


December, 2014 (1)
November, 2014 (3)
October, 2014 (2)
August, 2014 (3)
July, 2014 (1)
June, 2014 (2)
May, 2014 (4)
April, 2014 (1)
March, 2014 (4)
February, 2014 (3)
January, 2014 (4)
December, 2013 (6)
November, 2013 (2)
October, 2013 (3)
September, 2013 (5)
August, 2013 (5)
July, 2013 (3)
June, 2013 (2)
May, 2013 (3)
April, 2013 (2)
March, 2013 (2)
February, 2013 (3)
January, 2013 (5)
December, 2012 (4)
November, 2012 (4)
October, 2012 (3)
September, 2012 (3)
August, 2012 (4)
July, 2012 (1)
June, 2012 (4)
May, 2012 (2)
April, 2012 (4)
March, 2012 (2)
February, 2012 (4)
January, 2012 (3)
December, 2011 (3)
November, 2011 (8)
October, 2011 (9)
September, 2011 (8)
August, 2011 (5)
July, 2011 (4)
June, 2011 (7)
May, 2011 (5)
April, 2011 (3)
March, 2011 (8)
February, 2011 (4)
January, 2011 (3)
December, 2010 (8)
November, 2010 (5)
October, 2010 (6)
September, 2010 (7)
August, 2010 (11)
July, 2010 (12)
June, 2010 (8)
May, 2010 (8)
April, 2010 (4)
March, 2010 (8)
February, 2010 (6)
January, 2010 (12)
December, 2009 (13)
November, 2009 (11)
October, 2009 (12)
September, 2009 (12)
August, 2009 (2)
July, 2009 (7)
June, 2009 (12)
May, 2009 (9)
April, 2009 (9)
March, 2009 (9)
February, 2009 (8)
January, 2009 (7)
December, 2008 (6)
November, 2008 (7)
October, 2008 (9)
September, 2008 (12)
August, 2008 (9)
July, 2008 (6)
June, 2008 (24)
May, 2008 (13)
April, 2008 (16)
March, 2008 (8)
February, 2008 (10)
January, 2008 (1)
December, 2007 (14)
November, 2007 (11)
October, 2007 (11)
September, 2007 (13)
August, 2007 (11)
July, 2007 (5)
June, 2007 (15)
May, 2007 (11)
April, 2007 (9)
March, 2007 (9)
February, 2007 (10)
January, 2007 (8)
December, 2006 (18)
November, 2006 (11)
October, 2006 (14)
September, 2006 (9)
August, 2006 (10)
July, 2006 (4)
June, 2006 (4)
May, 2006 (6)
April, 2006 (3)
February, 2006 (6)
January, 2006 (10)
September, 2005 (2)
August, 2005 (4)

Post Categories

NZ Trains
Visual Studio
Web Development