Hi,

I've been creating a quite complex query (mostyl due to a non-optimal datastructure which we have to work with) and the GetMulti().Count & DbCount return different quantities.

GetMulti gives: 13 (which is correct if I check it in the database) DbCount gives: 29

This is the Orginal Query (executed after GetMulti())

products.GetMulti(filterProducts, -1, sortProducts, joins, pathProducts, llblCurrentPage, this.PagingPageSize);

(llblCurrentPage = 1 & this.PagingPageSize = 100).

SELECT   [ArgosInternet].[dbo].[Product].[ProductId], [ArgosInternet].[dbo].[Product].[BrandId], [ArgosInternet].[dbo].[Product].[Number], [ArgosInternet].[dbo].[Product].[Name], 
                      [ArgosInternet].[dbo].[Product].[PriceEx], [ArgosInternet].[dbo].[Product].[BtwId], [ArgosInternet].[dbo].[Product].[CategoryId], [ArgosInternet].[dbo].[Product].[TimeStamp], 
                      [ArgosInternet].[dbo].[Product].[CustomerFriendlyName], [ArgosInternet].[dbo].[Product].[Note], [ArgosInternet].[dbo].[Product].[SubcodeId], 
                      [ArgosInternet].[dbo].[Product].[ImageFileName], [ArgosInternet].[dbo].[Product].[FrontPage], [ArgosInternet].[dbo].[Product].[PriceAdvice], 
                      [ArgosInternet].[dbo].[Product].[PriceCost], [ArgosInternet].[dbo].[Product].[PriceReturn], [ArgosInternet].[dbo].[Product].[ExternalId]
FROM         (([ArgosInternet].[dbo].[Product] INNER JOIN
                      [ArgosInternet].[dbo].[ProductAlteration] [LPA_C1] ON [ArgosInternet].[dbo].[Product].[ProductId] = [LPA_C1].[ProductId] AND ((([LPA_C1].[AlterationId] = @AlterationId1 AND
                       [LPA_C1].[AlterationOptionId] = @AlterationOptionId2) OR
                      ([LPA_C1].[AlterationId] = @AlterationId3 AND [LPA_C1].[AlterationOptionId] = @AlterationOptionId4) OR
                      ([LPA_C1].[AlterationId] = @AlterationId5 AND [LPA_C1].[AlterationOptionId] = @AlterationOptionId6)))) INNER JOIN
                      [ArgosInternet].[dbo].[ProductAlteration] [LPA_P2] ON [ArgosInternet].[dbo].[Product].[ProductId] = [LPA_P2].[ProductId] AND (([LPA_P2].[AlterationId] = @AlterationId7 AND
                       [LPA_P2].[AlterationOptionId] = @AlterationOptionId8)))
WHERE    ((([ArgosInternet].[dbo].[Product].[Name] LIKE @Name9 OR
                      [ArgosInternet].[dbo].[Product].[Note] LIKE @Note10)))
ORDER BY [ArgosInternet].[dbo].[Product].[Name] ASC ',N' @AlterationId1 int, @AlterationOptionId2 int, @AlterationId3 int, @AlterationOptionId4 int, @AlterationId5 int, 
                      @AlterationOptionId6 int, @AlterationId7 int, @AlterationOptionId8 int, @Name9 varchar(9), @Note10 varchar(9) 
                      ',@AlterationId1=91,@AlterationOptionId2=1418,@AlterationId3=92,@AlterationOptionId4=1418, @AlterationId5=93,@AlterationOptionId6=1418,@AlterationId7=97,@AlterationOptionId8=1430,@Name9='
                       % pp TAPE % ',@Note10=' % pp TAPE %

And this is the Count Query:

products.GetDbCount(filterProducts, joins)

SELECT   [ArgosInternet].[dbo].[Product].[ProductId], [ArgosInternet].[dbo].[Product].[BrandId], [ArgosInternet].[dbo].[Product].[Number], [ArgosInternet].[dbo].[Product].[Name], 
                      [ArgosInternet].[dbo].[Product].[PriceEx], [ArgosInternet].[dbo].[Product].[BtwId], [ArgosInternet].[dbo].[Product].[CategoryId], [ArgosInternet].[dbo].[Product].[TimeStamp], 
                      [ArgosInternet].[dbo].[Product].[CustomerFriendlyName], [ArgosInternet].[dbo].[Product].[Note], [ArgosInternet].[dbo].[Product].[SubcodeId], 
                      [ArgosInternet].[dbo].[Product].[ImageFileName], [ArgosInternet].[dbo].[Product].[FrontPage], [ArgosInternet].[dbo].[Product].[PriceAdvice], 
                      [ArgosInternet].[dbo].[Product].[PriceCost], [ArgosInternet].[dbo].[Product].[PriceReturn], [ArgosInternet].[dbo].[Product].[ExternalId]
FROM         (([ArgosInternet].[dbo].[Product] INNER JOIN
                      [ArgosInternet].[dbo].[ProductAlteration] [LPA_C1] ON [ArgosInternet].[dbo].[Product].[ProductId] = [LPA_C1].[ProductId] AND ((([LPA_C1].[AlterationId] = @AlterationId1 AND
                       [LPA_C1].[AlterationOptionId] = @AlterationOptionId2) OR
                      ([LPA_C1].[AlterationId] = @AlterationId3 AND [LPA_C1].[AlterationOptionId] = @AlterationOptionId4) OR
                      ([LPA_C1].[AlterationId] = @AlterationId5 AND [LPA_C1].[AlterationOptionId] = @AlterationOptionId6)))) INNER JOIN
                      [ArgosInternet].[dbo].[ProductAlteration] [LPA_P2] ON [ArgosInternet].[dbo].[Product].[ProductId] = [LPA_P2].[ProductId] AND (([LPA_P2].[AlterationId] = @AlterationId7 AND
                       [LPA_P2].[AlterationOptionId] = @AlterationOptionId8)))
WHERE    ((([ArgosInternet].[dbo].[Product].[Name] LIKE @Name9 OR
                      [ArgosInternet].[dbo].[Product].[Note] LIKE @Note10))) TmpResult ',N' @AlterationId1 int, @AlterationOptionId2 int, @AlterationId3 int, @AlterationOptionId4 int, 
                      @AlterationId5 int, @AlterationOptionId6 int, @AlterationId7 int, @AlterationOptionId8 int, @Name9 varchar(9), @Note10 varchar(9) 
                      ',@AlterationId1=91,@AlterationOptionId2=1418,@AlterationId3=92,@AlterationOptionId4=1418, @AlterationId5=93,@AlterationOptionId6=1418,@AlterationId7=97,@AlterationOptionId8=1430,@Name9='
                       % pp TAPE % ',@Note10=' % pp TAPE %

could it be the result of paths maybe? Or any other way to resolve this?

Thanks, Gab

Hi Gab,

Are you sure pageSize is set to 100? Could you test without paging and sorting to see where could be the problem? I supose you are using the latest build, aren't you? And... Could you test the GetDBCount with an empty collection?

Thanks,

Will do during the day... it's one of the final delivery points of a project.

Cheers, Gab

Please also post the FULL dbcount query. The second query is just a select, it should have COUNT(*) somewhere.

Sorry for not responding to this thread for so long, but... Busy busy busy and the client didn't complain... But yesterday they did, so I got back in to it.

I've got an idea where this comes from.

I ran both queries and the SELECT query returned 264 rows and the COUNT query returned 264. So that's the same. BUT, the GetMulti() only gets a .Count of 88 because in the SELECT resultset all products are returned 3 times. I guess the GetMulti proceses the results and only adds 1 Entity for each 3 returned rows (which are identical).

Both queries are indeed almost identical except for the COUNT function ofcourse.

I've got it resolved by doing a GetMulti with all fields excluded instead of the DBCount, but I guess this is solveable in a better way.

Thanks, Gab

The Queries:

SELECT:

exec sp_executesql N'
SELECT 
[ArgosInternet].[dbo].[Product].[ProductId], 
[ArgosInternet].[dbo].[Product].[BrandId], 
[ArgosInternet].[dbo].[Product].[Number], 
[ArgosInternet].[dbo].[Product].[Name], 
[ArgosInternet].[dbo].[Product].[PriceEx], 
[ArgosInternet].[dbo].[Product].[PriceReturn], 
[ArgosInternet].[dbo].[Product].[PriceAdvice], 
[ArgosInternet].[dbo].[Product].[PriceCost], 
[ArgosInternet].[dbo].[Product].[BtwId], 
[ArgosInternet].[dbo].[Product].[CategoryId], 
[ArgosInternet].[dbo].[Product].[TimeStamp], 
[ArgosInternet].[dbo].[Product].[CustomerFriendlyName], 
[ArgosInternet].[dbo].[Product].[Note], 
[ArgosInternet].[dbo].[Product].[SubcodeId], 
[ArgosInternet].[dbo].[Product].[ImageFileName], 
[ArgosInternet].[dbo].[Product].[FrontPage], 
[ArgosInternet].[dbo].[Product].[ExternalId], 
[ArgosInternet].[dbo].[Product].[LeadTime] 
FROM ((( [ArgosInternet].[dbo].[Product]  
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_C1]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_C1].[ProductId] 
AND 
( ( ( [LPA_C1].[AlterationId] = @AlterationId1 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId2) 
OR 
( [LPA_C1].[AlterationId] = @AlterationId3 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId4) 
OR 
( [LPA_C1].[AlterationId] = @AlterationId5 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId6)))) 
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_P2]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_P2].[ProductId] 
AND 
 ( ( [LPA_P2].[AlterationId] = @AlterationId7 
AND 
[LPA_P2].[AlterationOptionId] = @AlterationOptionId8))) 
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_P3]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_P3].[ProductId] 
AND 
( ( [LPA_P3].[AlterationId] = @AlterationId9 
AND 
[LPA_P3].[AlterationOptionId] = @AlterationOptionId10)))',N'
@AlterationId1 int,
@AlterationOptionId2 int,
@AlterationId3 int,
@AlterationOptionId4 int,
@AlterationId5 int,
@AlterationOptionId6 int,
@AlterationId7 int,
@AlterationOptionId8 int,
@AlterationId9 int,
@AlterationOptionId10 int',
@AlterationId1=113,
@AlterationOptionId2=1814,
@AlterationId3=114,
@AlterationOptionId4=1814,
@AlterationId5=115,
@AlterationOptionId6=1814,
@AlterationId7=119,
@AlterationOptionId8=1819,
@AlterationId9=120,
@AlterationOptionId10=1879

The COUNT:

exec sp_executesql N'SELECT COUNT(*) AS NumberOfRows FROM (
SELECT 
[ArgosInternet].[dbo].[Product].[ProductId], 
[ArgosInternet].[dbo].[Product].[BrandId], 
[ArgosInternet].[dbo].[Product].[Number], 
[ArgosInternet].[dbo].[Product].[Name], 
[ArgosInternet].[dbo].[Product].[PriceEx], 
[ArgosInternet].[dbo].[Product].[PriceReturn], 
[ArgosInternet].[dbo].[Product].[PriceAdvice], 
[ArgosInternet].[dbo].[Product].[PriceCost], 
[ArgosInternet].[dbo].[Product].[BtwId], 
[ArgosInternet].[dbo].[Product].[CategoryId], 
[ArgosInternet].[dbo].[Product].[TimeStamp], 
[ArgosInternet].[dbo].[Product].[CustomerFriendlyName], 
[ArgosInternet].[dbo].[Product].[Note], 
[ArgosInternet].[dbo].[Product].[SubcodeId], 
[ArgosInternet].[dbo].[Product].[ImageFileName], 
[ArgosInternet].[dbo].[Product].[FrontPage], 
[ArgosInternet].[dbo].[Product].[ExternalId], 
[ArgosInternet].[dbo].[Product].[LeadTime] 
FROM ((( [ArgosInternet].[dbo].[Product]  
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_C1]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_C1].[ProductId] 
AND 
( ( ( [LPA_C1].[AlterationId] = @AlterationId1 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId2) 
OR 
( [LPA_C1].[AlterationId] = @AlterationId3 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId4) 
OR 
( [LPA_C1].[AlterationId] = @AlterationId5 
AND 
[LPA_C1].[AlterationOptionId] = @AlterationOptionId6)))) 
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_P2]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_P2].[ProductId] 
AND 
( ( [LPA_P2].[AlterationId] = @AlterationId7 
AND 
[LPA_P2].[AlterationOptionId] = @AlterationOptionId8))) 
INNER JOIN 
[ArgosInternet].[dbo].[ProductAlteration] [LPA_P3]  
ON  
[ArgosInternet].[dbo].[Product].[ProductId]=[LPA_P3].[ProductId] 
AND 
( ( [LPA_P3].[AlterationId] = @AlterationId9 
AND 
[LPA_P3].[AlterationOptionId] = @AlterationOptionId10)))) TmpResult',N'
@AlterationId1 int,
@AlterationOptionId2 int,
@AlterationId3 int,
@AlterationOptionId4 int,
@AlterationId5 int,
@AlterationOptionId6 int,
@AlterationId7 int,
@AlterationOptionId8 int,
@AlterationId9 int,
@AlterationOptionId10 int',
@AlterationId1=113,
@AlterationOptionId2=1814,
@AlterationId3=114,
@AlterationOptionId4=1814,
@AlterationId5=115,
@AlterationOptionId6=1814,
@AlterationId7=119,
@AlterationOptionId8=1819,
@AlterationId9=120,
@AlterationOptionId10=1879

BUT, the GetMulti() only gets a .Count of 88 because in the SELECT resultset all products are returned 3 times. I guess the GetMulti proceses the results and only adds 1 Entity for each 3 returned rows (which are identical).

That's because the GetMulti fills an entityCollection which in turn has the following property set to true: DoNotPerformAddIfPresent

Ref. wrote:

When set to true, an entity passed to Add() or Insert() will be tested if it's already present. If so, the index is returned and the object is not added again. If set to false (default: true) this check is not performed. Setting this property to true can slow down fetch logic. DataAccessAdapter's fetch logic sets this property to false during a multi-entity fetch.

Thanks for the input.

So I probably did something wrong in the Joins which has resulted in the duplicate rows. I will try to get the joins right.

Thanks, Gab