Try .NET /elinq/
Powered by Try .NET

SQL Server Scalar Functions

Calling a scalar function

To connect an SQL UDF, declare a c# function with the correct signature and add [Function] attribute. In fact everything is connected this way. ELINQ does not have any special SQL knowledge for SELECT or FROM. It reads the attributes and generates the query based on metadata.

[Function("sales.udfNetSale")]
public static decimal NetSale(int quantity, decimal listPrice, decimal discount)
{
    // the method is not supported on .NET platform.
    // It's supported in SQL Server only
    throw new NotSupportedException();
}

public void CallScalarUDF()
{
    var quantity = 10;
    var listPrice = 100M;
    var discount = 0.1M;

    var scalar = DbContext.Set<Scalar<decimal>>()
        .Query((Scalar<decimal> alias) =>
            SELECT<Scalar<decimal>>(NetSale(quantity, listPrice, discount).@as(alias.Value)))
        .Single();

    Console.WriteLine(scalar.Value);
}
inside SELECT:
var query = DbContext.Set<OrderNetSale>()
                .Query((OrderItems orderItems, OrderNetSale alias) =>
                {
                    var sum = SUM(NetSale(orderItems.Quantity, orderItems.ListPrice, orderItems.Discount));

                    var result = SELECT<OrderNetSale>(orderItems.OrderId.@as(alias.Order.OrderId), sum.@as(alias.NetSale));
                    FROM(orderItems);
                    GROUP(BY(orderItems.OrderId));

                    return result;
                })
                .OrderByDescending(s => s.NetSale)
                .Include(c => c.Order);

foreach (var orderNetValue in query.Take(3))
    Console.WriteLine((orderNetValue.Order.OrderId, orderNetValue.NetSale));

< BACK | HOME