Manual (Dynamic) LINQ subquery using IN clause
- by immortalali-msn-com
Hi Everyone,
I want to query the DB through LINQ writing manual SQL, my linq method is:
var q = db.TableView.Where(sqlAfterWhere);
returnValue = q.Count();
this method queries well if the value passed to variable "sqlAfterWhere" is: (this variable is String type)
it.Name = 'xyz'
but what if i want to use IN clause, using a sub query. (i need to use 'it' before every column name in the above query to work), i cant use 'it' before the sub query columns as its a separate query, so what should i do, if i dont use any thing, and use column names directly it gives error saying " could not be resolved" where is my column names with out 'it' at the begining.
So the query not working is: (this is a string passed to the variable above):
it.Name IN (SELECT Name FROM TableName WHERE Address LIKE '%SomeAddress%')
the errors come out as:
Name could not be resolved
Address could not be resolved
The exact error is:
"'Name' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 6, column 25."
Same error for "Address as well
if i use 'it.' before these columns it gives error as:
"The element type 'Edm.Int32' and the CollectionType 'Transient.collection[Transient.rowtype(GroupID,Edm.Int32(Nullable=True,DefaultValue=))]' are not compatible. The IN expression only supports entity, primitive, and reference types. , near WHERE predicate, line 6, column 14."
Thanks for the help
