You may switch from IQueryable to IEnumerable midway through a chain!

In following this and repurposing some of its code, I crafted this test which passes, but would throw a SQL exception if .AsEnumerable() were to be cut from the script as the whole of the casting-to-a-list would be of an IQueryable implementation thus staging an inability to cast the line immediately below .AsEnumerable() to SQL where my StateHelper class would make no sense. To solve the problem: part of our chain is of IQueryable and, later, part of our chain is of IEnumerable. If not for the one objectionable use of StateHelper, the whole thing could be of IQueryable.

using System.Collections.Generic;
using System.Data.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Whatever.Models;
using System.Linq;
namespace Whatever.Tests
{
   [TestClass]
   public class IQueryableTest
   {
      [TestMethod]
      public void DataTest()
      {
         string Ok = @"server=.\sqlexpress;database=OK;Integrated Security=true;";
         DataContext dataContext = new DataContext(Ok);
         Table<Contact> contacts = dataContext.GetTable<Contact>();
         List<Address> list = contacts.Where(c => c.Email.EndsWith("example.com"))
            .OrderBy(c => c.Name)
            .AsEnumerable()
            .Where(c => StateHelper.GetStateForCity(c.City) != "Florida")
            .Select(c => new Address
            {
               Street = c.Street,
               City = c.City,
               State = StateHelper.GetStateForCity(c.City)
            }).ToList();
         Assert.AreEqual(list.Count, 2);
         Assert.AreEqual(list[0].Street, "500 East Stassney");
         Assert.AreEqual(list[0].City, "Austin");
         Assert.AreEqual(list[0].State, "Texas");
         Assert.AreEqual(list[1].Street, "1515 Ocean");
         Assert.AreEqual(list[1].City, "Santa Cruz");
         Assert.AreEqual(list[1].State, "California");
      }
   }
}

 
 

This is another nugget of wisdom from Chapter 8 of C# 4.0 in a Nutshell! Chapter 8 also goes into LINQ to SQL which is less interesting. I suppose I should note the change I made to the Contact class however. It is like so:

using System;
using System.Data.Linq.Mapping;
namespace Whatever.Models
{
   [Table(Name="Contacts")]
   public class Contact
   {
      [Column(IsPrimaryKey=true)]
      public Guid Id;
      [Column]
      public string Name;
      [Column]
      public string Phone;
      [Column]
      public string Email;
      [Column]
      public string Street;
      [Column]
      public string City;
   }
}

 
 

I made the Contacts table like so:

BEGIN TRANSACTION
GO
CREATE TABLE Contacts
   (
   Id uniqueidentifier NOT NULL DEFAULT newid(),
   Name varchar(50) NOT NULL,
   Phone varchar(50) NOT NULL,
   Email varchar(50) NOT NULL,
   Street varchar(50) NOT NULL,
   City varchar(50) NOT NULL
   )
GO
ALTER TABLE Contacts ADD CONSTRAINT
   PK_Whatever PRIMARY KEY CLUSTERED
   (
   Id
   ) WITH( STATISTICS_NORECOMPUTE = OFF,
      IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
GO
INSERT INTO Contacts (Name, Phone, Email, Street, City)
Values ('Johnny', '(512) 419-8788', 'pil@example.com', '500 East Stassney', 'Austin')
INSERT INTO Contacts (Name, Phone, Email, Street, City)
Values ('Sid', '(928) 425-629', 'fevercheese@gmail.com', '1 South Broad', 'Globe')
INSERT INTO Contacts (Name, Phone, Email, Street, City)
Values ('Steve', '(561) 790-0349', 'metal@example.com', '5055 Club', 'West Palm Beach')
INSERT INTO Contacts (Name, Phone, Email, Street, City)
Values ('Paul', '(831) 475-8325', 'cook@example.com', '1515 Ocean', 'Santa Cruz')
COMMIT