I would like to create a method for common query. This query is joining three tables, and I would like to use property from all tables in the where clause.

The problem is when I create predicate on input then I can't implement it on selected anonymous object because all three tables are nested in anonymous object.

How should I implement this common method? I want to have it with different where conditions and use only one query.

CodePudding user response：

The most common pattern for this is something like:

public async Task<IList<QueryResult>> GetQueryResults(
    Expression<Func<Customer,bool>> customerFilter,
    Expression<Func<Project, bool>> projectFilter    )
{
    var q = from c in Set<Customer>().Where(customerFilter)
            join p in Set<Project>().Where(projectFilter)
              on c.Id equals p.Id
            select new QueryResult() { CustomerName = c.Name, ProjectName = p.Name };

    return await q.ToListAsync();

}

Which you would call like:

var results = await db.GetQueryResults(c => c.Name == "a", p => true);

You can give the calling code more power by letting it change the base IQueryables used in the query, eg

   public async Task<IList<QueryResult>> GetQueryResults( 
        Func<IQueryable<Customer>, IQueryable<Customer>> customerFilter,
        Func<IQueryable<Project>, IQueryable<Project>> projectFilter )
    {
        var q = from c in customerFilter(Set<Customer>())
                join p in projectFilter(Set<Project>())
                  on c.Id equals p.Id
                select new QueryResult() {  CustomerName = c.Name, ProjectName = p.Name };

        return await q.ToListAsync();
    }

which you would call like this:

var results = await db.GetQueryResults(c => c.Where(c => c.Name == "a"), p => p);