如何使用Criteria或QueryOver API编写此linq查询

Question

是否可以将下面的代码转换为Criteria或NHibernate中的QueryOver api？我正在使用它将数据格式化为DTO，而且它只适用于到数据库的一次往返。

注意：我尝试了transformers.aliastobean，但一次只能使用一个transformer。是否可以在一个查询中使用多个转换器？

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

让我们从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's 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语句，该语句将包含所有EntityType及其字段。现在我们必须提取唯一的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是一个特别的，仅用于此目的。。。但是这种方法可以扩展