locked
Generated SQL contains Unicode literals on non-Unicode Field when using List.Contains() in where clause RRS feed

  • Question

  • EF 4.0 is generating SQL with Unicode literals on non-Unicode fields when using the List.Contains() command in the where clause.

    EF 4.0 has been improved in generating non-Unicode literals in SQL when the field of the entity is non-Unicode and the == operator is used.
    EF 3.5 always generated Unicode literals, so database indices weren't used.

    The improvement is explained on: http://blogs.msdn.com/b/adonet/archive/2010/05/10/improvements-to-generated-sql-in-net-4-0.aspx 
    "5. Provide mechanism for efficient queries on non-Unicode columns"

    The example shown in this blog:

    var query = from p in context.ProductSet
    where p.ProductName == "Chocolade"
    select p.ProductID;

    is resulting in (EF 4.0):

    SELECT
    [Extent1].[ProductID] AS [ProductID]
    FROM [dbo].[Products] AS [Extent1]
    WHERE 'Chocolade' = [Extent1].[ProductName]

    But

    var productList = new List<string>() { "Chocolade", "Milk" }
    var query = from p in context.ProductSet
    where productList.Contains(p.ProductName)
    select p.ProductID;

    is generating Unicode-Literals:

    SELECT
    [Extent1].[ProductID] AS [ProductID]
    FROM [dbo].[Products] AS [Extent1]
    WHERE [Extent1].[ProductName] IN (N'Chocolade',N'Milk')

    So the database will not use an index on ProductName.

    Is there a workaround so that non-Unicode literals will be generated?

    Friday, October 8, 2010 8:24 AM

Answers

  • Hi mixmike,

    I think you could try using query builder method and do something like this:

    var productList = new List<string>() { "Chocolade", "Milk" };      
    string items = string .Empty;
    foreach (string str in productList)
    {
      items += "'" + str + "',";
    }
    
    string temp = "it.ProductName In {" + items.Substring(0, items.Length - 1) + "}";
    var query = from p in context.ProductSet.Where(temp)
          select p.ProductID;
    
    


    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Tuesday, October 19, 2010 7:13 AM
    Monday, October 11, 2010 7:41 AM

All replies

  • Hi mixmike,

    I think you could try using query builder method and do something like this:

    var productList = new List<string>() { "Chocolade", "Milk" };      
    string items = string .Empty;
    foreach (string str in productList)
    {
      items += "'" + str + "',";
    }
    
    string temp = "it.ProductName In {" + items.Substring(0, items.Length - 1) + "}";
    var query = from p in context.ProductSet.Where(temp)
          select p.ProductID;
    
    


    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Tuesday, October 19, 2010 7:13 AM
    Monday, October 11, 2010 7:41 AM
  • Hi mixmike,

    I am writing to follow up the post. Does the above suggestion work ?

    Please feel free to let me know if you need help.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 14, 2010 9:27 AM
  • Hi mixmike,

    I am marking the reply as answer. Please feel free to let me know if you need help.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 19, 2010 7:12 AM
  • Hi Alex,

    your suggested workaround works!

    However, would you please forward this issue to the entity framework team, because I think it is a big performance hit when no index is used.
    Non-Unicode-Columns are used in the most databases and List.Contains() is a standard way in LINQ to generate an IN statement.

    Thanks for your help
    Michael

     

    Thursday, October 28, 2010 3:19 PM
  • Hi Michael,

    Ok, I will forword this issue to the EF team, also you can report it on the Microsoft connect.
    https://connect.microsoft.com/dashboard/info/

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 29, 2010 2:09 AM