This post is about 2 thing: 1) I’m taking the right approach from a database viewpoint? 2) How to fetch the needed data using LLBLGen?

Said that I have 3 tables:

Customer CustomerID (Key, int32) CustomerName (string)

CustomerAddress CustomerAddressID (Key, int32) CustomerID AddressCategoryID Address (string)

AddressCategory AddressCategoryID (Key, int32) CategoryDescription (string) CategoryImportance (Unique, Int32) [from 0 to 10]

So: each customer can have multiple address each address belong to an addressCategory each address has ah “importance” level. Each customer can have only 1 single address for each AddressCategory.

This table structure is needed because we need to track the importance of each address. Now, in many query, we need to extract a list of customers with their most important address. Knowing that each customer has only one most important address, it’s possible to create query that extract the most important address of each customer.

First question: is this the right approach to manage these type of data?

Now, if I want the CustomerAddressID of the most important address of each customer, I can run the following query

SELECT  CustomerAddress.IDCustomerAddress, CustomerAddress.IDCustomer
FROM (  CustomerAddress INNER JOIN AddressCategory
            ON CustomerAddress.AddressCategoryID = AddressCategory.AddressCategoryID) 
    INNER JOIN 
            (SELECT CustomerAddress.IDCustomer, 
                    Max(AddressCategory.CategoryImportance) AS MaxImportance 
            FROM    AddressCategory INNER JOIN CustomerAddress  ON  AddressCategory.AddressCategoryID = CustomerAddres.AddressCategoryID 
            GROUP BY CustomerAddress.IDCustomer 
            ) AS MaxImportanceForCustomer 
    ON (CustomerAddress.IDCustomer     = [MaxImportanceForCustomer].IDCustomer) AND (AddressCategory.CategoryImportance = [MaxImportanceForCustomer].MaxImportance )

How can I create a similar filter in LLBLGen?

I need to use this filter in 2 different situation: For the GUI: I need to load an entityCollection of Customers, and I need a PrefetchPath that load the “most important” address of each customer. For the report: I will have a RelationPredicateBucket, defined by the user, that filter the customer in various way (filter on customer entity and on related entity). So I will need to fetch the most important address of the filtered customers in a dynamic list/datatable.

Thanks, Max

From the database design point of view:

Hint #1:

Each customer can have only 1 single address for each AddressCategory

You should set a Unique constraint on CustomerID + AddressCategoryID in the CustomerAddress table.

Hint #2: For the following field: CategoryImportance (Unique, Int32) [from 0 to 10] Assuming that 10 is the most important level, then you should have the default value set to 10 for this field. To make sure if you inserted only one Address for a customer, then its the most important one, and then you can work from there when you insert next addresses for the same customer, to either have a lower importance (most probably as the most important address is usualy inserted first), OR if you need to insert a more important address later, you would need to update the record holding the 10 value.

By the above approach, you will always have an address for a customer having the value of 10 (the most important address)

Then your query will be much much easier, without the need to get the MAX importance.

SELECT CustomerAddress.IDCustomerAddress, CustomerAddress.IDCustomer
FROM (  CustomerAddress INNER JOIN AddressCategory
            ON CustomerAddress.AddressCategoryID = AddressCategory.AddressCategoryID) 
WHERE AddressCategory.CategoryImportance = 10

Walaa wrote:

From the database design point of view:

Hint #1:

Each customer can have only 1 single address for each AddressCategory

You should set a Unique constraint on CustomerID + AddressCategoryID in the CustomerAddress table.

Oh, yes, I have that constraint in my db, but I forgot to write it in my post

Walaa wrote:

Hint #2: For the following field: CategoryImportance (Unique, Int32) [from 0 to 10] Assuming that 10 is the most important level, then you should have the default value set to 10 for this field. To make sure if you inserted only one Address for a customer, then its the most important one, and then you can work from there when you insert next addresses for the same customer, to either have a lower importance (most probably as the most important address is usualy inserted first), OR if you need to insert a more important address later, you would need to update the record holding the 10 value.

By the above approach, you will always have an address for a customer having the value of 10 (the most important address)

Then your query will be much much easier, without the need to get the MAX importance.

SELECT CustomerAddress.IDCustomerAddress, CustomerAddress.IDCustomer
FROM (  CustomerAddress INNER JOIN AddressCategory
            ON CustomerAddress.AddressCategoryID = AddressCategory.AddressCategoryID) 
WHERE AddressCategory.CategoryImportance = 10

Saddly, I can't take this approach. The "importance" is a property of the AddressCategory, not of the address. In the AddressCategory table I have only 10 rows. (but each installation of the software can modify the content of this table, so our customer can define their addressCategory and their respective "importance level") I need to be sure that the same address category give the same "importance" to the address that belong to this category, regardless of how many other address a customer may have.

Thanks anyway, Max

Ok I understand your point of view.

Another workaround is to add an extra column for the AddressCategory table. To clear things up, so whatever your client add to this table, your system should easily detect the most important table, a flag can be used for this approach.

AddressCategory
    AddressCategoryID (Key, int32)
    CategoryDescription (string)
    CategoryImportance (Unique, Int32) [from 0 to 10] 
    MostImportant (bit)

It may seem like a redundant data, but it may save a lot later on on querying the database, sometimes redundancy may be useful.

SELECT CustomerAddress.IDCustomerAddress, CustomerAddress.IDCustomer
FROM (  CustomerAddress INNER JOIN AddressCategory
            ON CustomerAddress.AddressCategoryID = AddressCategory.AddressCategoryID) 
WHERE AddressCategory.MostImportant = 1

Why not use a generic Category table for all your entities, like :

Category - CategoryId - CategoryName - CategoryNameShort - CreatedOn - CreatedBy

CategoryMember - CategoryId - EntityName - EntityId - CreatedOn - CreatedBy

EntityName and EntityId relate to the "to-be-categorized" entity, i.e. AccountEntity. This way, you can have one single storage table for all your category (group) info.

Kind regards, Danny

DvK wrote:

Why not use a generic Category table for all your entities, like :

Category - CategoryId - CategoryName - CategoryNameShort - CreatedOn - CreatedBy

CategoryMember - CategoryId - EntityName - EntityId - CreatedOn - CreatedBy

EntityName and EntityId relate to the "to-be-categorized" entity, i.e. AccountEntity. This way, you can have one single storage table for all your category (group) info.

Kind regards, Danny

This is a possible approach, but it make harder to setup relation between these generic table/entity and the related entity, and it seem even harder to use these information in entity object graph at runtime. Apart this, I do not see as this approach can simplify the solution of the problem.

Walaa wrote:

Ok I understand your point of view.

Another workaround is to add an extra column for the AddressCategory table. To clear things up, so whatever your client add to this table, your system should easily detect the most important table, a flag can be used for this approach.

AddressCategory
    AddressCategoryID (Key, int32)
    CategoryDescription (string)
    CategoryImportance (Unique, Int32) [from 0 to 10] 
    MostImportant (bit)

It may seem like a redundant data, but it may save a lot later on on querying the database, sometimes redundancy may be useful.

SELECT CustomerAddress.IDCustomerAddress, CustomerAddress.IDCustomer
FROM (  CustomerAddress INNER JOIN AddressCategory
            ON CustomerAddress.AddressCategoryID = AddressCategory.AddressCategoryID) 
WHERE AddressCategory.MostImportant = 1

This approach can work only if I'm sure that every customer has 1 address belonging to the MostImportant AddressCategory. But this is surely NOT going to happen . It's for this reason that I need to be able to obtain the most important address between the existing addresses of every customer.

I know, it's not solving your initial problem but I was triggered by your Category setup for addresses. Creating a relation between the address entity and the generic CategoryMember entity is very easy though in code, so that shouldn't stop you.

Kind regards, Danny

Perhaps this code solves your problem. I was having the same problem, fetching an address for an account but I doesn't have to exist and I wanted it to be a addresstype of POSTALADDRESS.

        
Dim result As New DataTable
        Dim accountList As New GetAccountOverviewTypedList
        Dim fields As ResultsetFields
        Dim bucket As RelationPredicateBucket = Nothing
        Dim sorter As New SortExpression
        Dim submain, submain1, submain2 As New PredicateExpression
        Dim subFilter As New PredicateExpression
        Dim sorter2 As SortExpression = Nothing

        Try
            bucket = DirectCast(defaultBucket.Clone, RelationPredicateBucket)

            'INSERT custom relations first at beginning of relationcollection, then the relations from typed list.
            'This way, the already existing relations in the parameter bucket are put at the beginning of the collection by using insert at pos 0.
            bucket.Relations.Insert(AccountEntity.Relations.DepotEntityUsingAccountId, 0)
            bucket.Relations.Insert(DepotEntity.Relations.TransactionDetailEntityUsingDepotId, 1)
            bucket.Relations.Insert(TransactionDetailEntity.Relations.TransactionEntityUsingTransactionId, 2)
            bucket.Relations.Add(AccountEntity.Relations.AddressEntityUsingAccountId, JoinHint.Left)

            'Set default relations from TypedList and add relations from Account to Depot to TransactionDetail 
            For i As Integer = accountList.GetRelationInfo.Relations.Count - 1 To 0 Step -1
                bucket.Relations.Insert(accountList.GetRelationInfo.Relations(i), 0)
            Next

            'grab field-definition from TypedList and expand 
            fields = DirectCast(accountList.GetFieldsInfo(), ResultsetFields)
            fields.Expand(7)
            fields.DefineField(AddressFields.AddressId, fields.Count - 7)
            fields.DefineField(AddressFields.AddressLine1, fields.Count - 6)
            fields.DefineField(AddressFields.PostalCode, fields.Count - 5)
            fields.DefineField(AddressFields.City, fields.Count - 4)
            fields.DefineField(AddressFields.Country, fields.Count - 3)
            fields.DefineField(AccountFields.LanguageId, fields.Count - 2, "LanguageIdAccount")
            fields.DefineField(New EntityField2("CountOfDepots", _
                               New ScalarQueryExpression(DepotFields.DepotId.SetAggregateFunction(AggregateFunction.Count), _
                               (DepotFields.AccountId = AccountFields.AccountId))), fields.Count - 1)

            'Do a subquery to fetch only 1 address row with type POST but do a LEFT join as well to fetch the account
            'anyway if no address row is available. Therefore, use an OR expression within the subquery and that requires a bit of a hassle
            'with predicates and adding them to the bucket
            subFilter.Add(AddressFields.AccountId = AccountFields.AccountId And AddressFields.AddressTypeId = Enums.AddressType.Post)
            submain2.Add(AddressFields.AddressId = DBNull.Value)
            'the actual subquery on AddressId for a TOP 1 from Address where Type = 10 (post)
            submain1.Add(New FieldCompareExpressionPredicate(AddressFields.AddressId, Nothing, ComparisonOperator.Equal, _
                         New ScalarQueryExpression(AddressFields.AddressId, subFilter, sorter2)))

            submain.Add(submain1) : submain.AddWithOr(submain2)
            bucket.PredicateExpression.Add(submain)
            bucket.PredicateExpression.Add(Me.DefaultTransactionPredicate(addBetweenDatesClause))

            'fetch involved Accounts
            _adapter.FetchTypedList(fields, result, bucket, config.SystemConfiguration.MaximumRowsOverallFetches, sorter, False, Nothing)
            result.TableName = "AccountDepotData"

        Catch ex As Exception
            _errorMessage = logger.FormatException(ex)
            logger.LogError(ex)
        End Try

DvK wrote:

I know, it's not solving your initial problem but I was triggered by your Category setup for addresses. Creating a relation between the address entity and the generic CategoryMember entity is very easy though in code, so that shouldn't stop you.

Kind regards, Danny

I never created new relation at runtime, I need to look into that.

Thanks, Max

DvK wrote:

Perhaps this code solves your problem. I was having the same problem, fetching an address for an account but I doesn't have to exist and I wanted it to be a addresstype of POSTALADDRESS.

You posted a nice piece of code, thanks It showed me some functions I didn't know (Relations.Insert / fields.Expand) these functions will be useful in future .

I've some question about your code: *) you define a ScalarQueryExpression; this, to me, seem a nested subquery that is re-executed for each returned row, I'm right?

*) to me it's not clear the purpose of this part of the filter "submain2.Add(AddressFields.AddressId = DBNull.Value)" do you have a row in the Address table with AddressID=NULL that works like a "default address" for account that don't have an address of type "Post"? And there is only 1 global row? or there exist a default row for each account?

I need to give a try to the approach of a scalar subquery re-executed for each returned row, maybe it can help to solve my problem.

Thanks, Max

I've some question about your code: *) you define a ScalarQueryExpression; this, to me, seem a nested subquery that is re-executed for each returned row, I'm right?

Right, I do this to count related Depots (Account -> Depot)

*) to me it's not clear the purpose of this part of the filter "submain2.Add(AddressFields.AddressId = DBNull.Value)" do you have a row in the Address table with AddressID=NULL that works like a "default address" for account that don't have an address of type "Post"? And there is only 1 global row? or there exist a default row for each account?

I do this to build the OR filter the correct way and I need this "AddressFields.AddressId = DBNull.Value" filter to keep at least the account row as result when no address row is available. Because of the left join, the Address.AddressId will return NULL if no address row is present for the current account.

Take my word for it, it works perfectly to solve your "problem" !

Kind regards, Danny

DvK wrote:

*) to me it's not clear the purpose of this part of the filter "submain2.Add(AddressFields.AddressId = DBNull.Value)" do you have a row in the Address table with AddressID=NULL that works like a "default address" for account that don't have an address of type "Post"? And there is only 1 global row? or there exist a default row for each account?

I do this to build the OR filter the correct way and I need this "AddressFields.AddressId = DBNull.Value" filter to keep at least the account row as result when no address row is available. Because of the left join, the Address.AddressId will return NULL if no address row is present for the current account.

Take my word for it, it works perfectly to solve your "problem" !

I'm working on it...

Max wrote:

DvK wrote:

*) to me it's not clear the purpose of this part of the filter "submain2.Add(AddressFields.AddressId = DBNull.Value)" do you have a row in the Address table with AddressID=NULL that works like a "default address" for account that don't have an address of type "Post"? And there is only 1 global row? or there exist a default row for each account?

I do this to build the OR filter the correct way and I need this "AddressFields.AddressId = DBNull.Value" filter to keep at least the account row as result when no address row is available. Because of the left join, the Address.AddressId will return NULL if no address row is present for the current account.

Take my word for it, it works perfectly to solve your "problem" !

I'm working on it...

**EUREKA! IT WORKS! **

Thanks to everybody!

And now... the solution:

make the following equivalence (these are the real name of my table/fields), I dont' want to rewrite all the code

Customer = Persona CustomerID = IDPersona

CustomerAddress = PersonaIndirizzo CustomerAddressID = IDPersonaIndirizzo

AddressCategory = L_IndirizzoTipo AddressCategoryID = CodTipoIndirizzo CategoryImportance = Ordinamento

this query (my original query)

SELECT  PersonaIndirizzo.IDPersonaIndirizzo 
FROM (  PersonaIndirizzo INNER JOIN L_IndirizzoTipo
            ON PersonaIndirizzo.CodTipoIndirizzo = L_IndirizzoTipo.CodTipoIndirizzo) 
    INNER JOIN 
            (SELECT PersonaIndirizzo.IDPersona, 
                    Max(L_IndirizzoTipo.Ordinamento) AS MaxDiOrdinamento 
            FROM    L_IndirizzoTipo 
            RIGHT JOIN PersonaIndirizzo 
            ON    L_IndirizzoTipo.CodTipoIndirizzo = PersonaIndirizzo.CodTipoIndirizzo 
            GROUP BY PersonaIndirizzo.IDPersona 
            ) AS PersInd 
    ON (PersonaIndirizzo.IDPersona     = [PersInd].IDPersona) AND (L_IndirizzoTipo.Ordinamento = [PersInd].MaxDiOrdinamento)

can be rewritten in 3 different way:

1) Using a Subquery/GroupBy/IN

SELECT  PersonaIndirizzo.IDPersonaIndirizzo, PersonaIndirizzo.IDPersona , L_IndirizzoTipo.CodTipoIndirizzo, L_IndirizzoTipo.Ordinamento
FROM    (PersonaIndirizzo INNER JOIN L_IndirizzoTipo ON PersonaIndirizzo.CodTipoIndirizzo = L_IndirizzoTipo.CodTipoIndirizzo) 
WHERE L_IndirizzoTipo.Ordinamento IN (
    SELECT Max(L_IndirizzoTipo.Ordinamento) AS MaxOrdinamento 
    FROM    L_IndirizzoTipo RIGHT JOIN PersonaIndirizzo AS PI ON L_IndirizzoTipo.CodTipoIndirizzo = PI.CodTipoIndirizzo 
    WHERE PI.IDPersona = PersonaIndirizzo.IDPersona 
    GROUP BY PI.IDPersona 
)

2) Using a Subquery/GroupBy/"="

SELECT  PersonaIndirizzo.IDPersonaIndirizzo, PersonaIndirizzo.IDPersona , L_IndirizzoTipo.CodTipoIndirizzo, L_IndirizzoTipo.Ordinamento
FROM    (PersonaIndirizzo INNER JOIN L_IndirizzoTipo ON PersonaIndirizzo.CodTipoIndirizzo = L_IndirizzoTipo.CodTipoIndirizzo) 
WHERE L_IndirizzoTipo.Ordinamento = (
    SELECT Max(L_IndirizzoTipo.Ordinamento) AS MaxOrdinamento 
    FROM    L_IndirizzoTipo RIGHT JOIN PersonaIndirizzo AS PI ON L_IndirizzoTipo.CodTipoIndirizzo = PI.CodTipoIndirizzo 
    WHERE PI.IDPersona = PersonaIndirizzo.IDPersona 
    GROUP BY PI.IDPersona 
)

3) Using a subquery/TOP 1/"="

SELECT  PersonaIndirizzo.IDPersonaIndirizzo, PersonaIndirizzo.IDPersona , L_IndirizzoTipo.CodTipoIndirizzo, L_IndirizzoTipo.Ordinamento
FROM    (PersonaIndirizzo INNER JOIN L_IndirizzoTipo ON PersonaIndirizzo.CodTipoIndirizzo = L_IndirizzoTipo.CodTipoIndirizzo) 
WHERE L_IndirizzoTipo.Ordinamento = (
    SELECT TOP 1  L_IndirizzoTipo.Ordinamento
    FROM    L_IndirizzoTipo RIGHT JOIN PersonaIndirizzo AS PI ON L_IndirizzoTipo.CodTipoIndirizzo = PI.CodTipoIndirizzo 
    WHERE PI.IDPersona = PersonaIndirizzo.IDPersona 
    ORDER BY L_IndirizzoTipo.Ordinamento DESC
)

This last one is the fastest on my system. To fetch all the data in a DataTable take 4,6 seconds (13'000 customers, about 40'000 address, 8 addressCategory)

now, the implementation of the subquery/TOP 1/"=":

Dim a As IDataAccessAdapter = get the dataadapter...

Dim scalarNestedSubQueryRelation As IRelationCollection = New RelationCollection
scalarNestedSubQueryRelation.Add(PersonaIndirizzoEntity.Relations.LIndirizzoTipoEntityUsingCodTipoIndirizzo, "PI", "LIT", JoinHint.Inner)

Dim scalarNestedSubQueryFilter As IPredicate
scalarNestedSubQueryFilter = (PersonaIndirizzoFields.Idpersona.SetObjectAlias("PI") = PersonaIndirizzoFields.Idpersona)

Dim scalarNestedSubQuery As ScalarQueryExpression
scalarNestedSubQuery = New ScalarQueryExpression(LIndirizzoTipoFields.Ordinamento.SetObjectAlias("LIT"), _
                                                 scalarNestedSubQueryFilter, _
                                                 scalarNestedSubQueryRelation, _
                                                 New SortExpression(LIndirizzoTipoFields.Ordinamento.SetObjectAlias("LIT") Or SortOperator.Descending))


Dim indRelationFilter2 As IRelationPredicateBucket = New RelationPredicateBucket
indRelationFilter2.Relations.Add(PersonaIndirizzoEntity.Relations.LIndirizzoTipoEntityUsingCodTipoIndirizzo)
indRelationFilter2.PredicateExpression.Add(LIndirizzoTipoFields.Ordinamento = New EntityField2("TopOrdinamento", scalarNestedSubQuery))


Dim fields As New ResultsetFields(5)
fields.DefineField(PersonaIndirizzoFields.Idpersona, 0)
fields.DefineField(PersonaIndirizzoFields.IdpersonaIndirizzo, 1)
fields.DefineField(LIndirizzoTipoFields.CodTipoIndirizzo, 2)
fields.DefineField(LIndirizzoTipoFields.Ordinamento, 3)
fields.DefineField(LIndirizzoTipoFields.TipoIndirizzo, 4)


Dim dt As DataTable
dt = New DataTable()
a.FetchTypedList(fields, dt, indRelationFilter2, 0, Nothing, True)

And now, the code for the solution using the groupBy (but in my test this is about 20% slower)

Dim a As IDataAccessAdapter = get the dataadapter...


Dim nestedGroupedSubQueryRelation As IRelationCollection = New RelationCollection
nestedGroupedSubQueryRelation.Add(PersonaIndirizzoEntity.Relations.LIndirizzoTipoEntityUsingCodTipoIndirizzo, "PI", "LIT", JoinHint.Inner)

Dim nestedGroupedSubQueryFilter As IPredicate
nestedGroupedSubQueryFilter = (PersonaIndirizzoFields.Idpersona.SetObjectAlias("PI") = PersonaIndirizzoFields.Idpersona)

Dim nestedGroupedSubQueryGroupBy As New GroupByCollection
nestedGroupedSubQueryGroupBy.Add(PersonaIndirizzoFields.Idpersona.SetObjectAlias("PI"))


Dim indRelationFilter As IRelationPredicateBucket = New RelationPredicateBucket
indRelationFilter.Relations.Add(PersonaIndirizzoEntity.Relations.LIndirizzoTipoEntityUsingCodTipoIndirizzo)
indRelationFilter.PredicateExpression.Add(New FieldCompareSetPredicate(LIndirizzoTipoFields.Ordinamento, Nothing, LIndirizzoTipoFields.Ordinamento.SetObjectAlias("LIT").SetAggregateFunction(AggregateFunction.Max), Nothing, SetOperator.In, nestedGroupedSubQueryFilter, nestedGroupedSubQueryRelation, "", 0, Nothing, False, nestedGroupedSubQueryGroupBy))


Dim fields As New ResultsetFields(5)
fields.DefineField(PersonaIndirizzoFields.Idpersona, 0)
fields.DefineField(PersonaIndirizzoFields.IdpersonaIndirizzo, 1)
fields.DefineField(LIndirizzoTipoFields.CodTipoIndirizzo, 2)
fields.DefineField(LIndirizzoTipoFields.Ordinamento, 3)
fields.DefineField(LIndirizzoTipoFields.TipoIndirizzo, 4)


Dim dt As DataTable
dt = New DataTable()

a.FetchTypedList(fields, dt, indRelationFilter, 0, Nothing, True)

Now, everything seem to work fine when I load the data into a datatable. The only problem, is that when I use this filter to load the data directly into an entityCollection, it never take less than 3 second, even if I only fetch 10 entity. I believe this has something to do with the rendered SQL, the only difference I can see is that during the fetch in the collection, a DISTINCT is put in front of the query. I will look into that...

Anyway thanks to everybody