Bug #67377 Specified method is not supported - FirstOrDefault()
Submitted: 25 Oct 2012 12:33 Modified: 3 Jan 2013 0:02
Reporter: P P Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.6.4 OS:Microsoft Windows
Assigned to: Fernando Gonzalez.Sanchez
Tags: .net, FirstOrDefault, MySql.Data.Entity, MySql.Data.Entity.SqlFragment Visit

[25 Oct 2012 12:33] P P
Description:
While executing the following LINQ. It used to work in the version 6.3.5

entities.Reclamation.Where(x=> x.idUser == idUser).Select(x=> new {
id = x.id,
ReclamationReport = x.Report.Count == 0 ? null : x.Report.FirstOrDefault().id
});

I get an exception of the type: System.Data.EntityCommandCompilationException

stack trace:
   em System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   em System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
   em System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   em System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
   em System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   em System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   em System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   em System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   em System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   em System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

How to repeat:
Executing the following query:

entities.Reclamation.Where(x=> x.idUser == idUser).Select(x=> new {
id = x.id,
ReclamationReport = x.Report.Count == 0 ? null : x.Report.FirstOrDefault().id
});
[29 Oct 2012 14:49] Fernando Gonzalez.Sanchez
Hi,

Trying to reproduce this, can you provide a sample SQL to use as schema for this scenario?
[29 Oct 2012 16:41] P P
-- a mysql database to test the scenario

/*
SQLyog Ultimate - MySQL GUI v8.2 
MySQL - 5.1.63-0ubuntu0.10.04.1 : Database - Excel
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`Excel` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `Excel`;

/*Table structure for table `Reclamation` */

DROP TABLE IF EXISTS `Reclamation`;

CREATE TABLE `Reclamation` (
  `idReclamation` int(11) NOT NULL,
  `idUser` int(11) NOT NULL,
  `Text` text NOT NULL,
  PRIMARY KEY (`idReclamation`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `Reclamation` */

insert  into `Reclamation`(`idReclamation`,`idUser`,`Text`) values (1,1,'Reclamation Test');

/*Table structure for table `Report` */

DROP TABLE IF EXISTS `Report`;

CREATE TABLE `Report` (
  `id` int(11) NOT NULL,
  `Text` text NOT NULL,
  `idReclamation` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idReclamation` (`idReclamation`),
  CONSTRAINT `FK_Report` FOREIGN KEY (`idReclamation`) REFERENCES `Reclamation` (`idReclamation`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `Report` */

insert  into `Report`(`id`,`Text`,`idReclamation`) values (1,'Text Report test',1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
[3 Jan 2013 0:02] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.5: 

Calling the method FirstOrDefault "LINQ to Entities" queries caused a
System.Data.EntityCommandCompilationException exception.