v2.6 Final, Adapter, SQL Server

I have a TargetPerEntity hierarchy set up from Equipment -> Vehicle. In order to let the base code determine which type to return when selecting a piece of equipment I'm using FetchNewEntity as follows:

Dim e As EquipmentEntity = Nothing Dim filter As IRelationPredicateBucket = New RelationPredicateBucket() filter.PredicateExpression.Add(EquipmentFields.EquipmentId = equipmentID) **equipmentid is the primary key Using da As IDataAccessAdapter = GetAdapter() e = CType(da.FetchNewEntity(New FactoryClasses.EquipmentEntityFactory(), filter), EquipmentEntity) End Using Return e

I correctly get back a VehicleEntity where appropriate but the SQL that is generated is SELECT DISTINCT TOP 1 etc. And I noted that the internal calls go from FetchNewEntityInternal to FetchEntityCollection. If I do a FetchEntity passing in a new entity who's pkey is set it does a FetchEntity to FetchEntityUsingFilter.

Is there anything I can do to get rid of the DISTINCT TOP 1? I looked to see if I could specify that the predicate was unique but could not find anything.

Thanks,

Heather

Why is it important to get rid of the DISTINCT TOP 1 ? If you are sure that the EquipmentID is unique on the table you are only ever going to get back one row...?

Can you post the generated SQL so that we can have a look at what it is actually doing ?

Thanks

Matt

Yes I am absolutely sure since EquipmentID is the primary key on the Equipment table and Vehicle table, and that is the way they are specified in my llbl project as well.

SELECT DISTINCT TOP 1 [dbo].[Equipment].[EquipmentID] AS [F1_0], [dbo].[Equipment].[EquipmentName] AS [F1_1], [dbo].[Equipment].[EquipmentTypeID] AS [F1_2], [dbo].[Equipment].[SerialNumber] AS [F1_3], [dbo].[Equipment].[ModelID] AS [F1_4], [dbo].[Equipment].[ModelYear] AS [F1_5], [dbo].[Equipment].[EstimatedDailyReading] AS [F1_6], [dbo].[Equipment].[EstimatedReadingPerUnit] AS [F1_7], [dbo].[Equipment].[FuelTypeID] AS [F1_8], [dbo].[Equipment].[PurchasePrice] AS [F1_9], [dbo].[Equipment].[SellPrice] AS [F1_10], [dbo].[Equipment].[OrganizationID] AS [F1_11], [dbo].[Equipment].[EquipmentGroupID] AS [F1_12], [dbo].[Equipment].[ColorID] AS [F1_13], [dbo].[Vehicle].[VehicleID] AS [F2_14], [dbo].[Vehicle].[LicensePlate] AS [F2_15], [dbo].[Vehicle].[LicensePlateState] AS [F2_16], [dbo].[Vehicle].[BodyTypeID] AS [F2_17], [dbo].[Vehicle].[BrakeTypeID] AS [F2_18], [dbo].[Vehicle].[PersonCapacity] AS [F2_19], [dbo].[Vehicle].[WheelChairCapacity] AS [F2_20] FROM ( [dbo].[Equipment] LEFT JOIN [dbo].[Vehicle] ON [dbo].[Equipment].[EquipmentID]=[dbo].[Vehicle].[VehicleID]) WHERE ( ( [dbo].[Equipment].[EquipmentID] = @EquipmentId1))

The Distinct Top 1 is information the query analyzer shouldn't have to worry about since I know I am retrieving by the primary key. Seems inefficient. I just looked at the sql server execution plan however and removing the distinct top 1 doesn't seem to effect it so guess this is OK. Seems like it would be more efficient to make the call knowing you were going to get only one item back...similar to FetchEntity.

I was using FetchEntity but that does not give me back my subtype (if pertinent) so I switched to FetchNewEntity.