Wooley's LINQ Wonderings

Insights and observations regarding LINQ

  • More on VS 2008 breaking changes

    In addition to the items I mentioned in my previous Beta 2 - RTM breaking change list, I found a link on the VS 2008 samples page that a whitepaper has been issued on this.

    In addition, there is a whitepaper specific to breaking changes between VB 2005 and VB 2008 available here.

    Technorati tags: 


    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Projecting XML from LINQ to SQL

    Among the new cool features in Visual Studio 2008, one of the best may be the XML Literal support with VB 9 and LINQ. In my last post, I mentioned some changing features from the Beta to RTM. One that could easily be overlooked is a change to the way LINQ to SQL can now directly project into XML literals.

    Through the Beta cycle, there was an issue with projecting XML elements directly from a LINQ to SQL query. If you haven't seen LINQ to SQL with XML, here's a code sample that explains what I'm referring to:

    Dim dc As New LinqBlogDataContext
    'Formulate the Query to get the last 10 blog posts
    Dim query = (From p In dc.PostItems _
    Order By p.PublicationDate Descending _
    Take 10 _
    Select p).ToArray

    'Create a root Site node with 10 child "item" nodes.
    'Each node will be filled in through a LINQ query
    Dim fooShort = <site>
    <%= From p In query _
    Select <item>
    <title><%= p.Title %></title>
    <link>http://ThinqLinq.com/Default.aspx?Postid=<%= p.Id %></link>
    <pubDate><%= p.PublicationDate %></pubDate>
    <guid isPermaLink="false">42f563c8-34ea-4d01-bfe1-2047c2222a74:<%
    p.Id %></guid>
    <description><%= p.Description %></description>
    </item> %>

    In this code, I'm performing two queries. The first one sets up the LINQ to SQL query and pre-fetches the results into an Array. In the beta builds, if we didn't include the pre-fetching ToArray, the second query which projects the results of the first into individual <item> nodes. What is the difference between these queries? The first query uses LINQ to SQL and projects results directly from the database. Because we pre-fetch the results into an array of objects, the resulting query only uses LINQ to Objects rather than the direct LINQ to SQL implementation.

    With the final RTM of Visual Studio, we no longer need to pre-fetch the results from the query. Instead, we can directly project our desired XML from the select statement without needing the intermediary step. Here is the revised code. Notice, we can now perform the same result with a single LINQ query rather than two.

    Dim fooNew = <site>
    <%= From p In dc.PostItems _
                       Order By p.PublicationDate Descending _
    Take 10 _
    Select <item>
    <title><%= p.Title %></title>
    <link>http://ThinqLinq.com/Default.aspx?Postid=<%= p.Id %></link>
    <pubDate><%= p.PublicationDate %></pubDate>
    <guid isPermaLink="false">42f563c8-34ea-4d01-bfe1-2047c2222a74:<%= p.Id %></guid>
    <description><%= p.Description %></description>
    </item> %>

    The result is more concise. You may find you want to continue separating your query definition from your XML creation in order to improve maintainability. If this is the case, simply keep the first code sample and remove the call to .ToArray. Because LINQ to SQL is composable, you can separate the queries into two code sets. When the query is evaluated, the two expressions will be combined into a single query to the database and the projection will continue to work.

    Enjoy working with VB 9 and XML. In my opinion it is one of the killer features of Visual Studio 2008. If you give it a try, I think you might find the same.

    Technorati tags: ,

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Visual Studio 2008 (Orcas) Changes from Beta 2 to RTM

    I've just updated the ThinqLinq proof of concept site for the Visual Studio 2008 release that came out today. If you're following the sample application, or are looking for a sample VB 9 implementation of LINQ in a web site, check out the download at http://devauthority.com/files/13/jwooley/entry101097.aspx. In case you are interested, here are the changes that were necessary to move from Beta 2 to the RTM. (The first two items are repeats from my post earlier today).

  • Open the DBML file as XML and change the UTF encoding from 16 to 8. Otherwise the compiler will complain about the DBML file's format.
  • Replace the Add methods with InsertOnSubmit. Likewise, change Remove to DeleteOnSubmit. You may be able to do a global search and replace on this, but be careful not to make changes to any IList object implementations, only LINQ to SQL ones.
  • SyndicationFeed.Load removed the option to pass in a URI. Instead, I used SyndicationFeed.Load(New System.Xml.XmlTextReader(UrlString)).
  • The SyndicationFeed's Item PublishDate property is changed to the new DateTimeOffset type rather than the simpler DateTime. As a result, get the date from the PublishDate.Date property.
  • When projecting XML elements as part of a LINQ to SQL query, you no longer need to pre-fetch the results into an array. I'll make a separate post on this item.
  • That's all I've found so far. I've already updated both the ThinqLinq site and the samples for the upcoming LINQ in Action book. I'm sure I've missed something, but so far, the upgrade is relatively easy this time around. The IDE does appear to be running faster as well.

    Technorati tags: ,

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • VS 2008 RTM and the .NET Framework 3.5 Shipped

    It's offical, Soma annouced on his blog that the 3.5 .Net Framework along with Visual Studio 2008 have shipped. I have been excited by the things coming with this release since PDC 2005 and am glad to finally work the the official bits. I am aware of a couple breaking changes between the Beta 2 and RTM, and will try to keep you updated as I find more items.

    The biggest changes to be aware of in regards to LINQ to SQL are the following:

    • Open the DBML file as XML and change the UTF encoding from 16 to 8. Otherwise the compiler will complain about the DBML file's format.
    • Replace the Add methods with InsertOnSubmit. Likewise, change Remove to DeleteOnSubmit. You may be able to do a global search and replace on this, but be careful not to make changes to any IList object implementations, only LINQ to SQL ones.

    There are a couple other minor updates that may catch you off-guard. I'll update you once I've had a chance to play with the final bits.

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Feedburner access to Wooley's various Wonderings

    I have set-up several feedburner syndication options for my postings for your enjoyment. Feel free to move your aggregator over to those versions of the feeds and show me how much you digg what I have to say. Here's the links for you:

    Wooley's Wonderings: My main feed
    Jim's Samples and Presentations: My downloads and sample applications from my presentations. Subscribe here to get updates whenever I add new samples.

    I have a bunch of things I want to discuss from my DevConnections trip, so stay tuned.


    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • VS 2008 Beta 2 VPC images will timebomb on 11/1/2007

    If you downloaded the VPC image version of Visual Studio 2008 beta 2, make sure to back-up your data and move anything off of the Team Foundation Server before November 1, 2007. I was just informed that the images will be time bombing on November 1 2007 rather than the originally intended March 15, 2008 date. If you don't download it now, you may not be able to retrieve your projects. More information is available at the VS 2008 beta 2 download site. If you installed the stand-alone version, you should be ok.

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Adding RSS posts to ThinqLinq using System.ServiceModel.Syndication.SyndicationFeed

    When I originally started the ThinqLinq project I began by loading the RSS feed from my DevAuthority blog, and iterating over the results adding them to the PostItems table in the data context. With LINQ this is relatively easy. Loading the XML from the feed is done with a single line:

    Dim Feed As System.Xml.Linq.XDocument = XDocument.Load("http://devauthority.com/blogs/jwooley/rss.aspx")

    The xml document consists some basic informational elements which are not terribly important in this instance as we are only pulling from a single blog. Following the initial elements, the document contains a series of "item" elements that contain the actual post information. We can easily query the document and return an IEnumerable(Of XElement) object that we can iterate over and create new post items. Below is an excerpt from my original implementation.

    For Each post As XElement In Feed...<item>
    Dim DataPost As New LinqBlog.BO.PostItem
        DataPost.Author =
    "Jim Wooley"
    DataPost.Description = post.Element("description").Value
        DataPost.PublicationDate = CDate(post.Element("pubDate").Value)
        DataPost.Title = post.Element("title").Value

    Once the records are added to the table, we can commit them to the database with a single call to SubmitChanges as follows:


    Ok, so that is easy enough. There are a couple of things to mention before we continue on. The .Add method will be changed to .InsertOnSubmit when LINQ and the .NET 3.5 Framework is released. This will be a breaking change for anyone who is currently working with the beta builds.

    Another item of note: this implementation does not bother importing the various sub-collections, including attachments, comments, categories, etc. We will address some of those in a future update.

    In looking back at the code and being more familiar with LINQ, it is about time to update this code taking advantage of some of LINQ's more interesting features. First VB 9 allows us to eliminate some of the functional construction syntax. Instead of weakly accessing the post.Element("description").Value, we can refer to post.<description>.Value. If we import the namespace, we will even get intellisense on the xml document.

    In addition, rather than iterating over the item elements explicitly, we can use a  LINQ query to create an IEnumerable(Of PostItem) list using the object initializers in the select projection. We then fill the entire collection using the table's AddAll (changing to InsertAllOnSubmit with RTM). With this change, we eliminate the entire for each loop. Below is the revised code:

    Dim Feed As System.Xml.Linq.XDocument = XDocument.Load("http://devauthority.com/blogs/jwooley/rss.aspx")

    Dim dc As New LinqBlogDataContext()

    dc.PostItems.AddAll(From post In Feed...<item> _
        Select New PostItem With { _
        .Author = "Jim Wooley", _
        .Description = post.<description>.Value, _
        .PublicationDate = CDate(post.<pubDate>.Value), _
        .Title = post.<title>.Value})


    That's it. We've read the feed from the site, filled the object collection and saved the resulting objects to the database. Clean and simple.

    But hold on... The title of this post refers to the System.ServiceModel.Syndication.SundicationFeed object. This is a new object as part of the WCF enhancements in the .NET 3.5 Framework. To use it, add a reference to the System.ServiceModel.Web library. This object lets you create and consume feeds in both RSS and ATOM formats and use a single object model against both options. It also gives easy access to a number of child object structures, including Authors, Categories, Contributors, and Links. Additionally it strongly types the results so that we don't need to explicitly cast the values ourselves (for example with the PublicationDate). Here is the complete code to load the feed using the SyndicationFeed.Load method, fill the PostItem collection and submit them to the database.

    Dim feed As SyndicationFeed = SyndicationFeed.Load(New System.Uri("http://devauthority.com/blogs/jwooley/rss.aspx"))

    Dim dc As New LinqBlogDataContext()

    dc.PostItems.AddAll(From p In feed.Items _
        Select New PostItem With _
        {.Author = If(p.Authors.Count > 0, p.Authors(0).Name, "Jim Wooley"), _
        .Description = p.Summary.Text, _
        .PublicationDate = p.PublishDate, _
        .Title = p.Title.Text})


    The code is substantially the same as the revised version using the XML Literals above. The advantage of using the Syndication services implementation is that it abstracts the feed structure (RSS/ATOM), giving direct and strongly typed access to the contents.

    Now that we've set this up, maybe I can work on using the SyndicationFeed to generate the feeds in ThinqLinq and present that in a future post. Stay tuned.

    Technorati tags: , , ,

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Code Snippets and Partial Methods

    In my VB 9 language enhancements talks, I do them withalmost all coding on the fly as I find people often can comprehend the code. I start by building a quick class that is used throughout the demos. To assist, I do use the snippet functionality in VB. For example, if you type "property" and then tab twice, the designer will generate a private field with public property accessors. The if you change the highlighted values, any associated names will be changed as well.

    Private newPropertyValue As String
    Public Property NewProperty() As String
    Return newPropertyValue
        End Get
        Set(ByVal value As String)
            newPropertyValue = value
    End Set
    End Property

    Personally, if you are not doing anything within your properties, there isn't much that you buy in using properties as compared to just exposing the field publically. I want a bit more functionality built into my properties. In the very least, I want to be able to include some change tracking. Once nice feature of the snippets is the fact that they are quite easy to modify and create your own.

    To begin, we need to find where the supplied snippets are located on your disk. We can find this by clicking "Tools" and then  the "Code Snippets Manager". We can find the location by drilling into the "Code Patterns" then "Properties, Procedures, Events" and find the "Define a Property" snippet. The location window will show you where this one is located. In the default install, it will be in your c:\Program Files\Microsoft Visual Studio 9.0\Vb\Snippets\1033\common code patterns\properties and procedures\ folder. Navigate to this folder and copy the DefineAProperty.snippet. Paste it as a new file and name it whatever you want keeping the .snippet extension.

    The snippet file is just a XML document. Open it with visual studio to edit it. The top is a Header which includes the description information that will show up in the snippet manager. One change you will need to make is to alter the "Shortcut" tag so that it will use the key combination you want to use to invoke your custom snippet. In my demos, I use "propertycc", thus I change the header as follows:

    <Title>Define a Property with PropertyChanging</Title>
    Author>Jim Wooley</Author>
    Description>Defines a Property with a backing field.</Description>

    The key is to change the info in the Snippet node. In my case, I like to use a convention where the private field is the same name as the public property with the exception that it is prepended by the underscore. Thus, my field may be called _Foo and the property is called Foo. Due to this, I can eliminate the PrivateVariable node and just keep the PropertyName and PropertyType nodes.

    With these changes in place, we can actually define our new generated code. This can be found in the CDATA section in the Code node. I use the following in my snippet declaration:

    <![CDATA[Private _$PropertyName$ As $PropertyType$
    Public Property $PropertyName$() As $PropertyType$
            Return _$PropertyName$
        End Get
        Set(ByVal value As $PropertyType$)
            If Not _$PropertyName$.Equals(value) Then
                _$PropertyName$ = value
            End If
        End Set
    End Property

    With this definition, any time we change the starting PropertyName, all associated values will be changed for each snippet. When we save our changed snippet and open a class libarry, we can start to use our new snippet by typing "propertycc" and the following code will be generated for us:

    Private _NewProperty As String
    Public Property NewProperty() As String
    Return _NewProperty
    End Get
    Set(ByVal value As String)
    If Not _NewProperty.Equals(value) Then
    _NewProperty = value
    End If
    End Set
    End Property

    Now our property has a detection as values change and we can then do something with it. In this case, we will call an OnPropertyChanged(propertyName as string) method, assuming we have defined one in our class. If we don't have one defined, we won't be able to compile our application. We have several options to provide the OnPropertyChanged method. The class could inherit from a base class implementtation. The additional complexity level may not necessary in many cases. Additionally, we could implement a concrete method in our class. This will mean a slight performance hit if we don't actually do anything in the method.

    As an alternative, we can use the new partial methods in VS 2008. The great thing about partial methods is that if they are not implemented they are compiled away. Additionally, we can place the partial stub in a partial class for generated code and then put the implementing method in the other half of a partial class which is isolated to the custom business functionality. With this architecture in mind, we can define our partial method in the class with the rest of our generated code properties:

    Partial Private Sub OnPropertyChanged(ByVal propertyName As String)

    End Sub

    In the other half of our partial class pair of files, we can implement the method as follows:

    Private Sub OnPropertyChanged(ByVal propertyName As String)

    RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs(propertyName))

    End Sub

    Public Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged

    If we don't need the implementation, the OnPropertyChanged method call in the property setters will be compiled away, otherwise we already have the stubs in place with our snippet in order to handle the functionality as necessary.

    If you are interested in trying out this snippet, I'm attaching it to this post. Simply unzip it to your snippets directory and try it out.

    Technorati tags:  ,
    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Code Camp 2007 downloads

    I've uploaded the files for my presentations for the fall Code camp season which I just presented last weekend at the Birmingham, Alabama code camp. If you missed the talks, you can pick up the downloads at the following links. Also, I will be at the Charleston, South Carolina code camp this coming weekend (10/13) so you can catch me there. Additionally, the ThinqLinq talk is still available on the aspnetpodcasts.com. Links to all three parts of thie webcasts are available at http://www.devauthority.com/blogs/jwooley/archive/2007/07/27/66845.aspx.

    Below are the links to each of the downloads for my three talks.

    These and all of my demos are available via my downloads page at http://www.devauthority.com/files/13/jwooley/default.aspx?ppage=1. Don't let the license notice keep you from downloading the samples. It is just a boilerplate message that I'm not able to change at this point.

    Technorati tags: , ,

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
    Updated: 11/9/2007 to fix the invalid hyperlinks due to cross posting.

  • LINQ to SQL Compiled Queries

    As LINQ nears release, people are starting to consider the performance implications that the extra overhead brings. Currently there are two threads on this: thread1, thread2. For those that are intested in the performance implications, I highly recommend checking out the outstanding series of posts by Rico Mariani.

    Ultimately if you want to get the best performance, you need to use the Compile function of the CompiledQuery class. Let's consider the following query (Note, turn this into C# by adding a semi-colon at the end if necessary):

    From p In context.Products() _
                Where (p.Price >= minimumPrice) _
                Select p)

    In this query, we are searching for the products that are "Expensive" in that their price exceeds a price value that we set. If we regularly consume this, we can eliminate the overhead of building the query pipeline. See the Matt Warren talk about this pipeline on the deep dive video at on Charlie Calvert's blog for more information regarding the overhead that is necessary to evaluate a query.

    To compile the query, we can leverage the CompiledQuery.Compile method. This method takes an argument list as input and a result type. It returns a delegate as a variable that we will be able to consume. Thus in this case, we can pass the DataContext instance and the minimumPrice variable in. We will return a IQueryable(of Product) object. thus we can start the definition of our compiled query as follows:

         CompiledQuery.Compile( _
             Function(context As MyDataContext, minimumPrice As Decimal) _
                From p In context.Products() _
                Where (p.Price >= minimumPrice) _
                        Select p)

                (MyDataContext context, decimal minimumPrice) =>
                        from p in context.Products
                        where p.Price >= minimumPrice
                        select p);

    In this case we are defining our query as a Lambda function which the Compile can convert to the corresponding expression tree representation. If you are unfamiliar with Lambdas in VB, check out Timothy Ng's recent MSDN Magazine article.

    With the delegate functions declared, all that is left is to actually assign them to a variable that we can consume later. To do this, we define a static/shared function which returns the Func anonymous delegate type. By defining it as a static/shared function, the compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. Notice, we are defining the signature of the query, not the results. We are free to change the parameter without needing to re-compile the query's structure. Here's the completed function calls in VB and C#:

    Public Shared ExpensiveProducts As Func(Of MyDataContext, Decimal, IQueryable(Of Product)) = _
           CompiledQuery.Compile(Function(context As MyDataContext, minimumPrice As Decimal) _
                From p In context.Products() _
                Where (p.Price >= minimumPrice) _
                Select p)

       public static Func<MyDataContext, decimal, IQueryable<Product>>
             ExpensiveProducts =
     CompiledQuery.Compile((MyDataContext context, decimal minimumPrice) =>
                        from p in context.Products
                        where p.Price >= minimumPrice
                        select p);

    The syntax for using the compiled query takes a bit of thinking to get your head around. We are essentially creating a function which returns a function rather than returning a value. If you're familiar with functional programming such as OCaml and F#, this concept should be easy to grok. For the rest of us, piece out the individual method components as laid out above and you should be able to understand it after a while ;-)

    To consume our new function, we simply instantiate a DataContext instance and pass it along with the minimumPrice we want to set as our minimum value.

      Dim context As New MyContext(My.Settings.MyConnectionString)
      Dim Items = ExpensiveProducts(context, minimumPrice)

    Technorati tags: , ,,

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • LINQ In Action chapters available in MEAP

    LINQ in Action

    If you can't wait for the print version, you can finally get all of the chapters for our LINQ in Action book, including the long awaited LINQ to SQL and LINQ to Amazon chapters. If you sign up now, you can get the current version and rights to all updates that we make until the book is published. Sign up today and be ready for Visual Studio tomorrow! Let us know what you think on the book forum. We're still working on the code samples and hope to have them available both in VB and C# soon

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • Using Stored Procedures with LINQ with concurrency checking

    When submitting data to the database, you need to watch out for concurrency issues (times when different users make simultaneous changes to the same record). To do this, in a disconnected environment, we either need to combine a ID and a TimeStamp column or pass all of the original values to the database as well as the changed values.

    When we issue the update statement, we include a check for the original values in the Where clause along with the new values in the Set clause. Below is a sample stored proc which updates an Author table with concurrency checking.

     PROCEDURE [dbo].[UpdateAuthorWithoutTimestamp]
     @ID UniqueIdentifier output,
     @LastName varchar(50),
     @FirstName varchar(50),
     @WebSite varchar(200),
     @OriginalLastName varchar(50),
     @OriginalFirstName varchar(50),
     @OriginalWebSite varchar(200),
     @UserName varchar(50)

    -- Save values
    UPDATE dbo.Author
    LastName=@OriginalLastName AND
    FirstName=@OriginalFirstName AND

    RETURN @@RowCount

    In this case, if another user made a conflicting change, the return value will be 0 because no records match the original values any more.

    With the stored proc set-up, we can now turn our focus to the LINQ implementation. With Beta 1, LINQ checked for methods in the DataContext which followed the following signature:

    Private Sub OnTUpdate(byval original as T, byval changed as T)

    In Beta 2, this signature was changed to:

    Private Sub TUpdate(byval instance as T)

    Thus if we want to use our stored procedure in place of the runtime supplied update implementation (for security/auditing/etc purposes), we simply need to define our procedure in a separate Partial data context class as follows:

    Private Sub AuthorUpdate(ByVal instance as Author)

    In this method, we can call our stored procedure. If we were leveraging the TimeStamp, we would have been fine. We are not using a timestamp on this table. Our stored procedure requires us to know about the original values. Due to the change between beta1 and beta2, we no longer have a handle on the original author instance that we need for concurrency check.

    Scott Guthrie pointed me to a helper method on the Table entity type which can grab the original value from an instance: GetOriginalEntityState. With this, we can find the original value and then call our stored procedure method passing these original values. Here is the implementation:

            Private Sub UpdateAuthor(ByVal instance As Author)
                Dim original As Author = Authors.GetOriginalEntityState(instance)
                Me.UpdateAuthorWithoutTimestamp( _
                    instance.ID, instance.LastName, instance.FirstName, instance.WebSite, _
                    original.LastName, original.FirstName, original.WebSite, _
            End Sub

            <System.Data.Linq.Mapping.Function(Name:="dbo.UpdateAuthorWithoutTimestamp")> _
            Public Function UpdateAuthorWithoutTimestamp( _
                <Parameter(Name:="ID")> ByVal iD As Guid?, _
                <Parameter(Name:="LastName")> ByVal lastName As String, _
                <Parameter(Name:="FirstName")> ByVal firstName As String, _
                <Parameter(Name:="WebSite")> ByVal webSite As String, _
                <Parameter(Name:="OriginalLastName")> ByVal originalLastName As String, _
                <Parameter(Name:="OriginalFirstName")> ByVal originalFirstName As String, _
                <Parameter(Name:="OriginalWebSite")> ByVal originalWebSite As String, _
                <Parameter(Name:="UserName")> ByVal userName As String) As Integer
                Dim result As IExecuteResult = Me.ExecuteMethodCall( _
                                Me, _
                              DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
                                iD, lastName, firstName, webSite, _
                                originalLastName, originalFirstName, originalWebSite, userName)
                iD = DirectCast(result.GetParameterValue(0), Guid)
                Return DirectCast(result.ReturnValue, Integer)
            End Function

    Authors.GetOriginalEntityState(instance) gives us our missing information and we can now perform our concurrency handling again.

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • ThinqLinq demo app updated for Beta 2

    For anyone who has attended my presentations on ThinqLinq I have updated the sample solution for Visual Studio 2008 Beta 2. Feel free to download it and try it out.

    If you have not had the opportunity to attend the sessions live, you can see it in action on the AspNet Podcasts, This is a proof of concept web site that uses VB 9 to manage a blogging web site that uses LINQ for the data access. We leverage LINQ to Objects, LINQ to SQL, LINQ to XML, Updating data, Heterogeneous joins, and other related technologies.

    Let me know if you have any recommendations for updates and enhancements to this sample.

    Download it now.

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
  • LINQ in Action available for pre-order at Amazon

    I was scrolling through some sites and on a whim decided to check to see if LINQ in Action was listed on Amazon yet. Not only is it listed but I found that they have a new feature where they syndicate RSS feeds from a book' site (www.LinqInAction.net) on theirs. Check it out. I just wish there was a way of finding out how many hits those posts get so I could combine the count with my own site to get a better feeling just how many fans I have out there.

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx

  • Use the new LINQ "Contains" extension method for the SQL "IN" clause

    For a while, users were requesting a way to map the SQL "IN" clause as in the following:

    SELECT Customer.Name
    FROM Customer
    WHERE Customer.State IN ('GA', 'FL', 'AL')

    Prior to Beta 2, this would mean either separate OR clauses for each item, or worse yet a heterogeneous join between the database and an in memory array. Why do I say, worse yet? Because the heterogeneous join option meant bringing all of the records from the database and filtering on the client.

    Beta 2 introduced a new "Contains" extension method which converts to the TSQL "IN" clause. Below is an example that I put together for one of my first production apps that is already live.

    In this case, I fetch a list of items that the user has selected in a CheckBoxList and use the values from that for my IN clause. The selected items are declared as follows:

     Dim channels = From channel As ListItem In Me.CheckBoxList1.Items _
                           Where channel.Selected _
                           Select CStr(channel.Value)

    Next, we need to set-up a lambda expression that will serve as a filtered table set in my join clause.

     Dim companies = dc.Companies.Where(Function(c As Company) channels.Contains(c.Channel))

    Here I am identifying an IQuerable<Company> which consists of the company table filtered by the declared function. In case this syntax is new to you, the "Function" clause is VB's implementation of a Lambda expression. I'll post more on this soon. Basically, the function states that, given a company that we will call "c", return true if the channels collection contains the channel for this company.

    Because of the IQuerable composability, we can then leverage this object as part of a larger query as follows:

            Dim users As IEnumerable(Of String) = From user In dc.Users _
                    Join company In companies On user.CompanyId Equals company.CompanyId _
                    Order By user.UserName _
                    Select user.UserName Distinct

    Here's the great part. When we look at the generated SQL, not only is the Contains expression translated into the IN clause, but the separate portions of the query are pieced together into a single query. Take a look:

    SELECT DISTINCT [t0].[UserName]
    FROM [dbo].[User] AS [t0] INNER JOIN
    [dbo].[Company] AS [t1] ON [t0].[CompanyId] = [t1].[CompanyId]
    WHERE (CONVERT(NVarChar(MAX),[t1].[Channel])) IN (@p0, @p1)

    Notice also, the VB team has added a number of query expressions that are not available in C#. This query uses the Distinct expression at the end of the select clause which is likewise translated directly as part of the TSQL select statement. You no longer have to call the .Distinct method on the query to get the desired results, it is now a native query expression.

    Ah, feel the VB love...

    Crossposted from http://devauthority.com/blogs/jwooley/default.aspx