This is a migrated thread and some comments may be shown as answers.

Sorting with LLBLGen LinqMetaData

3 Answers 215 Views
Grid
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jennifer Hughes
Top achievements
Rank 1
Jennifer Hughes asked on 18 Jan 2010, 08:22 PM

Using SQL Server 2005, Northwind DB, LLBLGen 2.6, MVC 2, and Telerik MVC 2009.3.1223 and Internet Explorer 8

Clicking the sort headers generates SQL errors (below) I was able to use the Linq2SQL provider with no trouble.

The problem appears to be in the file ExpressionFactory.cs in the following function

private static Expression CreateConditionExpression(Expression instance, Expression memberAccess, Expression defaultValue)

{

var nullExpression = DefaltValueExpression(instance.Type);

var isNotNullExpression = Expression.NotEqual(instance, nullExpression);

return Expression.Condition(isNotNullExpression, memberAccess, defaultValue);

}

It returns
[NorthwindDB.EntityClasses.CustomersEntity]).OrderBy(item => IIF((item != null), item.ContactName, null)).Take(10)

and I believe it should be returning
[NorthwindDB.EntityClasses.CustomersEntity]).OrderBy(item => IIF((item.ContactName != null), item.ContactName, null)).Take(10)

I took the grid 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 is what I suspect should actually be generated by the grid. Testing for the column, not the row.

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

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

*/

3 Answers, 1 is accepted

Sort by
0
Atanas Korchev
Telerik team
answered on 19 Jan 2010, 08:28 AM
Hi Jennifer Hughes,

Please use the solution suggested in this forum thread. We are aware of this issue and will try to resolve it soon.

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
0
Jennifer Hughes
Top achievements
Rank 1
answered on 19 Jan 2010, 05:46 PM

Thank you for the pointer!

As suggested in the thread I changed the true to false and now it works correctly.

return

ExpressionFactory.MakeMemberAccess(this.ParameterExpression, this.MemberName, false);

FWIW I also contacted LLBLGen and as a result of my report they were able to not only find a bug in their code, it also solved a related bug reported by another developer! They also provided a fix that can be found in this thread http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17196

Jennifer

0
Atanas Korchev
Telerik team
answered on 20 Jan 2010, 09:47 AM
Hi Jennifer Hughes,

I am glad I was able to help. Indeed a linq provider should be able to process such an expression. The sole purpose of that IIF is to check whether the IQueryable supplied does not contain null values. Since it is very unlikely for an ORM to return null as part of object collection we decided to add the IIF only when dealing with linq to objects. The fix in our code will appear in the upcoming service pack.

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Tags
Grid
Asked by
Jennifer Hughes
Top achievements
Rank 1
Answers by
Atanas Korchev
Telerik team
Jennifer Hughes
Top achievements
Rank 1
Share this question
or