Get SQL LINQ Results Based off of String List

Question

Lets start off with a list of strings that will be used to filter the results:

 List<String> RadioNames = new List<String>();
 RadioNames.AddRange(new String[] { "abc", "123", "cba", "321" });

I want to be able to filter a LINQ to SQL database table based on RadioNames but the catch is that I want RadioNames to be a partial match (meaning it will catch Radio123 and not just 123).

The source that I need to filter is below:

var ChannelGrants = from cg in sddc.ChannelGrants
                    select new
                    {
                        cg.ID,
                        cg.Timestamp,
                        cg.RadioID,
                        cg.Radio
                    };

So I need to perform something similar to below (outside of the original ChannelGrants results as this is a conditional search)

 if(RadioNamesToSearch != null)
 {
      List<String> RadioNames = new List<String>();

      // Here I split all the radio names from RadioNamesToSearch based on a command separator and then populate RadioNames with the results

      ChannelGrants = from cg in ChannelGrants
                      where ???
                      select cg;
 }

I need help where ??? is in the code above (or if ChannelGrants = ... is invalid all together). Repeating above, I need to filter ChannelGrants to return any matches from RadioNames but it will do partial matches (meaning it will catch Radio123 and not just 123).

All the code is contained in a method as such...

 public static DataTable getBrowseChannelGrants(int Count = 300, String StartDate = null, String StartTime = null, String EndDate = null, String EndTime = null, String RadioIDs = null, String RadioNamesToSearch = null, String TalkgroupIDs = null, String TalkgroupNames = null, bool SortAsc = false)

What is the difference between `RadioNames` and `RadioNamesToSearch`? Where does the latter come from? You haven't declared it anywhere. — Mark Byers, Jun 06 '12 at 22:47
Sorry, I have edited my post to show the method the code is contained in. I left out most of the 270 lines of code to help readability. — Gabriel Graves, Jun 06 '12 at 22:54

seekerOfKnowledge · Answer 1 · 2012-06-07T03:36:55.087

in place of the ???

RadioNames.Where(rn=>cg.Radio.ToLower().Contains(rn.ToLower())).Count() > 0

That should do it...

The ToLower() calls are optional, of course.

EDIT: I just wrote this and it worked fine for me in a Console Application. The result contained one item and the WriteLine spit out "cbaKentucky". Not sure what to tell ya.

class Program
{
    static void Main(string[] args)
    {
        List<String> RadioNames = new List<String>();
        RadioNames.AddRange(new String[] { "abc", "123", "cba", "321" });

        List<ChannelGrants> grants = new List<ChannelGrants>();
        grants.Add(new ChannelGrants() { ID = 1, Radio = "cbaKentucky", RadioID = 1, TimeStamp = DateTime.Now });

        var result = from cg in grants
                  where RadioNames.Where(rn=>cg.Radio.ToLower().Contains(rn.ToLower())).Count() > 0
                  select cg;

        foreach (ChannelGrants s in result)
        {
            Console.WriteLine(s.Radio);
        }
    }
}

class ChannelGrants
{
    public int ID { get; set; }
    public DateTime TimeStamp { get; set; }
    public int RadioID { get; set; }
    public string Radio { get; set; }
}

I'm getting the error "System.NotSupportedException was unhandled by user code - Message=Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator." — Gabriel Graves, Jun 07 '12 at 00:47
The code I was using was: `where filterRadioNames.Where(frn => cg.Radio.Name.ToLower().Contains(frn.ToLower())).Count() > 0` — Gabriel Graves, Jun 07 '12 at 00:48

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

What field in ChannelGrants are you comparing RadioNames to?

To retrieve entries that are only in your RadioNames list, you'd use the contains method like this

ChannelGrants = from cg in ChannelGrants
                  where RadioNames.Contains(cg.Radio)
                  select cg;

(If you wanted to find all rows that had one of your RadioNames in the Radio property. Replace cg.Radio with the appropriate column you are matching)

This gives you a similar outcome if you had this where clause in SQL

where cg.Radio in ("abc", "123", "cba", "321")

from this link How to do SQL Like % in Linq? it looks like you can combo it with like matching as well, but adding slashes, by it's not something I've done personally.

edited May 23 '17 at 11:49

Community

1
1

answered Jun 06 '12 at 23:49

JP Lee

53
5

Sorry, the field `cg.Radio.Name` matched with the list `RadioNames` - I'll try out this code now. – Gabriel Graves Jun 07 '12 at 00:50
I had no choice but to translate what you had to `where RadioNames.Any(frn => cg.Radio.Name.ToLower().Contains(frn.ToLower()))` because I'm looking to see if any `RadioNames` are contained in the cg.Radio.Name property. I got the error "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator." – Gabriel Graves Jun 07 '12 at 00:55

score 0 · Accepted Answer · answered Jul 03 '12 at 19:30

0

At the moment, there doesn't seem to be a best way so I'll answer this until a new answer that doesn't repeat the other answers that don't work on this thread.

answered Jul 03 '12 at 19:30

Gabriel Graves

1,751
1
22
40

Get SQL LINQ Results Based off of String List

3 Answers3