1

I am currently using the Entity Framework with underlying Oracle connection.

Sometimes, there are Oracle exception returned (foreign key violated, EF-generated SQL not compatible with some old Oracle version, etc.)

Although I can see the high level message, it is difficult to tell what is exactly happening without knowing the error SQL statement passing to the DB. For example, when it is complaining foreign key, I have no idea which ID it is referring to.

Checked the OracleException/DbException, I cannot find any where I can get the Sql statement and the binded parameters of that statetment.

The only solution I can think of is to log the EF generated statement explicitly each time before its execution by my own.

Is there any elegant way to do so?

================================================

Update:

How do I view the SQL generated by the Entity Framework? is the same way (The only solution I can think of is to log the EF generated statement explicitly each time before its execution by my own.) as mentioned in the original question. I do not want to do so because I need to get the generated SQLs each time and log them explicitly.

For example, normally we get the value in the following way:

var shop = (
    from shop in shops
    where shop.ID = xxx
    select shop.Name
).FirstOrDefault();

If I need to get the query string and the bounded parameter(s) like below, it is quite tedious.

IQeuryable shop = (
    from shop in shops
    where shop.ID = xxx
    select shop.Name
)
Log("Sql : " + shop.ToQueryString());
Log("Parameter ID: " + xxx);

It works, but not elegant.

The question is how can I get those info from OracleException (or somewhere else) without logging them explicitly in advance so that when I get the Exception, I can log them directly based on the Exception. Below is an example what I want to do (Those properties do not exist in the Exception object)

try
{
    // EF to Oracle query
}
catch (OracleExcetpion ex)
{
    Log("SQL: " + ex.SQL.ToQueryString());
    foreach (var boundParameter in ex.SQL.BoundParameters)
    {
        Log("Parameter " + boundParameter.Name + ": " + boundParameter.Value.ToString());
    }
}
kzfid
  • 688
  • 3
  • 10
  • 17
  • Does this answer your question? [How do I view the SQL generated by the Entity Framework?](https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) – Lei Yang Apr 08 '22 at 08:07
  • Actually it is exactly the same solution I mentioned in the question. That is, get the generated sql and log it explicitly before execution. So, I guess there is no other better way to do so. Thanks. – kzfid Apr 08 '22 at 10:14
  • how about [catch DbEntityValidationException](https://stackoverflow.com/a/16347943/1518100) – Lei Yang Apr 11 '22 at 02:00
  • @LeiYang Thanks for your reply. Not exactly what I want. For example, when I try to insert a record to child table without a record at parent table, which violates the foreign key constraint. The exception thrown is OracleException instead of DbEntityValidationException. DbEntityValidationException (from its name) seems a exception for EF only but not the exception from the underlying DB. – kzfid Apr 11 '22 at 03:40
  • did you try it? – Lei Yang Apr 11 '22 at 03:45
  • Yes. In the foreign key case mentioned, the Oracle.ManagedDataAccess.Client.OracleException (instead of DbEntityValidationException) is thrown with ex.Message = "ORA-02291: integrity constraint (xxx.xxx) violated - parent key not found" – kzfid Apr 11 '22 at 05:47
  • isn't it clear enough? – Lei Yang Apr 11 '22 at 05:48
  • Not 100% clear, it just tells the foreign key constraint is violated without telling which parent ID is not found. (That is why I need to know the bounded parameter in this case). – kzfid Apr 11 '22 at 05:56
  • what oracle client do you use, i know at least oracle and mircrosoft both has its own. did you try both? – Lei Yang Apr 11 '22 at 07:26

0 Answers0