Hi Guys,
I was wondering if you could help me as I am a bit stumped at the moment.
I am trying to use the SQLDependency object(s) to write a windows service for sending e-mails upon the creation of entries in a table.
My DAL is produced using Entity Framework version 5 and is working for one object. However I have now tried to repeat the process for another object and as soon as my Notifier tries to register the query, I am getting the following error:
[Invalid, Statement, Subscribe]:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Sid] AS [Sid],
[Extent1].[Created] AS [Created],
[Extent1].[LastUpdated] AS [LastUpdated],
[Extent1].[Completed] AS [Completed],
[Extent1].[Private] AS [Private],
[Extent1].[Status] AS [Status]
FROM [dbo].[Initiatives] AS [Extent1]
WHERE ( NOT ([Extent1].[Id] IN (22,24,25,38,39,40,41,42,43,44,45,47))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[InitiativeAnswers] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[CompletedId]
))
Now, in order to get to this stage I am using the EFChangeNotify from this Code Project Article:
SqlDependency with Entity Framework 5.0[
^]
This is the C# code that is resulting in the above error:
using (var initiativeCreationMonitor =
new EntityChangeNotifier<Initiative, Task_Entities>(
i => !InitiativeIds.Contains(i.Id) && i.InitiativeAnswers.Any()))
{
}
I've had a look around and tried to see why this would not work and nothing seems to jump out as a cause. the query, as you can see uses the Scheme.TableName approach which is required for SQL Dependency. Now the only thing I can think of that is causing it is that I am trying to query 2 tables at the same time. Although I have not found anything to say this is not possible, it seems like the logical assumption based on the results I am getting.
Does anyone have any experience with this sort of thing, or have any ideas on how I can work around the issue? Unfortunately I am not able to change the database structure which I ideally would.