title: EF6 EntityCommand get parameters and performance
body: <p>In a EF6 context, I want to filter multiples entities by dynamic fields/attributes. I'm new to EF and my perspective is very corrupted by T-SQL and stored procedures and dynamic SQL queries.</p>
  
  <p>For instance in a ERP environment, the user can filter by a code, and the system should return the:</p>
  
  <ul>
  <li>Customer with CustomerID = code</li>
  <li>Supplier with SupplierID = code</li>
  <li>User with UserID = code</li>
  <li>Orders with CustomerID/SupplierID = code</li>
  <li>etc.</li>
  </ul>
  
  <p>But can not only be a code, can multiples concepts to filter for: a name, a city, a date, ... and may all not apply to all the entities.</p>
  
  <p>So since each entity has different attributes names to refer to that "code" concept, I've thought that the best solution is to use EntityCommand instead of LinQ.</p>
  
  <p>And the code should look something like:</p>
  
  <pre><code>// Create a query that takes two parameters.
  string eSqlCustomerQuery =
      @"SELECT VALUE Contact FROM AdventureWorksEntities.Customer AS Customer";
  string eSqlCustomerQuery =
      @"SELECT VALUE Contact FROM AdventureWorksEntities.Customer AS Customer";
  // Create a list of parameters
  var param = new SortedList&lt;string, EntityParameter&gt;();
  
  // for each clauses add a pamater and build the query command dynamically.
  if(!code)
  {
      eSqlCustomerQuery += "WHERE Customer.CustomerID = @CODE";
      eSqlSupplierQuery += "WHERE Supplier.SupplierID = @CODE";
      //... more entities to 
      param["CODE"].ParameterName = "CODE";
      param["CODE"].Value = code;
  }
  // more parameters here...
  
  using (EntityConnection conn =
      new EntityConnection("name=AdventureWorksEntities"))
  {
      conn.Open();
      using (EntityCommand cmd = new EntityCommand(eSqlCustomerQuery, conn))
      {
          cmd.Parameters.Add(param["CODE"]);
          cmd.Parameters.Add(param["DATE"]);
          // more parameters here...
      }
      // the same for each query...
      // ...
      // run all the queries ...
      // ...
      // Etc.
      conn.Close();
  }
  </code></pre>
  
  <p>My questions are 3:</p>
  
  <ul>
  <li><strong>At the time I'm doing <code>cmd = new EntityCommand(eSqlCustomerQuery, conn)</code> can I use something like the <code>System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);</code>?</strong></li>
  <li><strong>Since this dynamic query it's so dynamic that it can be cached or have a reusable execution plan, how can it be improved?</strong></li>
  <li><strong>Is it possible to do it with LinQ in a cleaner way?</strong></li>
  </ul>