Can you add a Where() to an IQueryable when the field is not in the selected output

Question

Suppose I have a 2 table join in a function that returns an IQueryable, but the output is a named type that is neither of the two tables:

var qry = from p in Persons 
          join h in Hobbies on p.PersonId equals h.PersonId
          select new OutputType
          {  
              Name = p.FirstName, 
              Hobby = h.HobbyName
          } 

return qry

Let's say now I wanted to take this returned query and do something like:

 var newQuery = qry.Where( p=>p.Age > 18 )

As you can see this is a problem because the IQueryable is of type OutputType, so I can't add a where to a person's age unless I were to add the Age to OutputType.

Is there anyway of 'breaking into' the IQueryable expression tree and adding a lambda somehow that will query on the source collection specified in it and add a Where clause to it? Or do I have do I have to add a Where field to the OutputType even though I'm uninterested in ultimately projecting it?

While I suspect you could find a way to do this if you really wanted to, really it's just a sign that your design is flawed, and you need to structure your code such that you're doing the filtering before projecting the sequence. — Servy, Jul 28 '16 at 20:49
Maybe you could redesign a little and pass in the value for `Persons`. So in place of `Persons` you'd pass in `Persons.Where(p => p.Age > 18)` — James R., Jul 28 '16 at 20:54
The example I gave was just that, an example. I was just showing the concept, not the usage. The actual case is a 4-table join on top of an existing legacy Oracle database I have 3 different variations of the same (large) query, only differing in the Where clause on non-projected values from different tables. I'm trying to reduce the redundancy in the code so I'm not maintaining multiple large LINQ queries. The first answer below is very helpful in that I'm not penalized for adding a field early on and not projecting it ultimately. The only negative is having to make multiple new types. — Randy Magruder, Jul 29 '16 at 14:02

score 1 · Accepted Answer · answered Jul 28 '16 at 21:26

It is easier to narrow your view later than to try to backtrack. Here is a stripped down example of how I like to layer methods for reuse so that they spit out nice sql.

private IQueryable<Part> GetParts_Base()
{
    //Proprietary. Replace with your own.
    var context = ContextManager.GetDbContext();

    var query = from c in context.Component
                where c.Active
                //kind of pointless to select into a new object without a join, but w/e
                select new Part()
                {
                    PartNumber = c.ComponentNumber,
                    Description = c.ComponentDescription,
                    Cost = c.ComponentCost,
                    Price = c.ComponentPrice
                };

    return query;
}

//Exclude cost from this view
public IEnumerable<Part_PublicView> GetParts_PublicView(decimal maxPrice)
{
    var query = GetParts_Base();

    var results = from p in query
                  where p.Cost < maxPrice
                  select new Part_PublicView()
                  {
                      PartNumber = p.PartNumber,
                      Description = p.Description,
                      Price = p.Price
                  };

    return results;
}

public class Part_PublicView
{
    public string PartNumber { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}

private class Part : Part_PublicView
{
    public decimal Cost { get; set; }
}

Linq-to-entity does not penalize you for selecting the extra column early on. As you can see, the sql includes the Cost column in the constraint but not in the select.

SELECT
  1 AS [C1],
  [Extent1].[ComponentNumber] AS [ComponentNumber],
  [Extent1].[ComponentDescription] AS [ComponentDescription],
  [Extent1].[ComponentPrice] AS [ComponentPrice]
FROM [dbo].[Component] AS [Extent1]
WHERE [Extent1].[ComponentCost] < @p__linq__0

The only negative is creating all these named types for each layer, but the the answer you gave is very solid. It is indeed a pity that the bits of the IQueryable are so difficult to access on a method return, but I suspected this would be the case. Thanks! — Randy Magruder, Jul 29 '16 at 14:03

Can you add a Where() to an IQueryable when the field is not in the selected output

1 Answers1