LINQ Query with dynamic where clause based on search criteria that is NOT part of the returned object

Question

I have an AlumniRecords table with 60+ columns. I created an AlumniSearchResults class that only contains the handful of fields I need for display in a table of search results. This is an MVC2 app so I want to keep the objects clean (in other words, I don't want to pass the 60+ field object to my view). I am trying to build my AlumniSearchResult as part of my query against AlumniRecords. This worked fine BUT I also want to dynamically add where clauses only if they are in the SearchCriteria object - the problem is my search criteria are not necessarily in the results object (for instance lastname).

FIRST TRY:

var alumniRecords = iuaaOlcEntities.AlumniRecords;

        if (searchCriteria.lastname != null) alumniRecords.Where(ar => ar.lastname == searchCriteria.lastname);

        return alumniRecords.Select(ar => new AlumniSearchResult
        {
            person_id = ar.person_id,
            fullname  = ar.fullname,
            city      = ar.city,
            state     = ar.state,
            emp_name  = ar.emp_name,
            emp_title = ar.emp_title
        }); 

This just ignores the where clause for lastname. I cannot chain the where clause after select because AlumniSearchResult has no lastname field.

SECOND TRY:

            var searchResults = from ar in iuaaOlcEntities.AlumniRecords
                            where ar.lastname == searchCriteria.lastname
                            select new AlumniSearchResult
                            {
                                person_id = ar.person_id,
                                fullname  = ar.fullname,
                                city      = ar.city,
                                state     = ar.state,
                                emp_name  = ar.emp_name,
                                emp_title = ar.emp_title
                            };

This format works but I have a dozen search criteria and only want to add "ar.lastname == searchCriteria.lastname" searchCriteria.lastname has a value.

Any thoughts for this LINQ newb?

Answer 1

    var alumniRecords = iuaaOlcEntities.AlumniRecords.AsQueryable();

    if (searchCriteria.lastname != null) 
        alumniRecords = alumniRecords.Where(ar => ar.lastname == searchCriteria.lastname);

    return alumniRecords.Select(ar => new AlumniSearchResult
    {
        person_id = ar.person_id,
        fullname  = ar.fullname,
        city      = ar.city,
        state     = ar.state,
        emp_name  = ar.emp_name,
        emp_title = ar.emp_title
    }); 

Answer 2

You need to update alumniRecords with the return value from the call to Where:

if (searchCriteria.lastname != null) {
  alumniRecords = alumniRecords.Where(ar => ar.lastname == searchCriteria.lastname);
}

Answer 3

You need to do something with the results of your where statement in your first example. Try assigning it back to your alumniRecords variable.

Or, in your second example try something like:

where (searchCriteria.lastname == null || ar.lastname == searchCriteria.lastname)

This way, your criteria will get short-circuited if it is null.