We're updating the issue view to help you get more done. 

NHibernate Linq Query To Postgresql Failing

Description

I'm having a lot of trouble trying to figure out why this linq query might be failing:

1 2 var result = _userRepo.Table.FirstOrDefault(x => x.NormalizedUserName == user.NormalizedUserName)

The error I am receiving is this:

1 {"42883: operator does not exist: character varying = jsonb"} System.Exception {Npgsql.PostgresException}

When I get the query and run it against the postgresql database it works perfectly fine:

1 2 3 4 5 6 7 8 9 10 11 12 13 select applicatio0_.AspNetUser_Id as Id1_0_, applicatio0_1_.UserName as UserNa2_0_, applicatio0_1_.NormalizedUserName as Normal3_0_, applicatio0_1_.Email as Email4_0_, applicatio0_1_.NormalizedEmail as Normal5_0_, applicatio0_1_.PasswordHash as Passwo6_0_, applicatio0_1_.SecurityStamp as Securi7_0_, applicatio0_1_.EmailConfirmed as EmailC8_0_ from ApplicationUser applicatio0_ inner join AspNetUsers applicatio0_1_ on applicatio0_.AspNetUser_Id=applicatio0_1_.Id where applicatio0_1_.NormalizedUserName=? limit 1

I just replaced the question mark at the end with a random string and there was no issue.

Is this a bug with NHibernate or Npgsql? How can I get this simple linq query to work? It works if I use Criteria or QueryOver, but I'd like to fix this bug with Linq.

Here is the IdentityUser class:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 public class IdentityUser : EntityWithTypedId<string> { public virtual string UserName { get; set; } public virtual string NormalizedUserName { get; set; } public virtual string Email { get; set; } public virtual string NormalizedEmail { get; set; } public virtual string PasswordHash { get; set; } public virtual string SecurityStamp { get; set; } public virtual bool EmailConfirmed { get; set; } public virtual ICollection<IdentityRole> Roles { get; protected set; } public virtual ICollection<IdentityUserClaim> Claims { get; protected set; } public virtual ICollection<IdentityUserLogin> Logins { get; protected set; } public IdentityUser() { this.Roles = new List<IdentityRole>(); this.Claims = new List<IdentityUserClaim>(); this.Logins = new List<IdentityUserLogin>(); } public IdentityUser(string userName) : this() { this.UserName = userName; } }

Here is the mapping by code file:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 public class IdentityUserMap : ClassMapping<IdentityUser> { public IdentityUserMap() { Table("AspNetUsers"); Id(x => x.Id, m => m.Generator(new UUIDHexCombGeneratorDef("D"))); Property(x => x.UserName); Property(x => x.NormalizedUserName); Property(x => x.PasswordHash); Property(x => x.SecurityStamp); Property(x => x.Email); Property(x => x.NormalizedEmail); Property(x => x.EmailConfirmed); Bag(x => x.Claims, map => { map.Key(k => k.Column("UserId")); }, rel => { rel.OneToMany(); }); Set(x => x.Logins, cam => { cam.Table("AspNetUserLogins"); cam.Key(km => km.Column("UserId")); cam.Cascade(Cascade.All.Include(Cascade.DeleteOrphans)); }, map => { map.Component(comp => { comp.Property(p => p.LoginProvider); comp.Property(p => p.ProviderKey); comp.Property(p => p.DisplayName); }); }); Bag(x => x.Roles, map => { map.Table("AspNetUserRoles"); map.Key(k => k.Column("UserId")); }, rel => rel.ManyToMany(p => p.Column("RoleId"))); } }

I have a separate mapping file that uses the sql types jsonb and bytea that seem to be causing this issue...

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 public class ActivityMap : ClassMapping<Activity> { public ActivityMap() { Table("Activity"); Id(x => x.Id, m => m.Generator(Generators.Sequence, g => g.Params(new { sequence = "hibernate_sequence" }))); Property(x => x.Entity, m => { m.Type<JsonbUserType>(); m.Column(c => c.SqlType("jsonb")); }); Property(x => x.EditedByUser); Property(x => x.TypeName); Property(x => x.TypeId); Property(x => x.ModifiedDate); Property(x => x.FileBytes, m => { m.Column(c => c.SqlType("bytea")); }); } }

When I remove this from the mapping it works just fine... Why would those sql types break the linq provider?

Environment

This is an asp.net 4.6.2 project developed on Windows 7 & 10.

Status

Assignee

Alexander Zaytsev

Reporter

Sammi Maan Sinno

Components

Affects versions

4.1.0

Priority

Major