one of the fields is count(*) NHibernate

Can I do mapping to query like this :

select id,name,address,(select count(*) from account where record_id=id ) as counter
from data where id = :id

Currently , I'm using a native SQL .

class person
{
    public virtual long Id{get;set;}
    public virtual string Name{get;set;}
    public virtual string Address{get;set;}
    public virtual long Counter{get;set;}
}

mapping :

<property name="Counter" formula="(select count(*) from account where record_id=id )"      type="long开发者_开发问答"/>

Yes, you should use formula.

Your mappings might look like this:

<property name="CountOfAccounts"
    formula="(select count(*) from account where account.id = id)"/>

It depends on the business classes you want to use. You could have these classes;

class Person
{
  int Id { get; private set; }
  string Name { get; set; }
  string Address { get; set; }
  IList<Account> Accounts { get; private set; } 
}

class Account
{
  // ...
}

Then you map it "normally" as one-to-many. Don't forget to make use of lazy loading. You may make it bidirectional.

You may create an optimized query which prevents loading the accounts just to count them:

select 
  p,
  size(p.Accounts)
from
  Person p
where 
  p.id = :id

This produces quite a similar query then yours. You get a Person in the first column and the number of accounts in the second.