CASE Statement Equivalent in LINQ

Posted: May 16, 2010 in .NET, ADO.NET Entity Framework
Tags: ,

Last Friday while having a discussion on various LINQ standard query operators the topic of expressing T-SQL CASE statement in LINQ came up.

The most obvious solution to this is using the ternary operator as shown below:

using (SchoolEntities sc = new SchoolEntities())
            {
                var query = from grade in sc.StudentGrade
                            join student in sc.Person on grade.Person.PersonID
                                          equals student.PersonID
                            select new
                            {
                                FirstName = student.FirstName,
                                LastName = student.LastName,
                                Grade = grade.Grade.Value >= 4 ? "A" :
                                            grade.Grade.Value >= 3 ? "B" :
                                            grade.Grade.Value >= 2 ? "C" :
                                            grade.Grade.Value != null ? "D" : "-"
                            }; 

                foreach (var q in query)
                {
                    Console.WriteLine("{0} {1} has {2} grade", q.FirstName, q.LastName, q.Grade);
                }
                Console.Read();
            } 


This works fine and the a SQL is fired in the background with T-SQL CASE statement as shown below:

SELECT 
1 AS [C1], 
[Extent2].[FirstName] AS [FirstName], 
[Extent2].[LastName] AS [LastName], 
CASE WHEN ([Extent1].[Grade] >= cast(4 as decimal(18))) THEN N'A' ELSE CASE WHEN ([Extent1].[Grade] >= cast(3 as decimal(18))) THEN N'B' ELSE 
CASE WHEN ([Extent1].[Grade] >= cast(2 as decimal(18))) THEN N'C' ELSE CASE WHEN ([Extent1].[Grade] IS NOT NULL) THEN N'D' ELSE N'-' END END 
END END AS [C2]
FROM  [dbo].[StudentGrade] AS [Extent1]
INNER JOIN [dbo].[Person] AS [Extent2] ON ([Extent1].[StudentID] = [Extent2].[PersonID]) OR (([Extent1].[StudentID] IS NULL) AND 
([Extent2].[PersonID] IS NULL)) 

I though if this CASE statement becomes more complicated we need to extract it into a separate method as shown and call the method from LINQ query.

static void Main(string[] args)
        {
            using (SchoolEntities sc = new SchoolEntities())
            {
                 var query = from grade in sc.StudentGrade
                            join student in sc.Person on grade.Person.PersonID
                                          equals student.PersonID
                            select new
                            {
                                FirstName = student.FirstName,
                                LastName = student.LastName,
                                Grade = GetGrade(grade.Grade.Value)
                            }; 

                foreach (var q in query)
                {
                    Console.WriteLine("{0} {1} has {2} grade", q.FirstName, q.LastName, q.Grade);
                }
                Console.Read();
            }
        }
        public static string GetGrade(decimal value)
        {
            string grade; 

                if(value >=4 ) grade = "A";
                if( value >=3) grade = "B";
                if( value >=2) grade = "C";
                if( value >=0)
                {
                    grade = "D";
                }
                else
                {
                    grade = "-";
                } 

            return grade;
        } 


But this results in the exception:

LINQ to Entities does not recognize the method ‘System.String GetGrade(System.Decimal)’ method, and this method cannot be translated into a store expression.

This query compiles but fails during execution as Entity Framework is not able to convert this custom method to a SQL equivalent function or statement.This issue will be in Entity Framework or LINQ to SQL but will work well LINQ to Objects where there is no backing database.

About these ads
Comments
  1. CASE Statement Equivalent in LINQ « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. CASE Statement Equivalent in LINQ « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from Servefault.com…

  3. CASE Statement Equivalent in LINQ « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from PimpThisBlog.com…

  4. [...] CASE Statement Equivalent in LINQ « Sankarsan's Journal The Case for Staying With Facebook | The American ProspectAre You Really Hungry or Just Thirsty? — My Weight Loss SystemHow to Jump Rope by Fitness Celebrity Jennifer Nicole Lee — My …Cisco posts 63% profit jump – FierceVoIP View the Contact Powered by Staff [...]

  5. [...] This post was mentioned on Twitter by ittyurl. ittyurl said: New at IttyUrl: http://ittyurl.net/Suyi.ashx case statement equivalent in linq « sankarsan’s journal [...]

  6. kapil dev says:

    ok, its really helpful for me,please update that type of artical again…………..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s