Changes entity search hitting the 2100 parameter limit (sql-server)

Assigned to Tony.

Temporary fixes have been applied, however there will be changes to this context which may update the requirements for this component (potentially removing the need to join contexts, which is the initial cause of this issue).

Temporary fixes have been applied, however there will be changes to this context which may update the requirements for this component (potentially removing the need to join contexts, which is the initial cause of this issue).

AvV: Either create a view or support join in L2S.

Tony, what do you think of the following view? Do you think you could copy the dbml and the contexts to resolve this issue?

CREATE VIEW [dbo].[AdapterEntityChanges] AS
SELECT [Entity].EntityKey, [Entity].EntityId, [Entity].PartitionId, [Entity].ObjectClassId, [Entity].DN, [Changes].ChangeTimestamp
FROM [Entity]
INNER JOIN [Changes]
ON [Entity].PartitionId = [Changes].AdapterId
AND [Entity].EntityId = [Changes].EntityId
WHERE [Changes].ChangesKey = (SELECT TOP 1 C.ChangesKey
	FROM [Changes] C
	ORDER BY C.ChangeTimestamp ASC)

CREATE VIEW [dbo].[AdapterEntityChanges] AS
SELECT [Entity].EntityKey, [Entity].EntityId, [Entity].PartitionId, [Entity].ObjectClassId, [Entity].DN, [Changes].ChangeTimestamp
FROM [Entity]
INNER JOIN [Changes]
ON [Entity].EntityId = [Changes].EntityId
WHERE [Changes].ChangesKey = (SELECT TOP 1 C.ChangesKey
	FROM [Changes] C
	ORDER BY C.ChangeTimestamp ASC)

Being used.

SELECT dbo.Entity.EntityKey, 
	   dbo.Entity.EntityId, 
	   dbo.Entity.PartitionId, 
	   t1.ChangeTimestamp, 
	   dbo.Entity.ObjectClassId, 
	   dbo.Entity.DN
	   
FROM  dbo.Entity INNER JOIN
      ( SELECT DISTINCT dbo.Changes.EntityId, t2.ChangeTimestamp
		FROM dbo.Changes INNER JOIN (
			SELECT MAX(dbo.Changes.ChangeTimestamp) as ChangeTimestamp, dbo.Changes.ChangesKey
			FROM dbo.Changes 
			GROUP BY dbo.Changes.ChangesKey
		) t2 ON t2.ChangesKey = dbo.Changes.ChangesKey) t1
	  ON dbo.Entity.EntityId = t1.EntityId
	  

Work in progress.

SELECT dbo.Entity.EntityKey, dbo.Entity.EntityId, dbo.Entity.PartitionId, dbo.Entity.ObjectClassId, dbo.Entity.DN, C.ChangeTimestamp
FROM dbo.Entity INNER JOIN
dbo.Changes AS C ON dbo.Entity.EntityId = C.EntityId
WHERE (C.ChangesKey =
(SELECT TOP (1) ChangesKey
FROM dbo.Changes AS D
WHERE (dbo.Entity.EntityId = EntityId)
ORDER BY ChangeTimestamp)) AND (dbo.Entity.ObjectClassId IS NULL) AND (dbo.Entity.DN IS NULL)

Assigned for review.

To turn on first chance exceptions and check what the InterLinq host is doing on start up.

Tony, there were a few little things slightly out that was stopping this from working.

The context appears to be working on the service, are you able to test over InterLinq?

Thanks.

Tony,

I have removed a strange cast from the InterLinq query.

It has created a nightmare across the solution. Could you please take a look and make sure I haven't stuffed up the search? I would appreciate it if you were able to test once you have confirmed it's fine.

Thanks.

When attempting to view the pending changes for an adapter, I received the following error which did not allow me to progress until I restarted the console:

I've fixed up the nightmare, namely the UI components have all been re-factored so they can be reused for the different contexts - however it appears the the InterLINQ call against entity changes is still not working, and now standard Entity search appears to be completing evaluation on each request (it take ~7 seconds to get 20 entities on the engine side, so best guess is that it's trying to evaluate the 10k in the DB.) I've spent some time looking through the recent changes to these contexts and haven't been able to come up with anything.

Tony, I have got the query visitor working now for the ChangeTimestamp. You mentioned on Friday that everything was coming through, but that it had stopped. Can you please test this again. If there are any further problems please let me know.

Thanks.

After the recent set of service side changes I've been able to locally confirm this is now functioning as expected. Reassigned for confirmation.

Confirmed. Closed.

reopened for time.

closed following time.