Joining two tables with specific columns in nhibernate

Question

I have two table: Customers and Products

public class Customers
{
    public virtual int Id { get; set; }
    public virtual string CName { get; set; }
    public virtual int Age { get; set; }
    public virtual string Address { get; set; }
    public virtual int Salary { get; set; }
}
public class Product
{
    public virtual int Id { get; set; }
    public virtual string PName { get; set; }
    public virtual Customers CustomerID { get; set; }
    public virtual int Amount { get; set; }
}

whit this Values in DB:

------------Customer Table----------------
| id  |  CName  | Age | Address | Salary |
------------------------------------------
| 1   |  Ben    | 18  |    a    | 1000   |
| 2   |  Mark   | 20  |    b    | 2000   |
| 3   |  Ben    | 18  |    a    | 3000   |
| 4   |  Ben    | 19  |    c    | 4000   |
| 5   |  Mark   | 20  |    b    | 5000   |
| 6   |  Jane   | 21  |    d    | 6000   |

------------Customer Table----------------
|  id  |  PName  | CustomerID_id | Amount |
------------------------------------------
| 1    |  A      |       1       | 5      |
| 2    |  B      |       2       | 10     |
| 3    |  C      |       1       | 15     |
| 4    |  D      |       2       | 20     |
| 5    |  E      |       2       | 25     |
| 6    |  F      |       6       | 30     |
| 7    |  G      |       6       | 40     |

When I run this query in SQL Server Management:

SELECT CName , Amount
FROM [TestNhibernate].[dbo].[Product]
 Inner Join [TestNhibernate].[dbo].[Customers]
   on [TestNhibernate].[dbo].[Product].[Customerid_id] 
             = [TestNhibernate].[dbo].[Customers].[id]

SQL result is:

-------------------
| CName  | Amount |
-------------------
| Ben    | 5      |
| Mark   | 10     |
| Ben    | 15     |
| Mark   | 20     |
| Mark   | 25     |
| Jane   | 30     |
| Jane   | 40     |

And when I run this query

SELECT CName , Sum(salary) as SumSalary, sum(amount) as SumAmount
FROM [TestNhibernate].[dbo].[Product]
  Inner Join [TestNhibernate].[dbo].[Customers]
   on [TestNhibernate].[dbo].[Product].[Customerid_id] 
             = [TestNhibernate].[dbo].[Customers].[id] 
Group By Cname

results is :

----------------------------------
| CName  | SumSalary | SumAmount |
----------------------------------
| Ben    | 2000      |  20       | 
| Jane   | 12000     |  70       |
| Mark   | 6000      |  55       |
----------------------------------

How can I express that in NHiberante query?

UPDATE: some attempts

I try this code

session
    .QueryOver<Product>()
    .JoinQueryOver<Customers>(p => p.CustomerID)
    .SelectList(w => w
      .Select(x => x.Amount)
      .Select(z => z.CustomerID))
    .List<object[]>() 

this is done but when i write this code

session
    .QueryOver<Product>()
    .JoinQueryOver<Customers>(p => p.CustomerID)
    .SelectList(w => w
      .Select(x=>x.Amount)
      .Select(z=>z.CustomerID.CName))
    .List<object[]>() 

doesn't work!

Answer 1

Based on the information in the question, there is some draft of the QueryOver syntax, which should help to understand. Firstly we should create some DTO, representing the result:

public class ProductDTO
{
    public virtual string ClientName { get; set; }
    public virtual decimal SumSalary { get; set; }
    public virtual decimal SumAmount { get; set; }
}

Now, we should have business model with a bit more standard naming:

public class Product
{
    ...
    // instead of this
    //public virtual Customers CustomerID { get; set; }
    // we should use 
    public virtual Customers Customers { get; set; }
}

Personally I would rather see Customer than Customers... but still better than CustomersID

Because our mapping here must be <many-to-one representing reference relation, not just <property - representing the value type / integer.

Now the query:

// to have access to client name
Customers client = null;
// that would be result - used for proper columns aliasing
ProductDTO dto = null;

var result = session.QueryOver<Occupation>()
    .JoinQueryOver<Customers>(p => p.Customers , () => client)
    .SelectList(w => w
        // SUM
        .SelectSum(x => x.Amount)
            .WithAlias(() => dto.SumAmount)
        .SelectSum(x => x.Salary)
            .WithAlias(() => dto.SumSalary)
        // GROUP BY
        .Select(x => client.CName)
            .WithAlias(() => dto.ClientName)
    )
    // we do have enough info to ask NHibernate for 
    // fully typed result - Product DTO
    .TransformUsing(Transformers.AliasToBean<ProductDTO>())
    .List<ProductDTO>();

That should give some idea how to do querying with NHibernate. Also, I would suggest to extend Customer with IList<Products>