Problem with joining

Current Solution

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Linq.Expressions;
using System.ServiceModel;
using System.ServiceModel.Activation;
using Ks.Sf.Config;
using Ks.Sf.Modules.Dynamic;
using Ks.Sf.Modules.Taxonomies;
using Ks.Sf.Web.Mapping;
using Ks.Sf.Web.Model.Shared;
using Telerik.Microsoft.Practices.ObjectBuilder2;
using Telerik.OpenAccess;
using Telerik.Sitefinity.DynamicModules.Model;
using Telerik.Sitefinity.Model;
using Telerik.Sitefinity.Services;
using Telerik.Sitefinity.Taxonomies;
using SqlParameter = Telerik.OpenAccess.SqlParameter;

namespace Ks.Sf.Web.Services
{
    [ServiceBehavior(IncludeExceptionDetailInFaults = true, InstanceContextMode = InstanceContextMode.Single,
        ConcurrencyMode = ConcurrencyMode.Single)]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class ProductionsService : IProductionsService
    {
        private readonly ProductionListItemMapper _mapper;
        private readonly PerformanceManager _performanceManager;
        private readonly ProductionManager _productionManager;
        private readonly TaxonomyHelper _taxonomyHelper;
        private readonly TessituraSettings _tessituraSettings;

        public ProductionsService()
        {
            _productionManager = ProductionManager.GetManager();
            _performanceManager = PerformanceManager.GetManager();
            var contentLocationService = SystemManager.GetContentLocationService();
            _taxonomyHelper = new TaxonomyHelper(TaxonomyManager.GetManager());
            _mapper = new ProductionListItemMapper(_taxonomyHelper, contentLocationService);
            _tessituraSettings = _tessituraSettings = Telerik.Sitefinity.Configuration.Config.Get<TessituraSettings>();
        }

        // This is a disaster: no way to properly do this using the ORM and Dynamic Contents
        public List<ProductionListItem> GetProductionListItems(Guid taxonomyGuid, Guid taxonGuid)
        {
           
            var filters = TaxonsAre(taxonomyGuid, taxonGuid);
            var activeSeasons = GetActiveSeasons();
            filters.Add(x => activeSeasons.Contains(x.GetValue<string>(ProductionFieldNames.SeasonId)));

            var productions = _productionManager.GetLiveAndVisibleWhere(filters);

            var productionAndPerfDateTimes = new List<Tuple<DynamicContent, DateTime?>>();
            productions.ForEach(p =>
            {
                var performance = _performanceManager.GetMasters(
                    x =>
                        x.SystemParentId == p.OriginalContentId &&
                        x.GetValue<DateTime?>(PerformanceFieldNames.DateTime) >= DateTime.Now.Date)
                    .OrderBy(x => x.GetValue<DateTime?>(PerformanceFieldNames.DateTime))
                    .FirstOrDefault();
                if (performance != null)
                {
                    productionAndPerfDateTimes.Add(new Tuple<DynamicContent, DateTime?>(p, performance.GetValue<DateTime?>(PerformanceFieldNames.DateTime)));
                }
            });

            var orderProductionAndPerfDateTimes = productionAndPerfDateTimes.OrderBy( x  => x.Item2 );

            return _mapper.GetProductionListItems(orderProductionAndPerfDateTimes.Select(x => x.Item1)).ToList();
        }

        private IEnumerable<string> GetActiveSeasons()
        {
            var seasonIds = _tessituraSettings.SeasonIds.Values.Select(x => x.SeasonId.ToString());
            return seasonIds;
        }

        private List<Expression<Func<DynamicContent, bool>>> TaxonsAre(Guid taxonomyGuid, Guid taxonGuid)
        {
            var returnExpressions = new List<Expression<Func<DynamicContent, bool>>>();
            if (taxonomyGuid == Guid.Empty || taxonGuid == Guid.Empty) return returnExpressions;
            var taxonomy = _taxonomyHelper.Manager.GetTaxonomy(taxonomyGuid);
            var taxonomyName = taxonomy.Name;
            if (taxonomyName == TaxonomyNames.GenresTaxonomy)
            {
                var presenters = _taxonomyHelper.GetPresentersTaxonomy();
                var opaPresenter = presenters.Taxa.FirstOrDefault(x => x.Title == "Omaha Performing Arts");
                if (opaPresenter != null)
                    returnExpressions.Add(
                        x => x.GetValue<TrackedList<Guid>>(TaxonomyNames.PresentersTaxonomy).Contains(opaPresenter.Id));
            }
            returnExpressions.Add(x => x.GetValue<TrackedList<Guid>>(taxonomyName).Contains(taxonGuid));
            return returnExpressions;
        }
    }
}

ProblemWithJoining

// The rawest version of the issue
var manager = DynamicModuleManager.GetManager();
var foo = from parent in manager.GetDataItems(<parent_content_type>)
          join child in manager.GetDataItems(<child_content_type>) 
          on parent.Id equals child.SystemParentId
          select new { ProdId = pd.Id, PerfId = pf.Id };
                      
// More details 
var filters = TaxonsAre(taxonomyGuid, taxonGuid);
filters.Add(x => x.GetValue<DateTime?>(ProductionFieldNames.LastDate) >= DateTime.Now.Date);

var foo = from pd in _productionManager.GetMasters(filters)
    join pf in _performanceManager.GetMasters( x => x.GetValue<DateTime?>(PerformanceFieldNames.DateTime) >= DateTime.Now.Date) on pd.Id equals pf.SystemParentId
    select new { ProdId = pd.Id, PerfDate = pf.GetValue<DateTime?>(PerformanceFieldNames.DateTime) }
    into j1
    group j1 by j1.ProdId
    into g
    select new { ProdId = g.Key, PerfDateTime = g.Min(q => q.PerfDate) };


 private List<Expression<Func<DynamicContent, bool>>> TaxonsAre(Guid taxonomyGuid, Guid taxonGuid)
  {
      var returnExpressions = new List<Expression<Func<DynamicContent, bool>>>();
      if (taxonomyGuid == Guid.Empty || taxonGuid == Guid.Empty) return returnExpressions;
      var taxonomy = _taxonomyHelper.Manager.GetTaxonomy(taxonomyGuid);
      var taxonomyName = taxonomy.Name;
      if (taxonomyName == TaxonomyNames.GenresTaxonomy)
      {
          var presenters = _taxonomyHelper.GetPresentersTaxonomy();
          var opaPresenter = presenters.Taxa.FirstOrDefault(x => x.Title == "Omaha Performing Arts");
          if (opaPresenter != null)
              returnExpressions.Add(
                  x => x.GetValue<TrackedList<Guid>>(TaxonomyNames.PresentersTaxonomy).Contains(opaPresenter.Id));
      }
      returnExpressions.Add(x => x.GetValue<TrackedList<Guid>>(taxonomyName).Contains(taxonGuid));
      return returnExpressions;
  }
        
public IQueryable<DynamicContent> GetMasters()
{
    return DynamicModuleManager.GetDataItems(Type).Where(s => s.Status == ContentLifecycleStatus.Master);
}