none
Replace function in a LINQ to SQL Query RRS feed

  • Question

  • Hi everyone!

     

    How can I write an similar query with LINQ To SQL?

     

    SELECT *
     FROM [STORE].[CRM].[PHONE]
     where REPLACE(phone, ' ', '') like '98880104'
    

     

    I tried using:

    var searchResults = from p in dcdc.PHONEs
    where p.phone1.Replace(" ", string.Empty).Contains(externalUser)
                        select p;
    

     

    But it doesn't seem to do the trick....
    Roman Alifanov(1989)- MCTS-MCAD-MCP-MCT - IT Hero - www.animaonline.blogspot.com
    Tuesday, June 7, 2011 2:02 PM

Answers

  • Hi Animaonline - Roman Alifanov;

    I did a test of your Linq query

    var searchResults = from p in dcdc.PHONEs
              where p.phone1.Replace(" ", string.Empty).Contains(externalUser)
              select p;
    
    and it sent to the SQL server a SQL query as follows :
    
    SELECT [t0].[Column1], [t0].[phone1], ..., [t0].[ColumnN]
    FROM [PHONE] AS [t0]
    WHERE REPLACE([t0].[phone1], @p0, @p1) LIKE @p2          
    
    where 
    @p0 NVarChar(1000) = ' '
    @p1 NVarChar(1000) = ''
    @p2 NVarChar(1000) = '% The string value of externalUser %'
    

    This is the same as your original SQL query. So what is the Linq query NOT doing that the original SQL query is doing?

     

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, June 7, 2011 3:18 PM

All replies

  • Hi Animaonline - Roman Alifanov;

    I did a test of your Linq query

    var searchResults = from p in dcdc.PHONEs
              where p.phone1.Replace(" ", string.Empty).Contains(externalUser)
              select p;
    
    and it sent to the SQL server a SQL query as follows :
    
    SELECT [t0].[Column1], [t0].[phone1], ..., [t0].[ColumnN]
    FROM [PHONE] AS [t0]
    WHERE REPLACE([t0].[phone1], @p0, @p1) LIKE @p2          
    
    where 
    @p0 NVarChar(1000) = ' '
    @p1 NVarChar(1000) = ''
    @p2 NVarChar(1000) = '% The string value of externalUser %'
    

    This is the same as your original SQL query. So what is the Linq query NOT doing that the original SQL query is doing?

     

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, June 7, 2011 3:18 PM
  • Also you can see what the Linq to SQL is sending to the server by doing the following after creating the DataContext but before the first query.

    dcdc.Log = Console.Out;

    Then view the SQL query in the IDE Output window after the program has iterated the results.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, June 7, 2011 3:22 PM
  • Hi Animaonline,

    I think it should do the trick. I tried something similar and the generated query and the results looks fine on my machine.

    In your select you are using phone and in the linq query p.phone1. Are you sure it is right?

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Tuesday, June 7, 2011 3:24 PM
  • Hi Animaonline,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 10, 2011 7:50 AM
    Moderator