# CSDL Function

In my previous post (in French), I used a CSDL Function in order to have a DateDiff using the DB current date from the LINQ To Entities query.

In this post, I want to calculate the OrderDetail amount with:

(double)od.Quantity * (double)od.UnitPrice * (1D – od.Discount)

We can add a property to OrderDetail class but in this case, we can’t use it in a L2E query.

So my idea is to use a CSDL Function instead.

First, we will add the function in the edmx:

<Function Name="GetAmount" ReturnType="Double">
<Parameter Name="orderDetail" Type="Self.OrderDetail" />
<DefiningExpression>
CAST(orderDetail.Quantity AS Edm.Double) * CAST(orderDetail.UnitPrice AS Edm.Double) * (1 – orderDetail.Discount)
</DefiningExpression>
</Function>

Then we will add the following extension method:

public static class OrderDetailExtension
{
[EdmFunction("MyNorthwindEFModel", "GetAmount")]
public static double GetAmount(this OrderDetail orderDetail)
{
return (double)orderDetail.Quantity * (double)orderDetail.UnitPrice * (1D – orderDetail.Discount);
}
}

Now we can write this query:

var q = from e in context.Employees
let sold = e.Orders.Sum(o => o.OrderDetails.Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1D – od.Discount)))
orderby sold descending
select new { Employee = e, Sold = sold };

like this:

var q = from e in context.Employees
let sold = e.Orders.Sum(o => o.OrderDetails.Sum(od => od.GetAmount()))
orderby sold descending
select new { Employee = e, Sold = sold };

This entry was posted in 12253, 7671, 7674, 7675. Bookmark the permalink.

### 2 Responses to CSDL Function

1. Jiri Cincura says:

You don’t even have to write a body of function with EdmFunction attribute if you’re going to use it only in queries. It’ll be translated automagically.

http://blog.cincura.net/230897-model-defined-function-as-a-method-on-entity-or-on-type-for-store-function/

2. Matthieu MEZIL says:

Hi
Of course I know it.
For example, when I want to use the SQL Server date, I will have
throw new NotSupportedException();
on my body method.
In my L2E query, only
CAST(orderDetail.Quantity AS Edm.Double) * CAST(orderDetail.UnitPrice AS Edm.Double) * (1 – orderDetail.Discount)
will be used.
However, if I want to use GetAmount extension method outside L2E, I have to define the body method as I did.