How to do Linq LIKE or Contains when both values have strings added to them

Question

I am trying to write a Linq query to look for one matching value in a database column that is a comma-delimited string of values. To make sure that I don't match to part of one of those values, or miss the first or last value, I have usually added a comma to the beginning and end of both the target and source. Unfortunately, this isn't working with Linq, and I haven't figured out how to use SqlMethods.Like in this context. My code:

int totalItems = 0;

var userList = from r in db.User
               select r;

if (!string.IsNullOrEmpty(filter.department))
{
    userList = userList.Where(s => s.department.Contains(filter.department));
}
if (!string.IsNullOrEmpty(filter.role))
{
    // The following code that includes commas in the strings 
    // causes a runtime error regarding simple or enumerated objects:
    userList = userList.Where(
        r => (','+r.roles.Trim()+',').Contains(','+filter.role.Trim()+',')
    );

    // The following code works, but is not what I need to do the search properly:
    //userList = userList.Where(s => s.roles.Contains(filter.role));
}

totalItems = userList.Count(); // here is where the error actually occurs

I would not mind using SqlMethods.Like, but don't know how to use it when amending a query like this.

Answer 1

Try this :

userList = userList.Where(
    r => (r.roles.Split(',')).Any(o => o == filter.role)
);