0

I have Entity Framework with PostgreSQL Now I do something like this:

string query = "select * from dbo.Products where name ilike '%test%phrase%' 
                or code ilike '%test%phrase%' or code2 ilike '%test%phrase%'";

var result = context.Products.SqlQuery(query);

So I get products with name like this:

test some phrase
some test
some phrase and so on.

How to perform same query with linq or other type of query without raw sql?

I need part of phrase from start of string + part from middle, mayby part from end. Something like regular expression. In postgres it can be done with % symbol

dm k
  • 111
  • 3
  • 9
  • Try this for regex http://buildregex.com/ – Eldho Feb 15 '17 at 09:54
  • Take a look at my answer here: http://stackoverflow.com/questions/1040380/wildcard-search-for-linq/42307642#42307642 I do not know if it works for Postgresql as well, but I think it does since it there are equivalent functions like str_pos and position. –  Feb 17 '17 at 21:31

2 Answers2

1

My original answer was wrong (my apologies to the OP.)

You can do Name.StartsWith, Name.EndsWith, or Name.Contains.

On top of that you can use Devart.Data.PostgreSql.Entity.PgSqlFunctions to achieve LIKE comparisons. i.e.

where PgSqlFunctions.Like(PgSqlFunctions.Lower(c.Name), PgSqlFunctions.Lower(pattern))

Regards,

AB

PS: If you have a lot of data that needs searching like that check out trigram indexes - https://www.postgresql.org/docs/9.6/static/pgtrgm.html

Adam Benson
  • 7,480
  • 4
  • 22
  • 45
0

You could try this.

var result = context.Products.Where(h=>h.Name.Contains("TestPhrase") ||
                                    h.Code.Contains("TestPhrase"))
                             .ToList();
Eldho
  • 7,795
  • 5
  • 40
  • 77
  • How it works if: Search phrase is **"some%phrase"** and result have to be **"some test phrase"** – dm k Feb 15 '17 at 09:31
  • @dmk could you update the question to correct your intention – Eldho Feb 15 '17 at 09:36
  • I need search as example I gave. I can't look up with equlity of phrase. I need part of phrase from start of string + part from middle, mayby part from end. Something like regular expression. In postgres it can be done with % symbol – dm k Feb 15 '17 at 09:43