Querying a Database with LINQ to SQL Using Visual Basic 2010

Introduction

Vanity is as ill at ease under indifference as tenderness is under a love which it cannot return.
--George Eliot

"Mirror, mirror on the wall, who is the smartest of us all?" "While you Paul are smart tis true, there are millions of guys smarter than you."

Crap, now what?

The mirror of vanity perhaps is a compelling story because the mirror lies. Vanity is a lie. Vanity lets us focus on a couple of traits and focus on perfecting them and comparing them to others. If the mirror is kind then what? There is no real prize simply because Western society values beauty, power, wealth, and intelligence (in that order) if one happens to win the mirror lottery. If the mirror says "you are the smartest" does this mean there is nothing to be learned from others? It is such a silly notion that when stated so matter of factly it sounds ludicrous. If one stares into the mirror long enough one may get the desired answer to the question being asked; it doesn't mean it is the right question or even a meaningful one.

The opposite of vanity is humility. Where vanity metaphorically causes us to look at our own reflection and admire it, humility has us looking outward at the reflection of others. Emerson wrote "every man I meet in some way is my superior". In a broader sense I think what Emerson meant is that each of us is given a set of gifts in varying degrees-looks, intelligence, health, wealth, luck, humor, joy--and focusing intently on the one or two in which we are more fortunate than some others separates us from humanity. That is the lie.

Humility is an acknowledgement that each individual is the sum of all of the gifts in their varying degrees and each person is superior to the next in some way. I try to incorporate some humility in these articles. I am sure some of you may be able to write better than I do. I am sure some of you may be able to improve upon the code samples, and for some this article will contain information you have known about for some time. I accept that. If you respond with refinements to the prose or code then each reader is a benefactor. If something new to any particular reader is conveyed then that person benefits. With humility a community of shared information is created and nurtured; with vanity nothing much really happens.

I get a little philosophical as I get older. Chemically, it may be an increase in estrogen, but that's not important. What is important is that this article covers three pretty specific aspects of LINQ to SQL: retrieving data, updating changes, and managing concurrency conflicts. If you have these subjects mastered then I invite you to read and critique so that others may benefit. If these subjects are new to you then enjoy.

Defining a Typed DataContext and an Entity

LINQ to SQL is dead. Long live the Entity Framework. (April Fools!). Usually when something is dead it is physically taken away, sealed in a container, and sent to the Smithsonian. (If you don't know, the short story is that the Entity Framework is another implementation of LINQ for managing data.) LINQ to SQL is one of several implementations on top of LINQ, and I have no problem using LINQ to SQL whenever the mood strikes. While it may be true that in the resource game, more resources may be given to the Entity Framework right now, one could easily make the argument that LINQ to SQL is a baked cookie for now and it needs fewer resources.

The central object in LINQ to SQL is the DataContext. The DataContext is sort of like an C

To create the demo based on the Northwind database follow these steps:

  1. Starting with a Console Application add a LINQ to SQL item from the Add New Items dialog (see Figure 1)
  2. Expand the Server Explorer and drag a table onto the DBML designer-see Figure 2. (The Northwind Customers table was used for the demo, but any table will do as long as you change the referenced items in the code.)


Figure 1: Add a LINQ to SQL item from the Add New Items dialog.


Figure 2: Drag at least one table onto the DBML design surface in Visual Studio

That's it. You are ready to start coding.

Based on the steps above your project will now have a file named Customers.designer.vb. This is generated code, so leave it alone. It does contain partial classes so if you want to add custom code then create a new file and define the other part of the partial class, which will prevent the IDE from cuisinarting your custom code if you update the DBML.

The designer.vb code will contain a subclass that inherits from DataContext. Think of this as your central management point. It will also contain an entity class for each of the tables dropped onto the DBML designer. From here things get real easy.



Querying a Database with LINQ to SQL Using Visual Basic 2010

Querying with LINQ to SQL

I'd like to make a lot of fuss over how you obtain data with LINQ to SQL, but it is pretty easy. Just create an instance of the DataContext and reference the Table(Of T) property-Customers in this example-and you have access to the customers data. You can bind it to a control, query it with extension methods or LINQ, but one line of code and you have access to the entity data.

If you compare LINQ to SQL with vanilla ADO.NET you will see just how much simpler LINQ to SQL is. To illustrate, most of the code in Listing 1 is expended on putting the data in a formatted output and sending it to the Console. The first line starting with Dim context gets you access, but from there forward it is window dressing.

Module Module1
    Sub Main()

      Dim context As CustomersDataContext = New CustomersDataContext()

      ' view data - can also bind results to controls
      Dim startsWithA = From customer In context.Customers
                        Where customer.CustomerID.StartsWith("A")
                        Select customer

      For Each cust In startsWithA
        Dim props = From prop In cust.GetType().GetProperties()
                    Select New With {.Name = prop.Name, .Value = prop.GetValue(cust, Nothing)}


        Console.WriteLine("----------------------------------")
        Console.WriteLine("Customer: {0}", cust.CompanyName)
        Console.WriteLine("----------------------------------")
        Array.ForEach(props.ToArray(), Sub(p) Console.WriteLine("{0}: {1}", p.Name, p.Value))

      Next
      Console.ReadLine()
  End Sub
End Module

Listing 1: Create a DataContext and you have access to the entity data immediately.

The LINQ to SQL query shows you how to query the DataContext Table(Of T) properties. The query returns customers who's IDs start with 'A'. The For Each loop is just dumping property states of Customer objects. You could just have easily referenced each property using the Customer object and properties-cust.CustomerID, cust.CompanyName, cust.City, cust.Region, etc. I use Reflection for dumping an object's state more out of habit than for any other reason.

When you get an individual entity object just think of it as any other custom object and access the properties in the usual way.

Updating Changes with LINQ to SQL

If you think reading or browsing data is easy then you will love updating entities with LINQ to SQL. To perform updates you can insert Customer objects, delete Customer objects, or change properties of existing Customer objects and then just call SubmitChanges on the DataContext. Change tracking and SQL generation are all managed by the LINQ to SQL infrastructure. On your end just think of Table(Of T) properties as collections, entities as classes, and create, delete, or modify willy nilly. LINQ to SQL takes care of the rest.

Listing 2 shows a new variation on Sub Main that reads some Customers using LINQ to SQL, updates the Region for each Customer and writes the changes back to the database.

Module Module1

  Sub Main()
    Dim context As CustomersDataContext = New CustomersDataContext()
    ' you can use extension methods and Lambdas too
    Dim nullRegions = context.Customers.Where(Function(cust) cust.Region Is Nothing)
    Console.WriteLine("Found: {0}", nullRegions.Count())
    Array.ForEach(nullRegions.ToArray(), Sub(cust) cust.Region = "PK")

    ' saving changes is easy
    context.SubmitChanges()
  End Sub

End Module

Listing 2: Submitting changes with LINQ to SQL.

The code in Listing 2 uses the CustomersDataContext to access the Northwind Customers data. Instead of a LINQ query the second statement uses an extension method (Where) and a Lambda Expression Sub to find Customers who's Region field is empty. (For simple queries extension methods and Lambda expressions work great too.) If you aren't familiar with Lambda Expressions, they are just highly condensed anonymous methods for all intents and purposes. The Array.ForEach statement updates all of the null Regions to "PK" and SubmitChanges is called against the DataContext.

If you want to insert a new Customer then create a Customer object and add it to context.Customers collection. If you want to delete a Customer then delete it from the Customers collection. The LINQ to SQL plumbing will generate the SQL necessary to ensure each operation is pushed through to the database.

If your self-imposed choices are vanilla ADO.NET or LINQ to SQL then use LINQ to SQL; LINQ to SQL is a lot easier than all of the work ADO.NET forces you to do.



Querying a Database with LINQ to SQL Using Visual Basic 2010

Resolving Concurrency Exceptions with LINQ to SQL

I was doing some consulting with Microsoft Consulting Services in 2005. The code name they have for outside consulting consultants was ninja. At the time it seemed kind of flattering. One day having a conversation I asked "does Anders provide all of these teams with UML models to aid in building things, like ADO.NET?" The person I was talking to said let's ask the ADO.NET developers.

I am totally serious when I tell you that this programmer came up from some deep recesses of Microsoft in a bathrobe and bath slippers. I said I have a question about how things are designed. He said, well this is where the rubber hits the road. So I put the question to him. Blank stare. Apparently, my ninja brain pan wasn't speaking ADO.NET-ese. That question seemed as foreign to him as his appearance seemed to me, but I remembered the rubber meeting the road comment.

Simple table updates are pretty easy no matter what technology you are using. Things only start to get funky when transactions, concurrency, and relationships are involved, and any technology isn't going to be worth spit if it doesn't support road-rubbery problems like concurrency. Fortunately, LINQ to SQL handles concurrency in a pretty straight forward manner too.

To manage concurrency issues wrap your SubmitChanges call in an exception handler. Catch ChangeConflictException and if LINQ to SQL detects a concurrency issue this exception will be raised. Resolving concurrency exceptions can be handled through the DataContext as well. Listing 3 simulates two users by using two instances of CustomersDataContext. Each "user" makes similar changes and the exception handler resolves the conflict.

Imports System.Data.Linq

Module Module1

  Sub Main()
    Dim context1 As CustomersDataContext = New CustomersDataContext()
    Dim context2 As CustomersDataContext = New CustomersDataContext()

    Dim regions1 = context1.Customers.Where(Function(cust) cust.Region Is Nothing)
    Dim regions2 = context2.Customers.Where(Function(cust) cust.Region Is Nothing)

    Array.ForEach(regions1.ToArray(), Sub(cust) cust.Region = "PK")
    Array.ForEach(regions2.ToArray(), Sub(cust) cust.Region = "AK")

    Try
context1.SubmitChanges(ConflictMode.ContinueOnConflict)
context2.SubmitChanges(ConflictMode.ContinueOnConflict)
    Catch ex As ChangeConflictException

      ' simulating known DataContext causing problem
      ' and dumping  values
      For Each obj In context2.ChangeConflicts
        For Each mem In obj.MemberConflicts
          Console.WriteLine("original: {0}", mem.OriginalValue)
          Console.WriteLine("database: {0}", mem.DatabaseValue)
          Console.WriteLine("current: {0}", mem.CurrentValue)
        Next
      Next

      context2.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues)
    End Try
    Console.ReadLine()

  End Sub
End Module
Listing 3: Catching concurrency errors and resolving them.


LINQ to SQL uses optimistic concurrency. Optimistic concurrency assumes that multiple transactions can take place without affecting each other. LINQ to SQL will assume updates won't trample each other but compares old, database, and new values and tracks conflicts.

In Listing 3 the code simulates two users through two DataContexts. Each performs similar updates which causes a ChangeConflictException. In reality only one context would be present; in this case we know it is context2 that is going to experience conflicts because it is used last. The catch handler dumps the conflicting values-simulating reporting on them in a GUI-and context2.ChangeConflicts resolves the conflicts by keeping the current values.

You can call SubmitChanges with the ConflictMode enumeration. ConflictMode can be FailOnFirstConflict or ContinueOnConflict. FailOnFirstConflict will raise an exception for the first conflict, and ContinueOnConflict will accumulate each conflict and try to make all updates. DataContext.ChangeConflicts.ResolveAll accepts RefreshMode.KeepChanges, RefreshMode.KeepCurrentValues, or RefreshMode.OverwriteCurrentValues. KeepCurrentValues swaps original values with those retrieved from the database. KeepChanges will keep current values but updates other values from the database values, and OverWriteCurrentValues overrides all current values with the values from the database.

Experiment with the code in Listing 3 trying the three RefreshMode options to get comfortable with how these changes affect concurrency clashes.

Summary

To borrow from Mark Twain, "stories of LINQ to SQL's death have been greatly exaggerated". LINQ is an extensible technology. It has been extended to support objects, XML, DataSets, SQL, the Entity Framework, ActiveDirectory, and Sharepoint, although support for the latter two technologies are unofficial extensions. In a nutshell LINQ to SQL is one way to support SQL database access using LINQ. LINQ to SQL works. It supports common and necessary scenarios like relationships, transactions, creating databases, and concurrency management.

In this article I covered defining and querying a DataContext, performing updates, and concurrency management. The examples also demonstrated that extension methods and Lambda expressions can be used as well as LINQ. LINQ to SQL is a lot easier to use than ADO.NET. If your choices are between the two approaches-vanilla ADO.NET or LINQ to SQL-use LINQ to SQL. I certainly wouldn't avoid LINQ to SQL because someone said it was "dead". Done for now, maybe. Dead, I don't think so.

Related Articles





About the Author

Paul Kimmel

Paul Kimmel is the VB Today columnist for CodeGuru and has written several books on object-oriented programming and .NET. Check out his upcoming book Professional DevExpress ASP.NET Controls (from Wiley) now available on Amazon.com and fine bookstores everywhere. Look for his upcoming book Teach Yourself the ADO.NET Entity Framework in 24 Hours (from Sams). You may contact him for technology questions at pkimmel@softconcepts .com. Paul Kimmel is a Technical Evangelist for Developer Express, Inc, and you can ask him about Developer Express at paulk@devexpress.com and read his DX blog at http:// community.devexpress.com/blogs/paulk.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds