2

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, 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.

Roy123
  • 383
  • 4
  • 17
  • 4
    Is this Linq 2 Objects or Linq 2 SQL or Entity Framework or similar, that is, which one is it? – Lasse V. Karlsen Oct 25 '16 at 08:18
  • @LasseV.Karlsen Entity Framework – Roy123 Oct 25 '16 at 08:20
  • Then stay with the 10 cases. Bad design fosters bad solutions, or you can drop that part of the query on the database-side, convert the end-result (unfiltered) to a list or `IEnumerable`, then do normal Linq 2 objects to handle this part. Neither is ideal. – Lasse V. Karlsen Oct 25 '16 at 08:20
  • You could also define a UDF in your database which uses the PATTERN functionality in SQL Server and then import it into your EF Model as a composble function. – Dai Oct 25 '16 at 08:25

2 Answers2

4

If targetting SQL Server, SqlFunctions.PatIndex can be used. Unlike the EF mapping of string.Contains, PatIndex supports LIKE syntax.

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

A value of 1 means "matched at the first character".

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;

As with LIKE, SQL Server is capable of leveraging indexing with PatIndex - in this case a direct seek (if applicable) prior to any other checking because of the fixed anchored "M" start. Actual plan selection may vary.

Another option is to pull it client-side and use Linq 2 Objects with a regular expression or whatnot. This can still be done with little performance impact if there are a low percent of 'edge cases' because the original query be restricted so that most of the work is done in SQL where appropriate indexes can be applied.

var query = (from c in db.Clgcom
    where c.Prjid == 7
      && c.Modid.StartsWith("M") // .. LIKE "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));

See Like Operator in Entity Framework? for general hullaballo.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
1

If you're certain that the domain of Modid values is exactly as you set-out in your original question then you can simply check for unwanted known values which, by elimination, means the remainder will match M... and C..., and the query will be fast and efficient if you use an index (StartsWith is fairly cheap in databases)

IQueryable<String> query = db.Clgcom
    .Where( c =>
        c.Prjid == 7
        &&
        c.Modid != "Merge"
        &&
        ( 
            c.Modid.StartsWith("M") || c.Modid.StartsWith("C")
        )
    )
    .Select( c => c.Modid );

But if you can't make that guarantee then you'll have to use PATINDEX, which is exposed to EF as SqlFunctions.PatIndex.

Dai
  • 141,631
  • 28
  • 261
  • 374