LINQ to Entity Framwework Multiple Joins with Multiple Dynamic Search Criteria

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();
    }

Answers


You might want to look at the DynamicQuery library here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Maybe you can explain how you fill the AlumniSearchCriteria (UI -> AlumniSearchCriteria).


 var SearchData(string sc1, string sc2, string sc3, string sc4)
    {

var res = (from t1 in T1
join t2 in T2 on t1.AnyField equals t2.CorrespondentField
join t3 in T3 on t1.AnyField equals t3.CorrespondentField

where (t1.AnyField.Equals(sc1) || String.IsEmptyOrNull(sc1))
&& (t2.AnyField.Equals(sc2) || String.IsEmptyOrNull(sc2))
&& ( 
 (t3.AnyField.Equals(sc3) || String.IsEmptyOrNull(sc3)
&& 
(t3.AnyField.Equals(sc4) || String.IsEmptyOrNull(sc4)
)
);
return res.ToList();
}

Need Your Help

boost::spirit::qi Expectation Parser and parser grouping unexpected behaviour

c++ parsing boost boost-spirit boost-spirit-qi

I'm hoping someone can shine a light through my ignorance of using the &gt; and &gt;&gt; operators in spirit parsing.

Django ModelForm does not validate image file upload

django forms file-upload

I have a ModelForm with one image field as follows: