Fetch Only the distinct records -Fluent Nhibernate Mapping

Question

I have two tables

Table A:
Id  | Attachment_id
1   | 123

Table B:
Id | doc_name |Attachment_id | version
1  | sab.txt  | 123          | 1
2  | sab.txt  | 123          | 2

When i upload a file with same name and content it is saved with increasing versions. As the attachment_id is repeating it's causing a blow up. Can you please let me know how to provide the FluentNhibernate mappings to fetch only the latest version of the doc (i.e 2, sab.txt,123,2)

Answer 1

using a Formula which fetches the last entry

References(x => x.LastAttachment)
    .Formula("(Select b.Id FROM B b WHERE b.Attachment_id = Attachment_id ORDER BY version DESC LIMIT 1)");