Complex linq compiled query

Question

Im trying to figure out how i can transform a complex query like that into a compiled query and keep the code clean. This one is not that complex but its an example i have much more complicated query.

So i would like to transform this projected query:

public List<GetAnswers> GetAnswers(int pQuestionId, string pSearch,   int?       pSkip, int? pTake, GetAnswersOrderBy pOrderBy, bool  pOrderByIsAscending, out int pTotalNumberOfLines)
{
    string search = pSearch.ToUpper().Replace(" ", "");
    string languageCode = Thread.CurrentThread.CurrentCulture.ToString().ToLower().Substring(0, 2);
    IQueryable<Answer> query = this.Context.Set<Answer>();
    //Filter question by questionnaire
    query = query.Where(w => w.QuestionID == pQuestionId);

    if (!string.IsNullOrEmpty(pSearch))
        query = query.Where(w => (w.AnswerTranslations.Any(a => languageCode == a.LanguageCode)
                         ? w.AnswerTranslations.FirstOrDefault(a => languageCode == a.LanguageCode).Label
                         : w.AnswerTranslations.OrderBy(o => o.Language.Priority).FirstOrDefault().Label)
                         .ToUpper().Replace(" ", "")
                         .Contains(search));
    switch (pOrderBy)
    {
        case GetAnswersOrderBy.CorrectAnswer:
            query = pOrderByIsAscending ? query.OrderBy(w => w.CorrectAnswer) : query.OrderByDescending(w => w.CorrectAnswer);
            break;
        case GetAnswersOrderBy.ResponseCount:
            query = pOrderByIsAscending ? query.OrderBy(w => w.ResponseCount) : query.OrderByDescending(w => w.ResponseCount);
            break;
        default:
            break;
    }
    pTotalNumberOfLines = query.Count();

    var pagedQuery = query.Skip(pSkip ?? 0)
                         .Take(pTake ?? pTotalNumberOfLines);
    var projectedQuery = pagedQuery.Select(s =>
        new
        {
            Answer = s,
            s.AnswerTranslations,
            AnswerTranslationsLanguages = s.AnswerTranslations.Select(qt => qt.Language),
        }).ToList();
    return projectedQuery.Select(s => new GetAnswers()
    {
        Id = s.Answer.Id,
        Label = s.Answer.Label,
        CorrectAnswer = s.Answer.CorrectAnswer,
        Feedback = s.Answer.Feedback,
        Weighting = s.Answer.Weighting
    }).ToList();
}

To something like this to keep the code clean :

public List<Answer> GetAnswers(int pQuestionId, string pSearch,   int?       pSkip, int? pTake, GetAnswersOrderBy pOrderBy, bool  pOrderByIsAscending, out int pTotalNumberOfLines)
{
    var compiledQuery = CompiledGetAnswers(params...);

    var projectedQuery = compiledQuery .Select(s =>
        new
        {
            Answer = s,
            s.AnswerTranslations,
            AnswerTranslationsLanguages = s.AnswerTranslations.Select(qt => qt.Language),
        }).ToList();

    return projectedQuery.Select(s => new GetAnswers()
    {
        Id = s.Answer.Id,
        Label = s.Answer.Label,
        CorrectAnswer = s.Answer.CorrectAnswer,
        Feedback = s.Answer.Feedback,
        Weighting = s.Answer.Weighting
    }).ToList();
} 

public static Func<AppDbContext, Answer, IQueryable<Answer>> CompiledGetAnswers(int pQuestionId, string pSearch,   int?       pSkip, int? pTake, GetAnswersOrderBy pOrderBy, bool  pOrderByIsAscending, out int pTotalNumberOfLines)
{
    string search = pSearch.ToUpper().Replace(" ", "");
    string languageCode = Thread.CurrentThread.CurrentCulture.ToString().ToLower().Substring(0, 2);
    IQueryable<Answer> query = this.Context.Set<Answer>();
    //Filter question by questionnaire
    query = query.Where(w => w.QuestionID == pQuestionId);

    if (!string.IsNullOrEmpty(pSearch))
        query = query.Where(w => (w.AnswerTranslations.Any(a => languageCode == a.LanguageCode)
                         ? w.AnswerTranslations.FirstOrDefault(a => languageCode == a.LanguageCode).Label
                         : w.AnswerTranslations.OrderBy(o => o.Language.Priority).FirstOrDefault().Label)
                         .ToUpper().Replace(" ", "")
                         .Contains(search));
    switch (pOrderBy)
    {
        case GetAnswersOrderBy.CorrectAnswer:
            query = pOrderByIsAscending ? query.OrderBy(w => w.CorrectAnswer) : query.OrderByDescending(w => w.CorrectAnswer);
            break;
        case GetAnswersOrderBy.ResponseCount:
            query = pOrderByIsAscending ? query.OrderBy(w => w.ResponseCount) : query.OrderByDescending(w => w.ResponseCount);
            break;
        default:
            break;
    }
    pTotalNumberOfLines = query.Count();

    var pagedQuery = query.Skip(pSkip ?? 0)
                         .Take(pTake ?? pTotalNumberOfLines);
    return CompiledQuery(AppDbContext db, Answer a) => pagedQuery;
}

Liu Liu · Accepted Answer · 2017-03-10T17:48:02

I guess what you meant by clean is "more readable". You are actually doing it. And my suggestion will be putting your complex query into a method and giving it a meaningful name.

For example, if people don't look into your query, they won't understand what are you doing in the following code

query = query.Where(w => (w.AnswerTranslations.Any(a => languageCode == a.LanguageCode)
                         ? w.AnswerTranslations.FirstOrDefault(a => languageCode == a.LanguageCode).Label
                         : w.AnswerTranslations.OrderBy(o => o.Language.Priority).FirstOrDefault().Label)
                         .ToUpper().Replace(" ", "")
                         .Contains(search));

You can make an extension class

public static class MyQueryExtension
{
    public static IQueryable<Answer> SearchAnswersByLanguageCode(this IQueryable<Answer> query, string languageCode, string search)
    { 
        return query.Where(w => (w.AnswerTranslations.Any(a => languageCode == a.LanguageCode)
                     ? w.AnswerTranslations.FirstOrDefault(a => languageCode == a.LanguageCode).Label
                     : w.AnswerTranslations.OrderBy(o => o.Language.Priority).FirstOrDefault().Label)
                     .ToUpper().Replace(" ", "")
                         .Contains(search))
    }
}

And call it in your code

query = query.SearchAnswersByLanguageCode(languageCode, search);

Then you are able to make your main method clean and readable

public static Func<AppDbContext, Answer, IQueryable<Answer>> CompiledGetAnswers(int pQuestionId, string pSearch,   int?       pSkip, int? pTake, GetAnswersOrderBy pOrderBy, bool  pOrderByIsAscending, out int pTotalNumberOfLines)
{
    //Do something
    if (!string.IsNullOrEmpty(pSearch))
        query = query.SearchAnswersByLanguageCode(languageCode, search);

    query = query.GetItOrdered(parameters);

    //Do something
}

Complex linq compiled query

1 Answers