CODEWITHSUNDEEP
c#linq
lonelydev101
lonelydev101

Reputation: 1901

How to dynamically add .Where() clause in LINQ

I have a method:
Where I defined an expression, which will be in linq query only if input param userType has value.

public Institution GetInstitutionWithServices(int institutionId, UserTypes? userType)
{
  Expression<Func<Service, bool>> serviceUserTypeFilter = (s => true);

  if (userType.HasValue)
  {
    serviceUserTypeFilter = (s => s.UserType == userType.Value);
  };

  var query = _dbContext.Institution.Include(a => a.Services)
                                    .Where(a => a.Id == institutionId)
                                    .Select(m => new Institution
                                     {
                                       Id = m.Id,
                                       Name = m.Name
                                    .Where(x => x.Status == Service.ServiceStatus.Published) 
                                     //&& userType.HasValue ? x.UserType == userType.Value : false )
                                    .Where(serviceUserTypeFilter)
                                    .ToList()
                                    }).FirstOrDefault();
  return query;
}

I don't know how to add filter for other table (db context) which has foreign key.
It has Services.
In commented where part that I need to invoke thorugh expression, because I'm getting null reference exception with that code
The error with this code that I'm getting is:

Error CS1503
Argument 2: cannot convert from 'System.Linq.Expressions.Expression<System.Func<Test001.Domain.Service, bool>>' to 'System.Func<Test001.Domain.Service, bool>'
Test001.Repositories

Upvotes: 0

Views: 783

Answers (2)

AndreevOlegArtemovich
AndreevOlegArtemovich

Reputation: 1

It seems your code contains errors (what is the "..m.Name.Where(x => x.Status..."?), but i guess you need something like this solution:

Expression<Func<Service, bool>> servicesWhereClause;
        if (!userType.HasValue)
            servicesWhereClause = x => x.Status == Service.ServiceStatus.Published;
        else
            servicesWhereClause = x => x.Status == Service.ServiceStatus.Published
                && x.UserType == userType.Value;
        var result = _dbContext.Institution
            .Include(a => a.Services)
            .Where(a => a.Id == institutionId)
            .Select(i => new
            {
                Institution = i,
                Services = i.Services.Where(servicesWhereClause)
            })
            .ToList()
            .Select(anon => new Institution
            {
                Id = anon.Id,
                Name = anon.Name,
                // etc...
                Services = anon.Services
            })
            .ToList();

if you want to get filtrated child records with parent records at once you should use anonymous return type and do map after that. Unfortunately, ef will no track such records.

Upvotes: 0

Neil
Neil

Reputation: 11891

There is nothing stopping you adding .Where clauses after the main query has been defined:

 var query = _dbContext.Institution.Include(a => a.Services);
 if(something)
 {
    query = query.Where(a => a.Id == institutionId);
 }

 ...
 var results = query.ToList();  // <-- This 'executes' the query

 etc

At any point up to when the query is actually executed (in your case .ToList()) you can keep adding other linq clauses (.Where, .OrderBy etc).

Upvotes: 2

Related Questions