none
Problem with the LINQ and Contains RRS feed

  • Question

  • Hi,
    I'm starting using LINQ with my DB and when I make a query with the Contains of a string array, it puts the query parameters sqlDbType to NVarChar, and I have it in Char. That makes problems of not using the Index.
    string[] p = new string[] {"384263810000010004"};
    var aqy = _ProcesosUbicacionesDC.Palets.Where(e => p.Contains(e.SSCCPalet));

    Other consults with out the array takes the parameters as char instead of nVarChar
    var aqy = _ProcesosUbicacionesDC.Palets.Where(e => e.SSCCPalet=="384263810000010004");

    Is this a bug or is there to change the parameters Sqltype to char?
    Thank you
    • Moved by VMazur Tuesday, November 3, 2009 11:31 AM (From:ADO.NET Data Providers)
    Monday, October 26, 2009 5:19 PM

Answers

  • Hi Fiege,

     

    Welcome to MDSN Forums & Thank you for your posting!

     

    I can reproduce this problem at my lab.  As you have said, if the SqlParameter is passed as NVarChar, the index won't be used. (http://support.microsoft.com/default.aspx/kb/271566)  

    I have discussed this issue with the product team.  They have confirmed that it is a product issue.  Such an incorrect type conversion in LINQ to SQL has been fixed in the next version (.NET 4.0).  

    In the current version, a possible workaround would be to define a second index using the conversion functions.  Then you will get two indexes that would be utilized depending on the operations, one on char and the other on nvarchar, getting rid of the scan and using instead the index seek.
     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    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 29, 2009 9:22 AM
    Moderator