I am trying to retrieve a list of Items using LINQ to NHibernate, but it is a complex query involving multiple tables including a left join. I was originally using HQL, but I wanted to return an IQueryable so that I could dynamically add where clauses as needed based on user input. I am trying to populate a DTO that has some fields from a few different tables.
The SQL query would be something like this:
SELECT
i.Id as ItemId,
s.StemNumber,
i.ItemNumber,
i.Prompt,
s.Question,
s.Area,
i.Status,
ss.SurveyFk as SurveyId
FROM Items i
INNER JOIN ItemStem s
ON i.Id = s.ItemFk
LEFT JOIN SectionItems si
ON si.ItemFk = i.Id
LEFT JOIN SurveySections ss
ON ss.SectionFk = si.SectionFk
The following LINQ works when there are no data (i.e., from a unit test), but when there are actual data, I get the IndexOutOfBounds exception. I've seen this addressed in another post, but not with a satisfactory solution or explanation.
public IQueryable<ItemSummaryDto> GetItemsProjected()
{
ISession session = NHibernateSession.Current;
//creating the subquery for the left join first
var surveys = from si in session.Linq<SectionItem>()
from ss in si.Section.SurveySections
select new { ItemId = si.Item.Id, SurveyId = ss.Survey.Id };
//LINQ to NHibernate doesn't support joins, so using the 'in' syntax
//described in yet another post
IQueryable<ItemSummaryDto> q =
from s in session.Linq<ItemStem>()
from p in s.Items
from x in surveys.Where(t => t.ItemId == p.Id).DefaultIfEmpty()
select new ItemSummaryDto
{
ItemId = p.Id,
StemNumber = s.StemNumber,
ItemNumber = p.ItemNumber,
Prompt = p.Prompt,
Question = s.Question,
Area = s.Area,
Status = p.Status,
SurveyId = x.SurveyId
};
return q;
}
fails here (items.Count())
IPagination<ItemSummaryDto> items = GetItemsProjected().AsPagination(1, 10);
//add where clauses, or not
if (items.Count() > 0)
..do something
Thanks in advance!