Extend DbFunctions EF6

Question

Is it possible to extend DbFunctions, assuming you are using existing DbFunctions as helper methods. I am essentially rewriting the exact same line of sql code again and again. Are there any alternatives?

Update: Here is an example of what I'm trying to do, but I want to define my own Add function, not use one that I've constructed in my database

var locations = context.Data.Where(e => Functions.Add(e.X, e.Y) >= 10)

why not create a public method that uses the DbFunction and call that method instead of rewriting it? — redanesc, Nov 28 '17 at 04:44
not sure I understand your question. Would this help : https://stackoverflow.com/a/29539227/1236044 ? — jbl, Nov 28 '17 at 08:19
How would calling your own add function be any less code? Why not just e => (e.X + e.Y) >= 10) ? — Mant101, Nov 28 '17 at 14:54
@Mant101 consider Add to just be foo, the actual function is more complicated, but that's not important, it's still using code that works fine in a query, but i want to reuse — TruthOf42, Nov 28 '17 at 15:30
Is "e" always the same type, or does it need to work on different types? Is it always properties X and Y? How flexible does it need to be? — Mant101, Nov 28 '17 at 15:41
@Mant101 it needs to be able to take any property on the model, but it is always going to be the same type — TruthOf42, Nov 28 '17 at 16:33

score 0 · Answer 1 · answered Nov 28 '17 at 11:16

Yes of course you can extend it. Class DbFunctions only contains code that helps the provider of your IQueryable to translate the expression into SQL.

An IEnumerable holds all code to create an Enumerator for you upon request. The Enumerator represents a sequence. Upon request it will give you the first element of the sequence, and once you've got an element it will give you the next one (provided there is one).

An IQueryable works differently. Usually an IQueryable is not meant to be performed by your process, but for instance a database, a remote web site, a CSV file controller, etc.

That is why you need to tell an object that produces IQueryables for which process it must create the IQueryable. In case of Entity Framework you inform the DbContext which database to use.

The IQueryable object holds an Expression to be performed and a Provider. The provider knows which process will perform the query. It also knows how to translate the Expression into the format that the other process understands. Quite often this will be SQL.

If you investigate the remarks section of the MSDN descriptions of IQueryable functions like Where, GroupBy, Select, you'll see that most of these functions will only change the Expression.

As long as you don't ask for the Enumerator, usually implicitly by asking for the first element of a sequence, like in ToList, foreach, FirstOrDefault, etc, the Provider has nothing to do.

But once you ask for the Enumerator, the Expression will be translated by the Provider, who will use the translation to query the data from the other process and create an Enumerator object that can give you the first element of the sequence, and the next ones.

DbFunctions are used when the Provider translates the Expression into SQL. If you create a Queryable with DbFunctions and in your debugger look at the created Expression, you'll still find the used DbFunctions.

The DbFunctions only translate the input into SQL. If does not perform the query itself. The translation is done in local memory.

Having understood this, you can use any function as long as it only changes the Expression into new Expressions into formats that your provider understands.

This means you can't use any of your own functions, or classes. There are even several LINQ functions you can't use

See supported and non-supported LINQ methods

However, if your extension functions input an IQueryable and output an IQueryable, then your extension function will only change the Expression. As long as you fill the Expression with supported LINQ methods you're fine

So if you want to extend IQueryable with a function that returns an IQueryable containing only the invoice that are due to day:

public static IQueryable<Invoice> WhereDueToday(this IQueryable<Invoice> invoices)
{   // returns all invoices that must be paid today
    return invoices
       .Where(invoice => DbFunctions.TruncateTime(invoice.DueDate) == DateTime.Today);
}

Usage:

 IQueryable<Invoice> invoices = dbContext.Invoices
    .Where(invoice => ..);
 IQueryable<Invoice> invoicesDueToDay = invoices
     .WhereDueToday();

The drawback of these type of methods is they can't be used inside subqueries against the db, you can't use WhereDueToday inside a Where clause to say get all the children of an item because it can't be translated to SQL. If you define expressions they are more flexible and can cover both scenarios `db.Invoices.Where(InvoiceRules.DueToday)` and `db.Customers.Where(c => c.Invoices.AsQueryable().Where(InvoiceRules.DueToday))` — Mant101, Nov 28 '17 at 17:36
This function works as IQueryable, the same way as all other IQueryables work: they change the expression. It is not the function that must be translated to SQL, it is the Expression that must be translated by the Provider. As long as you don't ask IQueryable.GetEnumerator(), the Expression is not evaluated, and you are free to change it using any function. Try it yourself with a simple School-with-many-students database — Harald Coppoolse, Nov 28 '17 at 19:40
What you have written works, but returning expressions is better as it is more flexible, I used to write extensions methods like this but ran into their limitations. If I want all customers who have an invoice due today I can't use that extension method to write `db.Customers.Where(c => c.Invoices.AsQueryable().WhereDueToday().Any())` it will try to translate "WhereDueToday" into a store expression and give a "does not recoginise the method" error. If I define an expression I can use it in subqueries and write `db.Customer.Where(c => c.Invoices.AsQueryable().Any(InvoiceRule.DueToday))`. — Mant101, Nov 29 '17 at 09:20

score 0 · Answer 2 · answered Nov 28 '17 at 17:38

You can define a method that returns an Expression and use that in your where clause. Since you want to pass different properties of the object in you can't just write one expressions

public Expression<Func<T, bool>> MyFunc<T>(Expression<T, int> property1, Expression<T, int> property2, int greaterThan)
{
    // Build expression tree
}

I realise "Build Expression tree" isn't hugely useful, but if you don't really want to do add writing out the code to build "add" isn't going to help you either.

If there are just a couple of combinations it might be easier to just hard code for those

public Expression<Func<T, bool>> MyFunc<T>(PropertiesEnum p, int greaterThan)
{
     switch(p)
     {
          case (p.XandY):
              return item => (item.X + item.Y) > greaterThan;
          case (p.XandZ):
              return item => (item.X + item.Z) > greaterThan;
          case (p.YandZ):
              return item => (item.X + item.Z) > greaterThan;
          // other cases
     }
}

You would call this like:

var locations = context.Data.Where(MyFunc(PropertiesEnum.XandY, 10));

Extend DbFunctions EF6

2 Answers2