Nhibernate Joining another table

Question

I need to perform a join on the customer. Where address is primary and populate PrimaryAddress1 and PrimaryCity field. Customer mapping already has hasmany relationship with address class but I do not want to fetch all addresses (performance issues).

Please help..

classes:

public class Customer
{
    public Customer()
    {
        Addressess = new List<Address>();
    }
   public virtual int CustomerID { get; set; }
    public virtual int? BranchID { get; set; }
    public virtual int? CustTypeID { get; set; }
    public virtual string CompanyName { get; set; }
    public virtual string Prefix { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string MiddleName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string PrimaryAddress1 { get; set; }
    public virtual string PrimaryCity { get; set; }
    public virtual List<Address> Addresses { get; set; }
}


public class Address
{
    public Address()
    {
    }
    public virtual int LocationID { get; set; }
    public virtual int? CustomerID { get; set; }
    public virtual string LocationName { get; set; }
    public virtual string Address1 { get; set; }
    public virtual string Address2 { get; set; }
    public virtual string Address3 { get; set; }
    public virtual string City { get; set; }
    public virtual bool Primary { get; set; }
}

Mapping:

public TblCustomerMap()
{
    Table("tblCustomers");
    LazyLoad();
    Id(x => x.CustomerID).GeneratedBy.Identity().Column("CustomerID");
    Map(x => x.ProfileID).Column("ProfileID");
    Map(x => x.BranchID).Column("BranchID");
    Map(x => x.DateEntered).Column("DateEntered");
    Map(x => x.DateTerminated).Column("DateTerminated");
    Map(x => x.CustTypeID).Column("CustTypeID");
    Map(x => x.CompanyName).Column("CompanyName").Not.Nullable().Length(50);
    Map(x => x.Prefix).Column("Prefix").Not.Nullable().Length(50);
    Map(x => x.FirstName).Column("FirstName").Not.Nullable().Length(50);
    Map(x => x.MiddleName).Column("MiddleName").Not.Nullable().Length(50);
    Map(x => x.LastName).Column("LastName").Not.Nullable().Length(50);
    HasMany(x => x.Address).KeyColumn("CustomerID");
    Map(x => x.PrimaryAddress1).Column("PrimaryAddress1") // from table tbladdress where address is primary and get data from address1 column
    Map(x => x.PrimaryCity).Column("PrimaryCity") // from table tbladdress where address is primary and get data from city column
}

Query:

 var query = session
            .QueryOver<Customer>(() => customer)
            .JoinQueryOver(() => customer.Addresses, () => address)
            .Where(() => address.Primary)
            .List();

        foreach (var customer1 in query)
        {
            customer1.PrimaryAddress1 = customer1.Addresses[0].Address1;
            customer1.PrimaryCity = customer1.Addresses[0].City;
            customer1.PrimaryState = customer1.Addresses[0].StateOrProvince;

        }

New query:

  var query = session.Query<Customer>()
            .SelectMany(c => c.Addresses,
                        (c, a) => new {c, a})
            .Where(cust => cust.a.Primary)
            .Select(item => new CustomerView()
                              {
                                 CustomerID = item.c.CustomerID,
                                 CompanyName=  item.c.CompanyName,
                                 FirstName=  item.c.FirstName,
                                  LastName=item.c.LastName,
                                  Address1=item.a.Address1,
                                  Address2=item.a.Address2,
                                 Address3= item.a.Address3,
                                 City= item.a.City,
                                 StateOrProvince= item.a.StateOrProvince
                              });
        return query.ToList();

Answer 1

I have a similar address structure and struggled with this. One major concern is ensuring that a Customer record is returned if the Customer has no primary address (shouldn't happen but...).

Take a look at join mapping if you want to include the address fields in the Customer entity.

My approach is to use a data transfer object (DTO or view), in this case CompanyDto:

    internal static QueryOver<Company> GetCompanyDtoQuery()
    {
        Address addr = null;
        CompanyDto dto = null;

        var query = QueryOver.Of<Company>()
            .Left.JoinAlias(c => c.Addresses, () => addr)
            .SelectList(list =>
                        {
                            list.Select(c => c.CompanyId).WithAlias(() => dto.CompanyId)
                            list.Select(a -> addr.AddressId).WithAlias(() => dto.AddressId
                            return list;
                        })
            .Where(a => addr.AddressId == null || addr.IsPrimaryAddress)
            .TransformUsing(new AliasToDtoDeepTransformer<CompanyDto>("PrimaryAddress"));
        return query;
    }

[EDIT]

It's easier to do this using Query, for example:

            var target = (from c in session.Query<Company>()
                          from a in c.Addresses
                          where a.IsPrimaryAddress
                          select new
                              {
                                  c.CompanyId,
                                  a.Address1
                              })
                .Take(10).ToList();
            Assert.IsTrue(target.Any());

The problem with the query you posted is that you are forcing a lazy load by accessing the collection by index. You can use eager loading to fetch all the addresses in one select:

             var target = session.QueryOver<Company>()
                                 .Fetch(c => c.Addresses).Eager
                                 .Take(10).List();
             Assert.IsTrue(target.Any());

However, as I said in my original reply you should look at join or another approach instead of including fields from another table in your domain model.

Answer 2

There are (at least) two ways how to achieve this.

1) The more intuitive and readable is extending DB and adjusting NHibernate mapping. We will need new view viewPrimaryAddress to be created.

SELECT ***columns*** FROM [tbladdress] WHERE Primary = true

And Customer mapping will look like this:

Join("[viewPrimaryAddress]", 
{
  m.Fetch.Join();
  m.KeyColumn("CustomerID");
  m.Map(t => t.PrimaryAddress1).Column("PrimaryAddress1");
  m.Map(t => t.PrimaryCity).Column("PrimaryCity");
});

And that's it. One SQL Statement will be issued, so no need to load the Address collection

2) Oveview of the second approach

The second approach will create that view by a new class mapping, is a bit complicated, but can be done only on the application side (C# & NHiberante).

New class PrimaryAddress will be created and contain the ilter on a class level definition (xml mapping example:

<class name="PrimaryAddress" ... where="Primary = true" >

Then we can extend Customer with new many-to-one relation to PrimaryAddress. So getting properties for column "PrimaryAddress1" and "PrimaryCity" would be done via SQL select filtered in the WHERE Clause.

EXTENDED:

Next steps should guide you how to create new mapping, targeting the primary address as the one-to-one property 1) C# PrimaryAddress:

public class PrimaryAddress
{
    public virtual Customer Customer { get; set; }
    public virtual string Address { get; set; }
    public virtual string City { get; set; }
}

2) Mapping:

public TblPrimaryAddressMap()
{
    Table("tbladdress");
    LazyLoad();
    Where("Primary = 1");
    // Id as AddressId
    References(x => x.Customer).Column("CustomerID");
    Map(x => x.Address).Column("PrimaryAddress1")
    Map(x => x.PrimaryCity).Column("PrimaryCity")
}

3) New property for Customer

public class Customer
{  
   ..
   public virtual PrimaryAddress PrimaryAddress { get; set; }

4) New mapping for Customer public TblCustomerMap() { ... HasOne(x => x.PrimaryAddress)

At this moment, when you get Customer from session. You can access

customer.PrimaryAddress.Address
customer.PrimaryAddress.City

I am working with XML mapping mostly. But from these lines the concept should be clear... Playing with some fetch="join" you can load Customer and its PrimaryAddress in one SQL select

And if you need even customer property Customer.PrimaryAddress1, just wrap the PrimaryAddress.Address in the getter.

What you'll gain more, is possiblity to filter and order by over this new property PrimaryAddress

NOTE: this approach is fragile for Caching. The reason is, that whil you will change the real Address entity, there is no built mechanism to evict PrimaryAddress. And also, you should force the business layser to allow only Customer with one Address set to primary = true