4

I'm trying to recreate the following SQL query in LINQ:

select * from table where column like 'RECORD_%01'

What I have is:

var data = from t in context.table 
           where t.column.StartsWith("RECORD")
           && t.column.EndsWith("01")
           select t;

which is equivalent to:

select * from table where column like 'RECORD%01'

Can someone help me as to how I can add the condition for '_%', not '%'? If you guys don't know what I'm talking about with the SQL issue, please check the following image, which explains SQL.

0xCursor
  • 2,242
  • 4
  • 15
  • 33
puneet sharma
  • 81
  • 5
  • 1
    You are missing a length check. You need to add a check where the length of the string is at least 9 ("RECORD" = 6, "_" = 1, % doesnt matter, "01" = 2) – Franck Jan 19 '18 at 21:25
  • 1
    Perhaps an obvious question but did you try .StartsWith("RECORD_")? I'm curious if LINQ is smart enough to escape the underscore – ColdSolstice Jan 19 '18 at 21:46
  • You don't seem to be aware of [`Like` methods in various ORMs](https://stackoverflow.com/a/46975884/861716). – Gert Arnold Jan 19 '18 at 21:58
  • Is this linq2sql or another orm? There are database specific functions available that you can use – Joe Phillips Jan 19 '18 at 22:05
  • People don't seem to understand that an underscore is a valid placeholder in a SQL LIKE. I think that's where people are getting confused – Joe Phillips Jan 19 '18 at 22:08
  • 1
    @ColdSolstice A test shows that LINQ creates a custom escape character and uses it in the SQL query. – NetMage Jan 19 '18 at 22:10

3 Answers3

2

This is what you want (or something very similar):

https://learn.microsoft.com/en-us/dotnet/api/system.data.linq.sqlclient.sqlmethods.like

LINQ2SQL LIKE Command for Phrase

This allows you to specify a LIKE statement.

var data = from t in context.table 
           where SqlMethods.Like(t.column, "RECORD_%01")
           select t;
Joe Phillips
  • 49,743
  • 32
  • 103
  • 159
0

Here's one way:

var data = from t in context.table 
           where t.column.StartsWith("RECORD")
           && t.column.EndsWith("01")
           && t.column.Length > 8
           select t;

This makes sure that at there is at least one character between RECORD and 01.

Of course, depending on what Query Provider you're using, you may want to simply try use the LIKE operator using SqlMethods.Like, SqlFunctions.PatIndex or one of the methods describe here.

p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
  • Why the downvote? If there is something incorrect or inaccurate in my answer, I'd be glad to correct it. – p.s.w.g Jan 19 '18 at 23:42
0

From this it seems like you wanted to make sure that

  1. It starts with RECORD
  2. It ends with 01
  3. And there is atleast one character after RECORD before 01

In this case you can check start with, end with and also the length.

var data      =    from t in context.table 
                    where t.column.StartsWith("RECORD")
                    && t.column.EndsWith("01")
                    && t.column.Length>=9
                    select t;
PraveenVenu
  • 8,217
  • 4
  • 30
  • 39