Is it possible to use a compiled query as a source in the second query?

Question

I have a compiled request that works fine. I pass him the product_id and return the product view information for this product.

Can this compiled query be used as a source for a subquery? 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 to do something like this cause an error:

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

One of the alternatives that I was thinking about is to replace my compiled query with an SQL representation, but I'm concerned about the negative performance hit.

Thanks so much for any suggestions you can offer.

Answer 1

You can use the compiled query in another query, but you cannot make it dependent on an external 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 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 the first example just performs delegation (the compiled query is a delegate) and returns IQueryable . The second example cannot perform delegation, because it depends on the external data of the request, therefore, it accepts it as something that must be added to the expression tree and defined at the time the request is executed. This fails because the EF provider is not able to transfer the delegate call.

Answer 2

You can combine and insert queries, but I don't think you can use compiled queries. This really doesn't really matter, because EF will only compile the combined query once and then cache it (and the database backend should cache the associated query plan).

Therefore, you can use something in 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 }