How to convert this SQL query to LINQ or Lambda expression?

This might do what you want: Layout . Where(x => Position . Where(y => y.

Code == "TopMenu") . Select(y => SqlClient.SqlMethods. Like(x.

PositionID, "%" + y.ID.ToString() + "%") ).Count() > 0 ). Join( Category, x => x. CategoryID, x => x.ID, (o,i) => new { ID = i.

ID, Name = i. Name } ) Although you might want to materialize the 'Position' sub query to save on time like so: var innerSubQuery = Position. Where(y => y.

Code == "TopMenu"); Layout . Where(x => innerSubQuery . Select(y => SqlClient.SqlMethods.

Like(x. PositionID, "%" + y.ID.ToString() + "%") ).Count() > 0 ). Join( Category, x => x.

CategoryID, x => x. ID, (o,i) => new { ID = i.ID, Name = i. Name } ) I do, however, agree with Jon that to really make your life simpler you should change the way you're handling the many-to-many relationship by creating a 'Layout_Position' table.

This might do what you want: Layout . Where(x => Position . Where(y => y.

Code == "TopMenu") . Select(y => SqlClient.SqlMethods. Like(x.

PositionID, "%" + y.ID.ToString() + "%") ).Count() > 0 ). Join( Category, x => x. CategoryID, x => x.ID, (o,i) => new { ID = i.

ID, Name = i. Name } ) Although you might want to materialize the 'Position' sub query to save on time like so: var innerSubQuery = Position. Where(y => y.

Code == "TopMenu"); Layout . Where(x => innerSubQuery . Select(y => SqlClient.SqlMethods.

Like(x. PositionID, "%" + y.ID.ToString() + "%") ).Count() > 0 ). Join( Category, x => x.

CategoryID, x => x. ID, (o,i) => new { ID = i.ID, Name = i. Name } ); I do, however, agree with Jon that to really make your life simpler you should change the way you're handling the many-to-many relationship by creating a 'Layout_Position' table.

Thanks for your comment – ByulTaeng Oct 4 '10 at 15:35.

Well, you won't be able to express the second join as a join, because it's not an equijoin, but this should do it: from c in category join l in layout on c. Id equals l. CategoryId from p in position where p.Id.

Contains(l. PositionId) select new { c. Id, c.Name }; Note that your "contains/LIKE" clause will give you bad results when you've got more than 9 positions.

There are better approaches to many-to-many relations than using a comma-separated list. (Such as an intermediate table.).

Thanks for your help but P. ID type is Long and L. PositionID type is string and we can't use L.PositionID.

Contains(P.ID.ToString()) :( – ByulTaeng Oct 4 '10 at 10:37.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.