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.

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>