当字段不在所选输出中时,能否将Where()添加到IQueryable中

事后缩小视野比试图回溯更容易。下面是一个简单的例子,我喜欢将方法分层以便重用,这样它们就可以输出漂亮的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实体不会因为您提前选择了额外的列而惩罚您。正如您所看到的,sql在约束中包括Cost列,但在select中不包括Cost。

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