Quick info on what I am trying to accomplish. I have some history tables and am joining the data into a single row for each history item. The issue I am having is trying to replicate a STUFF(query,1,1,'') in NHibernate. I have all my relationships setup and working and the query with just the joins works fine, just cant figure out how to implement the STUFF with a query into it.
This is the entire query:
SELECT h.*, u.FirstName, u.LastName, eh.*,
STUFF((
SELECT CONCAT(c.Name, ' - ')
FROM SubHistory sh
LEFT JOIN Cust c ON c.CustID = sh.SubCustId
WHERE h.Id = sh.HistoryId
FOR XML PATH ('')), 1, 1, ''
) AS Subs
FROM History h
LEFT JOIN EmailHistory eh ON eh.HistoryId = h.Id
LEFT JOIN Usr u ON u.UsrID = h.UserId
The Result I need is (columns):
H.ID - H.ReportID - H.UserID - (Concatenated Subs) - u.FirstName - u.LastName - eh.Email
I can do this without the STUFF in Nhibernate like so:
IList<History> hist = session.QueryOver<History>(() => historyAlias)
.Left.JoinAlias(() => historyAlias.User, () => usrAlias)
.Left.JoinAlias(() => historyAlias.Email, () => emailAlias)
.List();
I followed the tutorial here http://blog.andrewawhitaker.com/blog/2014/08/15/queryover-series-part-7-using-sql-functions/ for creating the STUFF function, but I do not think it was created with the idea of using it the way I want to and I have been unable to successfully make it work.
So, my question can and how can I implement STUFF the way I want to? Or is there a better approach to getting the info I want?
Database Example for reference:
History Table
ID - ReportID - Name - UserID
SubHistory Table - (Can be many)
ID - HistoryID - SubInfo
UserTable
ID - FirstName - LastName
Cust Table
ID - CustInfo
EmailHistory Table - (Can only have one)
ID - HistoryID - Email