0

I'm using EF 6 to model an Oracle database and in turn wrote a simple LINQ to SQL statement to pull data. If I take the query object and do something like:

linqQuery.ToString()

I'll get the SQL but it has placeholders rather than the conditions passed in by my variables. I've searched around but I can't see any solution. The purpose of this use case is to use EF to model my entities, LINQ to SQL to craft the SQL which I then plan to hand off to another process.

Is there anyway of getting my SQL generated to include the variables?

Sulphy
  • 766
  • 2
  • 9
  • 29
  • 2
    Those aren't placeholders. Those are parameters. You should *NOT* hard-code values in a query string, unless you want to deal with SQL injection issues or even simple conversion errors. Imagine what would happen if someone passed `'; DROP TABLE Users;--` as a value – Panagiotis Kanavos Nov 16 '17 at 16:03
  • Maybe a duplicate https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework – wctiger Nov 16 '17 at 16:04
  • LOL, usually people tell others to use placeholders (parameters) and you want to take them out? But seriously why not write the SQL yourself? Translating anything is never very optimal, read any web pages translated by google lately? Notice the language comes out weird. It'll work but it's always going to be sub-optimal. – Rob Nov 16 '17 at 16:06
  • Hi, thanks for the clarification. The use case isn't your normal everyday setup. I have to pass SQL to 3rd party service to process the SQL and so can't use EF directly with the database (it's a long story). I have to craft manual SQL which is why I was hoping to use EF and LINQ to SQL to do the bulk of the work. It's just these parameters that are holding me back. – Sulphy Nov 16 '17 at 16:06
  • Do your LINQ queries use variables or literals for the parameter values? – David Browne - Microsoft Nov 16 '17 at 16:48
  • Hi David, they use variables e.g. 'from c in dbcontext.tblOrders where c.OrderId == passOrderId' – Sulphy Nov 16 '17 at 16:51
  • Typically providers will translate that to SQL parameters (or bind variables in Oracle-speak). And if you use literal values in your LINQ queries, they will be translated to literal values in the SQL queries. – David Browne - Microsoft Nov 16 '17 at 16:54
  • Ahh ok, looks like I'll be going back to hard coding the SQL statements :o) – Sulphy Nov 16 '17 at 17:10

1 Answers1

1

You should be able to use the logging facilities to get the exact complete SQL that is sent to the underlying RDBMS. Yes, it will contain parameters, but the definitions and values of the parameters should be part of that. See https://msdn.microsoft.com/en-us/library/dn469464%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

Dylan Nicholson
  • 1,301
  • 9
  • 23
  • Thank you Dylan. I managed to find the logging option now. Although it appears I have to execute the call in order for the log method to fire? I don't suppose there is a way of getting at it without actually calling the DB? I suspect not, but thought it worth double checking :o) – Sulphy Nov 17 '17 at 08:28
  • 1
    The rest of that article explains how to suppress execution using IDbCommandInterceptor. – Dylan Nicholson Nov 17 '17 at 08:34