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.

 

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

Like this? Share it!

No comments posted yet.

Post a comment
Please add 8 and 4 and type the answer here:
Remember me?
Ensure the word in this box says 'orange':