What's the difference between these LINQ queries?
asp.net, c#, linq, sql
Solution
This is probably down to the different ways LINQ handles `IEnumerable<T>` and `IQueryable<T>`.
You have declared `Get_SystemSalesTaxList` as returning `IEnumerable<SystemSalesTaxList>`. That means that when, in your first code sample, you apply the Where operator to the results of Get_SystemSalesTaxList, it gets resolved to the `Enumerable.Where` extension method. (Note that what matters is the declared type. Yes, at runtime Get_SystemSalesTaxList is returning an `IQueryable<SystemSalesTaxList>`, but its declared type -- what the compiler sees -- is `IEnumerable<SystemSalesTaxList>`.) `Enumerable.Where` runs the specified .NET predicate over the target sequence. In this case, it iterates over all the SystemSalesTaxList objects returned by Get_SystemSalesTaxList, yielding the ones where the ZipCode property equals the specified zip code string (using the .NET String `==` operator).
But in your last code sample, you apply the Where operator to `db.SystemSalesTaxList`, which is declared as being of type `IQueryable<SystemSalesTaxList>`. So the Where operator in that sample gets resolved to `Queryable.Where`, which translates the specified predicate expression to SQL and runs it on the database.
So what's different in the zip code methods is that the first one runs the C# `s.ZipCode == strZipCode` test in .NET, and the second translates that into a SQL query `WHERE ZipCode = 'CA 12345'` (parameterised SQL really but you get the idea). Why do these give different results? Hard to be sure, but the C# `==` predicate is case-sensitive, and depending on your collation settings the SQL may or may not be case-sensitive. So my suspicion is that `strZipCode` doesn't match the database zip codes in case, but in the second version SQL Server collation is smoothing this over.
The best solution is probably to change the declaration of Get_SystemSalesTaxList to return `IQueryable<SystemSalesTaxList>`. The major benefit of this is that it means queries built on Get_SystemSalesTaxList will be executed database side. At the moment, your methods are pulling back EVERYTHING in the database table and filtering it client side. Changing the declaration will get your queries translated to SQL and they will run much more efficiently, and hopefully will solve your zip code issue into the bargain.
Problem
I use LINQ-SQL as my DAL, I then have a project called DB which acts as my BLL. Various applications then access the BLL to read / write data from the SQL Database. I have these methods in my BLL for one particular table: ``` public IEnumerable<SystemSalesTaxList> Get_SystemSalesTaxList() { return from s in db.SystemSalesTaxLists select s; } public SystemSalesTaxList Get_SystemSalesTaxList(string strSalesTaxID) { return Get_SystemSalesTaxList().Where(s => s.SalesTaxID == strSalesTaxID).FirstOrDefault(); } public SystemSalesTaxList Get_SystemSalesTaxListByZipCode(string strZipCode) { return Get_SystemSalesTaxList().Where(s => s.ZipCode == strZipCode).FirstOrDefault(); } ``` All pretty straight forward I thought. `Get_SystemSalesTaxListByZipCode` is always returning a null value though, even when it has a ZIP Code that exists in that table. If I write the method like this, it returns the row I want: ``` public SystemSalesTaxList Get_SystemSalesTaxListByZipCode(string strZipCode) { var salesTax = from s in db.SystemSalesTaxLists where s.ZipCode == strZipCode select s; return salesTax.FirstOrDefault(); } ``` Why does the other method not return the same, as the query should be identical ? Note that, the overloaded `Get_SystemSalesTaxList(string strSalesTaxID)` returns a record just fine when I give it a valid `SalesTaxID`. Is there a more efficient way to write these "helper" type classes ? Thanks!