检查多个表中是否存在值
本文关键字:是否 存在 检查 | 更新日期: 2023-09-27 18:37:06
我的数据库中有这样的表,如Customer
、Member
、Instructor
、Employee
等。这些用户中的每一个都有自己的电子邮件。我需要检查是否已经有用户使用给定的电子邮件。我在想:
- 检查每个表,如下所示:
public bool IsEmailAddressExists(string email)
{
if (!Context.Customers.Any(c => string.Equals(c.Email, email, StringComparison.InvariantCultureIgnoreCase)))
if (!Context.Members.Any(m => string.Equals(m.Email, email, StringComparison.InvariantCultureIgnoreCase)))
...
}
- 选择所有电子邮件并检查:
public bool IsEmailAddressExists(string email)
{
var emails = Context.Customers.Select(c => c.Email).Union(Context.Members.Select(m => m.Email))...; //other unions
return emails.Any(e => string.Equals(e, email, StringComparison.InvariantCultureIgnoreCase));
}
有更多的表和许多用户,所以我想知道实现这种检查的最有效方法是什么。
谢谢。
听起来你有几个选择。
创建视图。 您可以在数据库中创建一个仅显示电子邮件地址的视图。 假设您使用的是 MSSQL Server,如下所示:
CREATE VIEW EmailView AS
SELECT Email from Customers
UNION ALL
SELECT Email from Instructors
....
。然后使用绑定到该视图的实体,以便您可以检查电子邮件列表以查看该电子邮件是否已存在。 有关详细信息,请查看文档。
规范化数据库。 这些表中的每一个是否共享电子邮件以外的通用信息,例如名字和/或姓氏? 重新组织数据模型以将该信息放在"Persons"表中,然后将其他表外键到该表可能是值得的。 (如果您的用户是两个不同的东西,例如,客户和讲师,这也将有所帮助。
在纯SQL中,这将是最有效的,因为它一旦匹配就停止搜索:
。作为存储过程:
CREATE PROCEDURE EmailExists
@email varchar(254) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @emailExists bit
SET @emailExists = 0
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Customer WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Member WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Instructor WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Employee WHERE email = @email)
END
END
END
SELECT @emailExists
END
。作为标量值函数:
CREATE FUNCTION EmailExists
(
@email varchar(254)
)
RETURNS bit
AS
BEGIN
DECLARE @emailExists bit
SET @emailExists = 0
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Customer WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Member WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Instructor WHERE email = @email)
IF @emailExists = 0
BEGIN
SELECT @emailExists = 1 WHERE EXISTS(SELECT 1 FROM Employee WHERE email = @email)
END
END
END
-- Return the result of the function
RETURN @emailExists
END
在 C# 和 Linq 中,可以使用 Any 扩展名和 || 运算符。由于 Any 通常在 SQL 中被转换为 EXISTS 和 ||C# 中的运算符是惰性的,一旦到达电子邮件的第一个实例,评估就会停止。
bool emailExists = customerEmails.Any(e => string.Equals(e, email, StringComparison.InvariantCultureIgnoreCase))
|| memberEmails.Any(e => string.Equals(e, email, StringComparison.InvariantCultureIgnoreCase))
|| instructorEmails.Any(e => string.Equals(e, email, StringComparison.InvariantCultureIgnoreCase))
|| employeeEmails.Any(e => string.Equals(e, email, StringComparison.InvariantCultureIgnoreCase));