如何使用Criteria或QueryOver API编写此linq查询 [英] How to write this linq query with Criteria or QueryOver API

问题描述

是否可以在下面将使用Query(linq)api编写的代码转换为NHibernate中的Criteria或QueryOver API?我正在使用它来将数据格式化为DTO，它也只需要与数据库一次往返即可.

Is it possible to convert this code at below, written by using Query(linq) api to Criteria or QueryOver API in NHibernate? I'm using this to format data into DTO's also it works with just one round-trip to db.

注意::我尝试了Transformers.aliastobean，但一次只能使用一个变压器.可以在一个查询中使用多个转换器吗?

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

     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();

推荐答案

让我们从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实例并填写其字段"列表

This QueryOver will generate the SQL statement which will contain all EntityTypes with their Fields. Now we have to extract the unique EntityType instances and fill their Fields lists

这里有DTO类的概述(以及上面的QueryOver，这些仅包含很少的属性作为示例):

There is an overview of DTO classes (as well as QueryOver above, these contain only ver few properties as an example):

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是临时工具，仅用于此目的...但是这种方法可以扩展

MyTransformer is ad hoc one, only for this purpose... but this approach could be extended

这篇关于如何使用Criteria或QueryOver API编写此linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！