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

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.