This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello, I have quite strange issue (strange for me) and I had no idea how to name subject of this thread :). Scenario: I want to fetch whole table directly to one list so I can fast search through it. I'm using this command to access this table: List<WebsiteProduct> productSearchDownloaded = uow.WebsiteProducts.ToList(); later in some part of my code (it depends which type of file names user want to search) I use: productSearch = productSearchDownloaded.Where(productItem => productItem.Name.Contains(String.Format("MID{0}", FileHelper.GetFileName(photoDirs[i]).ToLower().Replace(".jpg", "")))).ToList(); which as you can see it looks for some string which should be inside Name variable and now there is strange thing. This query returns always count equal to 0 but when I remove ".ToList()" from this command: uow.WebsiteProducts.ToList(); and leave it only (and change type of this variable): uow.WebsiteProducts; then my second linq query returns proper row in table. Any idea how this is happening? Thanks |
|
|
When you write: productSearchDownloaded = uow.WebsiteProducts; then productSearchDownloaded is a pending database query (an IQueryable). So when you then write: productSearchDownloaded.Where(...).ToList(); the Where clause gets translated into SQL and run in the database. When you write: productSearchDownloaded = uow.WebsiteProducts.ToList(); then productSearchDownloaded is a client-side list of items. So now when you write: productSearchDownloaded.Where(...).ToList(); the Where clause is interpreted as a .NET method and run using the CLR. Consequently, the semantics of the Where clause can end up being slightly different. In the client-side case, String.Contains is using the default CLR string comparer, which is ordinal case-sensitive. For example, if the argument is "MIDabc", then a product named "XMIDabcX" will match, but a product named "XmidABCX" will not. In the server-side case, all bets are off. String.Contains is being translated into a SQL LIKE operator, and the LIKE matching will be determined by the database engine and the culture/collation settings of the table or column. For example, if the database does case-insensitive comparisons, or the table/column is set to a case-insensitive collation, and the argument is "MIDabc", then both "XMIDabcX" and "XmidABCX" will match. Therefore I suspect that your WebsiteProduct names do not have the same case as your "MID{0}" expression, and that your database is set up to be case-insensitive. So when you remove the ToList(), you get matches because the incorrect casing doesn't matter to the database; but when you include the ToList(), you get nothing because the .NET Contains method requires the cases to match. To get around this, use a String method that takes a StringComparer or StringComparison to force the comparison to be case-insensitive, e.g. .Where(p => p.Name.IndexOf(..., StringComparison.OrdinalIgnoreCase) >= 0) |
|
|
Thank you very much. Your solution for my problem has worked. |
|