LINQ to SQL objects and doing inmemory queries over them

This is extremely easy and for most of you probably so obvious that you’ll start spamming me that this is time spend better on other things. But it has helped others and maybe it can help some of my visitors. I’ve used it when migrating data between two databases, where the data models were nothing alike.

When you’re using LINQ to SQL and are creating new objects that are defined in your LINQ to SQL classes (or .dbml) you can still query over them before committing them to the database. Here’s an example.

// Create root collection that we can query in our application.
List<customer> customers = new List<customer>();  

Customer customer = new Customer();
customer.CompanyName = "Class-A";
customer.ContactName = "Dennis van der Stelt";

// Add this customer to our collection.

Product product = new Product();
product.ProductName = "i pwn n00bs t-shirt";

Order_Detail orderDetails = new Order_Detail();
orderDetails.Product = product;

Order order = new Order();
order.Customer = customer;

// Select all ordered products by Dennis
var query = from c in customers
            from o in c.Orders
            from d in o.Order_Details
            select d.Product;

As you can see I never used db.SubmitChanges() so it’s not in the database yet. I did however query over the in-memory collection(s) I’ve build. I’ve created a root collection customers which I’ll use in my application to start querying. You can pass around the list of customers if you’d like.

With this example I can go through all customers in one database and pass every product. Imagine that Alex Thissen had ordered the same t-shirt (which he didn’t btw, he ordered the übergamer t-shirt from the n00b store 😉 I’d be able to query the created products, see that it was already created and make a reference to that product, instead of (again) creating a new one.

Hope that last paragraph makes sense.