MySql Connector issues

[Matthew Kuhn]

So, I'm working with a project that was originally written for MS-Sql Server using Entity Framework and I'm having a number of issues with the connector. I have no objections to grabbing the source files and rebuilding and intend to dig through the source for a solution anyways as these are fairly large issues. The database I'm working with contains Approximately 130 Tables with multiple tables having more than 1M rows. It is optimized to 3rd normal with only slight deviations for the purposes of indexing.

The first issue I have had with the connector regards to deep joins in a greedy loading environment (greedy loading is very important as this is an e-commerce solution)

var pfs = tackle.ProductFamilies
.Include("Categories")
.Include("ProductImages")
.Include("ProductFamilySections")
.Include("ProductFamilyViews")
.Include("ProductFamilyVideos")
.Include("Discount_ProductFamily.Discount.Discount_ProductFamily.ProductFamily")
.Include("RelatedProductFamilies.ToFamily.Discount_ProductFamily.Discount")
.Include("RelatedFromProductFamilies.FromFamily")
.Include("Reviews.ReviewTags")
.Include("Reviews.Shopper")
.Include("Questions.Shopper") // submitted by
.Include("Questions.Shoppers") // followers
.Include("Products.ComboElements.ProductPiece.ProductFamily")
.Include("Products.ComboParents.ProductParent.ProductFamily.Discount_ProductFamily.Discount")
.Include("Products.AttributeValues.Attribute")
.Include("Products.ProductImage")
.Where(p => p.ProductFamily_DisplayURL == pfURL).ToList();

This statement will fail giving the following error

Exception
Source: System.Web
Message: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.catalog_product_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\tackleshopnet\0acf046a\fbcfed4\App_Web_3j0nwrh5.0.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Inner Exception
Source: EntityFramework
Message: An error occurred while preparing the command definition. See the inner exception for details.

Stack Trace: at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator) at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.b__6() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.b__5() at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1..GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Catalog_Product.get_Fam() in c:\inetpub\wwwroot\MySql------.com\TackleshopNet\Catalog\Product.aspx.cs:line 47 at ASP.catalog_product_aspx.__RenderContent1(HtmlTextWriter __w, Control parameterContainer) in c:\inetpub\wwwroot\MySql------.com\TackleshopNet\Catalog\Product.aspx:line 6 at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) at ASP.masterpage_master.__RenderHead1(HtmlTextWriter __w, Control parameterContainer) in c:\inetpub\wwwroot\MySql------.com\TackleshopNet\MasterPage.master:line 26 at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at System.Web.UI.HtmlControls.HtmlHead.RenderChildren(HtmlTextWriter writer) at System.Web.UI.HtmlControls.HtmlContainerControl.Render(HtmlTextWriter writer) at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) at ASP.masterpage_master.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\tackleshopnet\0acf046a\fbcfed4\App_Web_amerpfpm.0.cs:line 0 at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at System.Web.UI.Page.Render(HtmlTextWriter writer) at FrontEndPage.Render(HtmlTextWriter writer) in c:\inetpub\wwwroot\MySql------.com\TackleshopNet\App_Code\PageClasses.cs:line 213 at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Inner Exception
Source: MySql.Data.Entity.EF6
Message: The method or operation is not implemented.
Stack Trace: at MySql.Data.Entity.SelectGenerator.Visit(DbApplyExpression expression) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbUnionAllExpression expression) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbSortExpression expression) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)





the second issue also regards greedy loading. When I attempt any joins using MySql Cluster the entire thing freezes up and I suspect that it has to do with the method used for the join. I remember seeing somewhere that the connect joins to a select statement. I need to know where I can change the Sql Generation so that I can change it to do a straight join since the select causes 2 bottlenecks. 1: it forces a read of the whole table, and 2: it changes what should be an index operation into a scan operation. This makes the join method used completely useless in a cluster environment.