FetchExcludedFields assigned to wrong entities

Posts   
 
    
chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 22-May-2008 10:37:28   

I'm using LLBLGen Pro 2.5 Final (latest version from website) with adapter and .NET 2.0/c# connecting to SQL Server 2005 dBs.

I have discovered an issue with the FetchExcludedFields method which results in the field values being assigned to the wrong entities in my collection.

My Activity table includes a nText "Description" column and I'm using data paging so when fetching a collection of ActivityEntity objects with a filterBucket that includes a join I'm excluding the field from the FetchEntityCollection so that the Distinct statement can used. I'm then using FetchExcludedFields to set the field values.

This is the code from my business manager class:


// fields that need to be excluded if LLBL Gen is going to use a distinct statement
ExcludeIncludeFieldsList excludeFields = GenericManager.DistinctExcludeFields(collection, filter, pageSize);

using (DataAccessAdapter adapter = new DataAccessAdapter(Config.MainConnectionString))
{
    // fetch the collection of entities
    adapter.FetchEntityCollection(collection, filter, numberOfRecordsToReturn, sorter, prefetchPath, excludeFields, pageNumber, pageSize);

    // now fetch any excluded fields
    if (excludeFields.Count > 0) adapter.FetchExcludedFields(collection, excludeFields);
}

In my scenario:

filter: includes a relation to the ActivityParticipantEntity and a number of predicate expressions numberOfRecordsToReturn: 0 sorter: ActivityEntity.StartDate prefetchPath: includes a number of prefetches for the likes of ActivityTypeEntity excludeFields: includes ActivityEntity.Description pageNumber: 1 pageSize: 20

However, I still do not get the correct Description values if I set: filter = null sorter = null prefetchPath = null pageNumber = 0 pageSize = 0

I can see that all the correct Description values are being retrieved by the method using the "RecId" primary key field but just not assigned to the correct entities.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-May-2008 11:39:52   

Would you please post a Northwind repro solution?

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 22-May-2008 12:10:22   

I don't have a Northwind database so would a backup of my database and a cut down version of my solution be of any use?

I also notices that v2.6 includes the following fix so was just wondered whether my issue might be in some way related?

"When FetchExcludedFields is called with a field defined as a subtype field, the values are inserted in the wrong entity"

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-May-2008 12:29:38   

I don't have a Northwind database so would a backup of my database and a cut down version of my solution be of any use?

This will do.

with a field defined as a subtype field

Is this your case?

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 22-May-2008 13:16:44   

No my Description field is just a standard field.

I have created a cutdown solution with a corresponding database that exhibits the issue so how do I go about sending it to you?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-May-2008 14:47:31   

Please attache to a message in this thread. Make sure you remove executables (obj/bin) folders and zip the package.

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 22-May-2008 14:57:22   

Attached is a SQL2005 database backup file and the cut-down solution. Once you've restored the database, run the solution and you'll see that you'll need to set your connection properties in the textbox on the form then hit the "Populate List" button.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-May-2008 16:16:56   

Reproduced.

Also for simplification I've used the following code:

            this.listView1.Items.Clear();

            ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList();
            excludedFields.Add(ActivityFields.Description);

            EntityCollection<ActivityEntity> activities = new EntityCollection<ActivityEntity>();
            
            using (DataAccessAdapter adapter = new DataAccessAdapter(textBox1.Text))
            {
                adapter.FetchEntityCollection(activities, null, 0, null, null, excludedFields);
            }

            using (DataAccessAdapter adapter = new DataAccessAdapter(textBox1.Text))
            {
                adapter.FetchExcludedFields(activities, excludedFields);
            }

            foreach (ActivityEntity activity in activities)
            {
                ListViewItem item = new ListViewItem();
                item.Text = activity.Subject;
                item.SubItems.Add(activity.Description);
                listView1.Items.Add(item);
            }
        }
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-May-2008 07:42:44   

We discovered that if an excluded field was defined using a subtype, a type filter was added to the query which obtained the excluded fields. This caused a problem.

The thing is this: the entities for which excluded fields have to be fetched are sorted in a view. Then this set is traversed from front to back and batches are created which fetch for the set of PK fields specified the excluded fields, with the SAME sorter as the one used in memory (which is on the PK)

Then the returned set is traversed and the entities have to match. If a type filter is added, there are less rows returned in the excluded fields list, and it fails. I've to check why the returned set of values for the excluded fields is smaller than expected (so it adds values to the wrong entities).

I couldnt directly see if you use inheritance. If you do, this is the cause (due to the added typefilters). For example: if you have Employee <- Manager, and you exclude Manager.Name, while 'Name' is defined in Employee, it will add a filter for Manager. But that's not correct, if there are also Employee or other subtype entities in the set.

This means that you should exclude Employee.Name. the v2.6 fix makes this possible behind the scenes. I'm not sure if your project uses entity inheritance, if it does, could you check when you specify the excluded fields with the entity they're defined in (as a workaround) to see if it works?

Frans Bouma | Lead developer LLBLGen Pro
chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 23-May-2008 09:01:34   

Morning Frans.

My project doesn't use entity inheritance. It would appear that the cause of my issue is different to the one that you've described as when tracing the server traffic from my test solution (using the original code) I can see that the FetchEntityCollection returns the first 20 Activity records and the FetchExcludedFields returns all 20 matching Description values in the same order.


exec sp_executesql N'SELECT DISTINCT TOP 20 [dbo].[Activity].[recID] AS [RecId], [dbo].[Activity].[subject] AS [Subject], 
[dbo].[Activity].[location] AS [Location], [dbo].[Activity].[probability] AS [Probability], [dbo].[Activity].[quoteReference] AS [QuoteReference], 
[dbo].[Activity].[estimatedRevenue] AS [EstimatedRevenue], [dbo].[Activity].[startDate] AS [StartDate], [dbo].[Activity].[endDate] AS [EndDate], 
[dbo].[Activity].[completeDate] AS [CompleteDate], [dbo].[Activity].[duration] AS [Duration], [dbo].[Activity].[dayeventflag] AS [DayEventFlag], 
[dbo].[Activity].[privateFlag] AS [PrivateFlag], [dbo].[Activity].[typeID] AS [TypeId], [dbo].[Activity].[priorityID] AS [PriorityId], 
[dbo].[Activity].[directionID] AS [DirectionId], [dbo].[Activity].[stateID] AS [StateId], [dbo].[Activity].[statusID] AS [StatusId], 
[dbo].[Activity].[regardingTypeID] AS [RegardingTypeId], [dbo].[Activity].[regardingAccountID] AS [RegardingAccountId], 
[dbo].[Activity].[regardingContactID] AS [RegardingContactId], [dbo].[Activity].[regardingProspectID] AS [RegardingProspectId], 
[dbo].[Activity].[reminderFlag] AS [ReminderFlag], [dbo].[Activity].[reminderDate] AS [ReminderDate], [dbo].[Activity].[createDate] AS [CreateDate], 
[dbo].[Activity].[createUserName] AS [CreateUserName], [dbo].[Activity].[modifyDate] AS [ModifyDate], [dbo].[Activity].[modifyUserName] AS [ModifyUserName], 
[dbo].[Activity].[ownerUserName] AS [OwnerUserName] FROM ( [dbo].[Activity]  INNER JOIN [dbo].[ActivityParticipant]  
ON  [dbo].[Activity].[recID]=[dbo].[ActivityParticipant].[activityID]) WHERE ( ( [dbo].[ActivityParticipant].[userName] = @UserName1))',
N'@UserName1 nvarchar(50)',@UserName1=N'chris.hilton'

exec sp_executesql N'SELECT TOP 250 [dbo].[Activity].[recID] AS [RecId], [dbo].[Activity].[description] AS [Description] 
FROM [dbo].[Activity]  WHERE ( ( ( [dbo].[Activity].[recID] IN (@RecId1, @RecId2, @RecId3, @RecId4, @RecId5, @RecId6, @RecId7, @RecId8, 
@RecId9, @RecId10, @RecId11, @RecId12, @RecId13, @RecId14, @RecId15, @RecId16, @RecId17, @RecId18, @RecId19, @RecId20)))) 
ORDER BY [dbo].[Activity].[recID] ASC',N'@RecId1 uniqueidentifier,@RecId2 uniqueidentifier,@RecId3 uniqueidentifier,@RecId4 uniqueidentifier,
@RecId5 uniqueidentifier,@RecId6 uniqueidentifier,@RecId7 uniqueidentifier,@RecId8 uniqueidentifier,@RecId9 uniqueidentifier,
@RecId10 uniqueidentifier,@RecId11 uniqueidentifier,@RecId12 uniqueidentifier,@RecId13 uniqueidentifier,@RecId14 uniqueidentifier,
@RecId15 uniqueidentifier,@RecId16 uniqueidentifier,@RecId17 uniqueidentifier,@RecId18 uniqueidentifier,@RecId19 uniqueidentifier,@RecId20 uniqueidentifier',
@RecId1='5D528122-EE27-DD11-A2B7-0019D1462DD5', @RecId2='75528122-EE27-DD11-A2B7-0019D1462DD5',@RecId3='6D528122-EE27-DD11-A2B7-0019D1462DD5',
@RecId4='5F528122-EE27-DD11-A2B7-0019D1462DD5',@RecId5='77528122-EE27-DD11-A2B7-0019D1462DD5', @RecId6='6F528122-EE27-DD11-A2B7-0019D1462DD5',
@RecId7='A5097188-ED27-DD11-A2B7-0019D1462DD5',@RecId8='79528122-EE27-DD11-A2B7-0019D1462DD5', @RecId9='A7097188-ED27-DD11-A2B7-0019D1462DD5',
@RecId10='61528122-EE27-DD11-A2B7-0019D1462DD5',@RecId11='63528122-EE27-DD11-A2B7-0019D1462DD5', @RecId12='65528122-EE27-DD11-A2B7-0019D1462DD5',
@RecId13='57528122-EE27-DD11-A2B7-0019D1462DD5',@RecId14='67528122-EE27-DD11-A2B7-0019D1462DD5', @RecId15='59528122-EE27-DD11-A2B7-0019D1462DD5',
@RecId16='71528122-EE27-DD11-A2B7-0019D1462DD5',@RecId17='69528122-EE27-DD11-A2B7-0019D1462DD5', @RecId18='5B528122-EE27-DD11-A2B7-0019D1462DD5',
@RecId19='73528122-EE27-DD11-A2B7-0019D1462DD5',@RecId20='6B528122-EE27-DD11-A2B7-0019D1462DD5'


Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-May-2008 19:51:49   

Thanks, that's a different issue, I'll look into it a.s.a.p. to have a fix.

(btw, I've unapproved your attachment as it contains sensitive data (userid/pwd)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-May-2008 18:24:07   

The issue occurs due to the fact that the rows in the DB are ordered differently than in-memory. If you do: select * from activity order by recid asc

you'll see that the rows are sorted on the 2nd byte from the left (big endian), and not on the first byte. However, the entity view on the collection to fill, is sorted on the first byte (as the comparer used is the default comparer in .NET, which creates strings).

Changing the comparer is a breaking change, so that's not what you're looking for (as breaking changes aren't made to released versions). Adding a pk-lookup will slow things down noticeably...

I'll see what I can do about this.

(edit) comparer changes don't have an effect, as the quicksort routine (List<T>.Sort()) already uses the default comparer, which simply checks for IComparable, which is implemented by Guid.

So a hash-based pk-pk match mechanism has to be used. I'll implement that tomorrow as that's more complex than a couple of lines of code.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-May-2008 12:26:49   

Fixed it. Please see the attached runtime dll. That should fix your issue.

Frans Bouma | Lead developer LLBLGen Pro
chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 27-May-2008 12:38:52   

That one works perfectly so thank you very much for the swift resolution.