Can I use a compiled query as a source in a second query?

I have a compiled query that works great. I pass it a product_id and it returns the product review information for that product.

Is it possible to us开发者_开发技巧e this compiled query as a source for a sub-query? Example:

from cat in ctx.cat_table 
join prod in ctx.prod_table on cat.category_id equals prod.category_id
select new
{
    cat_id = cat.category_id,
    prod_id = prod.product_id,
    name = prod.product_name,
    descript = prod.product_description,
    price = prod.price,
    reviews = (from mcq in mycompiledquery(ctx, prod.product_id)
               select new 
               {
                   rating = mcq.review_rating,
                   review = mcq.review_text
               }    
}

My early attempts at doing something like this raises an error:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities

One alternative I've thought about is to replace my compiled query with a SQL view, but I'm concerned about a negative performance hit.

Many thanks for any suggestions you can offer.

---

You can use compiled query in other query but you can't make it dependent on the outer query. Examples

// You can do 
var someParams = 10;
var dataQuery = from x in ctx.SomeData
                join y in myCompiledQuery.Invoke(ctx, someParams) 
                    on x.Id equals y.Id
                where x.Name = "ABC"
                select new { x, y };

// You can't do - this example will not compile but let's use it for description
var dataQuery = from x in ctx.SomeData
                join y in myCompiledQuery.Invoke(ctx, x.SomeParams) 
                    on x.Id equals y.Id
                where x.Name = "ABC"
                select new { x, y };

The difference is that first example just executes delegate (compiled query is a delegate) and returns IQueryable. The second example can't execute delegate because it is dependent on outer query data so it takes it as something that must be added to expression tree and eveluated during query execution. This fails because EF provider is not able to translate delegate invocation.

---

You can combine and embed queries, but I do not think you can use compiled queries. It really shouldn't matter much though, because EF will only compile the combined query once and then cache it (and the database backend should cache the associated query plan).

You could therefore use something along these lines:

var reviewQuery = from mcq in reviews
                  select new 
                  {
                      prod_id = mcq.prod_id
                      rating = mcq.review_rating,
                      review = mcq.review_text
                  };

 from cat in ctx.cat_table 
 join prod in ctx.prod_table on cat.category_id equals prod.category_id
 select new
 {
      cat_id = cat.category_id,
      prod_id = prod.product_id,
      name = prod.product_name,
      descript = prod.product_description,
      price = prod.price,
      reviews = from r in reviewQuery where r.prod_id == prod_id select r
 }