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