未知列'Project2.Name'在'where子句“ [英] Unknown column 'Project2.Name' in 'where clause'
问题描述
我有一个简单的语句从我的MySQL数据库中获取数据,但它得到以下错误:
[个MySqlException(0X80004005):在未知列'Project2.Name'',其中
条款'] MySql.Data.MySqlClient.MySqlStream.ReadPacket()272结果
MySql.Data.MySqlClient.NativeDriver.GetResult(的Int32&安培; affectedRow ,
的Int64&安培; insertedId)+68结果
MySql.Data.MySqlClient.Driver.GetResult(的Int32 statementId,的Int32&安培;
affectedRows,Int64的&安培; insertedId)+17结果
MySql.Data.MySqlClient.Driver.NextResult(的Int32 statementId,布尔
力)+110 MySql.Data.MySqlClient.MySqlDataReader.NextResult()
761 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (的CommandBehavior
的行为)1557结果
MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(的CommandBehavior
的行为)+33结果
System.Data.Common.DbCommand .ExecuteReader(的CommandBehavior行为)
+12 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(entityCommand
entityCommand,行为的CommandBehavior)435
块引用>
的声明:
使用(myEntities CTX =新myEntities())
{
VAR结果= ctx.Items.Where(X => x.Contact.Country ==国家或地区)
.GroupBy(p值=> p.Name)。凡(X =>!x.Any(γ= GT; y.Value ==X))
。选择(G => g.OrderByDescending(p => p.Date).FirstOrDefault())//< - 错误
。选择(G =>克.FirstOrDefault())//< - 没有错误
.ToList();
}
当我用第
选择
我得到这个错误,与第二,代码工作正常。任何人都知道的原因?
同样的错误发现 p>
我使用.NET连接器6.7.4所以它的不能是错误的#68513
解决方案让见。你有一个完全有效的LINQ to Entities查询,它与SqlServer的供应商,不与MySQL的供应商合作。听起来像一个MySQL提供错误对我来说,它可能是什么吗?但哪一个?我不知道如何帮助,但把我的赌注押在#78610 (由 ASP MVC MSSQL开始到MySQL迁移 SO后),标记为重复#76663 。或#77543 等。
因此MySQL连接器具有与子查询
排序依据
的问题。作为一种变通方法,我可以建议(如果可能)的实施MaxBy
,即(在伪代码),而不是seq.OrderByDescending(COL的替代方法).FirstOrDefault()
使用seq.FirstOrDefault(COL == seq.Max(COL))
这工作模式:VAR结果= ctx.Items
。凡(X => x.Contact.Country ==国家)
。 GROUPBY(p值=> p.Name)
。凡(G =>!g.Any(X => x.Value ==X))
。选择(G => ; g.FirstOrDefault(E => e.Date ==栽培大豆(E1 => e1.Date)))
.ToList();
I have a simple statement to get data from my mySQL database but it get the following error:
[MySqlException (0x80004005): Unknown column 'Project2.Name' in 'where clause'] MySql.Data.MySqlClient.MySqlStream.ReadPacket() +272
MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +68
MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +17
MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +110 MySql.Data.MySqlClient.MySqlDataReader.NextResult() +761 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1557
MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +33
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +435The Statement:
using (myEntities ctx = new myEntities()) { var Result = ctx.Items.Where(x => x.Contact.Country == Country) .GroupBy(p => p.Name).Where(x => !x.Any(y => y.Value == "X")) .Select(g => g.OrderByDescending(p => p.Date).FirstOrDefault()) //<- Error .Select(g => g.FirstOrDefault()) // <- no Error .ToList(); }
When I use the first
Select
I get this error, with the second, the code is working fine. Anyone knows the reason?Same Error found here
I'm using .NET Connector 6.7.4 so it can't be bug #68513
解决方案Let see. You have a perfectly valid LINQ to Entities query, it works with SqlServer provider and does not work with MySQL provider. Sounds like a MySQL provider bug to me, what else it could be? But which one? I don't see how that helps, but put my bet on #78610(initiated by ASP MVC MsSql to MySQL migration SO post), marked as duplicate of #76663. Or #77543 etc.
So MySQL connector has issues with
OrderBy
in subqueries. As a workaround, I could suggest (when possible) the alternative way of implementingMaxBy
, i.e. (in pseudo code) instead ofseq.OrderByDescending(col).FirstOrDefault()
use theseq.FirstOrDefault(col == seq.Max(col))
pattern which works:var Result = ctx.Items .Where(x => x.Contact.Country == Country) .GroupBy(p => p.Name) .Where(g => !g.Any(x => x.Value == "X")) .Select(g => g.FirstOrDefault(e => e.Date == g.Max(e1 => e1.Date))) .ToList();
这篇关于未知列'Project2.Name'在'where子句“的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!