c# - 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();