Using Match in a Linq Expression

Question

I have a table with two records (there will be many at runtime). DeviceId entries: "DEVICE1" and "DEVICE2". I want to use regex to retrieve records. The code below compiles, but does not return a result. When I hover over the "devices.ToList ()" operator, I get the following error: "base {System.SystemException} = {" LINQ to Entities does not recognize the "System.Text.RegularExpressions.MatchCollection Matches (System) .String) method ', and this method cannot be translated into a storage expression. " } ". Can someone show me how I can modify my query so that it returns 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

, LINQ to Entities. , , "DEVICE", :

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

EDIT: , , , ( ) , , , , :

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));

, , . , . , AsEnumerable():

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

Answer 2

, LinqToEntities SQL-. SQL Server , .

, StartsWith . LinqToEntities WHERE DeviceId LIKE 'DEVICE%'.

StartsWith , , Contains :

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

: WHERE DeviceId LIKE '%DEVICE%'.

Answer 3

, Entity Framework Linq to Sql SQL. SQL . RegEx , , . .

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(); 
}

, , . , RegEx CLR.

Answer 4

LinqToEntities does not support redirecting Regex to the database. Just do Contains (which converts 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();
        }