Subquery in Where Clause of LINQ statement

asp.net c# entity-framework linq linq-to-entities

Question

So I tried to follow this example to have a sub-query in the where clause of this LINQ query.

var innerquery =
    from app in context.applications
    select new { app.app_id };

IEnumerable<postDatedCheque> _entityList = context.postDatedCheques
    .Where(e => innerquery.Contains(e.appSancAdvice.application.app_id));

The objective was to select those records from postDatedCheques that have app_id in applications table.

But I am getting following erros inside the where clause:

  1. Delegate 'System.Func' does not take 1 arguments
  2. Cannot convert lambda expression to type 'string' because it is not a delegate type
  3. 'System.Linq.IQueryable' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments
  4. Instance argument: cannot convert from 'System.Linq.IQueryable' to 'System.Linq.ParallelQuery'

What am I coding incorrect?

1
0
5/23/2017 10:31:25 AM

Accepted Answer

I think a simple join would do the job. It will filter out the 'cheques' that have no relative 'app':

  var _entitylist = 
    from cheque in context.postDatedCheques
    join app in context.applications on cheque.appSancAdvice.application equals app
    select cheque;

Edit:

Solutions using a .Contains(...) will be translated into a SQL IN statement. Which will be very inefficient. Linq join is translated into SQL INNER JOIN which is very efficient if your DB schema is well trimmed (FKs, index)

5
4/16/2015 6:57:39 AM

Popular Answer

What about?

IEnumerable<postDatedCheque> _entityList = context.postDatedCheques.Where(
     e => context.applications.Any(
          x => e.appSancAdvice.application.app_id == x.app_id));

And if you want to use two statements, set the first as an expression function.

Expression<Func<string, bool>> innerQuery = 
          x => context.applications.Any(y => y.app_id == x);

IEnumerable<postDatedCheque _entityList = 
  context.postDatedCheques.Where(
    x => innerQuery(x.appSancAdvice.application.app_id));


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow