LINQ IN subquery

Question

I am new to IQueryable, lambda expressions and LINQ in general. I would like to put a subquery in the where clause as follows:

Code example:

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 to do it in LINQ?

Answer 1

 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; 

Answer 2

 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; 

Answer 3

If you are new to Linq, you absolutely need two basic 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 request in your question. Another tool is LinqPad ( http://www.linqpad.net/ ). This will help you learn Linq when you train with queries.

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

Answer 4

The same example with the 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:

I provide this answer because when I was looking for the answer, I could not find a lot of answer that explains the same concept in the syntax of the method.

So, in the future this may be useful for people who are intensively looking for a syntax method similar to me today. thanks Karthik