Call LPAD function in Linq Query

Call LPAD function in Linq Query

Postby Rupesh » Wed 02 Mar 2016 12:58

Hello Team,

I am using DotConnect for Oracle (trial Profressional Edition) with Entity Framework 6. I am new to this DotConnect provider and having some issues as below :
1. How do we use or import native Oracle functions through Linq. I tried below code but got exception as "No overload of canonical function 'Edm.Substring' is..."
LPAD function :
var sql = "SELECT VALUE tbl FROM Product as tbl";
var predicate = "Substring(Concat('0000000',it.Product_Number), 7) = '0004321'";
var query = objectContext.CreateQuery<Product>(sql).Where(predicate);

Please reply ASAP.Thanks in advance.
Posts: 1
Joined: Wed 02 Mar 2016 12:28

Re: Call LPAD function in Linq Query

Postby Shalex » Wed 02 Mar 2016 17:34

There are public OracleFunctions (includes LPAD), OracleTextFunctions, OracleSpatialFunctions classes. Unfortunately, the documentation for these classes is not available yet. But you can use IntelliSense to find out the list of functions. JIC: refer to the corresponding article in other dotConnect provider (, its first sample could be used with EFv1,4,5,6 via ObjectContext descendant, its second sample could be used with EFv4,5,6 via ObjectContext or DbContext descendant), the list of functions in Oracle is different, but approach and use cases are the same.

Try this code:
Code: Select all
    // var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    var query = objectContext.Products.Where(p => Devart.Data.Oracle.Entity.OracleFunctions.Lpad(p.Product_Number, 7) == "0004321");
    var result = query.ToList();
Devart Team
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for Oracle