Calling Database Functions in LINQ to Entities in Entity Framework 4

Calling Database Functions in LINQ to Entities in Entity Framework 4

Another new feature in EF4 is 
the new SqlFunctions class.
In this post I’ll explain what is
SqlFunctions class and how to use
it in LINQ to Entities queries.

The SqlFunctions Class

The SqlFunctions is a new class in EF4. It contains methods
that expose SQL Server functions to use inside your
LINQ to Entities queries. When you use SqlFunctions methods
the corresponding database functions are being executed. This
feature is specific to SQL Server provider and is located in the
System.Data.Objects.SqlClient. As written in MSDN 
in a note about SqlFunctions – “Similar classes that expose
database functions may be available through other providers.”
There are many functions that you can call through the
SqlFunctions such as Average, CharIndex, Count, DateDiff,
Replace and many more. You should explore the members of
SqlFunctions for more details.

Examples

The following code shows how to get the year of a StartDate
field in a Department class:

using (var context = new SchoolEntities())

{

    var departments = from d in context.Departments

                     where SqlFunctions.Year(d.StartDate) == 2009

                     select d;

    Console.WriteLine(((ObjectQuery)departments).ToTraceString());

    Console.ReadLine();

    foreach (var department in departments)

    {

        Console.WriteLine(department.StartDate);

    }

    Console.ReadLine();

}

As you can see I’m calling the ToTraceString in order to see the
query that will be executed in the database. This is the query:

SELECT

[Extent1].[DepartmentID] AS [DepartmentID],

[Extent1].[Name] AS [Name],

[Extent1].[Budget] AS [Budget],

[Extent1].[StartDate] AS [StartDate],

[Extent1].[Administrator] AS [Administrator

FROM [dbo].[Department] AS [Extent1]

WHERE 2009 = (YEAR([Extent1].[StartDate]))

Another example is calculating the budget of the school:

using (var context = new SchoolEntities())

{

    var schoolBudget = SqlFunctions.Sum(from d in context.Departments

                                        select d.Budget);

    Console.WriteLine(schoolBudget);

    Console.ReadLine();

}

This function is also created in the database and this is the
query:

SELECT 

[GroupBy1].[A1] AS [C1]

FROM ( SELECT 

    SUM([Extent1].[Budget]) AS [A1]

    FROM [dbo].[Department] AS [Extent1]

)  AS [GroupBy1]

Summary

Lets sum up, in the new EF4 you can use the SqlFunctions class
in order to call database functions in LINQ to Entities. This class
is part of SQL Server provider but you should expect similar classes
that expose database functions for other providers in the future.
This is a very helpful tool in the arsenal of tools provided by EF4.