2

There are a number of records in the table, and there is a column called AssignedTo, and the value for AssignedTo is comma separated string, the possible values for it could be something like:

"1"
"2"
"3"
"11"
"12"
"1,2"
"1,3"
"2,3"
"1,2,3"
"1,3,11"
"1,3,12"

If I use the following LINQ query to search, in case value = 1

 records = records.Where(x => x.AssignedTo.Contains(value) || search == null);

It returns the records with AssignedTo value

"1", "11", "12", "1,2", "1,3", "1,2,3", "1,3,11", "1,3,12"

I really want to only return the records with AssignedTo containing "1", which are "1", "1,2", "1,3", "1,2,3", "1,3,11", "1,3,12", do not want "11" and "12"

If I use the following LINQ query to search the qualified records, still value = 1

records = records.Where(x => x.AssignedTo.Contains("," + value + ",") ||  
                        x.AssignedTo.StartsWith(value + ",") ||  
                        x.AssignedTo.EndsWith("," + value) || 
                        value == null);

It returns the records with AssignedTo value "1,2", "1,3", "1,2,3", "1,3,11", "1,3,12", but missing the record with AssignedTo value "1".

Steve
  • 213,761
  • 22
  • 232
  • 286
Denis
  • 75
  • 1
  • 5
  • Can you split each assignedto column into a list of integers before the LINQ? Then your code should work. – fooiey Sep 08 '20 at 20:51
  • I tried that, However I have var records = db.BudgetProjects.AsQueryable();, after that if I write something like string[] ids = AssignedTo.Split(',');, it has error that AssignedTo does not exist in the current context – Denis Sep 08 '20 at 20:56
  • https://stackoverflow.com/questions/35367621/entity-framework-query-a-comma-separated-field/35368849#35368849 – Ivan Stoev Sep 08 '20 at 21:37

3 Answers3

4

Since something like this is likely a search filter, doing the operation in-memory likely isn't a very good option unless the row count is guaranteed to be manageable. Ideally something like this should be re-factored to use a proper relational structure rather than a comma-delimited string.

However, the example you had was mostly there, just missing an Equals option to catch the value by itself. I'd also take the 'value == null' check out of the Linq expression into a conditional as to whether to add the WHERE clause. The difference is with the condition in the Linq, this will generate that into the SQL, where-as by pre-checking you can avoid the SQL conditions all-together if there is no value specified.

if (!string.IsNullOrEmpty(value))
    records = records.Where(x => x.AssignedTo.Contains("," + value + ",") ||  
                    x.AssignedTo.StartsWith(value + ",") ||  
                    x.AssignedTo.EndsWith("," + value) || 
                    x.AssignedTo == value);

This would catch "n,...", "...,n,...", "...,n", and "n".

Steve Py
  • 26,149
  • 3
  • 25
  • 43
2

A better method would be to split the string and search the results:

records = records.Where(x => x.AssignedTo.Split(',').Contains(value) || search == null);

Note that you can't use this method directly in an EF query since there's no way to translate it to standard SQL. So you may want to filter using your Contains as a starting spot (to reduce the number of false positives) and then filter in-memory:

records = records.Where(x => x.AssignedTo.Contains(value) || search == null)
                 .AsEnumerable()  // do subsequent filtering in-memory
                 .Where(x => x.AssignedTo.Split(',').Contains(value) || search == null)

Or redesign your database to use related tables rather than storing a comma-delimited list of strings...

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • There is an error when I try it {"Unable to cast object of type 'WhereEnumerableIterator`1[BudgetLib.BudgetProject]' to type 'System.Linq.IQueryable`1[BudgetLib.BudgetProject]'."} – Denis Sep 08 '20 at 21:40
  • @Denis `records` must be declared as an `IQueryable`, and the conversion to `IEnumerable` is breaking that contract. You can try using a new variable (`records2` would be the lazy name` to allow a different type, or no use `var` (explicitly use `IEnumerable<...>` when declaring the variable. – D Stanley Sep 08 '20 at 21:46
  • Thank you very much @D Stanley! – Denis Sep 08 '20 at 22:01
0

If you are building a linq expression against database then Split function will throw an error. You can use expression below there.

if (!string.IsNullOrEmpty(value)) { records = records.Where(x => (',' + x.AssignedTo + ',').Contains(',' + value + ',') }

Adil Sheikh
  • 1
  • 1