跨上下文 LINQ 加入 Azure 中
本文关键字:Azure 加入 LINQ 上下文 | 更新日期: 2023-09-27 18:35:38
我们目前正在将生产平台迁移到 Azure,因此我需要移动所有支持工具。以前,我们严重依赖数据适配器和存储过程,其中许多存储过程执行跨数据库联接。
迁移到 Azure 后,这些跨数据库联接都无法运行。我尝试将数据适配器移动到实体框架,但我似乎无法使它们工作。相反,我收到一个错误,指出不允许跨上下文联接。其中许多查询都依赖于来自多个数据库的数据,因此我只是想找出使其正常运行的最佳方法。
我已经查看了其他几个要求类似解决方案的问题,但它们似乎都不太适用于我的解决方案。
例如,下面是 SQL 中更简单的查询之一:
USE CustomerDB1234
SELECT DISTINCT u.[UserID]
,u.[UserLogin]
,u.[UserPhoneNumber]
,u.[UserPasswordHash]
, ISNULL(gl.[gl_login_name],'* no global login ID') AS [gl_login_name]
,gl.[gl_password_hash]
,gl.[gl_GUID]
,gl.[gl_Email_Validated]
,u.[usr_unit_set_id]
,oob.[oob_org_id]
FROM [dbo].[User] u WITH (NOLOCK)
LEFT JOIN [dbo].[OrganizationObjectBridge] oob WITH (NOLOCK) ON oob.[oob_object_type_id] = 9 AND oob.[oob_object_id]= u.UserID
LEFT JOIN [MainServer].[MainDb].[dbo].[GlobalLoginCustomerBridge] glcb WITH (NOLOCK) ON glcb.[glcbr_user_id] = u.UserID
AND glcb.[glcbr_customer_id] = dbo.efnGetCustomerID()
LEFT JOIN [MainServer].[MainDb].[dbo].[GlobalLogin] gl WITH (NOLOCK) ON gl.[gl_id] = glcb.[glcbr_gl_id]
WHERE ([UserID] = @userID OR @userID IS NULL)
AND ([UserDisabled] = @isDisabled OR @isDisabled IS NULL)
ORDER BY [gl_login_name]
在 Linq 中,它看起来类似于:
List<User2> userList = new List<User2>();
using (var e = new eContext())
using (var context = new CustomerContext(CustomerID))
{
var databaseConnections = e.DatabaseConnectionStrings;
var customer = e.Customers.Select(n => new
{
ID = n.CustomerID,
Name = n.CustomerName,
Email = n.CustomerEmail,
Website = n.CustomerWWW,
Logo = n.CustomerLogo,
DatabaseConnectionName = databaseConnections.FirstOrDefault(d => d.DatabaseConnectionID == n.DatabaseConnectionID).DatabaseConnectionName,
DatabaseConnectionString = databaseConnections.FirstOrDefault(d => d.DatabaseConnectionID == n.DatabaseConnectionID).DatabaseConnectionString1,
AccountNumber = n.CustomerAcctNumber
}).FirstOrDefault(n => n.ID == CustomerID);
userList = context.Users
.Join(e.GlobalLoginCustomerBridges,
u => u.UserID,
glcb => glcb.glcbr_user_id,
(u, glcb) => new { u, glcb })
.Where(n => n.glcb.glcbr_customer_id == CustomerID)
.Join(e.GlobalLogins,
glcb => glcb.glcb.glcbr_gl_id,
gl => gl.gl_id,
(glcb, gl) => new { glcb, gl })
.Join(context.OrganizationObjectBridges,
glcb => glcb.glcb.u.UserID,
oob => oob.oob_object_id,
(glcb,oob) => new {glcb, oob})
.Where(n=>n.oob.oob_object_type_id == 9)
.Select(n => new
{
ID = n.glcb.glcb.u.UserID,
GlobalLogin = n.glcb.gl.gl_login_name,
FirstName = n.glcb.glcb.u.UserFirstName,
MiddleName = n.glcb.glcb.u.UserMiddleName,
LastName = n.glcb.glcb.u.UserLastName,
GUID = n.glcb.gl.gl_GUID,
UserID = n.glcb.gl.gl_id,
HasSHA256Hash = n.glcb.gl.gl_password_hash_sha256 == null,
Customer = customer,
Organization = context.Organizations
.Select(o => new
{
ID = o.org_id,
Name = o.org_name,
ParentID = o.org_parent_id,
ExternalID = o.org_external_id,
Default = o.org_default,
Logo = o.org_logo,
URL = o.org_url,
PeerGroupID = o.org_peer_grp_id,
ExternalInfo = o.org_external_info
}).Cast<Organization2>().FirstOrDefault(o=>o.ID == n.oob.oob_org_id)
}).Cast<User2>().ToList();
}
两种方法:
- 在内存中的应用程序中执行联接。根据查询,这或多或少是有效的。此外,所需的代码更改范围从微小到可怕。
- 将数据库合并在一起。拥有许多没有物理原因的数据库是一种反模式。数据库不是应用程序模块化的逻辑单元。通过架构或表名前缀实现文档模块化。数据库是部署的物理单元。
第三种方法:在 VM 中运行 SQL Server。
通过使用另一个答案发布的信息,我想出了一个似乎有效的解决方案。当然,它看起来不像跨数据库联接那样高效或简洁,但它可以完成工作。
using (var context = new CustomerContext(CustomerID))
using (var e = new eContext())
{
var globalUserList = e.GlobalLoginCustomerBridges
.Join(e.GlobalLogins,
glcb => glcb.glcbr_gl_id,
gl => gl.gl_id,
(glcb, gl) => new { glcb, gl })
.Where(n => n.glcb.glcbr_customer_id == CustomerID)
.Select(n => new User2
{
ID = (int)n.glcb.glcbr_user_id,
GlobalLogin = n.gl.gl_login_name,
GUID = n.gl.gl_GUID
}).ToList();
var customer = e.Customers
.Join(e.DatabaseConnectionStrings,
c => c.DatabaseConnectionID,
d => d.DatabaseConnectionID,
(c, d) => new { c, d })
.Select(n => new Customer2
{
ID = n.c.CustomerID,
Name = n.c.CustomerName,
DatabaseConnectionName = n.d.DatabaseConnectionName,
DatabaseConnectionString = n.d.DatabaseConnectionString1,
GUID = n.c.cust_guid,
}).ToList().FirstOrDefault(n => n.ID == CustomerID);
var orgs = context.Organizations
.Select(o => new Organization2
{
ID = o.org_id,
Name = o.org_name,
}).ToList();
var users = context.Users
.Select(n => new User2
{
ID = n.UserID,
FirstName = n.UserFirstName,
}).ToList();
var userList = users
.Join(globalUserList,
u => u.ID,
gl => gl.ID,
(u, gl) => new { u, gl })
.Join(context.OrganizationObjectBridges,
u => u.u.ID,
oob => oob.oob_object_id,
(u, oob) => new { u, oob })
.Where(o => o.oob.oob_object_type_id == 9)
.Select(n => new User2
{
ID = n.u.u.ID,
GlobalLogin = n.u.gl.GlobalLogin,
FirstName = n.u.u.FirstName,
GUID = n.u.gl.GUID,
Customer = customer,
Organization = orgs.FirstOrDefault(o => o.ID == n.oob.oob_org_id)
}).Where(n => !isDisabled != null && n.Disabled == isDisabled).ToList();
return userList;
}