NHibernate-QueryOver子行作为逗号分隔的值 [英] NHibernate - QueryOver Child rows as comma separated values

问题描述

我必须写一个查询，其中每个供应商我都必须从VendorContact中读取供应商的联系电话，而要一行，每个唯一供应商的行和每个电话号码的一列，以逗号分隔字符串作为主要结果.这可以在普通的 SQL 中轻松完成，但是我必须在QueryOver中完成.我无法在QueryOver SQL 中声明变量.

I have to write a query where for each vendor I have to read vendor's contact numbers from VendorContact but instead in rows, one row for each unique vendor and one columns for each phone number as comma separated string in main result. This can be done easily in plain SQL but I have to do it in QueryOver. I cannot declare variables in QueryOver SQL.

var vendorvar = Session.QueryOver<Vendor>(() => V)
    .Left.JoinQueryOver(() => v.ContactNumbers, () => VendorContact)
    .SelectList(list => lst
       .Select(() => v.Name)
       .Select(() => VendorContact.PhoneNumber))
    .TransformUsing(Transformers.AliasToBean<VendorModel>())
    .List<VendorModel>();

一个供应商的电话号码可能更多，所以我需要

There could be more phone numbers for one vendor, so I need like

Vendor         Contacts
------         -----------------------------------------
V0001          905-2343444,416-4545454,647-8484834
V0002          905-2356219,416-4111111,647-8000004

谢谢

推荐答案

通常，如果那样做真的很简单:

In general, if that is really so easy that way:

...这很容易用普通的 sql 完成，但是...

...This can be done easily in plain sql but...

只需在原始查询中使用该SQL语句即可.有一些DTO可以得到结果

just use that SQL statement in the raw query. There is some DTO for results

public class MyDto
{
    public virtual string FirstAlias { get; set; } // set name and type you need
    public virtual string SecondAlias { get; set; }
}

在这里我们进行查询，转换和列出:

And here we do query, transform and list:

// raw SQL query
var query = session
    .CreateSQLQuery("Select " +
                    " SomColumn AS FirstAlias, " +
                    " SomComputedColumn AS SecondAlias " +
                    " FROM mySchema.MyTable" +
                    " join, where, order by..... "
                   );

// here we set transformer (check the aliases)
query.SetResultTransformer(Transformers.AliasToBean<MyDto>());

// and there is nice C# result
var list = query.List<MyDto>();

这篇关于NHibernate-QueryOver子行作为逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！