none
Inefficient SQL LIKE query when using StartsWith RRS feed

  • Question

  • I'm on .Net 4.0 and SQL Server 2008.

    I have a simple query that goes like this:

     “…FirstName.StartsWith ("Mary") == true ...”

    This, however, generates this SQL:

    WHERE 1 = (CASE WHEN ([Extent1].[FirstName] LIKE N'Mary%') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[FirstName] LIKE N'Mary%')) THEN cast(0 as bit) END)

    So I end up with a NOT LIKE query which can't use an index on PrimaryFirstName. There's also a few CASE ... WHEN clauses and a few casts. All I need it to generate is:

    WHERE[Extent1].[FirstName] LIKE N'Mary%'

    (I'm not using change tracking. In fact I'm not retrieving entities, but specific fields.)

    I've come across a few posts saying that this is what we get with L2E, and that we should move to L2S instead if we want efficient queries. At this stage of my project, that is quite some work; so before I do that I thought I'd check one last time whether there's any way out in the EF world itself.

    Thanks.

    -Venkatesh

    Tuesday, August 16, 2011 5:36 PM

Answers

  • Hi RealMSLover;

    About the only way around that is to use the ExecuteStoreQuery method. This can be done as follows, the snippet below is using the Microsoft Northwinds sample database:

    // List to hold results of the query
    List<ReturnedData> results;
    
    // Instandsiate the ObjectContext
    using( var ctx = new NorthwindEntities( ) )
    {
      // The query string to send to the SQL server using paramters to protect from SQL injection
      string cmdStr = "SELECT ContactID, ContactName, CompanyName FROM Contacts WHERE ContactName LIKE @FilterString";
      // Create the paramter object to send with the SQL command
      DbParameter[] param = { new SqlParameter( "@FilterString", SqlDbType.NVarChar, 30 ) };
      // Set the parameter value
      param[ 0 ].Value = "Ca%";
      // Execute the SQL command through the ObjectContext
      results = ctx.ExecuteStoreQuery<ReturnedData>( cmdStr, param ).ToList( );
    }
    
    // This class need to have all the fields that the resultset will have
    // each object of the results set will be meterialized by the ExecuteStoreQuery Command  
    public class ReturnedData           
    {                     
      public int ContactID { get; set; }   
      public string ContactName { get; set; }
      public string CompanyName { get; set; }
    }            
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Larcolais Gong Wednesday, August 24, 2011 9:29 AM
    Tuesday, August 16, 2011 7:55 PM