“where子句”中的未知列“extent3.id”

Question

我收到以下错误

unknown column 'extent3.id' in 'where clause' 

当我将下面一行添加到automapper.cs配置文件中时。

.ForMember(d => d.hq_state, m => m.MapFrom(o => o.firm_address.Where(c
 => (c.record_status != DeleteStatus && c.is_hq==true)).FirstOrDefault().ab_state_name))



x.CreateMap<firm, SupplierAutoCompleteDTO>()
                 .ForMember(d => d.display_name, m => m.MapFrom(o => o.name))
                 .ForMember(d => d.firm_contacts, m => m.MapFrom(o => o.firm_contacts.Where(c => c.record_status != DeleteStatus)))
                 .ForMember(d => d.hq_state, m => m.MapFrom(o => o.firm_address.Where(c => (c.record_status != DeleteStatus && c.is_hq==true)).FirstOrDefault().ab_state_name))
                 .ForMember(d => d.supplier_status, m => m.MapFrom(o => o.mst_firm_status.name));

Answer 1

当MySQL考虑column value as a column name时，通常会发生这种情况。让我举个例子来解释一下：

创建一个简单的表：

mysql> CREATE TABLE T1 (id int, name varchar(200), city varchar(200));
Query OK, 0 rows affected (0.24 sec)

插入一些记录：

mysql> INSERT INTO T1 values (1, 'rathish', 'ooty');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO T1 values (2, 'kumar', 'the nilgiris');
Query OK, 1 row affected (0.05 sec)

现在让我们尝试更新一条记录：

mysql> UPDATE T1 SET name = 'rathish kumar' WHERE name = `rathish`;
ERROR 1054 (42S22): Unknown column 'rathish' in 'where clause'

当我在列值周围使用backtick (`)时，就会发生这种情况。

让我们尝试使用单引号或双引号：

mysql> UPDATE T1 SET name = 'rathish kumar b' WHERE name = 'rathish';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

它成功了！让我们再来看一个场景：

mysql> UPDATE T1 SET name = 'rathish kumar b' WHERE name = kumar;
ERROR 1054 (42S22): Unknown column 'kumar' in 'where clause'

这里没有引号，同样的错误。让我们再来看一个场景：

mysql> UPDATE T1 SET name = 'rathish kumar b' WHERE id = kumar;
ERROR 1054 (42S22): Unknown column 'kumar' in 'where clause'

同样，MySQL认为，它是一个列名。所以我的猜测是，extent3.id列被backticks包围，或者试图赋值value with a data type which is not id data type and without quotes。

WHERE extent3.id = `value`; # backtick - value considered as column name

(或)

WHERE extent3.id = value; # without quotes - value considered as column name 

它需要是：

WHERE extent3.id = 'value'; # single or double quotes - for string type

或

WHERE extent3.id = 100; # for int type 

修复此错误将有助于解决此错误，请尝试并让我们知道它是否有帮助。如果不是，则共享生成的查询以供进一步分析。谢谢!