Monday, October 28, 2013

"WHERE IN" for LINQ

I was trying to convert the following query to LINQ :

select Osid,OsName,CompanyId,Pid,description,type from OrganisationStructure where OSId in (" + location + ") and type='location'
Where location is a string like '123,2345,12356' ...
What I got on some places the where in clause is converted as bellow :

List<string> locList = location.Split(',').ToList();
var orgloc = (from b in context.OrganisationStructures
                                  where locList.Contains(b.OSId.ToString()) select new{b.OSId,b.OSName,b.CompanyId,b.pID,b.description,b.Type});
This was not giving any error on design time. But while debugging I got the following error:
LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression
The reason is LINQ to SQL doesn't know how to translate the .ToString() call to a SQL expression.Therefore I had to change it like bellow:
var orgloc = (from b in context.OrganisationStructures select new{b.OSId,b.OSName,b.CompanyId,b.pID,b.description,b.Type}).ToList().Where( s => locList.Contains(s.OSId.ToString()) && s.Type.Equals("location") ) ;

Refrence :