CODEWITHSUNDEEP
nhibernatefluent-nhibernate
Sam
Sam

Reputation: 15761

NHibernate Relationships in Oracle

After countless hours of trying to make Entity Framework work with Oracle, I have given up and started down the path of NHibernate.

I am struggling with the lingo a bit, and have a question. Given the following classes, how do I make NHibernate (Fluent Mappings) output something similar to the SQL below using the WriteOffApprovalUser.UserName as the key to the Employee.MailID field.

C# Classes

public class WriteOffApprovalUser : EntityBase<WriteOffApprovalUser>
{
    public virtual string UserName { get; set; }
    public virtual Employee.Employee Employee { get; set; }
}

public class Employee : EntityBase<Employee>
{
    public virtual string EmployeeID { get; set; }
    public virtual string EmployeeStatusCode { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string PreferredName { get; set; }
    public virtual string JobTitle { get; set; }
    public virtual string Division { get; set; }
    public virtual string Department { get; set; }
    public virtual string Location { get; set; }
    public virtual string City { get; set; }
    public virtual string DeskLocation { get; set; }
    public virtual string MailID { get; set; }
    public virtual string Phone { get; set; }
    public virtual string Fax { get; set; }
    public virtual string SecCode { get; set; }
    public virtual string SupervisorID { get; set; }
}

SQL

SELECT c.user_name,
        a.LAST_NAME
     || ', '
     || DECODE (a.PREFERRED_NAME, ' ', a.FIRST_NAME, a.preferred_name)
        writeoff_approval_name
FROM writeoff_approval_user c, adp_employee a
WHERE c.USER_NAME = a.USER_ID AND c.exp_date IS NULL
ORDER BY 2

Upvotes: 0

Views: 96

Answers (1)

guillem
guillem

Reputation: 2988

In NHibernate all is about mapping the right way. If you're using fluent you should have defined a reference in WriteOffApprovalUser to an Employee entity. Like in this basic tutorial

public class WriteOffApprovalUserMap : ClassMap<WriteOffApprovalUser> 
{
  public WriteOffApprovalUserMap()
  {
    Id(x => x.UserName);
    References(x => x.Employee);
  }
}

Then all you need to do is a simple query like

List<Employee> employees = session.Where(e => e.exp_date == null);

I haven't seen mapped any Date but I this easy to fix.

Then to have the DECODE feature I will suggest you to do some DDD so instead of simply doing an anemic class for Employee create a property that returns the composed user name.

class Employee
{
    public string ComposedName
    {
        get {
            return this.LastName + string.IsNullOrEmpty(this.preferedName) ? 
            this.FirstName : this.PreferedName;
         }
     }
}

To me that should be treat as a calculation and there is no need to do it in the SQL query. As a bonus this code can be Unit tested.

Upvotes: 1

Related Questions