refactoring LINQ IQueryable expression to remove duplicated portions of queries

I have some linq queries that have redundancy I'd like to factor out a single piece of code. These are join experssions that are IQueryable, and its important I don't cause the query to be evaluated earlier than it would be without the refactoring.

Here is a simplified query:

var result = 
from T in db.Transactions
join O in db.Orders on T.OrderID equals O.OrderID
join OD in db.OrderDetails on O.OrderID equals OD.OrderID into OrderDetails
let FirstProductBought = OrderDetails.First().Select(OD => OD.Product.ProductName)
select new
{
  TransactionID = T.TransactionID,
  OrderID = O.OrderID,
  FirstProductBought = FirstProductBought
};

What I want to factor out is th logic "given an order, what is the first product bought". I am using the same logic in other queries. How can I factor it out into a shared method?

Generally, for code reuse and IQueryables, what I've been able to do is code that takes an IQueryable going in and produces an IQueryable/IOrderedQueryable as output. With such functions I can build up LINQ expressions with reusable code that still defer query until the query is fully constructed. Here, since I only have an int (the orderID) I'm not sure how to make it work.

thanks

From stackoverflow

• Sorry to answer my own question, but I found a good solution. I think though that depending on what you're trying to do, there are different way to factor out different LINQ expressions without evaluating the IQueryable. So I hope people share alternative solutions.

  My solution was to create a "view" for the factored out query. I call it a view because it has a lot in common with a SQL view (from the perspective of a LINQ client). Unlike a SQL view though, it cannot be indexed or have columns persisted. So using this view becomes a bottleneck, it would be appropriate to use an actual SQL view.

  static public class MyDataContextExtension
  {
      // The view exposes OrderSummary objects
      public class OrderSummary
      {
          public OrderID { get; set; }
          public string FirstProductListed { get; set; }
      }
  
      static public IQueryable<OrderSummary> OrderySummaryView(this MyDataContext db)
      {
           return (
                from O in db.Orders
                join OD in db.OrderDetails on O.OrderID equals OD.OrderID into OrderDetails
                let AProductBought = OrderDetails.First().Select(OD => OD.Product.ProductName)
                let TotalCost = OrderDetails.Aggregate(0
                select new OrderSummary()
                {
                    OrderID = OD.OrderID,
                    FirstProductListed = AProductBought.FirstOrDefault()
                };
      }
  }
  

  With this, I can factor out the duplicated portion of the query, replacing the original query with the following:

  var result = 
  from T in db.Transactions
  join OS in db.OrderSummaryView() on T.OrderID equals OS.OrderID
  select new
  {
    TransactionID = T.TransactionID,
    OrderID = T.OrderID,
    FirstProductBought = OS.FirstProductListed
  };
  

  You can imagine other columns being added... I think one cool thing is that if you add extra columns but don't use them in your final select, LINQ won't actually query for those things from the database.