在nhibernate中将具有特定列的两个表联接起来 [英] Joining two tables with specific columns in nhibernate

问题描述

我有两个表:客户和产品

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; }
}

在数据库中使用此值:

------------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     |

当我在SQL Server管理中运行此查询时:

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结果是:

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

当我运行此查询

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

结果是:

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

如何在NHiberante查询中表达这一点?

How can I express that in NHiberante query?

更新:一些尝试

我尝试此代码

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[]>() 

不起作用！

推荐答案

基于问题中的信息，有一些QueryOver语法的草稿，应该有助于理解.首先，我们应该创建一些DTO，以表示结果:

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; }
}

我个人希望看到 Customer 而不是 Customers ...，但还是比CustomersID

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

因为我们的映射必须是<many-to-one表示引用关系，而不仅仅是<property-表示值类型/整数.

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

现在查询:

// 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>();

那应该给一些想法如何使用NHibernate进行查询. 此外，我建议使用IList<Products>

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

这篇关于在nhibernate中将具有特定列的两个表联接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！