c# - LINQ检查列表是否包含另一个列表中的任何项目mysql语法错误
问题描述
我正在将 LINQ 与 MySql 一起使用
mysql版本:5.7.21-log
实体框架:v6.0.0.0
Mysql.数据:v6.9.12.0
MySql.Data.Entity.EF6:(v6.9.12.0
我没有使用MySql.Data.Entity
)
询问
var keywords = new List<string>();
keywords.Add("keyword1");
var query = dbContext.ads.Where(x => keywords.Any(y => x.Title.Contains(y)));
获取Title
包含任何keyword
例外
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%keyword1%))) AS `GroupBy1`' at line 10
完全例外:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%keyword1%)' at line 10
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.EntityFramework.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
--- End of inner exception stack trace ---
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ExamBuilder.Controllers.ExamController.EnterExam()
内部 SQL 查询
SELECT
`Extent1`.`packageMasterId`,
`Extent1`.`title`,
`Extent1`.`packageStatus`,
`Extent1`.`packageCreationDate`,
`Extent1`.`basePrice`,
`Extent1`.`validity`
FROM `package_master` AS `Extent1`
WHERE EXISTS(SELECT
1 AS `C1`
FROM (SELECT
1 AS `X`) AS `SingleRowTable1`
WHERE `Extent1`.`tile` LIKE %keyword1%)
如何修改查询以获取那些title
包含任何内容的记录keyword
?
更新:
问题不重复 链接的问题使用了我使用过的相同查询,但我遇到了 mysql 异常!
解决方案
我已经设法使用Entity Framework
和MySql.Data.*
库的版本重现您的错误。将版本提升到可用的最高版本6.*.*
无济于事,所以这是我使用SqlQuery
.
var keywords = new List<string>() { "Test123", "TestASD" };
var likeStatements = keywords.Select(keyword => $"title LIKE '%{keyword}%'");
var whereClauseBody = string.Join(" OR ", likeStatements.ToArray());
var query = context.Ads
.SqlQuery($"select * from stack_linq.ads where {whereClauseBody};");
笔记:
stack_linq
是架构名称。将其更改为您的架构名称。如果您在默认情况下指定了架构名称,<connectionStrings>
则SqlQuery
可以将其更改为$"select ads where ...
- 在
title LIKE ...
更改列的名称(例如,如果它应该Title
代替title
) - 我的
App.config
文件:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
<connectionStrings>
<add name="StackMySqlContext" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=stack_linq;uid=root;password=mysql" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="mssqllocaldb" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
</configuration>
我发现对设置MWE有用的链接:
- https://dev.mysql.com/downloads/windows/installer/
- https://dev.mysql.com/doc/workbench/en/wb-getting-started-tutorial-creating-a-model.html
- https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html
使用的 Visual Studio 版本:Community 2017 (15.9.5)
推荐阅读
- python - 麦克 | 使用 crontab 运行 Python 脚本不起作用
- html - 在 Angular2+ 中,在模板中使用 [disabled] 而不是“disabled”是否有性能优势
- .htaccess - 将整个域从非 www 重定向到 www 和 HTTP 到 HTTPS
- python - Tensorflow,训练选定的输出单元
- scheme - 如何将任意 s 表达式的第一个元素应用于它的其余部分?
- http-headers - (426):newsapi.org 需要升级错误
- c# - Winforms - 行动中心气球/吐司点击
- api - 空手道:在空手道 v0.9.5 中上传 .xslx 文件时出现 403 禁止错误
- javascript - Javascript 使用此变量获取索引值
- logstash - 在合并两个数据源时,Logstash 聚合映射仅适用于几行