DeterminePath degrees join fails it

gistfile1.txt

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dev.Data;

namespace UBoundTools.Database.LMP
{
    public class DeterminePath
    {

        public List<string> SchoolsFromAreaList(string area, string edu)
        {
            // Define the ID of the entity for supporting queries
            int eduIdentityBasement = Database.ProgramData.SubQuery.getEduIDBasement(edu);
            int eduIdentity = Database.ProgramData.SubQuery.getEduID(edu);
            int areaID = Database.ProgramData.SubQuery.getAOIID(area);

            // Query the database for schools with the given criteria
            var query = new DevDB().Select
                .From<School>()
                .InnerJoin<Program>()
                .InnerJoin<Degree>()
                .InnerJoin<DegreeCategory>()
                .Where(ProgramsTable.AreaOfInterestColumn).IsEqualTo(areaID)
                .And(ProgramsTable.ActiveColumn).IsEqualTo(true)
                .And(SchoolsTable.SchoolCodeColumn).IsNotNull()
                .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsLessThanOrEqualTo(eduIdentity)
                .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsGreaterThanOrEqualTo(eduIdentityBasement)
                .ExecuteTypedList<School>();

            //if we got any results, iterate through them and return a list of strings containing the schoolcode
            if (query.Count() > 0)
            {
                List<string> returnList = new List<string>();
                foreach (var x in query)
                {
                    if (!returnList.Contains(x.SchoolCode))
                    {
                        returnList.Add(x.SchoolCode);
                    }
                }
                return returnList;
            }

            return null;
        }

        public List<string> SchoolsFromProductList(string program, string edu)
        {
            // Define the ID of the entity for supporting queries
            int eduIdentityBasement = Database.ProgramData.SubQuery.getEduIDBasement(edu);
            int eduIdentity = Database.ProgramData.SubQuery.getEduID(edu);
            int progCode = Database.ProgramData.SubQuery.getProductID(program);

            // Query the database for schools with the given criteria
            var query = new DevDB().Select
                .From<School>()
                .InnerJoin<Program>()
                .InnerJoin<Degree>()
                .InnerJoin<DegreeCategory>()
                .Where(ProgramsTable.ProgramCodeColumn).IsEqualTo(progCode)
                .And(ProgramsTable.ActiveColumn).IsEqualTo(true)
                .And(SchoolsTable.SchoolCodeColumn).IsNotNull()
                .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsLessThanOrEqualTo(eduIdentity)
                .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsGreaterThanOrEqualTo(eduIdentityBasement)
                .ExecuteTypedList<School>();

            //if we got any results, iterate through them and return a list of strings containing the schoolcode
            if (query.Count() > 0)
            {
                List<string> returnList = new List<string>();
                foreach (var x in query)
                {
                    if (!returnList.Contains(x.SchoolCode))
                    {
                        returnList.Add(x.SchoolCode);
                    }
                }
                return returnList;
            }

            return null;
        }

        public List<string> SchoolsFromProgramCategory(string category, string edu)
        {
            int eduIdentityBasement = Database.ProgramData.SubQuery.getEduIDBasement(edu);
            int eduIdentity = Database.ProgramData.SubQuery.getEduID(edu);
            int categoryID = Database.ProgramData.SubQuery.getCategoryID(category);


            //var query = new DevDB().Select
            //    .From<School>()
            //    .InnerJoin<Program>()
            //    .InnerJoin<Degree>()
            //    .InnerJoin<DegreeCategory>()
            //    .InnerJoin<ProgramCategory>()
            //    .Where(ProgramCategoriesTable.CategoryTitleColumn).IsEqualTo(categoryID)
            //    .And(SchoolsTable.SchoolCodeColumn).IsNotNull()
            //    .And(SchoolsTable.ActiveColumn).IsEqualTo(true)
            //    .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsLessThanOrEqualTo(eduIdentity)
            //   .And(DegreeCategoriesTable.DegreeCategoryIDColumn).IsGreaterThanOrEqualTo(eduIdentityBasement)
            //   .ExecuteTypedList<School>();

            //Putting this in Linq to SQL
            var db = new DevDB();
            var query =
                (from s in School.All()
                 join p in db.Programs on s.SchoolID equals p.SchoolID
                 join d in db.Degrees on p.DegreeID equals d.DegreeCategoryID
                 //join pc in db.ProgramCategories on p.ProgramCategory equals pc.CategoryID
                 

                 where s.SchoolCode != null
                 && s.Active == true
                 && p.ProgramCategory == categoryID
                 //&& d.DegreeCategoryID <= eduIdentity
                 //&& d.DegreeCategoryID >= eduIdentityBasement
                 //&& pc.CategoryID == categoryID
                 select s);



            //if we got any results, iterate through them and return a list of strings containing the schoolcode
            if (query.Count() > 0)
            {
                List<string> returnList = new List<string>();
                foreach (var x in query)
                {
                    if (!returnList.Contains(x.SchoolCode))
                    {
                        returnList.Add(x.SchoolCode);
                    }
                }
                return returnList;
            }

            return null;
        }



    }
}

All aboard the failboat

Next stop: fail island