I've been playing with the Telerik MVC 2 Grid control. Test project using the classic Northwind database on SQL Server 2005 with SqlServerDQECompatibilityLevel=2 set in web.config

Clicking the sort header on the grid generates the following Linq query in LLBlGen LinqMetaData

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NorthwindDB.EntityClasses.CustomersEntity]).OrderBy(item => IIF((item != null), item.CompanyName, null)).Take(10)

which generates this SQL which causes an error "Incorrect syntax near ')'. "
(The ORDER BY Case statement has a pair of () instead of a valid value.
)

SELECT TOP(@top0) [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Region], [LPLA_1].[PostalCode], [LPLA_1].[Country], [LPLA_1].[Phone], [LPLA_1].[Fax] FROM [Northwind].[dbo].[Customers] [LPLA_1]  ORDER BY CASE WHEN CASE WHEN () THEN 1 ELSE 0 END=1 THEN [LPLA_1].[CustomerID] ELSE @LO16923f32 END ASC

To assist with troubleshooting, I generated a Linq2SQL datacontext with sqlmetal.exe and used it as a datasource for the same grid.

It generated the following Linq query and SQL which works

+queryExpression    {Table(Customers).OrderBy(item => IIF((item != null), item.CompanyName, null)).Take(10)}    

SELECT TOP (10) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
ORDER BY 
    (CASE 
        WHEN 1 = 1 THEN CONVERT(NVarChar(MAX),[t0].[CompanyName])
        ELSE NULL
     END)

LLBLGenPro 2.6 Final (October 9th, 2009) Adapter

        private readonly DataAccessAdapter _adapter;
        private readonly LinqMetaData _metaData;
        public CustomerController()
        {
            _adapter = new DataAccessAdapter();
            _metaData = new LinqMetaData(_adapter);
        }

        public ActionResult Index()
        {
            return View(_metaData.Customers);
        }

It appears to be coming from the DynamicQueryEngine.cs file from the line

_functionMappings.Add(new FunctionMapping(typeof(object), "IIF", 3, "CASE WHEN {0}=1 THEN {1} ELSE {2} END"));

But I don't know how to fix it... Any help would be appreciated.

Jennifer H

Which LLBLGen Pro runtime library version are you using?

Could you please post the declaration of the Grid?

Can you reproduce this with the normal vanilla .NET grid?

Runtime Library version is 2.6.9.1005

Telerik MVC Grid version is 2009.3.1223

<%
    Html.Telerik().Grid(Model)
        .Name("CustomersGrid")
        .Columns(colums =>
        {
            colums.Add(o => 
          {
              %>
                  <%= Html.ActionLink("Details", "Details", new { id = o.CustomerId}) %>
              <%
                  }).Title("Details");
            colums.Add(o => 
          {
              %>
                  <%= Html.ActionLink("Edit", "Edit", new { id = o.CustomerId })%>
              <%
          }).Title("Edit");

            colums.Add(o => o.CustomerId);
            colums.Add(o => o.CompanyName);
            colums.Add(o => o.City);
            colums.Add(o => o.Region);
            colums.Add(o => o.Country);
            colums.Add(o => o.ContactName);
            colums.Add(o => o.ContactTitle);
        })
        .Pageable()
        .Sortable()
        .Render();
     %>

Would you please try the latest runtime build.

Upgraded to release 2.6.9.1202 with same results

Trying to get it to work with the GridView but haven't used Linq with that component before.

LLBLGenProDatasource works fine but haven't quite figured out how to get paging/sorting to work with LinqMetaData

The linq expression is given in your first post, we'll use that to see if we can reproduce it and fix the issue.

I took the grids out of the equation and created a couple of unit tests to isolate the issue. I was not able to find a way to get the actual Linq query that was being executed so I crafted my own that created the same "Initial expression to process" that the Telerick MVC grid was creating.

TestMethod1 is equivalent to the actual query being generated and is the one that generates the error below.

The equivalent Linq query works with the Linq2SQL generated metadata but not with LLBLGen.

TestMethod2 (way at the bottom) is what I suspect should actually be generated by the grid.

Note that in both cases, the @LO2a5f6532 parameer is not assigned a value by LLBLGen.

That being said, I don't think that TestMethod1 should fail to generate a valid SQL query.

Thanks for the help!

Jennifer

[TestMethod]
public void TestMethod1()
{
    var adapter = new DataAccessAdapter();
    var metadata = new LinqMetaData(adapter);
    var q = (from item in metadata.Customers
             orderby (item != null) ? item.ContactName : null
             select item).Take(10);
    foreach (var c in q)
        Trace.WriteLine(c.ContactName);
}

/*

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NorthwindDB.EntityClasses.CustomersEntity]).OrderBy(item => IIF((item != null), item.ContactName, null)).Take(10)
M

Generated Sql query: 
    Query: SELECT TOP(@top0) [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Region], [LPLA_1].[PostalCode], [LPLA_1].[Country], [LPLA_1].[Phone], [LPLA_1].[Fax] FROM [Northwind].[dbo].[Customers] [LPLA_1]  ORDER BY CASE WHEN CASE WHEN () THEN 1 ELSE 0 END=1 THEN [LPLA_1].[ContactName] ELSE @LO1d4fb92 END ASC
    Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10.
    Parameter: @LO1d4fb92 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.

*/

Test method NorthwindMVCWeb.Tests.NWLinqTest.TestMethod5 threw exception:  SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Incorrect syntax near ')'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. --->  System.Data.SqlClient.SqlException: Incorrect syntax near ')'..
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
--- End of inner exception stack trace ---
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, ref IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute) in LLBLGenProProvider2.cs: line 140
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in LLBLGenProProviderBase.cs: line 258
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in LLBLGenProProviderBase.cs: line 93
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in LLBLGenProProviderBase.cs: line 709
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() in LLBLGenProQuery.cs: line 87
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in LLBLGenProQuery.cs: line 162
at NorthwindMVCWeb.Tests.NWLinqTest.TestMethod5() in NWLinqTest.cs: line 118

[TestMethod]
public void TestMethod2()
{
    var adapter = new DataAccessAdapter();
    var metadata = new LinqMetaData(adapter);
    var q = (from item in metadata.Customers
             orderby (item.ContactName != null) ? item.ContactName : null
             select item).Take(10);
    foreach (var c in q)
        Trace.WriteLine(c.ContactName);
}
/*
: Initial expression to process:
 value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NorthwindDB.EntityClasses.CustomersEntity]).OrderBy(item => IIF((item.ContactName != null), item.ContactName, null)).Take(10)

Generated Sql query: 
    Query: SELECT TOP(@top0) [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Region], [LPLA_1].[PostalCode], [LPLA_1].[Country], [LPLA_1].[Phone], [LPLA_1].[Fax] FROM [Northwind].[dbo].[Customers] [LPLA_1]  ORDER BY CASE WHEN CASE WHEN ( [LPLA_1].[ContactName] IS NOT NULL) THEN 1 ELSE 0 END=1 THEN [LPLA_1].[ContactName] ELSE @LO2a5f6532 END ASC
    Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10.
    Parameter: @LO2a5f6532 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.


*/

Thanks Jennifer, I think I can work with those to find the cause . It will be tomorrow (tuesday) before we can get back to you with a fix though.

Found the cause. It's a little odd that the OrderBy is added that way by the grid btw (as the null filter is of no use at all, as it will result in PK fields being filtered on null, which will never ever happen), but I guess it's done by the grid as it doesn't know if the source is a db source or a list in-memory.

The cause is that an entity == null filter is seen as an inheritance filter because the null check could be used in combination with 'as' and let. However in your case, it has to check on the pk fields. In fact, it can skip the whole clause, but that would be really cumbersome, as it has to cut out an expression at a higher level based on data in one of its leaves so I'll leave the expression as-is (so with the useless pk filter). As a non-inheritance entity doesn't have an inheritance filter it resolves to nothing. Branching it into the proper code in-place already solves it. I'll get back to you when a fix is done.

Please see the attached Linq provider dll for the fix. It also fixes: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17203

Thank you very much! That worked great.

Telerick also suggested a quick and easy patch to the source code for their grid that fixes the problem with the field in thread

http://www.telerik.com/community/forums/aspnet-mvc/grid/sorting-with-llblgen-linqmetadata.aspx

Jennifer wrote:

Thank you very much! That worked great.

Telerick also suggested a quick and easy patch to the source code for their grid that fixes the problem with the field in thread

http://www.telerik.com/community/forums/aspnet-mvc/grid/sorting-with-llblgen-linqmetadata.aspx

Thanks I already found the sort clause pretty odd. That said, the issue of the other developer was not related to your report, we just fixed 2 bugs in 1 new build .

Glad it's now working!