C# NHibernate 子字符串方法未找到

Question

我正在尝试对联系人列表运行查询并使用给定的字符串过滤器对其进行过滤。代码如下:

var query = Session.QueryOver<CrmContact>()
                .Where(x => x.Account.ID == account.ID && x.IsActive && x.IsContact);    

query.And(Restrictions.Disjunction()
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.FirstName), filtString, MatchMode.Anywhere))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.LastName), filtString, MatchMode.Anywhere))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Email), filtString, MatchMode.Anywhere))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Company), filtString))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Phone.Substring(0, 3)), filtString, MatchMode.Anywhere))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Phone.Substring(3, 3)), filtString, MatchMode.Anywhere))
  .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Phone.Substring(6, 4)), filtString, MatchMode.Anywhere)));

但是，我在最后 3 个语句中遇到了问题，因为在运行它们时出现“未找到方法子字符串”错误。我正在使用子字符串来尝试匹配电话号码的各个部分，以防过滤器是电话号码/电话号码的一部分(例如，“123”将匹配“1234567890”，但不匹配“0123456789”，因为它将跨越两个部分)。

如有任何帮助，我们将不胜感激，谢谢。

Answer 1

在 lambda 中使用 SubString 与 linq-to-nhibernate 是正确的，但不包括 queryover 。您正在使用queryover ，避免使用linq-to-nhibernate语法或使用 Linq 而不是 QueryOver。

Linq(Contains 和 == 的大小写不敏感取决于您的数据库，如果您的数据库已经不区分大小写，您可以删除 ToUpper 调用):

using System.Linq;
using NHibernate.Linq;

// ...

filtString = filtString.ToUpper();
var query = Session.Query<CrmContact>()
    .Where(x => x.Account.ID == account.ID && x.IsActive && x.IsContact)
    .Where(c => c.FirstName.ToUpper().Contains(filtString) ||
        c.LastName.ToUpper().Contains(filtString) ||
        c.Email.ToUpper().Contains(filtString) ||
        c.Company.ToUpper() == filtString ||
        c.Phone.ToUpper().Substring(0, 3).Contains(filtString) ||
        c.Phone.ToUpper().Substring(3, 3).Contains(filtString) ||
        c.Phone.ToUpper().Substring(6, 4).Contains(filtString));

查询:

var query = Session.QueryOver<CrmContact>()
    .Where(x => x.Account.ID == account.ID && x.IsActive && x.IsContact);    

query.And(Restrictions.Disjunction()
   .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.FirstName),
        filtString, MatchMode.Anywhere))
   .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.LastName),
        filtString, MatchMode.Anywhere))
   .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Email),
        filtString, MatchMode.Anywhere))
   .Add(Restrictions.InsensitiveLike(Projections.Property<CrmContact>(c => c.Company),
        filtString))
   .Add(Restrictions.InsensitiveLike(
        Projections.SqlFunction("substring", NHibernateUtil.String,
            Projections.Property<DimServicePoint>(c => c.Phone),
            Projections.Constant(0), Projections.Constant(3)),
        filtString, MatchMode.Anywhere))
   .Add(Restrictions.InsensitiveLike(
        Projections.SqlFunction("substring", NHibernateUtil.String,
            Projections.Property<DimServicePoint>(c => c.Phone),
            Projections.Constant(3), Projections.Constant(3)),
        filtString, MatchMode.Anywhere))
   .Add(Restrictions.InsensitiveLike(
        Projections.SqlFunction("substring", NHibernateUtil.String,
            Projections.Property<DimServicePoint>(c => c.Phone),
            Projections.Constant(6), Projections.Constant(4)),
        filtString, MatchMode.Anywhere)));