CODEWITHSUNDEEP
sqllinq
tranphu0ng
tranphu0ng

Reputation: 3

How to use where in list items

I have a database as below:

TABLE_B: 
ID    Name      LISTID
1     NameB1    1
2     NameB2    1,10 
3     NameB3    1025,1026

To select list data of table with ID. I used:

public static List<ListData> GetDataById(string id)
{
    var db = Connect.GetDataContext<DataContext>("NameConnection");  
    var sql = (from tblB in db.TABLE_B
               where tblB.LISTID.Contains(id)
               select new ListData
               {
                   Name= tblB.Name,
               });
    return sql.ToList();
}

When I call the function: GetDataById("10") ==> Data return "NameB2, NameB3" are not correct. The data correct is "NameB2". Please help me about that? Thanks!

Upvotes: 0

Views: 70

Answers (1)

Peter B
Peter B

Reputation: 24136

The value 10 will cause unintended matches because LISTID is a string/varchar type, as you already saw, and the Contains function does not know that there delimiters that should be taken into account.

The fix could be very simple: surround both the id that you are looking for and LISTID with extra commas.

So you will now be looking for ,10,.
The value ,10, will be found in ,1,10, and not in ,1025,1026,

The LINQ where clause then becomes this:

where ("," + tblB.LISTID + ",").Contains("," + id + ",")

Upvotes: 1

Related Questions