Entity Framework: Unknown column in 'where clause

Question

I am developing an application that will give an overview of what movies are being shown on theaters for the current day.

I get the following exception

Unknown column 'Extent2.Movie_ID' in 'where clause

This is where the exception is thrown (the extra variable, movies2, is just something I did for debugging):

public ActionResult MoviesToday()
{                
    var todaysDate = Convert.ToDateTime(DateTime.Now).Date;
    var showsToday = db.Shows.Where(s => s.StartTime.Year == todaysDate.Year && s.StartTime.Month == todaysDate.Month && s.StartTime.Day == todaysDate.Day);
    var movies = from firstItem in db.Movies
                 join secondItem in showsToday
                      on firstItem equals db.Movies.Where(x => x == secondItem.Movie).FirstOrDefault()
                 select firstItem;            
    movies = movies.Distinct();

    movies = movies.OrderBy(m => m.Name);
    var movies2 = movies.ToList(); // HERE THE EXCEPTION is THROWN
    return View(movies2);
}

This is my Show model class:

public class Show
{
        public int ID { get; set; }        
        public DateTime StartTime { get; set; }
        public string BookingLink { get; set; }
        public string StartTimeAsString { get; set; }

        public Movie Movie { get; set; }
        public Theater Theater { get; set; }
    }

Can you see whats wrong?

Edit: Full exception details + stacktrace:

System.Data.Entity.Core.EntityCommandExecutionException was unhandled by user code
  HResult=-2146232004
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
       vid System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       vid System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       vid System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
       vid System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       vid System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       vid System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
       vid System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       vid System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       vid System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       vid System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       vid System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       vid CinemaWeb.Controllers.MoviesController.MoviesToday() i d:\Development\Cinema\CinemaWeb\Controllers\MoviesController.cs:rad 31
       vid lambda_method(Closure , ControllerBase , Object[] )
       vid System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       vid System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       vid System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       vid System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod()
       vid System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
       vid System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
       vid System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
       vid System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag)
       vid System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
       vid System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
       vid System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
  InnerException: MySql.Data.MySqlClient.MySqlException
       HResult=-2147467259
       Message=Unknown column 'Extent2.Movie_ID' in 'where clause'
       Source=MySql.Data
       ErrorCode=-2147467259
       Number=1054
       StackTrace:
            vid MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            vid MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
            vid MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
            vid MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
            vid MySql.Data.MySqlClient.MySqlDataReader.NextResult()
            vid MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            vid MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            vid System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            vid System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
            vid System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
            vid System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
            vid System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            vid System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            vid System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException: 

Edit 3: I think it is something wrong with the way I set up the mapping between Show and Movie obviously, but examples I find suggests me to implement it this way. For clarity, I want a Show to have one Movie and one Theater..

Answer 1

I made a search, and found this: Entity Framework 5 complex type and unknown column in field list error

copy: By default EF expects columns for properties of complex types in form {complextypename_propertyname}. If you created your tables manually and named columns differently there will be a mismatch. Can you try renaming the columns accordingly (i.e. street to address_street) and try if it works. Alternatively you should be able to add an attribute to the properties on the complex type to tell EF that is should not use the convention but the name you specified (e.g. [Column("street")] for the street property).

Additionally I advise you to use a property like "Id" in the where clause, I had much issues comparing 2 objects like you did.

Ex: var movies = from firstItem in db.Movies join secondItem in showsToday on firstItem equals db.Movies.FirstOrDefault(x => x.ID == secondItem.Movie.ID) select firstItem;