Using "Match" in a Linq statement

Question

I have a table that has two records (there will be many at runtime). The deviceId of the records are, “DEVICE1” and “DEVICE2”. I want to use a regular expression to extract records. The code below compiles but fails to return a result. When I hover the cursor on the “devices.ToList()” statement I get the following error, “base {System.SystemException} = {"LINQ to Entities does not recognize the method 'System.Text.RegularExpressions.MatchCollection Matches(System.String)' method, and this method cannot be translated into a store expression."}”. Can anyone show me how I can modify my query so that this would return records based on the expression?

filterText = @"DEVICE.";
Regex searchTerm = new Regex(filterText);

using (var ctx = new MyEntities())
{
 var devices = from d in ctx.Devices
                let matches = searchTerm.Matches(d.DeviceId)
               where matches.Count > 0
               select ((Device)d);
return devices.ToList();
}

Answer 1

You should always remember that your LingToEntities queries must be translated to SQL queries. Since SQL Server has no support for regular expressions, this can not work.

As suggested in the comment by Paul Ruane, StartsWith will work. This can be translated by LinqToEntities into WHERE DeviceId LIKE 'DEVICE%'.

If StartsWith isn't enough because you may need to look for strings in the middle of database columns, Contains will also work:

var devices = from d in ctx.Devices
              where d.DeviceId.Contains("DEVICE")
              select d;

This will result in the following: WHERE DeviceId LIKE '%DEVICE%'.

Answer 2

I don't believe you can use regular expressions with LINQ to Entities. However, it looks like you're just trying to find devices which start with "DEVICE", so the query would be:

return ctx.Devices.Where(d => d.DeviceId.StartsWith("DEVICE"))
                  .ToList();

EDIT: If you actually need the flexibility of a regular expression, you should probably first fetch the device IDs (and only the device IDs) back to the client, then perform the regular expression on those, and finally fetch the rest of the data which matches those queries:

Regex regex = new Regex(...);

var deviceIds = ctx.Devices.Select(d => DeviceId).AsEnumerable();

var matchingIds = deviceIds.Where(id => regex.IsMatch(id))
                           .ToList();

var devices = ctx.Devices.Where(d => matchingIds.Contains(d.DeviceId));

That's assuming it would actually be expensive to fetch all the data for all devices to start with. If that's not too bad, it would be a simpler option. To force processing to be performed in process, use AsEnumerable():

var devices = ctx.Devices.AsEnumerable()
                         .Where(d => regex.IsMatch(d.DeviceId))
                         .ToList();

Answer 3

Remember when using Entity Framework or Linq to Sql that your query ends up being translated to SQL. SQL doesn't understand your regular expression object, and can't use its matches on the server side. To use your RegEx easily you could instead retrieve all the devices from the server first, and then use your existing logic. e.g.

using (var ctx = new MyEntities()) 
{ 
    var devices = from Device d in ctx.Devices select d;

    // Retrieve all the devices:
    devices = devices.ToList();

    devices = from d in devices
                  let matches = searchTerm.Matches(d.DeviceId) 
                  where matches.Count > 0 
                  select ((Device)d); 

    return devices.ToList(); 
}

This does carry the cost of retrieving more data than you need, potentially much more. For complex logic you may want to consider a Stored Procedure, which could potentially use the same RegEx via CLR functions.

Answer 4

LinqToEntities does not support pushing Regex's down to the database. Just do Contains (which gets converted to sql... where DeviceId Like '%DEVICE%').

    filterText = @"DEVICE.";


    using (var ctx = new MyEntities())
    {
            var devices = from d in ctx.Devices
                          d.DeviceId.Contains(filterText)

                          select d;
            return devices.ToList();
        }