score:2

Accepted answer

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.

score:0

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
 }

Related Articles