0

I have an issue that's causing me a lot of headache. I've already googled a solution some days ago, but I didn't found it yet, if one of you guys could help me, it would be great!

I have this method to take the SQL statement for me:

private static string GetClause<T>(IQueryable<T> clause) where T : BaseEntity
{
    string snippet = "FROM [dbo].[";

    string sql = clause.ToString();
    string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));

    sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
    sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");

    return sqlFirstPart;
}

But this doesn't work when I try to use some filter (e.g. where), cause it returns me some ""@p_linq_{0}"." instead of the values. I know these are the parameters of my query, but where are they stored?

I use this for batch delete purposes on the EF5 Code First.

jeb
  • 78,592
  • 17
  • 171
  • 225
Carlos Moreira
  • 38
  • 7

1 Answers1

0

If you create a query with a param like this:

var name = "xxx";
var query = ctx.Persons.Where(x => x.FirstName == name);

Then you can get the sql statement and parameters like this:

var oq = ((ObjectQuery<Person>)query);
var trace = oq.ToTraceString();    
var p = oq.Parameters;

Use the param name "p_linq_{0}" without the @ as an index on p, p["p_linq_{0}"].

Erwin
  • 3,060
  • 26
  • 24
  • I've already tried this but the Parameters property came to me as an empty collection. I guess it is because this solution you posted just do the work in LinqToSQL (the solutions I've seen so far were all LinqToSQL). It didn't solve the problem to me at all. I was in a hurry so by now I've just passed the values I needed as another argument of this methods. But I really want to know how to make this work without passing the methods twice (one to the IQueryable and another to the method). – Carlos Moreira Aug 22 '13 at 18:04
  • @CarlosMoreira I noticed that when I change the sequence of commands or forget the ToTraceString my parameters are empty too, try using the exact same sequence as in my example and look in the trace field to see the correct name of your parameter name. – Erwin Aug 23 '13 at 06:14
  • It gave me an exception during cast: _Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery1[MyType]' to type 'System.Data.Objects.ObjectQuery1[MyType]'_. There's something more that I should do before casting? – Carlos Moreira Aug 23 '13 at 14:08
  • I'm using db first approach, not code first, I think I found another SO post that deals with your casting problem: http://stackoverflow.com/questions/7208709/entity-framework-4-1-unable-to-cast-from-dbquery-to-objectquery – Erwin Aug 26 '13 at 06:35
  • I'll do it like this post you sent me. I'll tell you if it will work the way I need (probable will). Thanks, @Erwin! – Carlos Moreira Aug 28 '13 at 17:19