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

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 2

You should always remember that your LinqToEntities 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%'.