What's the difference between these LINQ queries ?

Question

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!

Answer 1

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.

Answer 2

The real issue here is the use of IEnumerable<T>, which breaks "composition" of queries; this has two effects:

• you are reading all (or at least, more than you need) of your table each time, even if you ask for a single row
• you are running LINQ-to-Objects rules, so case-sensitivity applies

Instead, you want to be using IQueryable<T> inside your data layer, allowing you to combine multiple queries with additional Where, OrderBy, Skip, Take, etc as needed and have it build the TSQL to match (and use your db's case-sensitivity rules).

Is there a more efficient way to write these "helper" type classes ?

For more efficient (less code to debug, doesn't stream the entire table, better use of the identity-map to short-circuit additional lookups (via FirstOrDefault etc)):

public IEnumerable<SystemSalesTaxList> Get_SystemSalesTaxList()
{
    return db.SystemSalesTaxLists;
}

public SystemSalesTaxList Get_SystemSalesTaxList(string salesTaxID)
{
    return db.SystemSalesTaxLists.FirstOrDefault(s => s.SalesTaxID==salesTaxID);
}

public SystemSalesTaxList Get_SystemSalesTaxListByZipCode(string zipCode)
{
    return db.SystemSalesTaxLists.FirstOrDefault(s => s.ZipCode == zipCode);
}