1

I have a comma separated string stored in database.

E.g.: record1 = "1,3,5,7,9,10" and record2 = "4,5,10"

And I have a given information, E.g.: 1.

I have to select the record using LINQ that contains the given info of 1.

The result returned should be record1.

If I were to use .contains() solely, it's not accurate as record2 will be returned as well.

How can I achieve that? Is it possible to achieve that in a single LINQ query?

Thanks for advise !

pekcheng
  • 354
  • 1
  • 5
  • 14

4 Answers4

6

With a single LINQ-to-objects query:

string[] records = new[] { record1, record2 };
string record = records.FirstOrDefault(r => r.Split(',').Any(s => s == "1"));

Demo

CodingIntrigue
  • 75,930
  • 30
  • 170
  • 176
  • 5
    Does that work also in LINQ-TO-"DB"? OP mentioned that he uses a database and wants to select a record. – Tim Schmelter May 07 '14 at 07:39
  • Well, if the table fits into memory, why not I guess. The options of doing this [in Sql look horrid](http://stackoverflow.com/q/2647/314291). The real issue seems to be a wonky model. – StuartLC May 07 '14 at 07:42
  • @TimSchmelter You're not really asking me are you ;) I assumed the data had already been projected, but re-reading the question it doesn't sound that way. – CodingIntrigue May 07 '14 at 07:45
  • Well... if this is the accepted answer, then wait till the IIS host runs out of memory. Bringing all to memory and then filtering with split? Oh dear. Not sure what OP is doing. But nothing wrong with the answer though... :) – brainless coder May 07 '14 at 10:05
  • @Mahmud Agreed, this data structure would bring any server to its knees. SQL, IIS or otherwise. You'd just be shifting burden. While my answer technically answers the question, the *real* answer is Tim Schmelter's comment - the data structure is just plain wrong. – CodingIntrigue May 07 '14 at 10:12
1

First of all I would like to mention what @Tim Schmelter said -

Have you noticed already that the real problem is your creepy datamodel? Use a table with real records instead of a column with a comma separated string.

It is not a good practice to use a datamodel where you need string split match. Because it leads to inefficient systems and not to mention slow queries. But yet, if you really need a solution why not try this -.

There are four occasions where you will get a match,

  1. A prefix match - starting with
  2. Inner Match - contains with
  3. Suffix Match - ends with
  4. The only match - only one item and this is it

considering the scenario I am suggesting the solution below - 

s is the value looking for say "1"
string prefixMatch = s + ",";
string suffixMatch = "," + s;
string innerMatch = "," + s + ",";
string record = <dbRecords>.FirstOrDefault(r=> r.StartsWith(prefixMatch) ||
r.Contains(innerMatch) || r.EndsWith(suffixMatch) || 
(!r.Contains(",") && r == s));

The reason for such a detailed query is to keep your memory utilisation less and letting the SQL query do the hard work of finding the results because this query will support LINQ-to-SQL conversion.

brainless coder
  • 6,310
  • 1
  • 20
  • 36
0

If i understand you correctly, you need as result record that contains "1". So you can use:

private bool GerRecord(string record)
{
    string[] arr=record.Split(',');

    return arr.Contains("1");
}
Gleb
  • 1,412
  • 1
  • 23
  • 55
  • 1
    It'll return 10 aswell. Also, your method has nothing to do with linq2sql. Also2: `GetRecord` (typo I suppose) should not return bool. – Tarec May 07 '14 at 07:56
  • 2
    @Tarec no. It will return true only for records that contains "1". – Gleb May 07 '14 at 09:22
0

Instead of searching 1 you can try 1,(1 and comma combined) for searching in Contains in linQ

Rajeev Ranjan
  • 1,006
  • 8
  • 18