I'm using the MySQL Entity Framework Connector to connect to my MySQL Database.
Is it compatible with any order by functionality? Whenever I remove .OrderBy()
from the code below, it works fine, but with it I get an error.
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();
}
}
}
An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.dll but was not handled in user code
Additional information: An error occurred while executing the command definition. See the inner exception for details.
Inner Exception Message: Unknown column 'Project1.C1' in 'field list'
This works
IEnumerable<Quiz> usersQuizzes = this.Context.Quizzes.Where(x => x.UserId == userId && x.Deleted == false).ToList();
This Doesn't
IEnumerable<Quiz> usersQuizzes = this.Context.Quizzes.Where(x => x.UserId == userId && x.Deleted == false).OrderBy(d => d.CreatedTime).ToList();
Here is the Quiz
class that relates to Quizzes
auto generated from my 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
{
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; }
}
}
I have generated a new EDMX from the same database within the same project called QuizEntities2
and this works fine, the only difference being that I haven't been through and changed the names for the mappings... here's the code I have within my controller. One works and the other doesn't it comes up with the error.
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>
Equivalent Quiz Class for Working DbContext
The auto pluralizer has mucked up the name and called it Quizze
and I haven't changed it because I have just been testing a new 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; }
}
}
I also faced this error, updating the entity model from database solved the issue.