开发者

NHibernate - query specific columns and return distinct records?

I am new to NH.

I have a table in a legacy DB that looks like this:

Id,
CompanyId,
Description,
[LOADS of other columns here]

I would like to return a DISTINCT set of data using NHibernate, selecting only specific columns and using a WHERE statement. The SQL would looks something like this:

SELECT DISTINCT
     [table_name].CompanyId, 
     [table_name].Description 
FROM 
     [table_name]
WHERE
     [table_name].CompanyId = 2

Having googled this I came up with:

ProjectionList projections = Projections.ProjectionList();
projections.Add(Projections.Property("CompanyId"), "CompanyId");
projections.Add(Projections.Property("Name"), "SomeName");


var companyDto = session.QueryOver<Company>()
    .Where(x => x.CompanyId == 2)
    .Select(projections)
    .TransformUsing(Transformers.AliasToBean<CompanyDto>())
    .List<CompanyDto>();

if (companyDto != null)
   Console.WriteLine(string.Format("{0}, {1}", companyDto.CompanyId, companyDto.SomeName));

Where the DTO is:

public class CompanyDto
{
  public int CompanyId { get; set; }
  public string SomeN开发者_Python百科ame { get; set; }
}

And the entity is:

public class Company
{
    public virtual int Id { get; private set; }
    public virtual int CompanyId { get; set; }
    public virtual string Name { get; set; }
}

This does not bring back disinct records. I know that normally I would have to use a different transform (DistinctRootEntity) but I cannot use two transforms. How can I combine all of the things I want, into a single call? It must be possible, its basic SQL ....

I need to:

  1. not use HQL
  2. not bring back all columns for the record
  3. not bring back duplicate rows


there is a Projection for this

var projections = Projections.Distinct(Projections.ProjectionList()
    .Add(Projections.Property("CompanyId").As("CompanyId"))
    .Add(Projections.Property("Name").As("SomeName"));
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜