MySql + entity framework = Every derived table must have its own alias

Question

I've got to access to some MySql views of another developer from my C# program.

So after some search I decided entity framework, and to use the driver mentioned in this question: Using MySQL with Entity Framework (MySQL .NET Connector).

Now, I'm trying to get the first element of one view:

myEntities.events.First();

and there, I get an exception:

System.Data.EntityCommandExecutionException was unhandled
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source)
       at J4N.GroopleReports.ReportManager.DataImporter.GenerateEvent(GroopleEntities groopleEntities) in C:\Users\J4N\Dropbox\WorkSpaces\DotNET\GroopleReports\ReportManager\DataImporter.cs:line 35
       at J4N.GroopleReports.ReportManager.DataImporter.Import() in C:\Users\J4N\Dropbox\WorkSpaces\DotNET\GroopleReports\ReportManager\DataImporter.cs:line 17
       at ImportTest.Program.Main(String[] args) in C:\Users\J4N\Dropbox\WorkSpaces\DotNET\GroopleReports\ImportTest\Program.cs:line 15
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: MySql.Data.MySqlClient.MySqlException
       Message=Every derived table must have its own alias
       Source=MySql.Data
       ErrorCode=-2147467259
       Number=1248
       StackTrace:
            at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
            at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& 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.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException: 

I searched what is this error "Every derived table must have its own alias" and how to resolve it, I found some responses on the net about what is wrong on a SQL request, but I cannot influence how entity framework constructs those.

Answer 1

In addition to the accepted answer, it should be noted that the same exception (Every derived table must have its own alias) will be thrown if you try to save changes in the database on a table that has no primary key.

This is a known bug that is still not fixed after years (appeared in MySQL 5.5.22 + .Net/Connector 6.5.4 on 04/04/12).

I know it's not a good idea to have a table without primary key, but as there is another bug in the DDL generator, some tables where the associated entity key has the property StoreGeneratedPattern set to None are created without primary key...

Answer 2

I have the same situation. My table has no primary key and I get the same exception. My solution is not very good, but it may be useful to someone.

var settings = dataModel.Settings.ToList();
var setting = settings[0];