I have 16 gazillon functions like this in my BLL to fetch a lookup table into a collection and return to UI for use in a drop-down list of some sort.

        public EntityCollection GetTubularTypes()
        {
            EntityCollection coll = new EntityCollection(new TubularTypeEntityFactory());
            ISortExpression sort = new SortExpression(SortClauseFactory.Create(TubularTypeFieldIndex.TubularType, SortOperator.Ascending));
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(coll, null, 0, sort);
            return coll;
        }

It seems like a simple problem to come up with a generic function to do this, but I don't want to be passing an entity factory from the UI to the BLL as a way to tell it what table it wants, nor do I really want to make a big enumeration that the BLL could then use to create the proper factory, though certainly I could do that.

The sort clause could maybe go away; I'm now mostly using the Devex LookupEdit control which can sort the data.

Just curious how other folks are tackling this problem, or do all of your manager classes have tons of these as well?

BTW Frans, when I click on an icon to the left, it inserts it at the end of my message, instead of where the cursor is. Didn't it put it where the cursor was before, or am I just remembering that wrong?

Ha! It does the same thing with

block

Instead of trying to come up with some brilliant generic solution that uses reflection and some other black belt vodoo, I look at your problem and see a perfect opportunity for CODE GENERATION!

You've got a very clear pattern that you want to repeat for some of your EntityCollections. Create a new template and recreate them all every time you generate.

I don't believe that the list of data used to populate drop down lists and such should be explicitly called out in the business layer as a method. Why? Because it's UI specific; what happens when you hook another UI to the BL, or need to change the process flow of the UI? The BL has to change and I don't like that.

I use something I call a DisplayCollectionDefinition (basically combines ResultsetFields, RelationPredicatBucket, SortExpression, etc functionality into a single object) that is returned from a factory. I then set up a single BL Method that takes in the DisplayCollectionDefinition, and, using FetchTypedList returns a bindable datatable.

Public Function FetchDisplayCollection(def as DisplayCollectionDefinition, filter as RelationPredicateBucket) as DataTable

The key point here is that the list of fields itself does not represent business logic; it's the predicate alone that represents business logic. The beauty of LLBLGen Pro is that you can specify each of these independently and combine them at the DAL level to generate the actual query.

So, you can use the filter parameter above as is and allow the UI to get the PredicateExpression from the BL using a factory, or just change out the filter parameter for an enum that fetches the appropriate filter itself. Law of Demeter would require that you pass in the filter directly, but as it's at the cusp between BL and UI, encapsulation might prefer you use the enum.

Hope that makes sense. I think this approach "cleans up" the BL surface, provides for increased flexibility in the UI layer, and generally promotes decoupling. The downside is that it's a late bound datatable, but this will change, I hope in the future.

Jeff...

jeffreygg wrote:

I don't believe that the list of data used to populate drop down lists and such should be explicitly called out in the business layer as a method. Why? Because it's UI specific; what happens when you hook another UI to the BL, or need to change the process flow of the UI? The BL has to change and I don't like that.

I use something I call a DisplayCollectionDefinition (basically combines ResultsetFields, RelationPredicatBucket, SortExpression, etc functionality into a single object) that is returned from a factory. I then set up a single BL Method that takes in the DisplayCollectionDefinition, and, using FetchTypedList returns a bindable datatable.

Public Function FetchDisplayCollection(def as DisplayCollectionDefinition, filter as RelationPredicateBucket) as DataTable

The key point here is that the list of fields itself does not represent business logic; it's the predicate alone that represents business logic. The beauty of LLBLGen Pro is that you can specify each of these independently and combine them at the DAL level to generate the actual query.

So, you can use the filter parameter above as is and allow the UI to get the PredicateExpression from the BL using a factory, or just change out the filter parameter for an enum that fetches the appropriate filter itself. Law of Demeter would require that you pass in the filter directly, but as it's at the cusp between BL and UI, encapsulation might prefer you use the enum.

Hope that makes sense. I think this approach "cleans up" the BL surface, provides for increased flexibility in the UI layer, and generally promotes decoupling. The downside is that it's a late bound datatable, but this will change, I hope in the future.

Jeff...

I disagree. The lookup types he wants to get aren't UI specific (though they are in this case). His business method could still be used if the UI changed because you still need the lookup types. For example, an order might have a list of payment types (credit card, cash, etc). It makes sense to me that he puts it there.

Skeeterbug wrote:

I disagree. The lookup types he wants to get aren't UI specific (though they are in this case). His business method could still be used if the UI changed because you still need the lookup types. For example, an order might have a list of payment types (credit card, cash, etc). It makes sense to me that he puts it there.

Yes, but the list of columns presented is UI specific. Why would you change the Business Layer code to meet the requirements of a specific UI need? The filter used to come up with the list is what's important, and that can be protected behind the BL surface, if desired.

In regard to the payment types you specified, what if you decide later that you wanted to display additional information about the payment type, such as surcharges, or shipping delays in the combobox? Where would you have to make the change? The BL, and this adds some stronger coupling as now you have to go and review each UI that calls that method to ensure they aren't going to be messed up with the additional column (or maybe you remove a column).

Now with all of that being said, sometimes you have to create a discrete method for specific lookups. For example, maybe you need to return the customer's approved payment types only.

Public Function GetApprovedPaymentTypes(customerID as Int, def as DisplayCollectionDefinition) as DataTable

In this case you need a new method because the signature's different, but at least you can still pass in the set of columns required. However, I prefer to do that on a per case basis instead of creating dozens (hundreds?) of methods that basically do the same thing with a different table, which then becomes a maintenance nightmare when each UI (or perhaps even different portions of the same UI) need the same data presented in different ways.

Jeff...

This is probably a really big hack, and makes enforcing some foreign keys difficult, but.... I have started hijacking the lists table from DotNetNuke and putting all of my lists in that table. Then I have a lookup helper, that gets lists by list name, in some cases I have concrete list methods, like GetStates or GetCountries or GetCurrencyCodes etc.

It seems to work ok. IMO 3rd normal form sometimes can lead to a maintenance nightmare, i.e. having lists spread out into many tables

Here is my approach to this issue:

1- Create a database View that Union(s) all your LookUp Tables (hence preserving the Refrential Integrity they have with another tables)

2- This view will have a new discriminator column that differentiate between values from each table.

3- Map the view to an Entity or a TypedView

4- You will need to pass the discrminating value to your Fetch Logic method to filter upon.

Walaa wrote:

Here is my approach to this issue:

1- Create a database View that Union(s) all your LookUp Tables (hence preserving the Refrential Integrity they have with another tables)

2- This view will have a new discriminator column that differentiate between values from each table.

3- Map the view to an Entity or a TypedView

4- You will need to pass the discrminating value to your Fetch Logic method to filter upon.

Oh, killer idea.

Walaa wrote:

Here is my approach to this issue:

1- Create a database View that Union(s) all your LookUp Tables (hence preserving the Refrential Integrity they have with another tables)

2- This view will have a new discriminator column that differentiate between values from each table.

3- Map the view to an Entity or a TypedView

4- You will need to pass the discrminating value to your Fetch Logic method to filter upon.

I presume you are using an Indexed View for this... since the data is fairly static this would give you index seek lookups...

Out of curiosity, do you also create entities for each of the individual lookup tables so that they can be managed through a user interface, or are your lookup tables only changed by developers/sysadmin?

Oh, killer idea.

Database views can do wonders

I presume you are using an Indexed View for this... since the data is fairly static this would give you index seek lookups...

I didn't have the need to do that most of the times I had about 20 Lookup tables with almost 10 records each which is rarely modified, increased or deleted, but as you said if my data are that static an indexed view might be a nice idea, and possibly I'll be using it. Thanks for the tip.

Out of curiosity, do you also create entities for each of the individual lookup tables so that they can be managed through a user interface, or are your lookup tables only changed by developers/sysadmin?

It depends on the requirements, but most of the times, I did map them to Entities to be easily edited and modified by a UI (mostly an Administration Module).

Walaa wrote:

Here is my approach to this issue:

1- Create a database View that Union(s) all your LookUp Tables (hence preserving the Refrential Integrity they have with another tables)

2- This view will have a new discriminator column that differentiate between values from each table.

3- Map the view to an Entity or a TypedView

4- You will need to pass the discrminating value to your Fetch Logic method to filter upon.

Great idea Walaa. One question; do you find it prcatical to use an ENUM for the discriminator values in this view? On a related issue, how do you solce "cyclic referential integrity action" issue between your tables? eaxmple, TableA relates to TableB with cascade update TableB relates to TableC with cascade update TableC relates to TableA with cascade update (what I do in similar situation is to choose not to use cascade update between TableC and TableA for example)

JimHugh wrote:

Out of curiosity, do you also create entities for each of the individual lookup tables so that they can be managed through a user interface, or are your lookup tables only changed by developers/sysadmin?

In my implementation, I create maintenance screens for whoever needs them. Some are user definable, some are admin only. All my items are ListEntity objects, so I tie my CRUD operations to the list name or hierarchy.

Walaa wrote:

I presume you are using an Indexed View for this... since the data is fairly static this would give you index seek lookups...

I didn't have the need to do that most of the times I had about 20 Lookup tables with almost 10 records each which is rarely modified, increased or deleted, but as you said if my data are that static an indexed view might be a nice idea, and possibly I'll be using it. Thanks for the tip.

Ahh but 20 Lookup tables will need to be UNIONED each time. That's okay for a client app, but if it were a web app with lots of users, then you might find the database CPU being eaten up... I say CPU because the data pages for those 20 tables would almost certainly be in SQL's page cache so you are not likely to have much IO going on.

For those interested in using the Indexed View be careful if you are not using SQL Server Enterprise Edition. http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_091405_2.mspx explains that you need a HINT in order for the index to be considered.

Marcus

Thanks everybody for the ideas and feedback!