none
LINQ doesn't work, foreach does

    Question

  • I am experiencing a strange issue. My code works as expected if I use foreach but if I change it a LINQ expression, it doesn't.

    Here's the code that works as expected. I see one element in the neededFireFlowCounties list:

    List<NeededFireFlowCounty> neededFireFlowCounties = new List<NeededFireFlowCounty>();
                
    foreach (var neededFireFlowCounty in _cmciContext.NeededFireFlowCounties)
    {
       if (neededFireFlowCounty.County.Trim().Equals(address.County.Trim(), StringComparison.OrdinalIgnoreCase) &&
           neededFireFlowCounty.State.StateName.Trim().Equals(address.State.Trim(), StringComparison.OrdinalIgnoreCase))
      {                            
         neededFireFlowCounties.Add(neededFireFlowCounty);
      }
    }
    
    if (neededFireFlowCounties.Any())
    {
      ....
      ....
    }


    Here's the code that doesn't work as expected. I see 0 elements in the neededFireFlowCounties list:

    List<NeededFireFlowCounty> neededFireFlowCounties =

    _cmciContext.NeededFireFlowCounties.Where(neededFireFlowCounty =>

    neededFireFlowCounty.County.Trim().Equals(address.County.Trim(), StringComparison.OrdinalIgnoreCase)

    && neededFireFlowCounty.State.StateName.Trim().Equals(address.State.Trim(), StringComparison.OrdinalIgnoreCase)).ToList(); if (neededFireFlowCounties.Any()) { }

    I am using Effort to test my data access layer. I am not sure if this difference in behavior has something to do with using Effort.

    Thanks for your help.

    Manu

    • Edited by mnu_m Wednesday, November 13, 2013 6:44 PM
    • Moved by Lilia gong - MSFT Monday, November 18, 2013 3:28 AM
    Wednesday, November 13, 2013 6:42 PM

Answers

  • Hi Fred,

    Thanks for looking into this but I think I figured the issue out.

    I was thinking that the LINQ version and the foreach version were equivalent but I missed a key difference. In the LINQ version the filtering is happening at the SQL level (in a where clause) but in the foreach version, the filtering is happening in .NET.

    But that still didn’t explain why the LINQ version didn’t work. I did some searching and found that when using Entity Framework and LINQ, since the filtering happens on the database side, the database defines how to do the string comparison and not the code. So basically when I use StringComparison.OrdinalIgnoreCase in a LINQ query, it’s totally ignored and the string comparison being case sensitive or insensitive depends on how the database is defined (COLLATION option). [The reason your code worked must be because in your case this COLLATION option ignores the case for string comparison. To validate this, try removing the StringComparison.OrdinalIgnoreCase and your code should still work].

    This is exactly what was happening when EF generated a query for Effort, it didn’t pass in the special case insensitive comparison option and hence the comparison failed resulting into 0 rows.

    If the above was true then removing the StringComparison.OrdinalIgnoreCase from the LINQ query and using ToUpper() should work so I tried that and sure enough the following worked:

    List<NeededFireFlowCounty> neededFireFlowCounties =

                   _cmciContext.NeededFireFlowCounties.Where(

    c => c.County.Trim().ToUpper().Equals(address.County.Trim().ToUpper())).ToList();

    These are the articles that help me understand this issue:

    https://effort.codeplex.com/discussions/450947

    http://stackoverflow.com/questions/5080727/string-equals-not-working-as-intended







    Tuesday, November 19, 2013 12:09 AM

All replies

  • Why don't you try using a different Linq query other than the one you are using? There is more than one way to skin the cat. 
    Wednesday, November 13, 2013 8:09 PM
  • Hi,

    Thanks for your posting.

    From your description, this issue is related to LINQ Expression, I think that Data Platform is more suitable for it. So for better help, I move it to Data Plat form forum.

    Data Plat form forum:  http://social.msdn.microsoft.com/Forums/en-US/home?category=dataplatformdev

    Hope these help.


    Lilia Gong <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, November 18, 2013 3:27 AM
  • Hello mnu_m,

    Being strange for this, because I made a test using codes which is similar with yours.

    However, it worked fine.

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    List<Order> ordeList = db.Orders.Where(o => o.OrderCode.Equals("O001", StringComparison.OrdinalIgnoreCase) && o.OrderName.Equals("o001", StringComparison.OrdinalIgnoreCase)).ToList();
    
                }
    

    The result:

    Please check whether the data is correct. And you can share the table structure and the regarding data so that we could do a test on it.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 18, 2013 8:50 AM
    Moderator
  • Hi Fred,

    Thanks for looking into this but I think I figured the issue out.

    I was thinking that the LINQ version and the foreach version were equivalent but I missed a key difference. In the LINQ version the filtering is happening at the SQL level (in a where clause) but in the foreach version, the filtering is happening in .NET.

    But that still didn’t explain why the LINQ version didn’t work. I did some searching and found that when using Entity Framework and LINQ, since the filtering happens on the database side, the database defines how to do the string comparison and not the code. So basically when I use StringComparison.OrdinalIgnoreCase in a LINQ query, it’s totally ignored and the string comparison being case sensitive or insensitive depends on how the database is defined (COLLATION option). [The reason your code worked must be because in your case this COLLATION option ignores the case for string comparison. To validate this, try removing the StringComparison.OrdinalIgnoreCase and your code should still work].

    This is exactly what was happening when EF generated a query for Effort, it didn’t pass in the special case insensitive comparison option and hence the comparison failed resulting into 0 rows.

    If the above was true then removing the StringComparison.OrdinalIgnoreCase from the LINQ query and using ToUpper() should work so I tried that and sure enough the following worked:

    List<NeededFireFlowCounty> neededFireFlowCounties =

                   _cmciContext.NeededFireFlowCounties.Where(

    c => c.County.Trim().ToUpper().Equals(address.County.Trim().ToUpper())).ToList();

    These are the articles that help me understand this issue:

    https://effort.codeplex.com/discussions/450947

    http://stackoverflow.com/questions/5080727/string-equals-not-working-as-intended







    Tuesday, November 19, 2013 12:09 AM
  • Glad to hear that you found a solution and share it with us.

    Cheers.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 22, 2013 8:54 AM
    Moderator