Hi there

We have (amongst others) 4 tables in our database. A Class Table, A ClassTemplate Table, a Member Table and a Waitlist Table

The Waitlist table has, as foreign keys, the MemberID, the ClassID and the CLassTemplateID - it has it's own ID as a primary key.

The flow is as follows a Member can ask to attend a class that is too full so we put them on a "waitlist" - they can wait either for the exact class they want or for any class that is made from a particular Class Template (i.e. "Any class of this type")

It is important to say that the Member can wait for a Class OR a Class Template not both - we have DEFINITELY prevented BOTH ID's being added to the Waitlist record before saving the business object.

We view the waitlists in a few different places - one of them is on the screen where users enter information about the class - they can click a tab and see a view of everyone who is waiting for either this class - or a class of this type

However when we return the Class's Waitlist collection we have found that both ID's can be present. In the database only one ID is present and when we track down the Select query in the GetMulti for the Waitlist object collection and run it in SQL Query Analyser it DOES NOT return both ID's - it returns exactly the data that is in the database

But somewhere between this point (i.e the query returning a result and declaring Grid.DataSource = mClass.Waitlist) the mystery ID's get added.

This is not true of every Waitlist object collection - we have some that work correctly and some that do not - it appears to be totally random

here is the code we use to lazy load are Waitlist collection

Dim oDataGrid As GridEX
Dim oDataSource As EntityCollectionBase
Dim filter As PredicateExpression = New PredicateExpression

filter.Add(PredicateFactory.CompareValue(LLBL.WaitlistFieldIndex.ClassID, ComparisonOperator.Equal, mClass.ClassID))
If Not mClass.ClassTemplateID.Equals(Guid.Empty) Then
    filter.AddWithOr(PredicateFactory.CompareValue(LLBL.WaitlistFieldIndex.ClassTemplateID, ComparisonOperator.Equal, mClass.ClassTemplateID))
End If

With mClass.Waitlist
  .GetMulti(filter)
  .SupportsSorting = True
  .Sort(LLBL.WaitlistFieldIndex.Waitlisteddate, System.ComponentModel.ListSortDirection.Ascending)
End With

oDataGrid = grdWaitlist
oDataSource = mClass.Waitlist

' - other unrelated code to do with the other tabs...

If Not oDataGrid Is Nothing Then
    If oDataGrid.DataSource Is Nothing Then
          oDataGrid.DataSource = oDataSource
    End If
End If

I am running the version "10 March 2005" and have regenerated my code - no difference...

Any thoughts?

regards

Randall

instead of:

With mClass.Waitlist
  .GetMulti(filter)
  .SupportsSorting = True
  .Sort(LLBL.WaitlistFieldIndex.Waitlisteddate, System.ComponentModel.ListSortDirection.Ascending)
End With

you should do:

mClass.GetMultiWaitlist(True, filter)
With mClass.Waitlist
  .SupportsSorting = True
  .Sort(LLBL.WaitlistFieldIndex.Waitlisteddate, System.ComponentModel.ListSortDirection.Ascending)
End With

As mClass.Waitlist.GetMulti() first fetches all the waitlist entities through lazy loading and then performs a getmulti again. Also consider: mClass.SetCollectionParametersWaitlist(filter, sorter)

Though this shouldn't be the cause of the problem though. I assume some classtemplateid's show up which shouldn't be there, but that the classid's are always correctly filtered?

Hi there - thanks for the prompt response (as always)

We tried using this (i.e. mClass.GetMultiWaitlist) and found that it "ignored" our "AddWithOr".

When we examined the SQL produced by this it was as follows

SELECT [dbo].[Waitlist].[WaitlistID] AS [WaitlistID],
[dbo].[Waitlist].[ProgramID] AS [ProgramID],
[dbo].[Waitlist].[MemberID] AS [MemberID],
[dbo].[Waitlist].[ClassTemplateID] AS [ClassTemplateID],
[dbo].[Waitlist].[ClassID] AS [ClassID],
[dbo].[Waitlist].[Notes] AS [Notes],
[dbo].[Waitlist].[Waitlisteddate] AS [Waitlisteddate],
[dbo].[Waitlist].[NotBefore] AS [NotBefore] 

FROM [dbo].[Waitlist] 

WHERE ( [dbo].[Waitlist].[ClassID] = @ClassID1 And ( [dbo].[Waitlist].[ClassTemplateID] = @ClassTemplateID2))

This will return nothing since it's an either or case. Can we add an AddWithOr? Or will it always interpret it to be an "And"

I assume some classtemplateid's show up which shouldn't be there, but that the classid's are always correctly filtered?

Not quite - it was the other way around.

BUT we have done more testing this morning and found the cause of the problem which is not where we first thought:

The user can open the waitlist records from the grid to edit them - they then choose whether they want to wait for this exact class or one of this type. To make this easier for them we remove the unselected ID from the form during saving, store it in a variable and replace it after they click the save button

This way they can swap back and forward between class and template without losing the last one they selected.

We use .Save(True) on our Entity objects and this is resulting in all records in the grid being saved. This seems to mean that every entity of the same type will also be saved if it is dirty - and ours are since after the last save we've added the "unused" ID temporarily

Surely this is not correct? Recursive save should definitely save "child" entities - but not peers? Is there a way to limit the recursive saving so it only saves child collections and not every entity of the same type?

Phew! What a mouthful! I hope that all makes sense

Thanks again for excellent response times and great support - it really sets you guys apart!

regards

Randall

jackscoldsweat wrote:

Hi there - thanks for the prompt response (as always)

We tried using this (i.e. mClass.GetMultiWaitlist) and found that it "ignored" our "AddWithOr".

When we examined the SQL produced by this it was as follows

SELECT [dbo].[Waitlist].[WaitlistID] AS [WaitlistID],
[dbo].[Waitlist].[ProgramID] AS [ProgramID],
[dbo].[Waitlist].[MemberID] AS [MemberID],
[dbo].[Waitlist].[ClassTemplateID] AS [ClassTemplateID],
[dbo].[Waitlist].[ClassID] AS [ClassID],
[dbo].[Waitlist].[Notes] AS [Notes],
[dbo].[Waitlist].[Waitlisteddate] AS [Waitlisteddate],
[dbo].[Waitlist].[NotBefore] AS [NotBefore] 

FROM [dbo].[Waitlist] 

WHERE ( [dbo].[Waitlist].[ClassID] = @ClassID1 And ( [dbo].[Waitlist].[ClassTemplateID] = @ClassTemplateID2))

This will return nothing since it's an either or case. Can we add an AddWithOr? Or will it always interpret it to be an "And"

Yes it uses an AND, for the following reason: it has to make sure the returned rows are for the entity containing the collection. So making it an OR query could return rows which don't have a relation with the containing entity.

BUT we have done more testing this morning and found the cause of the problem which is not where we first thought:

The user can open the waitlist records from the grid to edit them - they then choose whether they want to wait for this exact class or one of this type. To make this easier for them we remove the unselected ID from the form during saving, store it in a variable and replace it after they click the save button

This way they can swap back and forward between class and template without losing the last one they selected.

We use .Save(True) on our Entity objects and this is resulting in all records in the grid being saved. This seems to mean that every entity of the same type will also be saved if it is dirty - and ours are since after the last save we've added the "unused" ID temporarily

Surely this is not correct? Recursive save should definitely save "child" entities - but not peers? Is there a way to limit the recursive saving so it only saves child collections and not every entity of the same type?

Phew! What a mouthful! I hope that all makes sense

Child entities (depending entities) can only be saved if they don't have an FK pointing to an entity which is also dirty. If they do, that dependent entity (with the PK) has to be saved first. After that's done, it saves the depending entities, which can result in the save of all the peers of an entity you initially wanted to save. IMHO, there is no other way, as it would otherwise result in broken FK constraints which result in errors. It's a bit unclear what the temporary ID does, as it's a bit unclear to me what the 'unselected ID' is.

Thanks again for excellent response times and great support - it really sets you guys apart!

Thanks!

Yes it uses an AND, for the following reason: it has to make sure the returned rows are for the entity containing the collection. So making it an OR query could return rows which don't have a relation with the containing entity.

OK that makes sense - why use a collection attached to an object to display "child entities" that aren't really children - fair enough

Child entities (depending entities) can only be saved if they don't have an FK pointing to an entity which is also dirty. If they do, that dependent entity (with the PK) has to be saved first. After that's done, it saves the depending entities, which can result in the save of all the peers of an entity you initially wanted to save. IMHO, there is no other way, as it would otherwise result in broken FK constraints which result in errors. It's a bit unclear what the temporary ID does, as it's a bit unclear to me what the 'unselected ID' is.

Ahhh - this is coming clear now - so what is happening is I have a dirty Class Record with dirty Waitlist child entites - when I call save on any of the children it realises the parent record is dirty - so saves it because of the constraint and then that parent says "hang on - these other waitlist entities are dirty to so I better save them"

FYI: the waitlist screen has 2 radio buttons and you choose an option "Wait for Class" or "Wait for all classes of this type (template)" by clicking the corresponding button. Because the user might choose to switch between the two at any point we keep both ID's (i.e. ClassID and TemplateID) against the business object but when they click save we remove the one that isn't selected. Right after that we put it back tho - in case the user doesn't exit straight away - but this means that the record is dirty - the user can then close the form but if they save the class or edit and save any other waitlist entity then this first entity will also be saved - because of recursion

It all makes sense now

Cheers for the help

Randall