3

I am trying to get the row id from a database in which one of its column(string) contains a specific number

For example, given database structure

id      parent_id
---   -----------
1       12, 240, 79
2       13, 24, 46
3       24

The number that I'm trying to find is 24.

Thus, the result should be 2, 3.

This what I've tried so far

var childId = _context.tblName.Where(x => x.parent_id.Contains("24")).Select(x => x.id).ToList();

But the expression above will return all the ids.

S.Spieker
  • 7,005
  • 8
  • 44
  • 50
sicKo
  • 1,241
  • 1
  • 12
  • 35
  • Can you please provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) From my point the above should work. – MakePeaceGreatAgain Jun 21 '18 at 07:10
  • @HimBromBeere Yes, its a string type – sicKo Jun 21 '18 at 07:13
  • 3
    You are getting all records because in your first row there is 240, which contains 24 – Abhay Prince Jun 21 '18 at 07:16
  • instead of `x.parent_id.Contains("24"))` you can change it to `x.parent_id = "24"`? – Lucifer Jun 21 '18 at 07:19
  • 1
    Try to get a solution where everything is resolved in database, even if that means you need a few contains, beginswith and endswith. Split will be slow if the table have many rows because it gets everything and resolve rest in code – Thomas Koelle Jun 21 '18 at 07:20
  • 3
    @sicKo first of all, *don't* store such data in the database. If you want to store a many-to-many relation create a proper many-to-many table. It will be orders of magnitude faster because it can be indexed. What you did ensures the maximum possible delay because you'll always have to scan the entire table. – Panagiotis Kanavos Jun 21 '18 at 07:27
  • @sicKo if you want to specify a hierarchy, use a `hierarchyid` field and add an index to it. You can have multiple `hierarchyid` fields to map multiple hierarchies. If you want to model a graph, use SQL Server 2017's graph support. – Panagiotis Kanavos Jun 21 '18 at 07:28
  • @sicKo finally, if you really-really want to split strings, at least use the `STRING_SPLIT` function – Panagiotis Kanavos Jun 21 '18 at 07:29
  • @sicKo what are you trying to do? What is the *actual* problem you want to solve with that table? Whatever it is, there are far easier ways to solve it in the database that using CSV values. – Panagiotis Kanavos Jun 21 '18 at 07:43
  • @sicKo Check updated answer. – Gaurang Dave Jun 21 '18 at 07:49
  • 2
    @sicKo check the duplicate. Replace `Contains` with `SqlMethods.Like` and use the correct pattern to get a solution that doesn't load everything into memory, eg `Where(x=>SqlMethods.Like(x.parent_Id,"24,%") || SqlMethods.Like(x.parent_Id,"%, 24") || SqlMethods.Like(x.parent_Id,"%, 24,%")`. – Panagiotis Kanavos Jun 21 '18 at 07:59
  • @sicKo or use [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) and write a stored procedure/function/query that uses eg `from table where '24' in (select TRIM(value) from STRING_SPLIT(parent_ID,','))` – Panagiotis Kanavos Jun 21 '18 at 08:04
  • @PanagiotisKanavos I think your comment is the nearest match to the solution that I want for now nad it can also be done that way using lambda. Can you please put it up as an answer so I can mark it. – sicKo Jun 22 '18 at 03:18
  • @sicKo you haven't explained what you want to do yet, and why you don't use a proper many-to-many table or `hierarchyid`. You don't save any space and you definitely don't gain anything in performance. Finally, there are dozens of duplicates that explain how to find a number in a CSV, or how to split strings in SQL when `STRING_SPLIT` isn't available. Also many dozens that explain why you *shouldn't* use CSVs – Panagiotis Kanavos Jun 22 '18 at 07:35
  • @sicKo the second duplicate show how you can use `LIKE` in different ways to find a number in a comma-separated list. – Panagiotis Kanavos Jun 22 '18 at 07:38
  • @PanagiotisKanavos The database has existed wayyy back.. I dont have the authority to change the db structure – sicKo Jun 25 '18 at 01:36

2 Answers2

-1

If you want to do the comparision completely on the server side you need multiple conditions that either resolve to LIKE or = syntax:

var childId = _context.tblName.Where
                (x => x.parent_id == "24" ||
                 x.parent_id.StartsWith("24, ") ||
                 x.parent_id.EndsWith(" 24") ||
                 x.parent_id.Contains(" 24,")).Select(x => x.id).ToList();

A not so efficient approach would be a Regex. Just plug in a simple regex in that matches the number 24. Beware that the calculations will be done client side: 

var childId = _context.tblName.Where(x => Regex.Match(x.parent_id, @"(?:[^\d]|^)(24)(?:[^\d]|$)").Groups.Count > 1).Select(x => x.id).ToList();
Freggar
  • 1,049
  • 1
  • 11
  • 24
  • 1st, this wont' even run with Entity Framework. It will run in L2S BUT it will load everything in memory first. If you are going to load everything into memory, why use a regex instead of *splittin* the string and filling a dictionary? – Panagiotis Kanavos Jun 21 '18 at 07:30
  • How does splitting not load everything into the memory? – Freggar Jun 21 '18 at 07:33
  • You missed the point. Trying to split anything like that is a bad idea. Trying to do so on the client, no matter how you do it, is a very bad idea. At least, split the string and *create a dictionary* that can be used instead of an index, instead of applying the same regex to all rows. – Panagiotis Kanavos Jun 21 '18 at 07:38
  • And no, regex won't be faster even if it's compared to naive `Contains` conditions - `Contains` is translated to `LIKE` in SQL and will filter results on the server. Regex or splitting will transfer the entire table to the client though, resulting in network and disk saturation just to find a couple of records – Panagiotis Kanavos Jun 21 '18 at 07:41
  • @PanagiotisKanavos Oh sorry I misread your first comment. 1. yeah doing stuff on the client is suboptimal, but SQL cannot do what he wants to do (a stored procedure would be possible though). 2. as you said yourself `Split` will transfer stuff to the client, making `Contains` client side as well... Please post a answer if you have a better solution... – Freggar Jun 21 '18 at 07:47
-1

I find it can be easy to wrap the string with the separators so that you know that a specific search term will be found wherever it is in the string. If you are sure that the format shown in your example will always be adhered to (number comma space number...) then use this:

childId = _context.tblName.Where(x => $", {x.parent_id},".Contains(", 24,")).Select(x => x.id).ToList();

If there may be instances where a space is not included then also remove spaces first.

childId = _context.tblName.Where(x => $",{x.parent_id.Replace(" ", "")},".Contains(",24,")).Select(x => x.id).ToList();

You could reduce the data returned doing your initial search first and then apply this filter:

childId = _context.tblName.Where(x => x.parent_id.Contains("24")).Where(x => $", {x.parent_id},".Contains(", 24,")).Select(x => x.id).ToList();

EDIT: If you are really super concerned about performance and only returning the rows from the table you can actually specify the SQL yourself.

childId = _context.tblName
    .SqlQuery("SELECT * FROM tblName WHERE ', ' + parent_id + ',' LIKE '%, 24,%'")
    .ToList<recordType>().Select(x => x.id).ToList();

This can be improved further by only getting the ids from the table rather than the full row. And you should of course parameterise!

childId = _context.Database
    .SqlQuery<string>("SELECT id FROM tblName WHERE ', ' + parent_id + ',' LIKE '%, ' + @search + ',%'", new SqlParameter("@search", 24))
    .ToList();

There is a tutorial on doing this sort of thing here

Steve Harris
  • 5,014
  • 1
  • 10
  • 25