0

I have a query where I need to get the value from a view only if there are no results returned from a table used in the right side of a left outer join.

Let's take 'left' to be the left table in the join, 'right' to be the right table and 'optional' to be populated with a value only if there is no match between 'left' and 'right'.

from left in ctx.tblLeft
from right in ctx.tblRight.Where(x=>predicate(x)).DefaultIfEmpty()
let optional = 
  (from v in ctx.vwMyView
   where right==null && criteria
   select new anonType { x = v.prop }).FirstOrDefault();
//select projection

However, that would mean that an "outer apply" query will be generated for my view. Wouldn't this impact my performance or is there a way that I can omit the execution of the view itself?

So what I would ideally like would look like this:

from left in ctx.tblLeft
from right in ctx.tblRight.Where(x=>predicate(x)).DefaultIfEmpty()
let optional = right!=null ? null : //only execute query if the condition is false
  (from v in ctx.vwMyView 
   where criteria
   select new anonType { x = v.prop }).FirstOrDefault();
//select projection

However the issue is that EF would not know how to covert an object of anonType returned by my view into a "null" since only value, enums etc. are supported. Are there any other ways of getting this to work?

arviman
  • 5,087
  • 41
  • 48
  • Well, if I understood correctly, the operation (select from join, if not any, then select from view) can hardly be expressed in a sigle sql query. If so, 2 query is the solution – FireAlkazar Feb 18 '14 at 06:38
  • Yeah I'm trying to avoid a 2 query solution as well. Just hoping against hope there might be a way to do this. – arviman Feb 18 '14 at 07:05
  • Can you write raw sql query that do the job? If no, we are forced to use 2 queries. – FireAlkazar Feb 18 '14 at 07:16
  • Nope this is part of a much larger query. Maintaining it as a stored proc would be an issue. If there is a substantial benefit from converting this to a stored proc, then I could get the team to reconsider. – arviman Feb 18 '14 at 07:42

0 Answers0