Stack Exchange
Different results in execution of stored procedure using Entity framework and EXEC command
I have SP which verifies if current user exists in internal Db and returns user details. If user doesn't exist in internal Db it pulls details from external Db and inserts into internal.
PROCEDURE [common].[FindUser]
@Query VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS ( SELECT DISTINCT * FROM [common].Users 
                WHERE   Name LIKE '%' + @Query + '%'
                        OR DomainName LIKE '%' + @Query + '%'
                        OR Email LIKE '%' + @Query + '%'
              )
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Users ( Company, Division ,EmployeeNumber, Name, DomainName,Email)
                SELECT DISTINCT 
                    CoNo AS Company,
                    DvNo AS Division,
                    EeNo AS EmployeeNumber,
                    dbo.ProperCase(EmployeeName) AS Name,
                    LoginName AS DomainName,
                    Email AS Email 
                FROM AS400ForDummies.dbo.Employees ue
                WHERE 
                    IsDeleted = 0 AND 
                    TerminationDate IS NULL AND

                    (Email LIKE '%' + @Query + '%'
                    OR EmployeeName LIKE '%' + @Query + '%'
                    OR LoginName LIKE '%' + @Query + '%')

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH;
 END


SELECT DISTINCT * FROM [common].Users
WHERE   Email LIKE '%' + @Query + '%'
        OR Name LIKE '%' + @Query + '%'
        OR DomainName LIKE '%' + @Query + '%'
END

The problem is that if I execute SP using EXEC command it won't insert existing row, but if I'll execute it from my code using Entity Framework with same parameter's value it always inserts new user even if it's already in Db.
Any ideas why it happening? 
UPD
The real problem was that inside of my app after SP execution I was adding result to entity's EntityCollection using Add method. And the root cause was that parts where SP executes and where I'm Adding entity both uses different contexts. So I always got duplication. Usage single context resolves issue.

Is EF calling the same procedure? If so, I would recommend using SQL server profiler to capture the commands from the application (assuming this is local or isolated dev environment) and verify the command it's sending.

Could it have anything to do with the fact you're checking if a row exists in [common].Users then inserting into dbo.Users?

@David, your proposition really helped me to see that issue was not in SP. The real problem was that inside of my app after SP execution I was adding result to entity's EntityCollection using Add method. And the root cause was that parts where SP executes and where I'm Adding entity both uses different contexts. So I always got duplication. Usage single context resolves issue.

@MattWhitfield, good catch, one more bug from me, thanks for effort.

@Valerii Awesome, I'll put it as the answer then. Thanks.

Should I delete or close this question somehow? When i found the issue.

@Valerii hit the checkbox on the answer below : )

Is EF calling the same procedure? If so, I would recommend using SQL server profiler to capture the commands from the application (assuming this is local or isolated dev environment) and verify the command it's sending
