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.

I would suggest to use NHibernate instead I have not good experisense with EF — Sergey K, Mar 14 '13 at 10:35
@Serghei One year and half that I've resolved this issue ;) Thanks for the help anyway — J4N, Mar 14 '13 at 11:30

score 7 · Answer 1 · answered Apr 05 '12 at 08:26

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...

It should also be mentioned that in the real world, often you have no control over the underlying data structure and are left to try and fix any problems in the area you DO have control over, e.g. the ORM. — Captain Kenpachi, Aug 01 '16 at 11:13

score 1 · Answer 2 · answered Apr 09 '12 at 03:45

1

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];

answered Apr 09 '12 at 03:45

Sergey

1,837
1
18
22

score 0 · Answer 3 · answered Mar 14 '13 at 08:03

0

Those object relational Mappers required and identical id for a row. It used internally in the ORM. I know other ORM tools like NHibernate and Hibernate. Same siu

answered Mar 14 '13 at 08:03

senseyyy

1

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

3 Answers3

Linked