views:

26

answers:

0

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!