c# - 当参数不为空 Npgsql 时，在 linq 查询中包含 where 子句

Question

我有以下方法在数据库中注册联系人，但在注册之前我检查联系人是否存在:

bool RegisterContact(Contact contactInfo) { 
          bool entityExists =
                        _dbContext.Contacts.FirstOrDefault(
                            p => (p.FilesID.Equals(contactInfo.FilesID))
                                 && (p.EmailAddress ==
                                     (string.IsNullOrEmpty(
                                         contactInfo.EmailAddress)
                                         ? p.EmailAddress
                                         : contactInfo.EmailAddress))
                                 &&
                                 (p.DisplayName ==
                                  (string.IsNullOrEmpty(
                                      contactInfo.DisplayName)
                                      ? p.DisplayName
                                      : contactInfo.DisplayName)));
                                                                          }

此查询包括搜索条件(FilesID、EmailAddress、DisplayName)中包含值(非空)的字段

这项技术在 MSSQL 中运行良好，今天我将数据库管理器更改为 PostgreSQL 并使用 Npgsql。

除了上面的 linq 查询，所有的事情都有效，它引发了一个异常消息:“无法确定参数 $2 的数据类型”

我被迫这样解决:

bool RegisterContact(Contact contactInfo)
{ 
    Contact entityExists = null;

      if (string.IsNullOrEmpty(contactInfo.EmailAddress) &&
                        (string.IsNullOrEmpty(contactInfo.DisplayName)))
                        entityExists =
                            _dbContext.Contacts.FirstOrDefault(
                                p => p.FilesID.Equals(contactInfo.FilesID));

                    if (!string.IsNullOrEmpty(contactInfo.EmailAddress) && string.IsNullOrEmpty(contactInfo.DisplayName))
                        entityExists =
                            _dbContext.Contacts.FirstOrDefault(
                                p =>
                                    p.FilesID.Equals(contactInfo.FilesID) &&
                                    p.EmailAddress == contactInfo.EmailAddress);

                    if (string.IsNullOrEmpty(contactInfo.EmailAddress) && !string.IsNullOrEmpty(contactInfo.DisplayName))
                        entityExists =
                            _dbContext.Contacts.FirstOrDefault(
                                p =>
                                    p.FilesID.Equals(contactInfo.FilesID) &&
                                    p.DisplayName == contactInfo.DisplayName);


                    if (!string.IsNullOrEmpty(contactInfo.EmailAddress) &&
                        !string.IsNullOrEmpty(contactInfo.DisplayName))
                        entityExists =
                            _dbContext.Contacts.FirstOrDefault(
                                p =>
                                    p.FilesID.Equals(contactInfo.FilesID) &&
                                    p.EmailAddress == contactInfo.EmailAddress &&
                                    p.DisplayName == contactInfo.DisplayName);

}

这是 Npgsql 错误还是设计使然？该问题的任何已知解决方案/解决方法？

Answer 1

我目前有相同的案例。我认为问题在于 NpgSQL 无法识别 string.IsNullOrEmpty。

我用对空字符串的检查替换了测试，始终将输入参数识别为非 NULL。

-- 不好

        var data = from art in _ctx.Set<Soleo.Model.DLAR>()
                   from iva in _ctx.Set<Soleo.Model.DLAI>().Where(k => k.DITTA == art.DITTA && k.COD == art.CIVA).DefaultIfEmpty()
                   from fam in _ctx.Set<Soleo.Model.DLFA>().Where(k => k.DITTA == art.DITTA && k.COD == art.FAM).DefaultIfEmpty()
                   from mar in _ctx.Set<Soleo.Model.DLMA>().Where(k => k.DITTA == art.DITTA && k.COD == art.MAR).DefaultIfEmpty()
                   from udm in _ctx.Set<Soleo.Model.DLUM>().Where(k => k.DITTA == art.DITTA && k.COD == art.UM).DefaultIfEmpty()
                   where art.DITTA == DLAUTH.Config.Current.DITTA && art.COD.Contains(sel_cod) && art.DES.Contains(sel_des)
                   && (string.IsNullOrEmpty(sel_fam) || string.Compare(art.FAM, sel_fam, true) == 0)
                   && (string.IsNullOrEmpty(sel_mar) || string.Compare(art.MAR, sel_mar, true) == 0)
                   && (art.DIS >= sel_dis_da && art.DIS <= sel_dis_a)
                   select new
                   {
                       COD = art.COD,
                       DES = art.DES,
                       DES_UDM = udm.DES,
                       DES_MAR = mar.DES,
                       DES_FAM = fam.DES,
                       DES_CIVA = iva.DES,
                       MAG1 = art.MAG1,
                       MAG2 = art.MAG2,
                       DES_DIS = art.DIS == 1 ? "Si" : "No"
                   };

-- 好:

            var data = from art in _ctx.Set<Soleo.Model.DLAR>()
                   from iva in _ctx.Set<Soleo.Model.DLAI>().Where(k => k.DITTA == art.DITTA && k.COD == art.CIVA).DefaultIfEmpty()
                   from fam in _ctx.Set<Soleo.Model.DLFA>().Where(k => k.DITTA == art.DITTA && k.COD == art.FAM).DefaultIfEmpty()
                   from mar in _ctx.Set<Soleo.Model.DLMA>().Where(k => k.DITTA == art.DITTA && k.COD == art.MAR).DefaultIfEmpty()
                   from udm in _ctx.Set<Soleo.Model.DLUM>().Where(k => k.DITTA == art.DITTA && k.COD == art.UM).DefaultIfEmpty()
                   where art.DITTA == DLAUTH.Config.Current.DITTA && art.COD.Contains(sel_cod) && art.DES.Contains(sel_des)
                   && (string.Compare(sel_fam, "", true) == 0 || string.Compare(art.FAM, sel_fam, true) == 0)
                   && (string.Compare(sel_mar, "", true) == 0 || string.Compare(art.MAR, sel_mar, true) == 0)
                   && (art.DIS >= sel_dis_da && art.DIS <= sel_dis_a)
                   select new
                   {
                       COD = art.COD,
                       DES = art.DES,
                       DES_UDM = udm.DES,
                       DES_MAR = mar.DES,
                       DES_FAM = fam.DES,
                       DES_CIVA = iva.DES,
                       MAG1 = art.MAG1,
                       MAG2 = art.MAG2,
                       DES_DIS = art.DIS == 1 ? "Si" : "No"
                   };

但我认为这不是解决方案。我会向 NpgSQL 报告此情况。