How to write this linq query using criteria or QueryOver API

Question

Is it possible to convert this code below written using Query (linq) api in Criteria or QueryOver API in NHibernate? I use this to format data in DTO, and it only works with one round in db.

Note: I tried transformers.aliastobean, but I can only use one transformer at a time. Is it possible to use several transformers in one request?

     from entityType in Provider.GetSession().Query<crmEntityType>()
     .Fetch(x => x.Association)
     .Fetch(x => x.Fields)
     .AsEnumerable()
     where instanceIDs.Contains(entityType.Instance.instanceID)
     select new EntityTypeDTO()
     {
     ID = entityType.ID,
     Title = entityType.Title,
     Association = entityType.Association.Distinct().Select(asc => asc.ID).ToArray<int>(),
     Fields = entityType.Fields.Distinct().Select(fi => new CustomFieldDTO { 
 ID = fi.ID,
 Name =  fi.Name,
 Value = fi.Value,
 EntityType = fi.EntityType.ID,
 Type = fi.Type 
}).ToList()
     }).ToList();

Answer 1

Start with the syntax QueryOver:

// external filter data
instanceIDs = new int[] { 1, 2, 3 };

// aliasing
EntityTypeDTO entityDTO = null;
CustomFieldDTO fieldDTO = null;
Field field = null;

IQueryOver<EntityType, Field> query = Session.QueryOver<EntityType>()

  // filter Entity by ID list
  .Where(Restrictions.On<EntityType>(c => c.ID).IsIn(instanceIDs))

  // Join Fields
  .JoinQueryOver<Field>(c => c.Fields, () => field)
  .SelectList(list => list

    // entity
    .Select(c => c.ID)
    .Select(c => c.Title)
    // ... more Entity properties

    // field collection
    .Select(() => field.ID)
    .Select(() => field.Name)
    // ... more Field properties
  )
  .TransformUsing(new MyTransformer()); // see below

var dtos = query.List<EntityTypeDTO>();

QueryOver SQL, EntityTypes . EntityType

DTO ( QueryOver , ):

public class EntityTypeDTO
{
  public virtual int ID { get; set; }
  public virtual string Title { get; set; }
  public virtual IList<CustomFieldDTO> Fields { get; set; }
  ...
}
public class CustomFieldDTO 
{
  public virtual int ID { get; set; }
  public virtual string Name { get; set; }
  ...
}

, , MyTransformer():

public class MyTransformer : IResultTransformer
{
  // rows iterator
  public object TransformTuple(object[] tuple, string[] aliases)
  {
    var entity = new EntityTypeDTO
    {
      ID = (int)tuple[0],         // aliases should be used
      Title = tuple[1] as string  // first two are belong to Entity
    };
    var field = new CustomFieldDTO
    {
      ID = (int)tuple[2],         // last 2 columns are for a Field
      Name = tuple[3] as string   // see SelectList in QueryOver
    };
    entity.Fields = new List<CustomFieldDTO> { field };
    return entity;
  }

  // convert to DISTINCT list with populated Fields
  public System.Collections.IList TransformList(System.Collections.IList collection)
  {
    var results = new List<EntityTypeDTO>();
    foreach(var item in collection)
    {
      var entity = item as EntityTypeDTO;

      // was already the same ID appended
      var existing = results.SingleOrDefault(c => c.ID.Equals(entity.ID));
      if(existing != null)
      {
        // extend fields
        existing.Fields.Add(entity.Fields.First());
        continue;
      }

      // new ID found
      results.Add(entity);
    }
    // DISTINCT list of Entities, with populated FIELDS
    return results;
  }
  ...

MyTransformer , ...