NHibernate - query specific columns and return individual records?

Question

I am new to NH.

I have a table in an old database that looks like this:

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

I would like to return a DISTINCT dataset using NHibernate, selecting only specific columns and using the WHERE statement. SQL would look something like this:

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

Using googled, 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 is DTO:

 public class CompanyDto { public int CompanyId { get; set; } public string SomeName { get; set; } } 

And the essence:

 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 result in recording entries. I know that usually I will have to use another transformation (DistinctRootEntity), but I can not use two transformations. How can I combine all the things I want in one call? It must be possible its basic SQL ....

I need:

• do not use HQL
• does not return all columns for a record
• do not return duplicate rows

Answer 1

there is a projection for this

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