MySQL实体框架连接器是否与OrderBy()不兼容
本文关键字：OrderBy 不兼容 是否 实体 框架 连接器 MySQL | 更新日期: 2023-09-27 18:23:54
我正在使用MySQL实体框架连接器连接到我的MySQL数据库。
它是否按功能与任何订单兼容？每当我从下面的代码中删除.OrderBy()时，它都可以正常工作，但使用它时会出现错误。
using Microsoft.AspNet.Identity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using uQuiz.Domain;
using uQuiz.Domain.Abstract;
namespace uQuiz.WebUI.Controllers
{
[Authorize]
public class DashboardController : Controller
{
private QuizEntities Context;
public DashboardController(QuizEntities context)
{
this.Context = context;
}
/// <summary>
/// The quiz dashboard
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
// Get the logged in user ID
int userId = Convert.ToInt32(User.Identity.GetUserId());
IEnumerable<Quiz> usersQuizzes = this.Context.Quizzes.Where(x => x.UserId == userId && x.Deleted == false).OrderBy(d => d.CreatedTime).ToList();
return View();
}
}
}
类型的异常中出现"System.Data.Entity.Core.EntityCommandExecutionException"EntityFramework.dll，但未在用户代码中处理
附加信息：执行命令时出错释义有关详细信息，请参阅内部异常。
内部异常消息："字段列表"中的未知列"Project1.C1"
这是有效的
IEnumerable<Quiz> usersQuizzes = this.Context.Quizzes.Where(x => x.UserId == userId && x.Deleted == false).ToList();
这不
IEnumerable<Quiz> usersQuizzes = this.Context.Quizzes.Where(x => x.UserId == userId && x.Deleted == false).OrderBy(d => d.CreatedTime).ToList();
以下是与从我的EDMX:自动生成的Quizzes相关的Quiz类
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace uQuiz.Domain
{
using System;
using System.Collections.Generic;
public partial class Quiz
{
public Quiz()
{
this.Answers = new HashSet<Answer>();
this.FeaturedQuizzes = new HashSet<FeaturedQuiz>();
this.PersonalityOutcomes = new HashSet<PersonalityOutcome>();
this.PossibleAnswers = new HashSet<PossibleAnswer>();
this.Questions = new HashSet<Question>();
this.QuizLinks = new HashSet<QuizLink>();
this.QuizPasswords = new HashSet<QuizPassword>();
this.QuizSubmissons = new HashSet<QuizSubmission>();
this.QuizVersions = new HashSet<QuizVersion>();
this.ResultDescriptions = new HashSet<ResultDescriptions>();
this.Takers = new HashSet<Taker>();
}
public long Id { get; set; }
public int UserId { get; set; }
public long QuizVersionId { get; set; }
public string IPAddress { get; set; }
public int IsPublic { get; set; }
public string QuizType { get; set; }
public long CreatedTime { get; set; }
public Nullable<long> BackgroundId { get; set; }
public string UrlId { get; set; }
public long PasswordsDisabled { get; set; }
public long SharedFacebook { get; set; }
public long SharedTwitter { get; set; }
public Nullable<bool> Deleted { get; set; }
public long ShowCorrections { get; set; }
public long ShowBreakdown { get; set; }
public string Thumbnail { get; set; }
public string ShareImage { get; set; }
public Nullable<long> LinkId { get; set; }
public long ForceShareImage { get; set; }
public long ShareCount { get; set; }
public string BackgroundAlign { get; set; }
public string BackgroundColour { get; set; }
public string TextColour { get; set; }
public virtual ICollection<Answer> Answers { get; set; }
public virtual ICollection<FeaturedQuiz> FeaturedQuizzes { get; set; }
public virtual ICollection<PersonalityOutcome> PersonalityOutcomes { get; set; }
public virtual ICollection<PossibleAnswer> PossibleAnswers { get; set; }
public virtual ICollection<Question> Questions { get; set; }
public virtual QuizBackground QuizBackgrounds { get; set; }
public virtual ICollection<QuizLink> QuizLinks { get; set; }
public virtual ICollection<QuizPassword> QuizPasswords { get; set; }
public virtual ICollection<QuizSubmission> QuizSubmissons { get; set; }
public virtual ICollection<QuizVersion> QuizVersions { get; set; }
public virtual User Users { get; set; }
public virtual ICollection<ResultDescriptions> ResultDescriptions { get; set; }
public virtual ICollection<Taker> Takers { get; set; }
public virtual QuizVersion QuizVersion { get; set; }
}
}
更新1
我在同一个名为QuizEntities2的项目中从同一个数据库生成了一个新的EDMX，这很好，唯一的区别是我还没有完成并更改映射的名称。。。这是我控制器里的代码。一个有效，另一个不起作用——它会出现错误。
using (QuizEntities context1 = new QuizEntities())
{
// ERROR: Unknown column 'Project1.C1' in 'field list'
var quizzes1 = context1.Quizzes.Where(x => x.UserId == 1 && x.Deleted == false).OrderByDescending(x => x.CreatedTime).Skip(5).Take(5).ToList();
}
using (QuizEntities2 context2 = new QuizEntities2())
{
// Works completely fine
var quizzes = context2.quizzes.Where(x => x.user_id == 1 && x.deleted == false).OrderByDescending(x => x.created_time).Skip(5).Take(5).ToList();
}
Web.config
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="QuizEntities" connectionString="metadata=res://*/QuizModel.csdl|res://*/QuizModel.ssdl|res://*/QuizModel.msl;provider=MySql.Data.MySqlClient;provider connection string="server=localhost;user id=root;database=uquiznew"" providerName="System.Data.EntityClient" />
<add name="QuizEntities2" connectionString="metadata=res://*/ModelModel.Model1.csdl|res://*/ModelModel.Model1.ssdl|res://*/ModelModel.Model1.msl;provider=MySql.Data.MySqlClient;provider connection string="server=localhost;user id=root;database=uquiznew2"" providerName="System.Data.EntityClient" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
</providers>
</entityFramework>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="PreserveLoginUrl" value="true" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
<add key="owin:AppStartup" value="uQuiz.OwinStart" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
<pages>
<namespaces>
<add namespace="System.Web.Helpers" />
<add namespace="System.Web.Mvc" />
<add namespace="System.Web.Mvc.Ajax" />
<add namespace="System.Web.Mvc.Html" />
<add namespace="System.Web.Routing" />
<add namespace="System.Web.WebPages" />
</namespaces>
</pages>
</system.web>
<system.webServer>
<validation validateIntegratedModeConfiguration="false" />
<handlers>
<remove name="ExtensionlessUrlHandler-Integrated-4.0" />
<remove name="OPTIONSVerbHandler" />
<remove name="TRACEVerbHandler" />
<add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
</handlers>
</system.webServer>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
<bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-5.2.0.0" newVersion="5.2.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.Owin" publicKeyToken="31bf3856ad364e35" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>
工作DbContext的等效测验类
自动复数器把名字搞砸了，叫它Quizze，我没有更改它，因为我刚刚测试了一个新的EDMX。。。
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace uQuiz.Domain.ModelModel
{
using System;
using System.Collections.Generic;
public partial class quizze
{
public quizze()
{
this.answers = new HashSet<answer>();
this.featured_quizzes = new HashSet<featured_quizzes>();
this.personality_outcomes = new HashSet<personality_outcomes>();
this.possible_answers = new HashSet<possible_answers>();
this.questions = new HashSet<question>();
this.quiz_links = new HashSet<quiz_links>();
this.quiz_passwords = new HashSet<quiz_passwords>();
this.quiz_submissions = new HashSet<quiz_submissions>();
this.quiz_versions = new HashSet<quiz_versions>();
this.result_descriptions = new HashSet<result_descriptions>();
this.takers = new HashSet<taker>();
}
public long quiz_ID { get; set; }
public long user_id { get; set; }
public long quiz_version_id { get; set; }
public string ip_address { get; set; }
public int @public { get; set; }
public string quiz_type { get; set; }
public long created_time { get; set; }
public Nullable<long> background_id { get; set; }
public string url_id { get; set; }
public long passwords_disabled { get; set; }
public long shared_facebook { get; set; }
public long shared_twitter { get; set; }
public Nullable<bool> deleted { get; set; }
public long show_corrections { get; set; }
public long show_breakdown { get; set; }
public string thumbnail { get; set; }
public string share_image { get; set; }
public Nullable<long> link_id { get; set; }
public long force_share_image { get; set; }
public long share_count { get; set; }
public string background_align { get; set; }
public string background_colour { get; set; }
public string text_colour { get; set; }
public virtual ICollection<answer> answers { get; set; }
public virtual ICollection<featured_quizzes> featured_quizzes { get; set; }
public virtual ICollection<personality_outcomes> personality_outcomes { get; set; }
public virtual ICollection<possible_answers> possible_answers { get; set; }
public virtual ICollection<question> questions { get; set; }
public virtual quiz_backgrounds quiz_backgrounds { get; set; }
public virtual ICollection<quiz_links> quiz_links { get; set; }
public virtual quiz_links quiz_links1 { get; set; }
public virtual ICollection<quiz_passwords> quiz_passwords { get; set; }
public virtual ICollection<quiz_submissions> quiz_submissions { get; set; }
public virtual ICollection<quiz_versions> quiz_versions { get; set; }
public virtual quiz_versions quiz_versions1 { get; set; }
public virtual user user { get; set; }
public virtual ICollection<result_descriptions> result_descriptions { get; set; }
public virtual ICollection<taker> takers { get; set; }
}
}
除了。在d..CreatedTime中出现两次（但这可能并没有解决问题）CreatedTime也必须是字段属性，而不是方法。（我怀疑这可能是一种方法）。Linq试图将您的命令转换为SQL，SQL知道字段，但不知道C#方法。
此外，按时间戳排序是可疑的，因为值可能都是唯一的，而且它不会增加排序。
否则，我们将不得不检查您的类定义，并将其与sql表定义进行比较。
我已经弄清楚了问题所在。我已经将Users表上的主键Id的类型更改为Int32，以便可以将其与Identity Framework一起使用。
当我第一次生成EDMX文件时，它将其生成为Int64。
我将数据类型改回最初生成的Int64，然后它又工作了。
我也遇到了这个错误，从数据库更新实体模型解决了这个问题。