The gist of the problem is that we have an alumni table (one record per person) and also a couple of other tables (one to many) that have degree info and interest info. In a search screen in our app you can search for criteria that spans all three tables (there are actually more fields and tables than shown in the example below but I am trying to keep it simple).
The code below works (properly returns people without degrees for example) but still feels a little clunky or over-engineered to me. Are there easier ways to do this? NOTE: I have been through quite a few iterations/approaches to making the correct data be returned.
public IQueryable<AlumniSearchResult> FindAlumniRecords(AlumniSearchCriteria searchCriteria)
{
// tables
var alumniRecords = iuaaOlcEntities.AlumniRecords.AsQueryable();
var degreeRecords = iuaaOlcEntities.AlumniDegrees.AsQueryable();
var interestRecords = iuaaOlcEntities.AlumniInterests.AsQueryable();
// typed predicates
var alumniRecordPredicates = PredicateBuilder.True<AlumniRecord>(); // True for AND, False for OR???
var degreePredicates = PredicateBuilder.True<AlumniDegree>();
var interestPredicates = PredicateBuilder.True<AlumniInterest>();
if (!String.IsNullOrEmpty(searchCriteria.lastname))
alumniRecordPredicates = alumniRecordPredicates.And(item => item.lastname.StartsWith(searchCriteria.lastname));
if (!String.IsNullOrEmpty(searchCriteria.firstname))
alumniRecordPredicates = alumniRecordPredicates.And(item => item.firstname.StartsWith(searchCriteria.firstname));
if (!String.IsNullOrEmpty(searchCriteria.nickname))
alumniRecordPredicates = alumniRecordPredicates.And(item => item.nickname.StartsWith(searchCriteria.nickname));
if (!String.IsNullOrEmpty(searchCriteria.maiden_lastname))
alumniRecordPredicates = alumniRecordPredicates.And(item => item.maiden_lastname.StartsWith(searchCriteria.maiden_lastname));
if (!String.IsNullOrEmpty(searchCriteria.city))
alumniRecordPredicates = alumniRecordPredicates.And(item => item.city.StartsWith(searchCriteria.city));
// degrees
if (searchCriteria.school_name != null)
degreePredicates = degreePredicates.And(item => item.school_name.Contains(searchCriteria.school_name));
if (searchCriteria.degree_name != null)
degreePredicates = degreePredicates.And(item => item.name.Contains(searchCriteria.degree_name));
if (searchCriteria.major != null)
degreePredicates = degreePredicates.And(item => (item.major1_name.Contains(searchCriteria.major) || item.major2_name.Contains(searchCriteria.major) || item.major3_name.Contains(searchCriteria.major)));
// interests
if (searchCriteria.interests != null)
interestRecords = interestRecords.Where(item => item.interest_desc.Contains(searchCriteria.interests));
// the queries aren't running yet but applying the predicates outside of the join
alumniRecords = from a in iuaaOlcEntities.AlumniRecords.Where(alumniRecordPredicates).AsExpandable()
select a;
degreeRecords = from b in iuaaOlcEntities.AlumniDegrees.Where(degreePredicates).AsExpandable()
select b;
interestRecords = from c in iuaaOlcEntities.AlumniInterests.Where(interestPredicates).AsExpandable()
select c;
return (from a in alumniRecords
join b in degreeRecords on a.person_id equals b.person_id into temp1
from t1 in temp1.DefaultIfEmpty()
join c in interestRecords on t1.person_id equals c.person_id into temp2
from t2 in temp2.DefaultIfEmpty()
select new AlumniSearchResult
{
person_id = a.person_id,
fullname = a.lastname + ", " + (a.firstname ?? "") + " " + (a.mid_name ?? ""),
emp_city = a.emp_city,
emp_state = a.emp_state,
emp_name = a.emp_name,
emp_title = a.emp_title
}).Distinct();
}