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

问题描述

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

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，但我一次只能使用一个transformer.是否可以在一个查询中使用多个转换器?

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 语法开始:

Let's start with the QueryOver syntax:

// 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 将生成包含所有 EntityType 及其字段的 SQL 语句.现在我们必须提取唯一的 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屋！