Entity Framework Code First Succinctly^®
by Ricardo Peres

CHAPTER 8

Extending Entity Framework

Calling Database Functions

LINQ offers operators for some common database functions, but what happens if you want to call another operator that is not supported?

For example, in SQL Server, we could define a function called CalculateValue that would take a string as its only parameter and return an integer based on it.

CREATE FUNCTION CalculateValue

(

@parameter NVARCHAR

)

RETURNS INT

BEGIN

-- roll out your own implementation

RETURN 0

END

It is possible to call this function on a LINQ query; for that, we need to declare an extension method and decorate it with the EdmFunctionAttribute. This tells Entity Framework that it should call a database function with the given name and signature, and pass it the extension method parameters. Let’s see an example.

public static class StringExtensions

{

[EdmFunction("SqlServer", "CalculateValue")]

public static Int32 CalculateValue(this String phrase)

{

throw (new NotImplementedException());

}

The first parameter to EdmFunctionAttribute is a namespace, but just use whatever you like. The second is the function name, in case it is different from the .NET method name. This implementation throws an exception because it is not meant to be called directly, but on the database.

var value = ctx.Projects.Select(x => x.Name.CalculateValue()).ToList();

You can use this technique for calling other functions, including ones defined by you, but this won’t work with Table-Valued functions (TVL), only with scalar functions. Also, you can only pass it arguments of basic types, not entities or complex types.

Note: This is the exact same technique that the methods on SqlFunctions and EntityFunctions classes use.

Implementing LINQ Extension Methods

Another technique consists of leveraging LINQ expressions to build complex queries from extension methods.

The BETWEEN SQL operator does not have a corresponding LINQ expression. We can use two simultaneous conditions in our LINQ expression, one for the low end of the range (> X) and for the high end (< Y). We can also implement a LINQ extension method to provide us this functionality with a single expression.

public static class QueryableExtensions

{

public static IQueryable<TSource> Between<TSource, TKey>(

this IQueryable<TSource> source,

Expression<Func<TSource, TKey>> property, TKey low, TKey high

) where TKey : IComparable<TKey>

{

var sourceParameter = Expression.Parameter(typeof(TSource));

var body = property.Body;

var parameter = property.Parameters[0];

var compareMethod = typeof(TKey).GetMethod("CompareTo",

new Type[] { typeof(TKey) });

var zero = Expression.Constant(0, typeof(Int32));

var upper = Expression.LessThanOrEqual(Expression.Call(body, compareMethod,

Expression.Constant(high)), zero);

var lower = Expression.GreaterThanOrEqual(Expression.Call(body, compareMethod,

Expression.Constant(low)), zero);

var andExpression = Expression.AndAlso(upper, lower);

var whereCallExpression = Expression.Call

(

typeof(Queryable),

"Where",

new Type[] { source.ElementType },

source.Expression,

Expression.Lambda<Func<TSource, Boolean>>(andExpression,

new ParameterExpression[] { parameter })

);

return (source.Provider.CreateQuery<TSource>(whereCallExpression));

}

For a good understanding on how this is implemented, it is crucial to understand LINQ expressions. There are some good links on the Internet. This technology, although complex to master, has great potential and has drawn a lot of attention.

This is an extension method on IQueryable<T>, and it can be used like this.

//get projects starting between two dates

var projectsBetweenTodayAndTheDayBefore = ctx.Projects

.Between(x => x.Start, DateTime.Today.AddDays(-1), DateTime.Today).ToList();

//projects with 10 to 20 resources

var projectsWithTwoOrThreeResources = ctx.Projects.Select(x =>

new { x.Name, ResourceCount = x.ProjectResources.Count() })

.Between(x => x.ResourceCount, 10, 20).ToList();

The LINQ provider will happily chew the new expression and translate it to the appropriate SQL.

Build apps 2X faster

using Syncfusion Essential Studio^® suite

1800+ high-performance UI components.
Includes popular controls such as Grid, Chart, Scheduler, and more.
24x5 unlimited support by developers.

Get Your Free Trial Now

Extending Entity Framework

Calling Database Functions

Implementing LINQ Extension Methods

DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.