如何在LINQ to Entity Framework中使用SQL通配符 [英] How to use SQL wildcards in LINQ to Entity Framework

问题描述

我有一个如下所示的查询：

I have a query that looks like this:

IQueryable<Profile> profiles = from p in connection.Profiles
    where profile.Email.Contains(txtSearch)
    select p;

我知道当转换为SQL时，它使用 LIKE'％ < value of txtSearch>％'但是如果 txtSearch =jon％gmail.com将其转换为LIKE％jon〜％ gmail.com％'。 〜逃出中间的％是一个通配符。我如何解决这个问题？我需要能够将通配符放入我的LINQ到EF搜索。

I know that when this is converted to SQL it uses a LIKE '%<value of txtSearch>%' but if txtSearch = "jon%gmail.com" it converts it to `LIKE '%jon~%gmail.com%'. The ~ escapes the % in the middle that is a wild card. How do I get around that? I need to be able to put wild cards into my LINQ to EF searches.

推荐答案

我不确定这是可能直接使用linq因为你可以只调用基本的字符串函数包含， StartsWith 或 EndsWith 。 可以使用Entity SQL ，以便您可以组合这些方法。

I'm not sure that this is possible directly with linq because you can call only basic string functions like Contains, StartsWith or EndsWith. It is possible with Entity SQL so you can combine these approaches.

var query = new ObjectQuery<Profile>(
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE '" + wildcardSearch + "'",
    context);

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

ESQL注入触发回来：）

ESQL injection strikes back :)

第二个版本没有注入漏洞（我没有尝试，但它应该工作）：

Second version without injection vulnerability (I didn't try it but it should work):

var commandText =
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE @search";

var query = new ObjectQuery<Profile>(commandText, context);
query.Parameters.Add(new ObjectParameter("search", wildcardSearch));

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

这篇关于如何在LINQ to Entity Framework中使用SQL通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！