How do get the row based on comma separated value using LINQ

Question

0.00/5 (No votes)

See more:

Hi all,

Sno    User_ID
---    -----------
1       S191,S192,S193,S195
2       S291,S192,S193,SSS
3       S291,S292,S393,S395

I have comma-separated data in my column called col1, and I have an Single String usong LINQ

my input is String str_user_id=S193

Required Output is : I want that row which is match any User_id into User_ID Column

like

1    S191,S192,S193,S195
2    S291,S192,S193,SSS

this
thanks in advance

What I have tried:

I have tried this, not getting the correct output

string prefixMatch = SearchDistCode + ",";
string suffixMatch = "," + SearchDistCode;
string innerMatch = "," + SearchDistCode + ",";

var dist_code = (from r in en.temp_rate_target_schemes_new
where (r.Distributor_Code.StartsWith(prefixMatch) || r.Distributor_Code.Contains(innerMatch) || r.Distributor_Code.EndsWith(suffixMatch) || (!r.Distributor_Code.Contains(",") && r.Distributor_Code == SearchDistCode))
select new
{
ratebrokcode = r.Rate_Structure_ID
}).ToList();

Posted 26-Aug-16 0:31am

avelsamy

Updated 28-Aug-16 10:00am

Add a Solution

3 solutions

Solution 1

Seriously? Don't store it like that. Store each UserID separately - storing them as CSV data just makes your life difficult - and this is the trivial bit which is causing you a problem! Wait till you get to removing a user...

But... you can "bodge" to get this working:

C#

Dictionary<int, string> data = new Dictionary<int, string>();
data.Add(1, "S191,S192,S193,S195");
data.Add(2, "S291,S192,S193,SSS");
data.Add(3, "S291,S292,S393,S395");
var matches = data.Where(d => d.Value.Split(',').Contains("S193"));

Posted 26-Aug-16 2:06am

OriginalGriff

Comments

Maciej Los 28-Aug-16 16:00pm

You don't even need to use Split() function.
Where(d=>d.Contains(idToFind)); - should be enough!
See my answer ;)

OriginalGriff 29-Aug-16 6:22am

The only reason I'd use split is so that S193 doesn't match with these:
"S291,S1934,S2345"
"S291,AS193,S295"

Maciej Los 29-Aug-16 6:28am

Good point!
Cheers,
Maciej

OriginalGriff 29-Aug-16 6:41am

I've been there before, my friend! :laugh:

vermavivek 7-May-20 15:32pm

please let me know, how to check S191,S193 contains

Solution 2

refer this article How to Query Comma Separated Value Column in SQL[^]

Posted 26-Aug-16 4:35am

Karthik_Mahalingam

Comments

Maciej Los 28-Aug-16 16:02pm

Even if OP wants to find Linq solution, this is good enough!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-08-28T10:00:00

If you would like to use Linq, you can achieve that in few ways:

using Where + Any - bit complicated

C#

DataTable dt = new DataTable();
dt.Columns.Add("Sno", typeof(int));
dt.Columns.Add("User_ID", typeof(string));
dt.Rows.Add(new object[]{1, "S191,S192,S193,S195"});
dt.Rows.Add(new object[]{2, "S291,S192,S193,SSS"});
dt.Rows.Add(new object[]{3, "S291,S292,S393,S395"});

string suserid="S193";

var result = dt.AsEnumerable()
		.Where(x=>x.Field<string>("User_ID").Split(new string[]{","}, StringSplitOptions.RemoveEmptyEntries).Any(y=>y==suserid))
		.Select(x=>x)
		.ToList();
		
//returns:
//1    |    S191,S192,S193,S195
//2    |    S291,S192,S193,SSS

using Contains() - simpler way
See solution 1[^] provided by OriginalGriff[^]
But, please read my comment to that answer.

C#
```
.Where(x=>x.Field<string>("User_ID").Contains(idToFind))
```