How to select/filter against substring in a list of strings?

Question

I have a LINQ result set I'm trying to filter in a strange and peculiar way.

List<string> MyDomains = [Code to get list];

var x = (from a in dc.Activities
    where a.Referrer != null
        && a.Referrer.Trim().Length > 0
        && !a.Referrer.Contains("localhost")
        && a.SearchResults.Count() == 0
    orderby a.ID descending
    select a)
    .Take(20);

Now that I have that out of the way, let me explain it better. MyDomains is a list of strings; each one is a root domain I own.

a.Referrer is a string containing a referrer from a GET to one of my websites. Note this string will contain subdomains, folders, files, and querystrings.

I want to filter x by the root domain of a.Referrer being in the MyDomains list. That is, I want to return all records that do not match in this way. The result set should end up containing Activities whose Referrer is not one of my domains.

I've been learning Lambda expressions, but so far haven't been able to craft one to meet this goal since it effectively needs a where clause with logic inside it (Possibly loops, substrings, etc).

Currently I'm thinking of casting X to a List, filtering them manually, then binding the list to the target control instead of binding X. I have an extension method to get the root domain of a Uri and another to determine whether it's my domain, but can't put them in a Lambda here because they have "no supported translation to SQL".

Architectural disagreements aside, how can I fulfill this from within my LINQ query?

---

Edit: Note I want to do this from within the query so I remove the matched records before my .Take(20) is added. I'd like to get the same number of results every time without having to call the database for more.

Answer 1

I think that adding

&& !MyDomains.Contains(a.Referrer)

Should do the trick

Answer 2

If I understood correctly, you want to obtain the top 20 activities, sorted by ID, whose Referrer field do not contain any of the items in MyDomains as a substring.

The following, or something similar, should work:

var theActivities = (from a in dc.Activities
    where a.Referrer != null
        && a.Referrer.Trim().Length > 0
        && a.SearchResults.Count() == 0
    select a);

foreach(var domain in MyDomains) {
    theActivities = theActivities.Except(dc.Activities.Where(a => a.Referrer.Contains(domain)));
}

theActivities = theActivities.OrderBy(a => a.Id).Take(20);

//Now you can query theActivities

Note however that you will end up with a rather long SQL query, since a WHERE clause will be added for each item in MyDomains.

UPDATE: Indeed, this will not work. Since the query expression is evaluated when it is actually queried, all the Except clauses use the same value for the domain variable (which is the last value set).

The only solution I can think then, is to dinamically generate a SQL command to get the data. I have no way to validate code right now, but it would be approximately something like this:

var whereClauses=new List<string>();

for(int i=0; i<MyDomains.Length; i++) {
    whereClauses.Add(string.Format("(Referrer like {{{0}}})", i));
}

var sqlFormattedDomains=MyDomains.Select(d => string.Format("%{0}%", d)).ToArray();

var sqlCommand=string.Format(
    "select top 20 * from Activities where (not Referrer is null) and (not ({0})) order by Id",
    sqlFormattedDomains.Join(" or "));

var x=dc.ExecuteQuery<Activities>(sqlCommand, sqlFormattedDomains);

You will have to expand the SQL command for the SearchResults.Count() == 0condition, I guess that this has to do with adding a join clause to another table.