0

I'm using Entity Framework Core in .Net Core 2.2, with the recently released Oracle.EntityFrameworkCore library.

I'd like to be able to generate a query like this...

select nvl(nullablecolumn, 'N') from table;

I think I'm right in saying that I can't do this, at least not out of the box... I can however do something similar, using something like this (but then if I end up writing this, why not write actual SQL and skip Entity Framework???)...

from row in table 
select new { somedata = row.nullablecolumn ?? "N" };

The above linq query gets me the same sort of answer as I'm after... question is, can I do some expression tree magic to get the same result?

For example, this question looks like it generates an expression tree for a "like" query, so how would I generate an expression tree (or modify the existing expression tree) to make the select side of the statement emit nvl()?

This would be useful where you have Entity Framework Value Conversions...

Bonus points (if I could give bonus points) if you can give me a clue on how to create an expression tree to manipulate the where side of the query... AutoMapper manages this somehow with "Projections"?

Any thoughts/pointers would be greatly appreciated...

0909EM
  • 4,761
  • 3
  • 29
  • 40
  • You should let AutoMapper do that for you. It's much easier. – Lucian Bargaoanu Sep 06 '19 at 18:13
  • OK, I could do that, but that doesn't improve my understanding at all, does it? What if AutoMapper doesn't support an operation I want/need, then I'm back in the same situation. call it academic curiosity if you want to. Any thoughts/pointers? – 0909EM Sep 07 '19 at 00:21
  • Am I right in thinking this all comes down to expression trees with IQueryable? Can anyone point me in the right direction?? – 0909EM Sep 07 '19 at 00:52
  • Actually `ProjectTo` is [extensible](https://github.com/AutoMapper/AutoMapper/blob/master/src/UnitTests/Projection/BindersAndResultConverters.cs) and will support most things your provider supports. – Lucian Bargaoanu Sep 07 '19 at 02:51

1 Answers1

0

To translate your own method invocation to proper SQL function you can use HasDbFunction from docs.

You have to define own static method as

public static class OracleDbFunction
{
    public static string Nvl(string string1, string replace_with) => throw new NotImplementedException(); // You can provide matching in-memory implementation for client-side evaluation of query if needed
}

register it in your DbContext

protected overridevoid OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(OracleDbFunction).GetMethod(nameof(OracleDbFunctionExtensions.Nvl, builder => 
    {
        builder.HasName("nvl");
    });
}

and use it in your Where expression as

.Where(i => OracleDbFunction.Nvl(i.nullablecolumn, "N") == "N")

You can also use attribute DbFunctionAttribute on OracleDbFunctionExtensions.Nvl to avoid registering it in OnModelCreating

Gullard
  • 141
  • 10
  • I've tried to use this, the code for registration gives me The parameter '_' for the DbFunction 'OracleDbFunctionExtensions.Nvl' has an invalid type 'DbFunctions'. Ensure the parameter type can be mapped by the current provider. Using the atribute gets me further, but with this var anyValue = await _context.DataSet .Where(x => EF.Functions.Nvl(x.nullablecolumn, "N") == "N") .FirstOrDefaultAsync(); I still receive The LINQ expression 'where (__Functions_1.Nvl([x].nullablecolumn, "N") == "N")' could not be translated and will be evaluated locally.'. – 0909EM Sep 06 '19 at 23:50
  • Ok, my bad. They use custom [translators](https://github.com/aspnet/EntityFrameworkCore/blob/release/3.1/src/EFCore.Relational/Query/Internal/LikeTranslator.cs) to translate own functions (mentioned `Like` or `DateDiff*`). I rewrite answer to static function – Gullard Sep 07 '19 at 07:14