Relation AspNetUsers Not Found in Identity 2 integration

[ENRIQUEHIN]

I am working on an ASP.NET MVC project and I just added ASP.NET Identity 2. For this, I exactly followed instruction in https://www.devart.com/dotconnect/postg ... orial.html and ran the project (I have tried twice in fact). I clicked the Register link which is included by default and the page to add a new user appeared. I then typed email and password, clicked the Register button and an error appeared: "Relation AspNetUsers does not exist".

This is the error message:
Devart.Data.PostgreSql.PgSqlException: no existe la relación «AspNetUsers»
It is in Spanish, "no existe la relación" means "relation does not exist".

This is the code where the exception takes place in Line 166:
Line 164: {
Line 165: var user = new ApplicationUser { UserName = model.Email, Email = model.Email };
Line 166: var result = await UserManager.CreateAsync(user, model.Password);
Line 167: if (result.Succeeded)
Line 168: {

This is part of the stack:
[PgSqlException (0x80004005): no existe la relación «AspNetUsers»]
Devart.Data.PostgreSql.e.f(r A_0) +131
Devart.Data.PostgreSql.r.n() +124
Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords) +816
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery) +495
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +6
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12
Devart.Common.Web.Identity.DbDataSource.ExecuteReader(String commandText, List`1 parameterValues, CommandBehavior commandBehavior, Dictionary`2 typeMapping) +298
Devart.Common.Web.Identity.AspNetTable.a(Dictionary`2 A_0) +50
Devart.Common.Web.Identity.AspNetTable.a(String A_0, Object A_1) +56
Devart.Common.Web.Identity.UserTable`2.SelectByUserName(String userName) +20
Devart.Common.Web.Identity.UserStore`3.FindByNameAsync(String userName) +32
Microsoft.AspNet.Identity.UserManager`2.FindByNameAsync(String userName) +56
Microsoft.AspNet.Identity.<ValidateUserName>d__4.MoveNext() +392
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58

The connection string is a follows (in web.config):
<connectionStrings>
<add name="DefaultConnection" connectionString="host=localhost;database=dbtest;user id=postgres; password=*********;" providerName="Devart.Data.PostgreSql" />
</connectionStrings>

I created the DB, added a new schema called dbtest and ran the script called Install_identity_tables.sql (to create tables in dbtest schema) located in C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 2. All the tables appear in my dbtest schema. The public schema (created by default by pgAdmin) is empty.

I noticed that the names of the tables in the script use a combination of upper and lowercase letters while the names of the tables in pgAdmin are all lowercase.

I am using the following product versions:
a) Visual Studio 2017 Version 15.9.4
b) Target .Net Framework 4.6.1
b) PostgreSQL v9.1
c) DevArt dotConnect for PostgreSQL v7.10.1115.0

I will very much appreciate your feedback.

P.D.: I HAVE JUST FOUND THAT IT WORKS CORRECTLY IF IDENTITY TABLES ARE LOCATED IN THE public SCHEMA.
IS THIS A REQUIREMENT? IN MY WEB PROJECT, I USE A DIFFERENT SCHEMA, NOT public.

Respectfully,
Jorge Maldonado

[Shalex]

You should set the Schema parameter in your connection string.

If this doesn't help, please upgrade to the newest (7.12.1328) build of dotConnect for PostgreSQL and notify us about the result.

[ENRIQUEHIN]

Thanks for your reply.
I just upgraded to version 7.12.1328 and still have the exact same issue.
This is the entry in web.config that sets the connection string:

<add name="DefaultConnection" connectionString="host=localhost;database=dbtest;schema=dbtest;user id=postgres; password=********;" providerName="Devart.Data.PostgreSql" />

As you can see, I am setting the schema name which, in my case, is the same as the db name.
The connection name is "DefaultConnection" as described in the documentation. I, in fact, copied and pasted from the documentation guide and added the schema name.
I guess I will end up using 2 schemas, my "dbtest" schema and the "public" schema which is included by default when a new PostgreSQL DB is created. I have never used more than one schema in my projects but I have read this approach does not generate any conflict. It is even a good practice to do so in certain situations.

Have you performed tests to verify this is a dotConnect for PostgreSQL issue?

Regards.

[Shalex]

The application created basing on our tutorial https://www.devart.com/dotconnect/postg ... orial.html for a non-default (dbtest) schema successfully works in our environment.

1. Make sure that you created the tables "C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 2\Install_identity_tables.sql" in your dbtest schema. You can do that with pgAdmin or dbForge Studio for PostgreSQL.

2. Enable dbMonitor, create the PgSqlMonitor instance in Global.asax.cs

Code: Select all

...
    public class MvcApplication : System.Web.HttpApplication
    {
        PgSqlMonitor monitor;
        protected void Application_Start()
        {
            monitor = new PgSqlMonitor() { IsActive = true };

            AreaRegistration.RegisterAllAreas();
...

and check if your schema from connection string is actually applied ("SET search_path TO dbtest;"): http://prntscr.com/mwcfil.

[ENRIQUEHIN]

Hi,

I ran dbMonitor as suggested.
Below, you will find an image of pgAdmin showing DB and schema, an image of dbMonitor result, and a link with query code of Install_identity_tables.sql (saved as txt file).
I modifed the query code to create the tables in dbtest schema adding the schema name and removing the double quotes in table names. If double quotes were not removed, tables were created in public schema even if schema name was included.





http://lasuperlista.com/Install_identity_tables.txt

I will very much appreciate you prompt support as I am already late delivering this part of the project.

Best regards.

[Shalex]

I modifed the query code to create the tables in dbtest schema adding the schema name and removing the double quotes in table names.

Removing quotes in table names is the reason of the issue you have encountered. If the name is not quoted, it is created in a default (lower) case - "aspnetusers". In the same time, the provider looks for "AspNetUsers" (refer to log in dbMonitor).

If double quotes were not removed, tables were created in public schema even if schema name was included.

Add "SET search_path TO dbtest;" to the first line of the script "C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 2\Install_identity_tables.sql" and execute it. Now the tables should be created in your dbtest schema.

[ENRIQUEHIN]

Thank you.
My issue has been solved.

Best regards.