OakLeaf Systems: LINQ and Entity Framework Updates for 9/5/2007+

Friday, September 07, 2007

LINQ and Entity Framework Updates for 9/5/2007+

This post is late because of an extended investigation I'm making into use of stored procedures for LINQ to SQL data retrieval. I'm also working on a detailed comparison of the performance of LINQ to SQL queries and Typed DataSets. Stay tuned for the initial results and a more detailed performance analysis of LINQ to SQL queries in an article scheduled for the November 2007 issue of Visual Studio Magazine.

Julie Lerman on Creating DataTables from LINQ to Entities Queries

Julie's Converting a LINQ to Entities query to a DataTable post of 9/7/2007 shows you how to modify Mike Taulty's replacement for LINQ to SQL's now-missing ToDataTable() method in his "Orcas" March CTP - IEnumerable<T> to DataTable post to generate a DataTable from a LINQ to Entities query.

Julie Lerman Profiles LINQ to Entities Queries

In another How do LINQ to Entities queries surface in the database? post today, Julie explores the T-SQL generated by executing queries of varying complexity against the AdventureWorksLT database. Several of the convoluted T-SQL queries create by relatively simple LINQ to Entities queries are interesting (in the sense of the purported Chinese curse: "May you lead an interesting life."

Rob Conery Extends LINQ to XML to Geocoding

Rob Conery, creator of SubSonic, chief architect of the Commerce Starter Kit, and author of How To Use LINQ To Query Just About Anything, has been exploring the use of LINQ to SQL and the Haversine Formula with LINQ to SQL queries that find hotels within a given distance from a particular latitude and longitude. His first attempt led him to calculate the distance with a T-SQL scalar function in his August 30, 2007 LINQ and Geocoding post, because the simple-appearing lambda expression incurred a “Cannot translate this to SQL” from LINQ to SQL. (The query did return the correct result with LINQ to Objects, a.k.a. data in memory.)

Rob's September 6, 2007 LINQ and GeoCode, Part 2 post explains that the secret to making the lambda expression work was to declare it as an Expression<T> so the compiler builds an expression tree out of it. (See the Michael Giagnocavo post below.) Unfortunately, the expression tree approach turned out to be three orders of magnitude slower than the T-SQL function.

Mark Leistner Starting Unit Testing with LINQ to SQL Series

Mark's introductory post, Unit Testing in LINQ - Part 1, of 9/5/2007 says:

So recently I've started using LINQ a lot and it saved me a lot of time and effort once I got over the big hump of rewriting all my objects to take advantage of it. I began to see the potential that I could possibly use this to test my project better than any other method I had tried, and to do it rather easily.

I tried several ways to use LINQ in unit testing, but kept running into problems. Eventually I came up with a solution that so far has been rather easy to implement and provided just what I had been looking for. This is the first post in a series that I am creating to pass this knowledge on, hopefully you will find it useful for your projects as well.

Unit Testing in LINQ - Part 2 (9/7/2007) describes Mark's "base code" to allow unit testing with LINQ to SQL, which uses an IRepository interface having a Context property to access the underlying DataContext and an IRepositoryTable object for each DataTable. Unfortunately, this approach doesn't support EntityRefs and EntitySets.

Unit Testing in LINQ - Part 3 (9/7/2007) includes code for retrieving a simple Customer object by ID, three unit tests, implementation of the Repository, and implementation of the TestTable object.

I'm not sure I'm ready to give up EntityRefs and EntitySets at this point in my unit testing for LINQ search.

Michael Giagnocavo Solves the Haversine Lambda Problem

Michael's Calling custom methods in LINQ-to-SQL and Complicated functions in LINQ to SQL posts of September 5/6, 2007 explains that

[C]alling our [custom] method in our LINQ query won't work because the LINQ-to-SQL code isn't going to know what to do with it. A method is just an opaque block of code with a name.

Instead, you declare an Expression<LambaFunc> and change the Where appropriately, as shown in his first post. He references Thomas Petricek's Building LINQ Queries at Runtime (see below) and Joe Albahari's Dynamically building LINQ expression predicates. (Joe is the author of the great LINQPad application I described in my LINQ and Entity Framework Updates for 8/22/2007 post.)

In the second post that handles the Haversine Formula problem, Michael notes:

If you want to use your own "complicated" functions with LINQ to SQL, you'll need to manually construct the predicate expression. It's not pretty, but, it does let you convert somewhat detailed functions, such as Haversine, inside of LINQ-to-SQL queries. This is not always the right approach: in some cases it'll be better to use a UDF or stored procedure.

and shows the T-SQL output by the expression tree.

Bart de Smet Uses LINQ to XML for PowerShell Extension Methods

Bart's September 6, 2007 Extension Methods in Windows PowerShell post explains how to use LINQ to XML to generate the XML files needed to take advantage of Windows PowerShell's Extended Type System (ETS).

Jim Wooley on Compiling LINQ to SQL Queries

Jim Wooley's LINQ to SQL Compiled Queries of September 5, 2007 provides a more detailed analysis of compiling LINQ to SQL queries in VB 9.0. This is a topic that Rico Mariani introduced in his LINQ to SQL performance posts and that I commented about in Rico Mariani Starts LINQ to SQL Performance Analysis Series and expanded on in Rico Mariani's DLinq Performance Tips (Part 2) and Compiled Queries.

My performance data was based on VS 2008 Beta 1, so I'm planning update my Rico Mariani's DLinq (LINQ to SQL) Performance Tips (Round 3) post with Beta 2 data this weekend.

Cascading Deletions and the "DeleteOnNull" Problem

If you've encountered this error:

Error 9 DBML1055: The DeleteOnNull attribute of the Association element 'ConstraintName' can only be true for singleton association members mapped to non-nullable foreign key columns.

three potential solution to the problem are in my Cascade Deletion Problem with LINQ to SQL Beta 2 post of 9/5/2007.

Mike Taulty Posts Brief LINQ to Entities Queries

Mike's September 5, 2007 ADO.NET Entity Framework - LINQ. Getting Started post compares the ObjectContext.CreateQuery<T> method to create an ObjectQuery<T>, which implements IQueryable<T>, with a similar LINQ to SQL query over the DataContext to return the same IQueryable<T>.

Julie Lerman Explores the EDMDeploy Build Task

Entity Framework (EF) Beta 2 introduces the new EDMX file, which incorporates data previously contained only in the SSDL, MSL, and CSDL files. In her 9/4/2007 Sharing Entity Framwork models between projects (or teams) when developing post, Julie describes how the EDMDeploy build task generates the the SSDL, MSL, and CSDL files from the EDMX file and the files' default location in Windows and Web form projects. She also shows how to edit the EDMDeploy task in the Post-build Event Command Line editor to place the three files in a more convenient folder.

Matt Warren's Provider Implements Joins and SelectMany

Matt's LINQ: Building an IQueryable provider - Part VII of September 4, 2007 explains that LINQ to SQL queries with two From clauses produce a cross-product and a Join clause generates an explicit join; both return the same result. He then goes on to create an expression tree to generate T-SQL CROSS JOIN, INNER JOIN, or CROSS APPLY queries. T-SQL's CROSS APPLY, which is new in SQL Server 2005, solves problems with translating certain types of SelectMany clauses. His post contains a link to the Query7 project's source code.

Bart de Smet Finishes his New VB 9.0 Language Features Series

He added XML Support on 9/4/2007 and LINQ Queries on 9/5/2007:

If you want a concise guide to VB 9.0's new features, Bart's series is the best source.

Julie Lerman Checks Serialized Entity Keys in ASP.NET ViewState

She notes in her September 3, 2007 EntityKey and ASP.NET ViewState post that entity objects didn't serialize without errors in the Beta 1 Entity Framework release.

Bill Burrows on LINQ to XML with VB 9.0 (Article and Video Clips)

Programming With XML Using Visual Basic 9 by "VB Professor" Bill Burrows explains how to use VB 9 and LINQ to XML to transform Windows Live Expo's RSS feed of homes for sale into an Excel worksheet in Open Office XML format. You'll find 13 brief video clips that describe the entire process here.

Dynamic Searching with LINQ and VB 9.0 Expression Trees

The VB Team's Jonathan Aneja posted Implementing Dynamic Searching Using LINQ, which shows you how to add an InfoPath-like Condition Builder control to a Windows form and write a companion expression tree module to enable dynamic LINQ to SQL searches with property names and operators selectable from dropdown lists. Users enter values into text boxes, datetime pickers or check boxes, depending on the data type. (Posted August 29, 2007 and somehow missed at that time.)

Tomas Petricek Takes Another Approach to Dynamic Queries with C#

Building LINQ Queries at Runtime in C# by Tomas Petricek (a.k.a. Tom.ASP) demonstrates an alternative approach to coding dynamic LINQ to SQL queries: “call functions in LINQ queries.” Here's his introduction:

[Y]ou can call your functions (which are actually expressions) using the Expand extension method. ... The ToQueryable [method] creates a thin layer that processed the entire LINQ query before it is sent to the “LINQ to SQL” translator and it replaces all occurrences of the Expand call with the body of the function, so that the “LINQ to SQL” can understand the query.

A DLL is available for download from the post and source code is on CodePlex (Posted July 30, 2007 and missed).