using Npgsql with Identity framework v2

[n3u...@gmail.com]

I try to use Npgsql in combination with identity framework V2. so I created all the tables in Postgres database. When I call UserManager.FindAsync(model.UserName, model.Password), identity framework tries to determine the user in database with the given username. Then an exception occurs because the statement created by EF in combination with NpgSql.EntityFramework is incorrect.

The exception message is: {"ERROR: 42601: each UNION query must have the same number of columns"}
which is true because the generetad sql command (as shown below) returns 1 column in the first select of the union and many columns in the second select. Any suggestions how to solve this?

incorrect sql:
SELECT
"UnionAll2"."AccessFailedCount" AS "C1",
"UnionAll2"."Id" AS "C2",
"UnionAll2"."Email" AS "C3",
"UnionAll2"."EmailConfirmed" AS "C4",
"UnionAll2"."PasswordHash" AS "C5",
"UnionAll2"."SecurityStamp" AS "C6",
"UnionAll2"."PhoneNumber" AS "C7",
"UnionAll2"."PhoneNumberConfirmed" AS "C8",
"UnionAll2"."TwoFactorEnabled" AS "C9",
"UnionAll2"."LockoutEndDateUtc" AS "C10",
"UnionAll2"."LockoutEnabled" AS "C11",
"UnionAll2"."AccessFailedCount1" AS "C12",
"UnionAll2"."UserName" AS "C13",
"UnionAll2"."C1" AS "C14",
"UnionAll2"."UserId" AS "C15",
"UnionAll2"."RoleId" AS "C16",
"UnionAll2"."UserId1" AS "C17",
"UnionAll2"."C2" AS "C18",
"UnionAll2"."C3" AS "C19",
"UnionAll2"."C4" AS "C20",
"UnionAll2"."C5" AS "C21",
"UnionAll2"."C6" AS "C22",
"UnionAll2"."C7" AS "C23",
"UnionAll2"."C8" AS "C24",
"UnionAll2"."C9" AS "C25"
FROM (
(SELECT 1 FROM (
(SELECT
CASE WHEN ("Extent2"."UserId" IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C1",
"Alias1"."AccessFailedCount",
"Alias1"."Id",
"Alias1"."Email",
"Alias1"."EmailConfirmed",
"Alias1"."PasswordHash",
"Alias1"."SecurityStamp",
"Alias1"."PhoneNumber",
"Alias1"."PhoneNumberConfirmed",
"Alias1"."TwoFactorEnabled",
"Alias1"."LockoutEndDateUtc",
"Alias1"."LockoutEnabled",
"Alias1"."AccessFailedCount" AS "AccessFailedCount1",
"Alias1"."UserName",
"Extent2"."UserId",
"Extent2"."RoleId",
"Extent2"."UserId" AS "UserId1",
CAST (NULL AS int4) AS "C2",
CAST (NULL AS varchar) AS "C3",
CAST (NULL AS varchar) AS "C4",
CAST (NULL AS varchar) AS "C5",
CAST (NULL AS varchar) AS "C6",
CAST (NULL AS varchar) AS "C7",
CAST (NULL AS varchar) AS "C8",
CAST (NULL AS varchar) AS "C9"
FROM
(SELECT
"Extent1"."Id",
"Extent1"."Email",
"Extent1"."EmailConfirmed",
"Extent1"."PasswordHash",
"Extent1"."SecurityStamp",
"Extent1"."PhoneNumber",
"Extent1"."PhoneNumberConfirmed",
"Extent1"."TwoFactorEnabled",
"Extent1"."LockoutEndDateUtc",
"Extent1"."LockoutEnabled",
"Extent1"."AccessFailedCount",
"Extent1"."UserName"
FROM "public"."AspNetUsers" AS "Extent1"
WHERE upper("Extent1"."UserName") = upper((('tester123'))) OR
upper("Extent1"."UserName") IS NULL AND
upper((('tester123'))) IS NULL LIMIT 1) AS "Alias1"
LEFT OUTER JOIN "public"."AspNetUserRoles" AS "Extent2"
ON "Alias1"."Id" = "Extent2"."UserId")
UNION ALL (SELECT
2 AS "C1",
"Alias2"."AccessFailedCount",
"Alias2"."Id",
"Alias2"."Email",
"Alias2"."EmailConfirmed",
"Alias2"."PasswordHash",
"Alias2"."SecurityStamp",
"Alias2"."PhoneNumber",
"Alias2"."PhoneNumberConfirmed",
"Alias2"."TwoFactorEnabled",
"Alias2"."LockoutEndDateUtc",
"Alias2"."LockoutEnabled",
"Alias2"."AccessFailedCount" AS "AccessFailedCount1",
"Alias2"."UserName",
CAST (NULL AS varchar) AS "C2",
CAST (NULL AS varchar) AS "C3",
CAST (NULL AS varchar) AS "C4",
"Extent4"."Id" AS "Id1",
"Extent4"."UserId",
"Extent4"."ClaimType",
"Extent4"."ClaimValue",
CAST (NULL AS varchar) AS "C5",
CAST (NULL AS varchar) AS "C6",
CAST (NULL AS varchar) AS "C7",
CAST (NULL AS varchar) AS "C8"
FROM (SELECT
"Extent3"."Id",
"Extent3"."Email",
"Extent3"."EmailConfirmed",
"Extent3"."PasswordHash",
"Extent3"."SecurityStamp",
"Extent3"."PhoneNumber",
"Extent3"."PhoneNumberConfirmed",
"Extent3"."TwoFactorEnabled",
"Extent3"."LockoutEndDateUtc",
"Extent3"."LockoutEnabled",
"Extent3"."AccessFailedCount",
"Extent3"."UserName"
FROM "public"."AspNetUsers" AS "Extent3"
WHERE upper("Extent3"."UserName") = upper((('tester123'))) OR
upper("Extent3"."UserName") IS NULL AND
upper((('tester123'))) IS NULL LIMIT 1) AS "Alias2"
INNER JOIN "public"."AspNetUserClaims" AS "Extent4" ON
"Alias2"."Id" = "Extent4"."UserId")) AS "UnionAll2_1")
UNION ALL (
SELECT
3 AS "C1",
"Alias3"."AccessFailedCount",
"Alias3"."Id",
"Alias3"."Email",
"Alias3"."EmailConfirmed",
"Alias3"."PasswordHash",
"Alias3"."SecurityStamp",
"Alias3"."PhoneNumber",
"Alias3"."PhoneNumberConfirmed",
"Alias3"."TwoFactorEnabled",
"Alias3"."LockoutEndDateUtc",
"Alias3"."LockoutEnabled",
"Alias3"."AccessFailedCount" AS "AccessFailedCount1",
"Alias3"."UserName",
CAST (NULL AS varchar) AS "C2",
CAST (NULL AS varchar) AS "C3",
CAST (NULL AS varchar) AS "C4",
CAST (NULL AS int4) AS "C5",
CAST (NULL AS varchar) AS "C6",
CAST (NULL AS varchar) AS "C7",
CAST (NULL AS varchar) AS "C8",
"Extent6"."LoginProvider",
"Extent6"."ProviderKey",
"Extent6"."UserId",
"Extent6"."UserId" AS "UserId1"
FROM (SELECT
"Extent5"."Id",
"Extent5"."Email",
"Extent5"."EmailConfirmed",
"Extent5"."PasswordHash",
"Extent5"."SecurityStamp",
"Extent5"."PhoneNumber",
"Extent5"."PhoneNumberConfirmed",
"Extent5"."TwoFactorEnabled",
"Extent5"."LockoutEndDateUtc",
"Extent5"."LockoutEnabled",
"Extent5"."AccessFailedCount",
"Extent5"."UserName"
FROM "public"."AspNetUsers" AS "Extent5"
WHERE upper("Extent5"."UserName") = upper((('tester123'))) OR
upper("Extent5"."UserName") IS NULL AND
upper((('tester123'))) IS NULL LIMIT 1) AS "Alias3"
INNER JOIN "public"."AspNetUserLogins" AS "Extent6" ON
"Alias3"."Id" = "Extent6"."UserId")) AS "UnionAll2"
ORDER BY "UnionAll2"."Id" ASC ,"UnionAll2"."C1" ASC


correct part of the sql above would be

... "UnionAll2"."C9" AS "C25"
FROM (
(SELECT * FROM ( ...

Be tried with:
- Npg EntityFramework 2.2.3, 2.2.2, 2.2.0
- IdentitiyFramework 2

[Emil Lenngren]

Hi. Please see https://github.com/npgsql/npgsql/issues/461

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/dceb734b-94ef-45c3-b92a-a362012f424c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.