Calling Database Functions in LINQ to Entities in Entity Framework 4

May 23, 2009

Calling Database Functions in LINQ to Entities in Entity Framework 4

Another new feature in EF4 isCalling Database Functions in LINQ to Entities in Entity Framework 4 
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.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>