0

I have this string "1,2,3,4,5,6,7,8,9,10" and I want to know if any number is in a table of database.

Example

Entity

public class MyNumber {
     public string Numbers {get;set;}
}

MyNumber n = new MyNumber();
n.Numbers = "1,2,3,4,";

MyNumber n1 = new MyNumber();
n.Numbers = "5,6,7,8,9,10,"

context.MyNumbers.Add(n, n1);
context.SaveChanges();

//find numbers from a string of numbers
string _numbers = "10,11,12,15,"

//how to ?
var _myNum = context.MyNumbers.Where
.Select(x => x.Split(new string[]{","},StringSplitOptions.RemoveEmptyEntries))
.Where(x => _numbers.Split(new string[]{","},StringSplitOptions.RemoveEmptyEntries)
.Any(y => y.Equals(x)));

 //no results
 Debug.Writeline(_myNum.Count());

The string "_numbers" has the number 10 for example, and in database has the number "10", I would to know how could I find it using LINQ ?

FernandoPaiva
  • 4,410
  • 13
  • 59
  • 118
  • 1
    Where(n => n.Numbers.Contains("10,")) making sure to include the comma so it doesn't match numbers like 100, 101, etc.. – hijinxbassist Jul 06 '21 at 21:41
  • @hijinxbassist I would like to know how could I do it using the string `string _numbers = "10,11,12,15,"` directly and it will find 10 because had 10 in n1. Could you understand ?` – FernandoPaiva Jul 06 '21 at 21:45
  • 1
    You would need to split the string, and check for each individual number unless you wanted to match the exact string. You could also write a stored procedure to do the heavy lifting on the sql side. – hijinxbassist Jul 06 '21 at 21:46
  • 1
    Something like `context.MyNumbers.Select(x => x.Split(new string[]{","},StringSplitOptions.RemoveEmptyEntries)).Where(x => _numbers.Split(new string[]{","},StringSplitOptions.RemoveEmptyEntries).Any(y => y == x))` – Hayden Jul 06 '21 at 21:46
  • 1
    just a thought: isn't a comma separated string a _terrible_ choice to store a `List` of `Integers`? – Franz Gleichmann Jul 07 '21 at 05:51
  • @Hayden I am trying your suggestion but it does not works throwing an exception `Exception thrown: 'System.NotSupportedException' in EntityFramework.dll`. Could you help me ? – FernandoPaiva Jul 07 '21 at 12:37
  • 1
    Please edit your question to show us exactly what you tried so that we can assist you better. – Hayden Jul 07 '21 at 21:02
  • @Hayden I've edited – FernandoPaiva Jul 07 '21 at 22:35

1 Answers1

1

It is not a good idea to store multiple values as a single comma-separated value in a database column. Read More.

Either way, the problem with your approach is that Linq to SQL does not support String.Split. You can still use Linq to Objects by calling AsEnumerable() on your DbSet. Then you can use any Linq technique you prefer.

An example:

var _myNum = context.MyNumbers
    .AsEnumerable()
    .SelectMany(x => x.Numbers.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
    .Intersect(_numbers.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
    .ToArray();
Pharaz Fadaei
  • 1,605
  • 3
  • 17
  • 28