Projecting into an unmapped property from a LINQ to SQL query by ThinqLinq

Projecting into an unmapped property from a LINQ to SQL query

On page 216 of LINQ in Action, I made a comment that unmapped properties in a mapped class cannot be used in a LINQ to SQL projection. This was true with the beta bits, but only partially true with the release bits. To begin, let's consider the Author table we have in the book samples.

The Author class has separate fields for the first and last name. Each of these is mapped to the corresponding fields in the Author table. In the book, we show how you can create a read only property in a partial class (so that it won't get clobbered when we regenerate our classes in the designer). The new property is trivial:

   Partial Public Class Author
        Public ReadOnly Property FormattedName() As String
            Get
                Return Me.FirstName & " " & Me.LastName
            End Get
        End Property
    End Class

Notice here that there are no mapping attributes to this property. In part, that is because there is no corresponding field in the table. As we show in the book, you are free to query the author table and return Author objects. From there, you can display the FormattedName as follows:

           Dim authors = From a In context.Authors _
                          Select a
            For Each a In authors
                Console.WriteLine(a.FormattedName & "; " & a.WebSite)
            Next

This works fine because we are projecting the complete Author type. However, in early builds, we couldn't project the unmapped properties into an anonymous type like this:

            Dim authors = From a In context.Authors _
                          Select a.FormattedName, a.WebSite

If you tried to use this projection, you would get a runtime exception. In the RTM bits, the behavior was modified. Now, if you try to run the above query (sample 8.25 in the book samples for anyone following along). You will see that the query succeeds and the anonymous type is used. So how can they know how to populate the FormattedName when it is not mapped and doesn't exist in the table itself? No, the provider doesn't look inside the property, determine the mapped properties that are used, and fetch them. While that could work in our simple example, many unmapped properties  would use significantly more resources, many of which may not be members of our class, or methods without direct translation in TSQL. If you look at the generated SQL that is issued when the query is consumed, you might be able to figure out what is happening in this case.

SELECT [t0].[ID], [t0].[LastName], [t0].[FirstName], [t0].[WebSite], [t0].[TimeStamp]
FROM [dbo].[Author] AS [t0]

Notice here, our select clause to the database is not optimized to only return the fields we requested. Instead, all of the fields are returned. So what's going on? They discovered in evaluating the Select clause that there were unmapped properties. At that point, they just turned around and populated a full author object. Using this object, the provider turns around and generates the anonymous type from the object rather than directly from the underlying data store directly. It's a bit of smoke and mirrors at this point.

So the question that came up asks if the next printing of the book needs to be adjusted to remove the statement that you can't project an unmapped property. While you can indeed project these properties, you can't use them elsewhere in the query. Thus if you wanted to sort the data based on the unmapped property, the exception would be thrown. Consider the following query./p>

            Dim authors = From a In context.Authors _
                          Order By a.FormattedName _
                          Select  a.FormattedName, a.WebSite

In this case when we try to run it, we get the following error:

"System.NotSupportedException: The member 'LinqInAction.LinqBooks.Common.VB.SampleClasses.Ch8.Author.FormattedName' has no supported translation to SQL."

Because of this, I plan to leave the note in the chapter warning you of using the unmapped property in your query. Unfortunately, I don't have enough space in the book to insert this complete explanation at this time. I hope this explanation helps some of you who are confused at this point.

Posted on - Comment
Categories: LINQ - VB - VB Dev Center -
comments powered by Disqus