By now you're probably familiar with using PQL in the DFP API. The SQL-like filtering provides an expressive way of working with DFP, but occasionally you'll want to filter on a field that isn't supported by PQL. Luckily, if you're using the .NET library, you can utilize LINQ to augment your filtering capabilities.

Filter on any field

Similar to PQL, LINQ provides a friendly syntax for filtering iterables. As you page through entities from DFP, you can leverage LINQ to add that extra filtering with just a few lines of code.

This example finds all ImageCreatives that are missing altText - a field specific to the subclass and not filterable with PQL.

    CreativePage page = new CreativePage();
    var creativesMissingAltText = new List();

    // Create statement to select image creatives.
    StatementBuilder statementBuilder = new StatementBuilder()
        .Where("creativeType = :creativeType")
        .OrderBy("id ASC")
        .Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT)
        .WithBindVartiableValue("creativeType", "ImageCreative");

    // Page through all creatives and select those that are missing the
    // altText property with LINQ.
    do {
      page =
          creativeService.getCreativesByStatement(statementBuilder.ToStatement());

      creativesMissingAltText.AddRange(
          from ImageCreative creative in page.results
          where creative.altText == ""
          select creative);

      statementBuilder.IncreaseOffsetBy(StatementBuilder.SUGGESTED_PAGE_LIMIT);
    } while (statementBuilder.GetOffset() < page.totalResultSetSize);

    Console.WriteLine("Found {0} ImageCreatives missing altText",
        creativesMissingAltText.Count);

While LINQ offers a way to extend filtering, it's important not to ignore PQL. You'll notice this example used PQL to pre-filter on the creative type. Working with smaller result sets will save you network overhead and processing cycles.

Filtering with regular expressions

Now let's take a look at a more complex example. In this scenario, your application needs to validate the htmlSnippet of CustomCreatives. LINQ allows you to use regular expressions while filtering to extract the matches. Here we'll use a regular expression to make sure URLs in the htmlSnippet point to a certain subdomain.

    // Make sure any URLs to mydomain go through the CDN.
    Regex subdomainRegex = new Regex(@"https?://(?!cdn\.).*?mydomain.com");
    var errors =
        from CustomCreative creative in creativesToValidate
        let matches = subdomainRegex.Matches(creative.htmlSnippet)
        where matches.Count > 0
        select new {
          creativeId = creative.id,
          matchedUrls = from Match match in matches select match.Value
        };

    foreach (var error in errors) {
      Console.WriteLine("Invalid urls found in creative {0}: {1}",
          error.creativeId, error.matchedUrls.Join(","));
    }

 - Chris Seeley, DFP API Team