如何在 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 '%%' 但如果 txtSearch = "jon%gmail.com"> 它将它转换为 `LIKE '%jon~%gmail.com%'.~ 转义了中间的 % ，这是一个通配符.我该如何解决?我需要能够将通配符放入我的 LINQ to 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 直接实现，因为您只能调用基本的字符串函数，例如 Contains、StartsWith 或 EndsWith.可以使用实体 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屋！