c# - Nhibernate Query Over Map Entity List of Object Property To Comma Seperated DTO String Propery Using AliasToBean Transformer -

i want use query on map list of object flat comma-separated string using alias bean transformer.

this query over:

 var query = queryover<worklist>();  query.selectlist(list => list    .select(p => p.id).withalias(() => dto.id)    .select(p => p.name).withalias(() => dto.name)    .select( p => p.commodities).withalias(() => dto.commoditylist)   return query; 

the commodities in select clause list of commodity object. want map comma-separated string. code working fine want transform comma-separated string.

i using aliastobean transformer return flat dto object.

on top of head: first, should create user-defined function in sql server e.g. this:

create function listproductcommodities(@productid int) returns varchar(8000)   begin     declare @result varchar(8000)     declare @name varchar(8000)      set @result = null      declare com_cursor cursor     select name     commodities     userid = @productid      open com_cursor      fetch next com_cursor @name      while @@fetch_status = 0      begin         if @result null             set @result = @name         else             set @result = @result + ',' + @name          fetch next com_cursor @name     end      close com_cursor      deallocate com_cursor      return @result end 

then should use function in subquery:

worklist worklist = null; <your dto class name> dto = null; commodity commodity = null;  var subquery = queryover.of(() => commodity)     .where(() => commodity.productid == worklist.id) // instead of productid put foreign key property name     .select(projections.sqlfunction("listproductcommodities",         nhibernateutil.string,         projections.distinct(projections.property(() => commodity.name))); //instead of name put text field  var query = session.queryover(() => worklist)     .selectlist(list => list         .select(p => p.id).withalias(() => dto.id)         .select(p => p.name).withalias(() => dto.name)         .selectsubquery(subquery).withalias(() => dto.commoditylist))     .transformusing(transformers.aliastobean<your dto class name>())     .list<your dto class name>(); 

i made assumptions table/property/class names should adjust needs.

but main idea create function in sql server , map function subquery via nhibernate's sqlfunction. other database may have built-in "list" function sql server doesn't - should workaround it. implementation of listproductcommodities isn't uses cursor. however, on small amounts of data work.

but there's option - can issue sql query array of commodity names, build string array via string.join , assign dto's property manually.