CODEWITHSUNDEEP
c#linqentity-framework
Sri Reddy
Sri Reddy

Reputation: 7012

Entity Framework - LINQ statement with WHERE clause based on NULLABLE parameter

I have a a method that return a list of languages. It has a nullable parameter of languageId. If passed, the method returns that language otherwise returns the list of languages.

I want to know if I can simplify this piece of code to have a select and where clause in one statement.

public List<Language> GetLanguageList(LanguageMapper ctx, int? languageId)
{

    List<Language> languages = ctx.LANGUAGELIST
                                    .Select(e => new Language()
                                    {
                                        LanguageId = e.LANGUAGEID,
                                        LanguageName = e.LANGUAGE
                                    })
                                    .ToList();                    
    if (languageId.HasValue)
    {
        languages = languages.Where(x => x.LanguageId == languageId).ToList();
    }

    return languages;

}

Upvotes: 3

Views: 6105

Answers (2)

Jon Egerton
Jon Egerton

Reputation: 41579

You can include the HasValue check in the where clause:

public List<Language> GetLanguageList(LanguageMapper ctx, int? languageId)
{

    List<Language> languages = ctx.LANGUAGELIST
                                    .Where(e => !languageId.HasValue || e.LanguageId == languageId)
                                    .Select(e => new Language()
                                    {
                                        LanguageId = e.LANGUAGEID,
                                        LanguageName = e.LANGUAGE
                                    })
                                    .ToList();                    

    return languages;

}

Note: p.s.w.g's answer will lead to cleaner, more efficient sql in the instance where languageId is null in this particular example, but its worth seeing that this option is available for more complex examples.

Upvotes: 4

p.s.w.g
p.s.w.g

Reputation: 149068

Just do the .Where before the .Select, like this:

public List<Language> GetLanguageList(LanguageMapper ctx, int? languageId)
{
    var query = ctx.LANGUAGELIST.AsQueryable();
    if (languageId.HasValue)
    {
        query = query.Where(x => x.LanguageId == languageId.Value);
    }

    List<Language> languages = query.Select(e => new Language()
                                    {
                                        LanguageId = e.LANGUAGEID,
                                        LanguageName = e.LANGUAGE
                                    })
                                    .ToList();                    
    return languages;
}

By using IQueryable<Language> in this way, you ensure that only one call is made to the database, regardless of what parameters are passed to this method.

Upvotes: 5

Related Questions