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.

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)))

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));

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

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