CODEWITHSUNDEEP
c#.netlinq
Alan2
Alan2

Reputation: 24572

Is there a way I can defer the .Where clause in a LINQ statement?

I have this code here. I think it's not very efficient and I would like to know if there is a way that I make it so some of the .Where clauses are added at the start or the end rather than repeated three times. I would appreciate if anyone can give me any advice and show how I could do this:

      List<Phrase> phrases;

        switch (options.PhraseTypeSelectId)
        {
            case 0:
                phrases = await db.Phrases
                .Where(w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null) ||
                             (w.ChapterId == options.ChapterSelectId || options.ChapterSelectId == null)))
                .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
                .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2))
                .Where(w => (options.CreatedBy == 0 || w.CreatedBy == options.CreatedBy))
                .Where(w => (options.ModifiedBy == 0 || w.ModifiedBy == options.ModifiedBy))
                .Where(w => (options.JLPT == 0 || w.JLPT == options.JLPT))
                .AsNoTracking()
                .ToListAsync();
                return Ok(phrases);
            case 1:
                phrases = await db.Phrases
                .Where(w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null)))
                .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
                .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2))
                .Where(w => (options.CreatedBy == 0 || w.CreatedBy == options.CreatedBy))
                .Where(w => (options.ModifiedBy == 0 || w.ModifiedBy == options.ModifiedBy))
                .Where(w => (options.JLPT == 0 || w.JLPT == options.JLPT))
                .AsNoTracking()
                .ToListAsync();
                return Ok(phrases);
            case 2:
                phrases = await db.Phrases
                .Where(w => ((w.ChapterId == options.ChapterSelectId || options.ChapterSelectId == null)))
                .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
                .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2))
                .Where(w => (options.CreatedBy == 0 || w.CreatedBy == options.CreatedBy))
                .Where(w => (options.ModifiedBy == 0 || w.ModifiedBy == options.ModifiedBy))
                .Where(w => (options.JLPT == 0 || w.JLPT == options.JLPT))
                .AsNoTracking()
                .ToListAsync();
                return Ok(phrases);
        }
        return BadRequest();

I would like to include the switch statement as I tried without this and it became very confusing with the .Where clauses related to the PhraseTypeSelectId

Upvotes: 1

Views: 139

Answers (5)

Ivan Stoev
Ivan Stoev

Reputation: 205749

If I understand correctly, the question is how to build a dynamic filter. To do that, you can start with basic IQueryable<T> and apply parameter based conditional Where like this:

var query = db.Phrases.AsQueryable();

bool categoryFilter = options.CategorySelectId != null && options.PhraseTypeSelectId != 2;
bool chapterFilter = options.ChapterSelectId != null && options.PhraseTypeSelectId != 1;
if (categoryFilter && chapterFilter) query = query
    .Where(w => w.CategoryId == options.CategorySelectId || w.ChapterId == options.ChapterSelectId);
else if (categoryFilter) query = query
    .Where(w => w.CategoryId == options.CategorySelectId);
else if (chapterFilter) query = query
   .Where(w => w.ChapterId == options.ChapterSelectId);

query = query
    .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
    .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2));

if (options.CreatedBy != 0) query = query
    .Where(w => w.CreatedBy == options.CreatedBy);

if (options.ModifiedBy != 0)
    query = query.Where(w => w.ModifiedBy == options.ModifiedBy);

if (options.JLPT != 0)
    query = query.Where(w => w.JLPT == options.JLPT);

var phrases = await query
    .AsNoTracking()
    .ToListAsync();

return Ok(phrases);

Upvotes: 2

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

This should also work (details may be wrong, just conceptional):

// common filters here:
var filtered = db.Phrases
       .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
       .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2))
       .Where(w => (options.CreatedBy == 0 || w.CreatedBy == options.CreatedBy))
       .Where(w => (options.ModifiedBy == 0 || w.ModifiedBy == options.ModifiedBy))
       .Where(w => (options.JLPT == 0 || w.JLPT == options.JLPT));

    // specific filters
    switch (options.PhraseTypeSelectId)
    {
        case 0:
            filtered = filtered 
                .Where(w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null) ||
                         (w.ChapterId == options.ChapterSelectId || options.ChapterSelectId == null)))
             break;
        case 1:
            filtered = filtered 
                .Where(w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null)))
             break;
        case 2:
            // ???
             break;
        default: 
           return BadRequest();
    }
    var phrases = async filtered
        .AsNoTracking()
        .ToListAsync();
    return Ok(phrases);

Upvotes: 1

Matthew Walton
Matthew Walton

Reputation: 9969

LINQ queries on an IQueryable or an IEnumerable aren't evaluated until you actually iterate the results - by looping over it, or calling ToList() or suchlike.

Therefore, you can build things in stages, for example:

var exp = dbcontext.Logs.Where(x => x.Code == 4);

which won't execute any code on the database yet. Then you can later on say:

var logs = await exp.Where(x => x.Module == "AUTH").ToListAsync();

and that will execute the query, because of the ToListAsync().

The massive advantage of this is being able to build up queries in stages with sensible methods (instead of slicing strings of SQL around), and trust that Entity Framework or whatever LINQ provider you're using will turn it into a reasonable query (which it does most of the time).

So for your enormous example, I'm not going to attempt to rewrite it here, but you can certainly do something in this form:

var query = await db.Phrases;
// common where clauses

switch (options.PhraseTypeSelectId) {
    case 0:
        query = query.Where(w => /* appropriate restriction for 0 */);
        break;
    // other cases
}

// more conditional where clauses perhaps

// projection clauses like Select()

// finally, execute the query
List<Phrases> phrases = await query.ToListAsync();

Upvotes: 1

Stefan William-Worrall
Stefan William-Worrall

Reputation: 713

You could use the extension method .AsQueryable() this will return the former part of a linq statment as a queryable expression, allowing further manipulation and transformer if needed.

for example

var query =db.Phrases.where(phraseTypePredicate).AsQueryable();

switch(options.PhraseTypeSelectId)
{
   case 1: 
     query = query.where(case1Predicate);
     break;
   case 2: 
     query = query.where(case2Predicate);
     break;
   ... 
}

var results = query.ToList(); // or someother way of enumerating the expression

Upvotes: 3

Howwie
Howwie

Reputation: 275

Since I couldn't compile the example, I'm not 100% sure that this will compile either, but you should get the gist of it.

You can create specific expressions for the different PhraseTypeSelecteId and use it in the .Where() method later.

Expression<Func<Phrase, bool>> phraseTypePredicate = null;

switch (options.PhraseTypeSelectId)
{
    case 0:
        phraseTypePredicate =
            w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null) ||
                  (w.ChapterId == options.ChapterSelectId || options.ChapterSelectId == null));
        break;
    case 1:
        phraseTypePredicate =
            w => ((w.CategoryId == options.CategorySelectId || options.CategorySelectId == null));
        break;
    case 2:
        phraseTypePredicate =
            w => ((w.ChapterId == options.ChapterSelectId || options.ChapterSelectId == null));
        break;
}

if (phraseTypePredicate != null)
{
    List<Phrase> phrases = phrases = await db.Phrases
        .Where(phraseTypePredicate)
        .Where(w => (w.EnglishAscii >= es1 && w.EnglishAscii <= ee1) || (w.EnglishAscii >= es2 && w.EnglishAscii <= ee2))
        .Where(w => (w.RomajiAscii >= rs1 && w.RomajiAscii <= re1) || (w.RomajiAscii >= rs2 && w.RomajiAscii <= ee2))
        .Where(w => (options.CreatedBy == 0 || w.CreatedBy == options.CreatedBy))
        .Where(w => (options.ModifiedBy == 0 || w.ModifiedBy == options.ModifiedBy))
        .Where(w => (options.JLPT == 0 || w.JLPT == options.JLPT))
        .AsNoTracking()
        .ToListAsync();
    return Ok(phrases);
}

return BadRequest();

Upvotes: 3

Related Questions