NCache作为实体框架第二级缓存与数据库不同步 [英] NCache as Entity Framework second level cache not syncing with database

问题描述

使用NCache 4.6作为EF 6.1.3二级缓存，SqlDependency不起作用。 （它不能检测到我直接在SQL服务器中进行的更改，当使用EF添加或删除实体到数据库时也不会更新缓存或使其无效，尽管它检测到更新实体）

$ b $我已经在我的电脑中安装了NCache，IP： 192.168.56.1 和vbox中的SQL Server 2014在IP： 192.168.56.101

使用他们的网站，并定制他们以满足我的IP&用户，我执行以下SQL：

  ALTER DATABASE NORTHWND SET ENABLE_BROKER with rollback immediate; 
 GO 
  - 从sys.databases中选择is_broker_enabled，其中name ='NORTHWND'
 
授权订阅查询通知给user1 
 GRANT CREATE QUEUE TO user1 
 GRANT CREATE SERVICE TO user1 
 GRANT CREATE PROCEDURE TO user1 
 
 
 CREATE QUEUENCacheSQLQueue-192.168.56.1; 
 CREATE SERVICENCacheSQLService-192.168.56.1ON QUEUE。NCacheSQLQueue-192.168.56.1（[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]）; 
 
授权订阅QUERY NOTIFICATIONS TO user1; 
 GRANT RECEIVE ONNCacheSQLQueue-192.168.56.1TO user1; 
 GRANT RECEIVE ON QueryNotificationErrorsQueue TO user1; 
授权发送服务::NCacheSQLService-192.168.56.1到user1; 
  

另外， select from sys.dm_qn_subscriptions 显示没有人订阅。

解决方案

事实证明，我需要运行数据库中的ALTER AUTHORIZATION :: [NORTHWND] TO user1; 。

这是我做的：

1-恢复了一个新的Northwind数据库。

2-运行以下SQL：

 code> ALTER DATABASE NORTHWND SET ENABLE_BROKER with rollback immediate; 
 GO 
 
 GRANT订阅查询通知给sa 
 GRANT CREATE QUEUE TO sa 
 GRANT CREATE SERVICE TO sa 
 GRANT CREATE PROCEDURE TO sa 
 
数据库中的ALTER AUTHORIZATION :: [NORTHWND] TO sa 
  

请注意，在这个查询中有意省略 CREATE QUEUENCacheSQLQueue-192.168.56.1; 之后的所有查询。

另外，我发现实体框架分析器与NCache或SQL依赖关系不兼容，因此我禁用了它。

Using NCache 4.6 as EF 6.1.3 second level cache, SqlDependency is not working. (It cant detect changes that I make directly in SQL server. Also does not update cache or invalidate it when using EF to add or delete entities to database. Albeit it detect updating an entity)

I've installed NCache in my computer with IP: 192.168.56.1 and SQL server 2014 in vbox with IP: 192.168.56.101

using instruction from their site and customizing them to meet my IP & user, I executed following SQL:

ALTER DATABASE NORTHWND SET ENABLE_BROKER with rollback immediate;
GO
--Select is_broker_enabled from sys.databases where name ='NORTHWND'

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO user1
GRANT CREATE QUEUE TO user1
GRANT CREATE SERVICE TO user1
GRANT CREATE PROCEDURE TO user1


CREATE QUEUE "NCacheSQLQueue-192.168.56.1";
CREATE SERVICE "NCacheSQLService-192.168.56.1" ON QUEUE."NCacheSQLQueue-192.168.56.1"([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO user1;
GRANT RECEIVE ON "NCacheSQLQueue-192.168.56.1" TO user1;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO user1;
GRANT SEND ON SERVICE :: "NCacheSQLService-192.168.56.1" to user1;

Also, select * from sys.dm_qn_subscriptions shows that no one is subscribed.

解决方案

It turns out that I needed to run ALTER AUTHORIZATION ON DATABASE::[NORTHWND] TO user1;.

Here is what I did:

1- restored a fresh Northwind database.

2- ran following SQL:

ALTER DATABASE NORTHWND SET ENABLE_BROKER with rollback immediate;
GO

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa
GRANT CREATE QUEUE TO sa
GRANT CREATE SERVICE TO sa
GRANT CREATE PROCEDURE TO sa

ALTER AUTHORIZATION ON DATABASE::[NORTHWND] TO sa

Note that in this query I intentionally omitted CREATE QUEUE "NCacheSQLQueue-192.168.56.1"; and all queries after that.

Also, I found out that Entity Framework Profiler is incompatible with either NCache or SQL Dependency, so I disabled it.

这篇关于NCache作为实体框架第二级缓存与数据库不同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！