Feb 162011
 

This is a silly error. I am setting up NHibernate to access records in an Oracle database. The entities are something like this:

public class Person
{
  public virtual string SysID { get; set; }
  public virtual string LastName { get; set; }
  public virtual string FirstName { get; set; }

  public virtual Registration Registration { get; set; }
  public virtual ICollection Campuses { get; set; }
  public virtual ICollection Addresses { get; set; }
}

public class Registration
{
  public virtual string SysID { get; set; }

  public virtual DateTime RegDate { get; set; }
  public virtual DateTime RegEndDate { get; set; }
}
public class PersonMapping : ClassMap

{
  public PersonMapping()
  {
    Table("VW_PERSON_ALIAS");

    Id(x => x.SysID);

    Map(x => x.LastName);
    Map(x => x.FirstName);

    References(x => x.Registration).Column("SYSID");
  }
}

public class RegistrationMapping : ClassMap
{
  public RegistrationMapping()
  {
      Table("REGISTRY");

      Id(x => x.SysID);

      Map(x => x.RegDate).Column("REGISTRATION_DATE");
      Map(x => x.RegEndDate).Column("REGISTRATION_END_DATE");
  }
}

This seemed to work fine. I could list items and they we’re querying properly. That is, right until I added skip and take to narrow down the results (for paging).

List people = _session.Query()
  .OrderBy(pr => pr.LastName)
  .Distinct()
  .Skip(5)
  .Take(10)
  .ToList();

Oracle started complaining and threw this error: 

ORA-00918: column ambiguously defined.

What had happened is this. Notice in the casing of SysID in my entity definition. Then notice the casing of SYSID in my mapping file. The column used for the registration reference in the person object is SYSID and the column used as the ID field for person is SysID. NHibernate reads those as distinct and asks the database to return SysID twice, with slightly different casing.

This is fine when we were simply reading the fields. The reason it failed when I added the take and skip commands is because NHibernate enclosed those returned values in parentheses and queried against them again in order to only return the number of rows we wanted. And when it tried to return SysID it didn’t care about case and got two items returned, making it ambiguous.

 Posted by at 12:34 pm