Check if value exists in multiple tables

I have such tables in my database like Customer, Member, Instructor, Employee etc. Each of these users has his email adrress. I need to check if there is already a user with given email. I was thinking about:

  • Check each table, something like this:
    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)))
              ...
    }
  • Select all emails and check:
    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));
    }

There are more tables and many users, so I would like to know what would be the most efficient way to implement such kind of checking.

Thank you.

Answers


In pure SQL this would be your most efficient because it stops searching as soon as it hits a match:

... As a stored procedure:

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

... As a scalar-valued function:

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

In C# with Linq, you can use the Any extension and the || operator. Since Any usually gets translated to EXISTS in SQL and evalutation of the || operator in C# is lazy, evaluation will stop as soon as the first ocurrence of an email is reached.

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));

It sounds like you have a couple of options.

Create a view. You could create a view in the database that shows, say, email addresses only. Assuming you're using MSSQL Server, something like:

CREATE VIEW EmailView AS
SELECT Email from Customers
UNION ALL
SELECT Email from Instructors
....

... then using an entity bound to that view so you can check the list of emails to see if that email exists already. Check out the documentation for more information.

Normalize your database. Do each of these tables share common information beyond email, say, first name and/or last name? It might be worth your time to reorganize your data model to put that information in a "Persons" table, then foreign key your other tables to it. (This will also help if your users are two different things, say, a customer and an instructor.)


Need Your Help

Cakephp 2.3: How to send an array of data to an action in another controller for rendering?

cakephp cakephp-2.3

I have a controller action that is supposed to validate the data and pass the results (array of data) to an action in another controller for further processing. I don't want to use Session Componen...