LLBLGen Pro Version: 2.6 Final (April 15th, 2009) SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.9.511 .NET 3.5 MSSQL 2005 Adapter Template

I am getting duplicate rows returned from my FetchEntityCollection call. Here is my code:

                    IncludeFieldsList IFL = new IncludeFieldsList();
                    IFL.Add(VOracleClusterDetailFields.RacEnvId);
                    IFL.Add(VOracleClusterDetailFields.MachineId);
                    IFL.Add(VOracleClusterDetailFields.ProductNm);
                    IFL.Add(VOracleClusterDetailFields.AppId);
                    RelationPredicateBucket Filter = new RelationPredicateBucket();
                    Filter.PredicateExpression.Add(VOracleClusterDetailFields.RacEnvId % FilterExpression);
                    if (Sorter == null)
                    {
                        ISortExpression sorter = new SortExpression();
                        sorter.Add(VOracleClusterDetailFields.RacEnvId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.MachineId | SortOperator.Ascending);
                        sorter.Add(VOracleClusterDetailFields.ProductNm | SortOperator.Ascending);
                        Sorter = sorter;
                    }
                    _GV.DataSource = ddh.GetOracleClusterNodes(Filter, Sorter, true, IFL);

The call to ddh.GetOracleClusterNodes is:

        public EntityCollection<VOracleClusterDetailEntity> GetOracleClusterNodes(IRelationPredicateBucket irpb, ISortExpression ise, bool bExactMatch, ExcludeIncludeFieldsList EIFL)
        {
            EntityCollection<VOracleClusterDetailEntity> _collection = new EntityCollection<VOracleClusterDetailEntity>(new VOracleClusterDetailEntityFactory());
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(_collection, irpb, 0, ise, null, EIFL);
                if (_collection.Count == 0 && !bExactMatch)
                {
                    adapter.FetchEntityCollection(_collection, null);
                }
                return _collection;
            }

All fields included in the IncludeFieldList are char fields, so I have no fields in the select list that would negate a DISTINCT keyword. Here is the select statement it produces:

exec sp_executesql N'SELECT [dbinfo].[dbo].[v_oracle_cluster_detail].[rac_env_id] AS [RacEnvId], [dbinfo].[dbo].[v_oracle_cluster_detail].[machine_id] AS [MachineId], [dbinfo].[dbo].[v_oracle_cluster_detail].[product_nm] AS [ProductNm], [dbinfo].[dbo].[v_oracle_cluster_detail].[app_id] AS [AppId] FROM [dbinfo].[dbo].[v_oracle_cluster_detail]  WHERE ( ( [dbinfo].[dbo].[v_oracle_cluster_detail].[rac_env_id] LIKE @RacEnvId1)) ORDER BY [dbinfo].[dbo].[v_oracle_cluster_detail].[rac_env_id] ASC,[dbinfo].[dbo].[v_oracle_cluster_detail].[machine_id] ASC,[dbinfo].[dbo].[v_oracle_cluster_detail].[product_nm] ASC',N'@RacEnvId1 nvarchar(2)',@RacEnvId1=N'41'

What do I need to do to get only distinct rows in my resultset?

Thanks....

Hi there,

I'm not sure, but I think the DISTINCT keyword is not emitted here because of the IncludeFields. The DISTINCT in EntityCollection fetches is used to ensure no duplicates values exists for PKs.

If you want to fetch only those fields in DISTINCT mode, you should consider to use DynamicLists.

As the query contains PK fields and no joins, you can't have duplicate rows, as each row has a different PK value. Also, fetching entities will automatically filter out duplicates when entities are materialized, so there as well are duplicates prevented (if required, e.g. through a join).

I.o.w.: where do you see the duplicates to appear?

Otis wrote:

As the query contains PK fields and no joins, you can't have duplicate rows, as each row has a different PK value. Also, fetching entities will automatically filter out duplicates when entities are materialized, so there as well are duplicates prevented (if required, e.g. through a join).

I.o.w.: where do you see the duplicates to appear?

Not all of the primary key fields are included in the IncludeFieldList, therefore, I am getting duplicates.

Also, this is a view, so I have tried defining PK fields in the designer and removing the PK fields in the designer. Neither makes a difference.

jesterJP wrote:

Otis wrote:

As the query contains PK fields and no joins, you can't have duplicate rows, as each row has a different PK value. Also, fetching entities will automatically filter out duplicates when entities are materialized, so there as well are duplicates prevented (if required, e.g. through a join).

I.o.w.: where do you see the duplicates to appear?

Not all of the primary key fields are included in the IncludeFieldList, therefore, I am getting duplicates.

Also, this is a view, so I have tried defining PK fields in the designer and removing the PK fields in the designer. Neither makes a difference.

PK fields are always fetched, even if you don't specify them in the includefieldlist (same as fk fields). So your query results in duplicate entities in the collection?

Since this is a view and I didn't have all the PK fields specified in the Designer, it looked like I was getting duplicate rows (based on the columns that were being included in the select statement).

What I really want from that view is a distinct list of a subset of the columns. What is the best way to obtain this?

What if you use the view as a TypedView instead of a EntityCollection? Then you can fetch the records specifying whether or not you want duplicates.