LINQ quary question

Question

in my db i have a table with column (string) containing the ID (key) of another table.
column string content is in the form of > 1,2,3 exc...

lets say i nave table "TABLEA":
NAME FIELDS
john 1
bob 1,2

i'm passing the "itemFilter" quary in order to find all items in the table that column "FIELDS" match one or more ID's:

var itemFilter = "1";

query = query.Where(c => c.TABLEA.FIELDS.Contains(itemFilter));

"itemFilter" needs to bring back results for both john and bob but this is not working properly.

i belive this quary creates "AND" relation but i need to get an "OR" relation in order to get all items that match one or mote ID's in the array.

how do i do that?

Iftikar Hussain · Answer

try like this var itemFilter = "1"; var tempArry = f.Split(','); var result = (from m in query where m. FIELDS .Split(',').Intersect(tempArry).Any() select m).ToList(); Regards, Iftikar

nave · Answer

Sorry but still not working. Try to change var itemFilter = "1"; to var itemFilter = "1,2"; in order to get both john and bob in the results. i get only bob. any idea? thanks

Iftikar Hussain · Answer

Hi, It is working fine try like this var itemFilter = "1"; var result = query.Where(c => c.FIELDS.Contains(itemFilter)).ToList(); foreach (var info in result) { Response.Write(info.Name); } Regards, Iftikar

nave · Answer

Thank but this is not working. when i change var itemFilter = "1"; to var itemFilter = "1,2"; (want to find all items that contain both 1 or 2 in the array) i get only 1 result. needs to be 2 results (john, bob). when i change to var itemFilter = "1,3"; i get 0 results. any idea? thanks

Muralidharan Deenathayalan · Answer

Contains will do "IN" query from database point of view. public class Record { public string name { get; set; } public string id { get; set; } } List items = new List (); Record item = new Record(); item.name = "john"; item.id = "1"; items.Add(item); item = new Record(); item.name = "bob"; item.id = "1,2"; items.Add(item); item = new Record(); item.name = "demo"; item.id = "3"; items.Add(item); var itemFilter = "1"; var result = items.Where(c=>c.id.Contains(itemFilter)); MessageBox.Show(result.Count().ToString()); And it works fine.