Strange SQL Execution Error Building LINQ Query

jeffs · Post by **jeffs** » Wed 20 Oct 2010 17:14

Hi,

I';m testing out this tool for use connecting to a mySql system we have, and it's mainly been great, but I just hit a snag.

We have a query that is throwing an exception when we call First() on the collection it returns (when it enumerates, presumably), and the query is very similar to other queries that work fine.

Here is the query:

var oClients = (from aProvider in oDataContext.Users
join aFacility in oDataContext.Kids_facility_info on aProvider.id equals aFacility.user
where aProvider.id == iProviderId
select new ChildCareEntityInfo()
{
Type = EntityType.Provider,
Organization = aFacility.business_name,
FirstName = HttpUtility.HtmlDecode(aProvider.first_name),
LastName = aProvider.last_name,
SystemGuid = new Guid(aProvider.mm_id),
IntranetId = Convert.ToInt32(aProvider.id),
UserName = aProvider.user_name,
Email = aProvider.email,
Phone = aFacility.phone,
ParentOrganizationId = Convert.ToInt32(aProvider.sponsor)
});

The error is:

{"Method 'System.String HtmlDecode(System.String)' is not supported for execution as SQL."}
[System.InvalidOperationException]: {"Method 'System.String HtmlDecode(System.String)' is not supported for execution as SQL."}
Data: {System.Collections.ListDictionaryInternal}
HelpLink: null
InnerException: null
Message: "Method 'System.String HtmlDecode(System.String)' is not supported for execution as SQL."
Source: "Devart.Data.Linq"
StackTrace: " at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitRow(ak row)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitSelect(SqlSelect ss)\r\n at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitAlias(d a)\r\n at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)\r\n at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitIncludeScope(aj node)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitIncludeScope(aj node)\r\n at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)\r\n at Devart.Data.Linq.Provider.Query.SqlFormatter.Format(SqlNode node, CommandType& commandType)\r\n at Devart.Data.Linq.Provider.DataProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, IL
ist`1 externalParameterAccessors)\r\n at Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)\r\n at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n at Devart.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)\r\n at System.Linq.Queryable.First[TSource](IQueryable`1 source)\r\n at MinuteMenu.HelpDesk.BusinessLogic.ChildCareEntityInfo.GetProviderById(Int32 iProviderId) in C:\code\products\Intranet\Projects\MinuteMenu.HelpDesk.BusinessLogic\ChildCareEntityHelpers.cs:line 175"
TargetSite: {Devart.Data.Linq.Provider.Query.SqlNode Visit(Devart.Data.Linq.Provider.Query.SqlNode)}

The thing is, this query which uses the same data context and even more HtmlDecode works fine:

var oSponsors = (from aSponsor in oDataContext.Sponsors
join aUser in oDataContext.Users on aSponsor.admin_user equals aUser.id
where aSponsor.minutemenu_customer_id == iSponsorId
select new ChildCareEntityInfo()
{
Type = EntityType.Sponsor,
Organization = HttpUtility.HtmlDecode(aSponsor.name),
FirstName = HttpUtility.HtmlDecode(aUser.first_name),
LastName = HttpUtility.HtmlDecode(aUser.last_name),
SystemId = aSponsor.minutemenu_customer_id,
IntranetId = Convert.ToInt32(aSponsor.id),
UserName = aUser.user_name,
Email = HttpUtility.HtmlDecode(aSponsor.email),
Phone = aSponsor.phone
});

Any leads on where to look for this would be appreciated...

StanislavK · Post by **StanislavK** » Fri 22 Oct 2010 09:21

Could you please specify the exact version of dotConnect for MySQL (or LinqConnect) you are using?

I will send you a test project in a letter, please check that it was not blocked by your mail filter. Please specify what should be changed in the sample to reproduce the problem, or send us your test project (please include the database object definitions in this case).

jeffs · Post by **jeffs** » Wed 27 Oct 2010 16:15

It seems that Contains is not properly mapped either:

Code is:

var oRequesters = (from aUser in dc.TpUser
where oRequesterIDs.Contains(aUser.UserID)
select aUser).ToList();

Error is:

Method 'Boolean Contains(Int32)' has no supported translation to SQL.

SOURCE

System.Data.Linq

CALLSTACK

at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitMethodCall(SqlMethodCall mc)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp)
at System.Data.Linq.SqlClient.SqlVisitor.VisitSelectCore(SqlSelect select)
at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitSelect(SqlSelect select)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at MinuteMenu.HelpDesk.BusinessLogic.TargetProcessHelper.ProcessSelectedRequesters(IList`1 oRequesterIDs) in C:\code\products\Intranet\Projects\MinuteMenu.HelpDesk.BusinessLogic\TargetProcessHelper.cs:line 459

StanislavK · Post by **StanislavK** » Thu 28 Oct 2010 15:38

Could you please specify the definitions of oRequesterIDs and the TpUser table? I have tried the following code and couldn't reproduce the problem with it:

Code: Select all

int[] ints = new int[3]{10,20,30};
var depts = (from item in dc.Depts where ints.Contains(item.Deptno) select item).ToList();

Also, we have fixed the 'method not supported' issue. The fix will be available in the nearest build which we plan to release next week. We will inform you when this build is published.