Roy_Dorsthorst Roy_Dorsthorst - 1 month ago 9
C# Question

C# LINQ how to check if variable starts with "M####" where # is numeric

I'm making a changelog web application that is linked to GitLab, that gets data from a database table that is updated whenever there's a push on GitLab.

To make a push we add a custom ID to the message area followed by the message that could be whatever. This custom ID must be; M####/C#####/Merge/Revert. Where # represents a numeric value (0-9).

The application has to recognise every push's ID so that it can be later used for filtering etc.

Previously I've used this code to select only the M#### and C#####, but once 'Merge' was used as the ID it would also show up.

var query = from c in db.Clgcom
where c.Prjid == 7
&& (c.Modid.StartsWith("C")
|| c.Modid.StartsWith("M"))
select c.Modid;


result:

M1234, M2345, C12345, M0000, C75843, Merge


Using
.StartsWith("M####")
doesn't work because, and I've got no idea how to solve this other than using 10 different cases;

where c.Modid.StartsWith("M0")
|| c.Modid.StartsWith("M1")
|| c.Modid.StartsWith("M2")
|| c.Modid.StartsWith("M3")
// etc.


Is there a better way to solve my problem?

edit: I'm using Entity Framework so regex could cause problems.

Answer

For SQL Server, SqlFunctions.PatIndex can be used. Unlike the EF mapping of string.Contains, PatIndex supports LIKE syntax. A value of 1 means "matched at the first character".

Returns the starting position of the first occurrence of a [LIKE] pattern in a specified expression..

var query = from c in db.Clgcom
    where c.Prjid == 7
      && SqlFunctions.PatIndex("M[0-9][0-9][0-9][0-9]", c.Modid) == 1
    select c.Modid;

For a reasonable data-set / frequency, another option is to pull it client-side and use Linq 2 Objects with a regular expression or whatnot.

The original query be restricted so that 'most' of the work is done in SQL.

var query = (from c in db.Clgcom
    where c.Prjid == 7
      && c.Modid.StartsWith("M")
    select c.Modid)
    .ToList(); // -> IList<string>, in L2O land

// I'm a fan of this syntax :}
var re = new Regex(@"^M\d{4}", RegexOptions.IgnoreCase);
var finalClientFilter = query
    .Where(s => re.IsMatch(s));
Comments