none
Prepared Statements / Stored Procs RRS feed

  • Question

  • Have an Entity Framework query that looks something like the following:

    dim MyPerson as Person

    MyPerson = context.Persons _

    .Include("Address") _

    .Include("Phone") _

    .......

    Where PersonID = 1

     

    There are about 30 include clauses in the "real" statement.

     

    Using SQL Profiler, it was determined that the query translated to 7000+ lines of sql statements. The SQL returns

    < 30 rows. The Optimizer shows that it is an extremely efficient query in terms of indexes, etc...

     

    The problem is that the query runs for 10+ seconds. If the query is placed in a proc, it runs in < 1 second. I

    conclude that the 10+ seconds are spent parsing, optimizing, and compiling the query.

     

    So, two questions:

     

    1. Is it possible to get the Entity Framework to use Prepared Statements?

     

    2. If I create the proc from the SQL generated from the query, and run it using the ExecuteStoreQuery command, the

    "...type does not have a corresponding column in the data reader with the same name" error. Is there an easy way

    to generate the equivalent SQL that the query would produce without having column name mismatches?

     

     

     

    Wednesday, October 24, 2012 11:25 PM

Answers

  • Hi Davelll;

    To question 1, Entity Framework does not implement the keyword Prepared and therefore it can not be used.

    To question 2, This error is saying that the returned values from the database does not match one or more fields in the object you are assigning the results of the query to. Make sure all columns being returned has a corresponding field in your object.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by DaveIII Thursday, October 25, 2012 7:10 PM
    Wednesday, October 24, 2012 11:43 PM