One of the fields is count (*) NHibernate

Question

Is it possible to match the query as follows:

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

I am currently using 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;}
}

:

<property name="Counter" formula="(select count(*) from account where record_id=id )"      type="long"/>

Answer 1

Yes, you should use the formula .

Your mappings may look like this:

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

Answer 2

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 type it “usually” as “one to many”. Remember to use lazy loading. You can make it bidirectional.

You can create an optimized query that prevents the loading of accounts only for counting them:

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

, . .