7

I'm a newbie with the IQueryable, lambda expressions, and LINQ in general. I would like to put a subquery in a where clause like this :

Sample code :

SELECT * FROM CLIENT c WHERE c.ETAT IN (
 SELECT DDV_COLUMN_VAL FROM DATA_DICT_VAL
 WHERE TBI_TABLE_NAME = 'CLIENT' AND DD_COLUMN_NAME = 'STATUS'
           AND DDV_COLUMN_VAL_LANG_DSC_1 LIKE ('ac%'))

How do I translate this in LINQ ?

Patrice Cote
  • 3,572
  • 12
  • 43
  • 72

4 Answers4

14
var innerquery = from x in context.DataDictVal
                 where x.TbiTableName == myTableNameVariable
                    && x.DdColumnName == "Status"
                    && x.DdbColumnValLangDsc1.StartsWith("ac")
                 select x.DdvColumnVal;

var query = from c in context.Client
            where innerquery.Contains(c.Etat)
            select c;
kbrimington
  • 25,142
  • 5
  • 62
  • 74
  • We have a winner ! Thanks a lot kbrimington. Altough I would have like to understand a bit more. Would you have some good reading suggestions about LINQ, IQueryable and lambda expressions ? Thanks again ! – Patrice Cote Aug 13 '10 at 15:17
  • Although it wouldn't help with this exact scenario, I got a lot of mileage out of reading these examples: http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx. I also learned a lot from Scott Gu's series on Linq-to-SQL: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx – kbrimington Aug 13 '10 at 15:21
  • For me, this does not work. .Contains(column) creates EXISTS() which is not the same as IN(). I was trying to do this in my project and because it creates EXISTS() I get about 700 more rows returned. – ScubaSteve Jul 23 '12 at 14:10
  • Won't work with a Dynamic Linq query that returns IQueryable. There is no Contains method or extension method for IQueryable. – Triynko Sep 25 '15 at 17:06
  • @Triynko, of course it works with dynamic queries. You are probably missing a using statement. The extension method you refer to is `System.Linq.Queryable.Contains(this IQueryable source, TSource item);` If your dynamic query is returning a non-generic `IQueryable`, then you're right; though we are now in a realm far apart from what the OP was asking. – kbrimington Sep 25 '15 at 17:58
4
from c in db.Client
where (from d in db.DataDictVal 
       where d.TblTableName == "Client" 
         && d.DDColumnName == "Status"
         && dd.DdvColumnValLandDsc1.StartsWith("ac"))
       .Contains(c.Etat)
select c;
James Curran
  • 101,701
  • 37
  • 181
  • 258
1

If you are new to Linq, you absolutely need two essential tools. The first is a tool that converts most T-SQL statements to Linq called Linqer (http://www.sqltolinq.com/). This should take care of the query in your question. The other tool is LinqPad (http://www.linqpad.net/). This will help you learn Linq as you practice with queries.

I often use Linqer to convert a T-SQL query for me, and then use LinqPad to fine tune it.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
0

Same example with Linq method syntax:

var innerquery =  dbcontext.DataDictVal                  
                 .where(x=> x.TbiTableName == myTableNameVariable
                    && x.DdColumnName == "Status"
                    && x.DdbColumnValLangDsc1.StartsWith("ac"))
                 .select(x=>x.DdvColumnVal)

var query = dbcontext.Client
            .where( c=>innerquery.Contains(c.Etat))

Note:

Am providing this answer, because when i searched for the answer, i couldn’t find much answer which explains same concept in the method syntax.

So In future, It may be useful for the people, those who intestinally searched method syntax like me today. Thanks karthik

Karthik Elumalai
  • 1,574
  • 1
  • 11
  • 12