Rating and Stats

Document Actions

Share or Embed Document

Cyborgx37

Cyborgx37 You are viewing a feed that contains frequently updated content. When you subscribe to a feed, it is added to the Common Feed List. Updated information from the feed is automatically downloaded to your computer and can be viewed in Internet Explorer and other programs. Learn more about feeds. Subscribe to this feed

Getting started with LINQ
Monday, March 15, 2010, 9:50:35 AM | noreply@blogger.com (Cyborgx37) LINQ is syntactical sugar for extension methods and lambda expressions. To understand LINQ, it is important to first grapple with these concepts. To begin, let's look at a simple and somewhat common scenario. We have an unsorted list of names. We want to go through each letter of the alphabet and print a an alphabetized list of names for the current letter. Here's a typical approach.
Module Module1 Sub Main() Dim alphabet As String() = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _ "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", _ "U", "V", "W", "X", "Y", "Z"} Dim names As String() = New String() {"Adam", "Dave", "John", "Alex", "Daryll", "Jacob", _ "Christopher", "Bill", "Ronald", "Jeff"} For Each letter In alphabet Dim selected_names As New List(Of String) For Each name As String In names If name.StartsWith(letter) Then selected_names.Add(name) Next If selected_names.Count > 0 Then selected_names.Sort(AddressOf SortNamesMethod) Console.WriteLine("Names beginning with '" & letter & "'") For Each name As String In selected_names Console.WriteLine(name) Next Console.WriteLine("") End If Next 'Pause for the user Console.Read() End Sub Private Function SortNamesMethod(ByVal name1 As String, ByVal name2 As String) As Integer Return name1.CompareTo(name2) End Function End Module

While this approach certainly accomplishes the job, you will see in a moment how LINQ can make the line count smaller, the program flow more logical and the code easier to maintain. Extension Methods Extension methods allow programmers to add useful methods to existing types. In our example, we will be adding an extension method to the IEnumerable(Of String) type. The extension method will print the list of strings to the console, as well as the list title.

In order to create an extension method, we will need to import the System.Runtime.CompilerServices namespace.
Imports System.Runtime.CompilerServices

Next we will need to create a new subroutine. The sub will be named ToConsole and will take two parameters. The first parameter will define the type to which this method is being added, and the second parameter will be the title that we want to give our list. Finally, we will need to add the Extension attribute to the function to alert the compiler that this method is an extension method.
<extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String), _ ByVal title As String) End Sub

Now we need to add the functionality. We will borrow it from the Main function above:
<extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String), _ ByVal title As String) If items IsNot Nothing Then Console.WriteLine(title) For Each item In items Console.WriteLine(item) Next Console.WriteLine("") End If End Sub

We can use this method on any object that implements the IEnumerable(Of String) interface. In our example, the selected_names variable in the Main method implements this interface because the List(Of String) implements IEnumerable(Of String). We can modify our code to look like this:
Imports System.Runtime.CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _ "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", _ "U", "V", "W", "X", "Y", "Z"} Dim names As String() = New String() {"Adam", "Dave", "John", "Alex", "Daryll", "Jacob", _ "Christopher", "Bill", "Ronald", "Jeff"} For Each letter In alphabet Dim selected_names As New List(Of String) For Each name As String In names If name.StartsWith(letter) Then selected_names.Add(name) Next If selected_names.Count > 0 Then selected_names.Sort(AddressOf SortNamesMethod) selected_names.ToConsole("Names beginning with '" & letter & "'") End If Next 'Pause for the user Console.Read() End Sub Private Function SortNamesMethod(ByVal name1 As String, ByVal name2 As String) As Integer

Return name1.CompareTo(name2) End Function <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String), _ ByVal title As String) If items IsNot Nothing Then Console.WriteLine(title) For Each item In items Console.WriteLine(item) Next Console.WriteLine("") End If End Sub End Module

Typically an extension method is most useful in cases where you'd want to that method in more than one place. Our example is small and this is not really necessary, but the exercise will help when dealing with some of the built-in extension methods provided for LINQ. Lambda Expression Next, let's get rid of the SortNamesMethod. In a small application like this one, defining a function that is used only once is not a problem, but in a very large application, these kinds of extra functions get to be annoying and confusing. We will use a simple lambda expression instead of the function. The List(Of String).Sort() function accepts a reference to a function as a parameter. The function doesn't care where the function exists or how it was declared as long as the function takes two strings as parameters and returns an integer indicating if the first string is greater than, equal to or less than the second string. We can replace the AddressOf SortNamesMethod with the lambda expression
Function(name1 As String, name2 As String) name1.CompareTo(name2)

The compiler reads this lambda expression and creates a function for us. The lambda expression explicitly declares its parameters and the compiler is able to detect that the return type is boolean (because name1.CompareTo(name2) returns a boolean). Once the compiler has created the function, it replaces the lambda expression with the address of the compiler-created function. The resulting binary code is pretty much the same, but the benefit is that I no longer need to deal with that extra function floating around in my code. Getting closer to LINQ Now that we have seen how to create an extension method and use a lambda expression, let's look at some of the built-in extension methods available to us. Let's begin with the Where method. For any IEnumberable(Of T), the Where method will return an IEnumerable(Of T) where all items in the list match the predicate provided as a parameter. That's a little confusing, so let's looks at an example. We will be replacing the for loop which filters the names list by the first letter with the Where extension method.
Dim selected_names As New List(Of String) For Each name As String In names If name.StartsWith(letter) Then selected_names.Add(name) Next

"W". In this case. The lambda is transformed into a function at compile time. "Jeff"} For Each letter In alphabet Dim selected_names As New List(Of String) selected_names = names. "J". we want to order the list by the name.StartsWith(letter)).blogspot.Where(Function(name As String) name.Where(Function(name As String) name. "N".OrderBy(Function(name As String) name). "Y". "Bill".CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() {"A". "F". and the Where extension method applies the function to each element in our name list at run time. let's apply to OrderBy extension method. so all we need to do is return the name that we passed in as a parameter. "G". "M". _ "U". The Where extension method then returns an IEnumerable(Of String) containing all items in our name list where the result of the predicate (StartsWith(letter)) is true. The OrderBy extension method takes a name from our list as a parameter and returns some key by which to sort the list. "Ronald". "John". "Daryll".ToList()
becomes
selected_names = names.Runtime. "I".Cyborgx37
We've replace the for-loop with an extension method. We can take this a step further by sorting the list as we filter it. Then we use the ToList() extension method to transform the IEnumerable(Of String) into an IList(Of String) so that we can assign the result back to the selected_names variable. "R". "T". "V". "Jacob".WriteLine("") End If End Sub End Module
http://cyborgx37. "Q".StartsWith(letter)). "H".
selected_names = names. "S".Count > 0 Then selected_names.Read() End Sub <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String). "E". "C". "Alex".ToList()
The sort function later in the code is no longer necessary. Instead of applying the ToList extension method to the result of the Where extension method.Where(Function(name As String) name.com/feeds/posts/default[6/24/2010 2:33:46 PM]
. "Dave".ToConsole("Names beginning with '" & letter & "'") End If Next 'Pause for the user Console. "D". "X".OrderBy(Function(name As String) name). "P".StartsWith(letter)). Our complete code now looks like:
Imports System. "B". "Z"} Dim names As String() = New String() {"Adam". "L". we've used a lambda expression that accepts a String parameter and returns a boolean.WriteLine(item) Next Console. _ "Christopher". "O". _ ByVal title As String) If items IsNot Nothing Then Console. For the predicate parameter.WriteLine(title) For Each item In items Console. _ "K".ToList() If selected_names.

Cyborgx37

LINQ Now that we've covered extension methods and lambda expressions, we can convert our selected_names filter into a LINQ statement.
selected_names = names.Where(Function(name As String) name.StartsWith(letter)).OrderBy(Function(name As String) name).ToList()

becomes
selected_names = (From name In names _ Where name.StartsWith(letter) _ Order By name).ToList()

This is the exact same statement written two ways. As you can see, LINQ is syntactic sugar to make our extension methods look prettier. Not all extension methods can be replaced with LINQ, which is why we must still call the ToList() extension method as we did before. We must also wrap the LINQ in commas so that it is applied to the result of the entire LINQ statement instead of the name variable. In addition to what we have seen, there is the Select extension method. This method accepts as a parameter a name and returns whatever object you want to return. The result of applying this extension method to a list is a new list of whatever type you chose to return. LINQ even allows us to return an anonymous type (a new type inferred from the expression with whatever properties we specify). Using the Select extension method in LINQ, we can transform our code to look like this:
Imports System.Runtime.CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _ "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", _ "U", "V", "W", "X", "Y", "Z"} Dim names As String() = New String() {"Adam", "Dave", "John", "Alex", "Daryll", "Jacob", _ "Christopher", "Bill", "Ronald", "Jeff"} Dim rolodex = From list In (From letter In alphabet _ Select Letter = letter, Entries = (From name In names _ Where name.StartsWith(letter))) _ Where list.Entries.Count() > 0 For Each page In rolodex page.Entries.ToConsole("Names beginning with '" & page.Letter & "'") Next 'Pause for the user Console.Read() End Sub <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String), _ ByVal title As String) If items IsNot Nothing Then Console.WriteLine(title) For Each item In items Console.WriteLine(item) Next Console.WriteLine("") End If End Sub End Module

This creates a list of some anonymous type where the Names property of the anonymous type has more
http://cyborgx37.blogspot.com/feeds/posts/default[6/24/2010 2:33:46 PM]

Cyborgx37

than zero items in it. We then take this list and apply the ToConsole extension method to the Names property of each item and passes in a title using the Letter property. With fewer lines, this code is easier to read and more durable as there are not as many lines of code to break. As you can see, we have also made use of VB's ability to infer the type of a variable from the assignment expression. Our rolodex variable is strongly typed, but since we used an anonymous type there is no way to declare the type in a typical dim statement. By allowing VB to infer the type, we can sidestep this requirement and still have the Letter and Entries properties appear in the intellisense list (along with all of the advantages of compile-time type checking). View On CodeProject

Device Independent Units to Points to Inches
Thursday, November 05, 2009, 12:19:22 AM | noreply@blogger.com (Cyborgx37) The default unit of measurement in Windows Forms was pixels. This worked well when most monitors on the market used a simple 96 pixel per inch measurement, but measuring by pixel causes problems when working with devices that have different resolutions. For example, with Windows Forms, a 100 pixel button would appear to be about an inch wide on a 96 pixel/inch monitor. However, on a 120 pixel/inch monitor, the same button would appear to be 4/5 of an inch. A 160 pixel/inch monitor would show the button as 5/8 of an inch. And on a 60 pixel/inch screen, the button would appear to be about 1 2/3 inches wide. With the introduction of WPF Microsoft began using DIU (Device Independent Units). A DIU (also known as a Device Independent Pixel, or DIP) measurement is based on inches rather than hardware-specific pixels. A DIU is defined as 1/96 of an inch (smaller than the point, which is defined as 1/72 of an inch). For a standard 96 pixel per inch monitor, 96 DIU = 96 pixels. For monitors with 120 pixels/inch, 96 DIU = 120 pixels. For a monitor with 60 pixels/inch, 10 DIU = 60 pixels. When the measurement doesn't work out to a perfectly round number (as is often the case) WPF will automatically use anti-aliasing, or you have the option of “snapping” to the nearest pixel if you don't want "fuzzy" outlines for your button, etc. One kink is that all measurements, including text, are now measured in DIU. In Windows Forms, depending on what control you were using, font size was often measured in points (a point is 1/72 of an inch and is the measurement used in Word and other applications). So now when you specify font size 12, you are actually specifying a 9 point font (which is probably smaller than you intended). To help with this transition, I’ve put together a quick chart to quickly convert inches and points into DIU. I hope this helps: Units of Measurement Inches Points DIU 1/96 3/4 1 1/72 1 1 1/3 1/48 1 1/2 2 1/32 2 1/4 3 1/24 3 4 1/16 4 1/2 6 1/12 6 8 1/8 9 12 5/36 10 13 1/3 1/6 12 16 3/16 13 1/2 18

WriteLine("") End If End Sub
We can use this method on any object that implements the IEnumerable(Of String) interface. as well as the list title. The sub will be named ToConsole and will take two parameters. "O". "M".CompareTo(name2) End Function <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String).Runtime. "J". "L". "Jacob". "Daryll".aspx[6/24/2010 2:34:26 PM]
. we will need to import the System. "I".Sort(AddressOf SortNamesMethod) selected_names. "Bill". the selected_names variable in the Main method implements this interface because the List(Of String) implements IEnumerable(Of String) .Getting started with LINQ . "N". "P". Finally.WriteLine(item) Next Console.NET 4 Web Hosting
Next we will need to create a new subroutine. "X". We will borrow it from the Main function above:
Collapse Collapse Copy Code
<Extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String). "H". We can modify our code to look like this:
Collapse Collapse Copy Code
Imports System. "John".Count > 0 Then selected_names. "Alex". "G". "B".
Collapse Collapse Copy Code
Services
Product Catalog Job Board CodeProject VS2008 Addin
Imports System. _ "U".
Collapse Collapse Copy Code
<Extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String). "Q".Runtime. we will need to add the Extension attribute to the function to alert the compiler that this method is an extension method. In our example. "C". "Ronald".WriteLine(item) Next Console.NET Dev Library ASP. "V". "D". _ ByVal name2 As String) As Integer Return name1.WriteLine(title) For Each item In items Console.Add(name) Next If selected_names. "Jeff"} For Each letter In alphabet Dim selected_names As New List(Of String) For Each name As String In names If name.WriteLine(title) For Each item In items Console. _ "Christopher". "R".Read() End Sub Private Function SortNamesMethod(ByVal name1 As String. _ ByVal title As String) End Sub
Now we need to add the functionality. _ ByVal title As String) If items IsNot Nothing Then Console. The first parameter will define the type to which this method is being added. _ "K".Runtime. In order to create an extension method. _ ByVal title As String) If items IsNot Nothing Then Console.com/Articles/43392/Getting-started-with-LINQ. "S".CompilerServices
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts .
http://www. "E".CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() _ {"A".CodeProject
General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
of strings to the console.codeproject. "Z"} Dim names As String() = New String() _ {"Adam". "Y".StartsWith(letter) Then selected_names.WriteLine("") End If End Sub End Module
Typically an extension method is most useful in cases where you'd want that method in more than one place. and the second parameter will be the title that we want to give our list.CompilerServices namespace.ToConsole("Names beginning with '" & letter & "'") End If Next 'Pause for the user Console. "W". "Dave". "T". "F".

The function doesn't care where the function exists or how it was declared as long as the function takes two string s as parameters and returns an integer indicating if the first string is greater than. defining a function that is used only once is not a problem. these kinds of extra functions get to be annoying and confusing. so all we need to do is return the name that we passed in as a parameter.ToList()
We've replaced the for -loop with an extension method.
Collapse Collapse Copy Code
Function(name1 As String.
Lambda Expression
Next. name2 As String) name1.codeproject.StartsWith(letter)).
Getting closer to LINQ
Now that we have seen how to create an extension method and use a lambda expression.Where(Function(name As String) name. we want to order the list by the name. Instead of applying the ToList extension method to the result of the Where extension method.StartsWith(letter)).Sort() function accepts a reference to a function as a parameter. We can take this a step further by sorting the list as we filter it. equal to or less than the second string . For any IEnumberable(Of T) . so let's looks at an example. Let's begin with the Where method.CompareTo(name2) returns a boolean). but the exercise will help when dealing with some of the built-in extension methods provided for LINQ. let's apply to OrderBy extension method. For the predicate parameter. let's look at some of the built-in extension methods available to us. but the benefit is that I no longer need to deal with that extra function floating around in my code.CompareTo(name2)
The compiler reads this lambda expression and creates a function for us.com/Articles/43392/Getting-started-with-LINQ. The List(Of String).
Collapse Collapse Copy Code
selected_names = names. In a small application like this one. In this case.CodeProject
Our example is small and this is not really necessary. The lambda expression explicitly declares its parameters and the compiler is able to detect that the return type is boolean (because name1. let's get rid of the SortNamesMethod .aspx[6/24/2010 2:34:26 PM]
. the Where method will return an IEnumerable(Of T) where all items in the list match the predicate provided as a parameter.StartsWith(letter) Then selected_names. The resulting binary code is pretty much the same. We will use a simple lambda expression instead of the function.Where(Function(name As String) name.Getting started with LINQ . it replaces the lambda expression with the address of the compiler-created function.ToList()
becomes:
Collapse Collapse Copy Code
selected_names = names. The Where extension method then returns an IEnumerable(Of String) containing all items in our name list where the result of the predicate (StartsWith(letter) ) is true . but in a very large application. The lambda is transformed into a function at compile time.
Collapse Collapse Copy Code
Dim selected_names As New List(Of String) For Each name As String In names If name. We will be replacing the for loop which filters the names list by the first letter with the Where extension method.Add(name) Next
becomes:
Collapse Collapse Copy Code
Dim selected_names As New List(Of String) selected_names = names. That's a little confusing. Once the compiler has created the function. and the Where extension method applies the function to each element in our name list at run time.Where(Function(name As String) _
http://www. Then we use the ToList() extension method to transform the IEnumerable(Of String) into an IList(Of String) so that we can assign the result back to the selected_names variable. The OrderBy extension method takes a name from our list as a parameter and returns some key by which to sort the list. We can replace the AddressOf SortNamesMethod with the lambda expression. we've used a lambda expression that accepts a String parameter and returns a boolean.

CodeProject name. "Q". "O". "L". "Y". _ ByVal title As String) If items IsNot Nothing Then Console. "X". "Jeff"} Dim rolodex = From list In (From letter In alphabet _ Select Letter = letter.OrderBy(Function(name As String) name). "J". "W". "Z"} Dim names As String() = New String() _ {"Adam". "Alex". "G".WriteLine(item) Next Console.OrderBy(Function(name As String) name).WriteLine(title) For Each item In items Console. "Jacob".Where(Function(name As String) _ name. "Z"} Dim names As String() = New String() _ {"Adam". "P". "X".aspx[6/24/2010 2:34:26 PM]
. "C". "Y". "M". The result of applying this extension method to a list is a new list of whatever type you choose to return. which is why we must still call the ToList() extension method as we did before. This method accepts as a parameter a name and returns whatever object you want to return. "C". _ "K". As you can see.Getting started with LINQ . "B". "E". "T". "H". LINQ is syntactic sugar to make our extension methods look prettier.StartsWith(letter)). "N".StartsWith(letter)). _ "U". "Bill".ToList() If selected_names. "Ronald". we can convert our selected_names filter into a LINQ statement. "John". "V".StartsWith(letter) _ Order By name). "N". Using the Select extension method in LINQ. "Daryll". "L". "Alex". "Ronald". "Daryll".Runtime. "S". _ "Christopher". "T".WriteLine("") End If End Sub End Module
LINQ
Now that we've covered extension methods and lambda expressions. "I". "R". "F".ToConsole("Names beginning with '" & letter & "'") End If Next 'Pause for the user Console. "S".OrderBy(Function(name As String) name).ToList()
This is the exact same statement written two ways. "Jeff"} For Each letter In alphabet Dim selected_names As New List(Of String) selected_names = names.com/Articles/43392/Getting-started-with-LINQ. "R". "John". "Q".ToList()
The sort function later in the code is no longer necessary. Entries = _
http://www. we can transform our code to look like this:
Collapse Collapse Copy Code
Imports System. "H".Read() End Sub <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String). "F". "B". "P". We must also wrap the LINQ in commas so that it is applied to the result of the entire LINQ statement instead of the name variable. _ "Christopher". "Dave".CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() _ {"A". "G".
Collapse Collapse Copy Code
selected_names = names.Runtime. "Jacob".Count > 0 Then selected_names.CompilerServices Module Module1 Sub Main() Dim alphabet As String() = New String() _ {"A".StartsWith(letter)). "M". _ "U". "V". "E". "Dave". "D". "I". "O". LINQ even allows us to return an anonymous type (a new type inferred from the expression with whatever properties we specify).codeproject. "Bill".ToList()
becomes:
Collapse Collapse Copy Code
selected_names = (From name In names _ Where name. "D". there is the Select extension method. In addition to what we have seen. Not all extension methods can be replaced with LINQ.Where(Function(name As String) _ name. "W". Our complete code now looks like:
Collapse Collapse Copy Code
Imports System. "J". _ "K".

With fewer lines.Letter & "'") Next 'Pause for the user Console. _ ByVal title As String) If items IsNot Nothing Then Console. We then take this list and apply the ToConsole extension method to the Names property of each item and pass in a title using the Letter property. is licensed under The Code Project Open License (CPOL)
About the Author
Cyborgx37
Location: Member United States
Article Top
Sign Up to vote for this article
You must Sign In to use this message board.WriteLine(item) Next Console.CodeProject (From name In names _ Where name. By allowing VB to infer the type.WriteLine(title) For Each item In items Console.Count() > 0 For Each page In rolodex page. As you can see.codeproject. Our rolodex variable is strongly typed. this code is easier to read and more durable as there are not as many lines of code to break.WriteLine("") End If End Sub End Module
This creates a list of some anonymous type where the Names property of the anonymous type has more than zero items in it.Getting started with LINQ . we have also made use of VB's ability to infer the type of a variable from the assignment expression. along with any associated source code and files.aspx[6/24/2010 2:34:26 PM]
.Entries. we can sidestep this requirement and still have the Letter and Entries properties appear in the intellisense list (along with all of the advantages of compile-time type checking).com/Articles/43392/Getting-started-with-LINQ.StartsWith(letter))) _ Where list. View on CodeProject
License
This article.ToConsole("Names beginning with '" & page. FAQ Noise Tolerance
Medium Medium
Search
Layout
Normal Normal
Per page
25 25
Update
http://www.Read() End Sub <extension()> _ Private Sub ToConsole(ByVal items As IEnumerable(Of String). but since we used an anonymous type. there is no way to declare the type in a typical dim statement.Entries.

All you have to do is set the Log property of the DataContext object.g.NET4. FAQ
Search
http://www.5 C#4.484)
SQL-Server VB VB. You probably shouldn't leave this in your code without at least wrapping a #if DEBUG around it.250 (1.Log = Console. For example.. . All you have to do is set the Log property of the DataContext object.NET VB6 VC6 VC8.com/Tips/61692/See-the-SQL-Generated-by-LINQ.139. it can be very useful to see the SQL commands that are generated by your LINQ expressions.Out.
Filter (e.NET . along with any associated source code and files.NET Ajax Architect ASP ASP.CodeProject
7.0/5
3 votes
571
views
Tags: C#.5 .. C++)
Go
Tools
Print Share Discuss Report
When using LINQ to SQL.NET C C# C#2.0 C++ C++/CLI Design GimmeCode HTML Java Javascript LINQ Linux MFC PHP Server Silverlight SQL SQL2005
Permalink
MyDataContext.
Announcements
Write an iPhone Tutorial.aspx[6/24/2010 2:35:35 PM]
. Sometimes the results are surprising and you might be able to improve performance by tweaking the LINQ.708 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Report Bug
Lounge
Suggestions?
Search
See the SQL Generated by LINQ
When using LINQ to SQL.0 .
This will cause the SQL commands generated by MyDataContext to appear in the output window in the debugger. .000 members and growing! (19.NET2. is licensed under The Code Project Open License (CPOL)
WinXP WPF XAML XML
more.codeproject.0 C#3.NET.0 C#3...0 Vista Visual-Studio VS2005 VS2008 WCF WebForms Win2003 Win7 Windows
Add an Alternate
This content. SQL. Win an iPad Local Government Windows Azure Competition Monthly Competition
You must Sign In to use this message board.NET3.0 .0 ADO.
Sign Up to vote for this article
0
answers
5.See the SQL Generated by LINQ .
Posted 26 Feb '10 7:55 AM MarkLTX Rep: 1.NET3. LINQ
Drill down
. Sometimes the results are surprising and you might be able to improve performance by tweaking the LINQ. it can be very useful to see the SQL commands that are generated by your LINQ expressions.

Wanted
Graphics Software Engineer at Intel View this and other fine jobs on the Job Board. we can create a mock data layer.Linq.00 out of 5
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now. Dev Revision: Posted: Updated: Views: Bookmarked: Articles / Quick Answers Articles / Quick Answers
Lounge
Platforms.codeproject. Using Unity as the Dependency Injection framework.000 members and growing! (19. Dependency Injection. I've thrown PolicyInjection in the mix since the way the framework is put together. The configuration essentially maps interfaces to objects. This means. Frameworks & Libraries » LINQ » General
License: The Code Project Open License (CPOL)
Decoupled LINQ to SQL framework
By Shannon Deminick A decoupled LINQ to SQL framework using Dependency Injection (Unity) and Policy Injection (simple AOP). and I like this syntax as compared with LINQ to SQL.
Download source code . In this case. Since all of the objects in this framework will be aware of the IDataContext thanks to Dependency Injection. we can then add some "nice to have" methods to our entities such "Save" (example: Article. I suppose this article should really be called something like: Forcing LINQ to SQL to use interfaces. Frameworks & Libraries ATL MFC STL WTL COM / COM+ . Policy Injection framework (AOP). Both of these frameworks are found in the Microsoft Enterprise Library 4+. It contains all of the properties and methods that are found in the System.
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms. it is very easy to implement.Decoupled LINQ to SQL framework .DataContext and the System.004 21 times
Go!
Announcements
Write an iPhone Tutorial. or easily map to a completely different data layer.581 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Advanced
Job Board
Catalog
Help!
C#.CodeProject
7.Linq. LINQ. This way. The source project is based on NUnit tests.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation
Background
This article uses the following technologies that you'll need to be aware of: LINQ to SQL. By using Dependency Injection. which can be seen here. and Microsoft's simple version of AOP called Policy Injection. To make this work.Table<> classes.Linq.DataContext that can be implemented by custom classes:
Collapse Copy Code
http://www.Save().Data. we're going to ensure that only one DataContext (or. . Popularity: 1. It is a good example of combining dependency injection with Policy Injection. The reality is that Microsoft made it quite a bit hard to use Dependency Injection with LINQ to SQL since the most important things are not interfaced such as the System. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Advanced Search Add to IE Search
2 votes for this article. I use EntitySpaces quite a bit. ). which means it is very easy to change the type of object we want to be instantiated. IDataContext ) is used between any of the entities or services created. in our case.NET.com/KB/linq/LinqAndUnity2. we can rely on IoC to create objects that have all of their dependencies wired up. I've created an IDataContext interface.aspx[6/24/2010 2:36:53 PM]
. we define the data layer in the configuration file. we don't have to manage the scope of the DataContext manually.139.51 Rating: 5.Data.
15 (See All) 25 Aug 2008 15 Feb 2009 9.557 KB
Introduction
This article extends on the idea of a previous article which was a quick demo of combining dependency injection with LINQ to SQL. The framework defined in this article uses Microsoft's Dependency Injection framework called Unity. This allows for extremely easy method based logging and caching (amongst other things).Data.

and since this new constructor has more parameters than the default generated one. and Comments. Articles. Then. IArticle . it looks for the constructor with the most parameters. } void SubmitChanges(). so not all interface members have been implemented. In this article. it isn't IEnumerable<T> . params object[] parameters). Now. Each LINQ to SQL entity then needs to implement the model by creating a partial class for it.NET Dev Library ASP. we also have to define this:
Collapse Collapse Copy Code
public IEnumerableTable<T> GetITable<T>() where T : class { return new EnumerableTable<T>(this.aspx[6/24/2010 2:36:53 PM]
.com/KB/linq/LinqAndUnity2. and partial classes for Article . Setting up the data model classes Each entity model definition is created by creating an interface that simply defines the properties of the model. In this project. A class diagram of how the entities are setup:
http://www. params object[] parameters). the data structure that will be used will consist of Members.Data. string query. params object[] parameters). It was created simply to show that it can be done (it does work though :) To keep things simple. DbCommand GetCommand(IQueryable query). IMember . and Comment will need to be created to ensure that the LINQ to SQL classes implement these interfaces. Since the generated DataContext doesn't contain a definition for GetITable<T> .Decoupled LINQ to SQL framework .GetTable(typeof(T))). Unity knows that the IDataContext is a dependency).Linq. IEnumerable<TResult> Translate<TResult>(DbDataReader reader).GetITable<T>() to query the tables. This is because this method can not be implemented by other classes since there is no direct way to construct a System. a constructor method is created for the LINQ to SQL classes that has an IDataContext object as a parameter (when Unity constructs an object. I've created a simple XML data context (XDataContext ) to retrieve and store data in XML files instead of a SQL database. we need to make a partial class for the generated class and ensure it implements IDataContext .
Services
Product Catalog Job Board CodeProject VS2008 Addin
You'll notice that the GetTable<T> method is missing from the list.CodeProject
Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources Collapse
int ExecuteCommand(string command.codeproject. to get the functionality of both ITable and IEnumerable<T> into the IDataContext with one method. This has been kept quite simple. Microsoft did expose an interface of ITable which contains the basic methods required by the table. however.Table<> . and then having the interface inherit from IBaseEntity which exposes the dependency on the IDataContext and the basic methods that should be included in the entity such as Save and Delete . Setting up the DataContext In order to map the generated LINQ to SQL DataContext to the IDataContext . So. and cannot be used to write LINQ queries against. and still call all the ITable methods (such as InsertOnSubmit ) on the returned object. I've created another method:
Collapse Collapse Copy Code
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . IEnumerable ExecuteQuery(Type elementType. Member . IEnumerable<TResult> ExecuteQuery<TResult>(string query. ITable GetTable(Type type).NET 4 Web Hosting
IEnumerableTable<T> GetITable<T>() where T : class.
This method exposes a custom interface that extends ITable and IEnumerable<T> . IEnumerable Translate(Type elementType. This is essentially how we get around not being able to instantiate a LINQ Table<> object. and IComment will be manually created. MetaModel Mapping { get. }
The EnumerableTable class is literally just a wrapper class to expose both the ITable and IEnumerable<T> . in order for Dependency Injection to work. we can call IDataContext. DbDataReader reader).

codeproject. then the actual service as a class is created. A service interface needs to be created to define any data function that should take place. A class diagram of how the data services are setup:
Setting up the configuration The configuration section for Unity defines IoC containers. Once the interface is setup. the constructor for each service is created that has an IEntityServiceFactory object as its parameter.
http://www. This class inherits from BaseService<> which already defines the basic properties and methods required. and in each mapping.com/KB/linq/LinqAndUnity2. we can define it as a singleton. Since we only want one DataContext created for the LINQ to SQL container. Each container maps interfaces to real objects.aspx[6/24/2010 2:36:53 PM]
. For Dependency Injection to work. This maps the IDataContext to a singleton of the LINQ to SQL generated object.CodeProject
Setting up the service layer classes A service is setup to expose methods to interact with the data for each table. It then needs to inherit from the IBaseService<> which exposes the dependency on the IEntityServiceFactory (which in turn has a reference to the IDataContext and all other data services).Decoupled LINQ to SQL framework . we can define the lifetime of the object that Dependency Injection creates.

in this case.Comment. but then another custom class would need to be created to create the Unity objects.aspx[6/24/2010 2:36:53 PM]
. I wanted to keep the EntityServiceFactory as the basic object to use for the framework so that implementation of this framework didn't require any knowledge of Unity.Linq" mapTo="LinqUnity.IBaseService`1[[LinqUnity.IBaseService`1[[LinqUnity.. TheFarm. The default constructor for the EntityServiceFactory will load the container defined in the configuration file called DataLayer. LinqUnity]].
Collapse Collapse Copy Code
<!-. LinqUnity"/>
And finally.Service. LinqUnity"/> <type type="TheFarm. LinqUnity]]. LinqUnity"/> <type type="LinqUnity. LinqUnity]].Linq. a LinqUnity.Data. TQuery GetService<TEntity.
Using the code
http://www.Linq.Decoupled LINQ to SQL framework .Unity. LinqUnity" mapTo="LinqUnity.Linq" mapTo="LinqUnity. TQuery>() which creates a data service with the interface type of TQuery and the entity type of TEntity .Unity.Linq.CodeProject
Collapse Collapse
Copy Code
<type type="IDataContext" mapTo="LinqUnity.Data.Configuration.Data.
Collapse Collapse Copy Code
<type type="LinqUnity. you can pass a different container name to the overloaded constructor method.codeproject. To get this to work.com/KB/linq/LinqAndUnity2. LinqUnity" mapTo="LinqUnity.TypeInjectionElement.DataContext object when an IDataContext is requested.Model. it inserts itself into the container that it resolved from Unity at runtime as a singleton:
Collapse Collapse Copy Code
container. The XML configuration above does not define a mapping to the IEntityServiceFactory . TheFarm. In this case.Linq.Linq" mapTo="LinqUnity. when the EntityServiceFactory is constructed. Alternatively. TheFarm. LinqUnity"/>
The EntityServiceFactory object Now. etc. What this is actually doing is mapping IBaseService<T> to a real service.The mangled syntax is Microsoft's standard for generic types --> <type type="TheFarm.CommentService. the first mapping is mapping IBaseService<Member> to MemberService .. Each service is dependent on the IEntityServiceFactory because each service may need a reference to the IDataContext and potentially the other data services.Linq.IBaseService`1[[LinqUnity.Article.IMember. Microsoft. so in that case.Linq. the IoC container will give us a LinqUnity.Member. the generated LINQ to SQL classes. and so on.Model. With the above configuration in place. For example.Linq.Configuration"> <constructor/> <!-.Comment.Data.Data.Article object when an IArticle is requested.
The EntityServiceFactory implements IEntityServiceFactory which you'll notice is a property of the IBaseService<T> and therefore a dependency since it is a parameter of each data service constructor.Linq. LinqUnity"/> <type type="LinqUnity.Linq.Linq.Linq. entities are all dependent on the IDataContext .MemberService.Data.IComment.Practices.Service.DataContext.Ensure it is created with the default empty parameter constructor --> </typeConfig> </type>
Now we need to map our data model interfaces to real objects. the EntityServiceFactory class has been created which has some methods to get Unity to create these objects for us:
T CreateEntity<T>() which creates an new entity of the specified type. we need a way to get Dependency Injection to build all of the objects for us.
This mapping could be defined in the XML as well.Service. Dependency Injection wouldn't actually be able to wire up all of the objects. The syntax is really hard to look at.RegisterInstance<IEntityServiceFactory>(this. LinqUnity"> <lifetime type="singleton"/> <typeConfig extensionType="Microsoft. T BuildEntity<T>(T entity) which "re-wires" up an existing entity object with all of its dependencies. However.Member.Linq. we need to setup the data services. new ContainerControlledLifetimeManager()).ArticleService.Article. LinqUnity"/> <type type="TheFarm.Practices.IArticle. LinqUnity" mapTo="LinqUnity. but this is how Microsoft made the string syntax for defining generic types.Model.

ArticleId == 1). so we lose the additional extension methods available on the System.Factory. }
As stated in the beginning of this article. Where(x => x.Articles. our data service methods might look something like this:
Collapse Collapse Copy Code
public List<IMember> GetMemberStartingWith(char c) { return (from m in this. and is quite simple with three properties: CommentService .Wrap<IArticleService>(service). such as:
Collapse Collapse Copy Code
var article = myDataContext.Linq.codeproject. Exposing the data services The EntityServiceFactory includes the basic methods for creating services and entities with all of their dependencies wired up.GetTable<Article>().Table<T> object as compared to the IEnumerable<T> object.aspx[6/24/2010 2:36:53 PM]
.Linq.com/KB/linq/LinqAndUnity2. return PolicyInjection. In this example we'll use Policy Injection to get logging and caching happening at the method level by simply attributing the methods you want logged or cached. with LINQ to SQL. this class is called ServiceFactory . we change the above properties code to:
Collapse Collapse Copy Code
public IArticleService ArticleService { get { IArticleService service = this. one of the downfalls of this is that we're not querying directly against the System.Data. } }
Policy Injection Policy Injection is a simple AOP type of framework found in Microsoft's Enterprise Library. Each call to one of these properties will return a new service object created from Dependency Injection.GetITable<Article>(). IArticleService>().SingleOrDefault(). Instead. ArticleService .GetService<Article. In its most simple form. we would write queries based on the table properties generated on the DataContext .Data.StartsWith(c. however.CodeProject
Implementing the data services Normally. } }
http://www. IArticleService>().ToString()) select (IMember)m) .Decoupled LINQ to SQL framework .GetService<Article.SingleOrDefault()
This can't be done with this framework since neither Article s nor GetTable<T> are members of the IDataContext . and MemberService .Where(x => x.DataContext. we need to use the custom GetITable<T> method that has been created to expose an IEnumerable<T> object to query:
Collapse Collapse Copy Code
var article = myDataContext.ArticleId == 1).Name.Table<T> . To implement Policy Injection.
or:
Collapse Collapse Copy Code
var article = myDataContext. one of the properties may look like:
Collapse Collapse Copy Code
public IArticleService ArticleService { get { return this. a nicer implementation would be to extend this class and expose properties for accessing each of the data services.GetITable<Member>() where m. In this example.ToList().ArticleId == 1). Where(x => x.SingleOrDefault()
With the above syntax.

it simply will update all changes made. Though attributing is quite easy. it requires some entries in the configuration file (see the source code and Microsoft's documentation for more details):
Collapse Collapse Copy Code
[LogCallHandler(BeforeMessage = "Begin". for (int i = 0.CreateEntity<Member>().com". To cache the output of a method.SubmitChanges() . string email. the above is nicer to use :) LINQ to SQL doesn't have a nice way (as far as I know) to run an update on one entity or table.Save(). To save the changes to the DataContext . save the changes to the database.
Calling factory.CodeProject
Policy Injection requires that an object extends MarshalByRefObject . Since we've declared the IDataContext to be a singleton.codeproject. this means that we don't have to worry about which DataContext created which entity.Phone = phone. this has created a new Member object. "blah@blah..SubmitChanges() would also do the same thing (but I think. so the Save() method is really just a wrapper for the DataContext. newMember.com". 0)] public new List<IComment> SelectAll() { return base. and also called the InsertOnSubmit method of its corresponding member ITable . "12345676"). however. }
Now. //Create 20 new comments with the IMember and IArticle created above List<IComment> comments = new List<IComment>(). Logging is just as easy. i < 20.SelectAll() . Since all of our classes are interfaced. "Some description. "Some text. we can just call:
Collapse Collapse Copy Code
newMember..CreateNew("My Comment".
Behind the scenes.").DataContext. string phone) { Member member = this.CreateNew("Shannon".Email = email.MemberService ..Add(commentService. member. member. all you have to do is add the CachingCallHandler :
Collapse Collapse Copy Code
[CachingCallHandler(0. The configuration section for the logging application block will allow you to configure exactly what is logged and how it is formatted. member. without recompiling.ToList().DateCreated = DateTime.". However. since it will always be the same when it is resolved from the factory.Cast<IComment>() .aspx[6/24/2010 2:36:53 PM]
.. IArticle newArticle = articleService. "00000000"). AfterMessage = "End")] public IMember CreateNew(string name. you can configure Policy Injection in the configuration file as well to dynamically change what is cached. etc. return (IMember)member. Using the data services All you have to do to use the data services is create a ServiceFactory and access the properties to call the appropriate methods.Name = name. 5. IMember newMember = factory. link them together. i++) comments.com/KB/linq/LinqAndUnity2. This will create a new IMember :
Collapse Collapse Copy Code
ServiceFactory factory = new ServiceFactory(). the methods that are targeted still need to exist inside of an object that is wrapped or created with Policy Injection. newArticle))...Factory. This allows us to create different entities from different services. "blah@blah.CreateNew("My Name". or that it implements an interface containing the methods that will be used in Policy Injection. and after the method is called with the value of the returned object.CreateNew("My Article". this is really easy to do. member.Decoupled LINQ to SQL framework .Now. logged. //save all new comments to the database at once
http://www. }
The above will create a log entry before the method is called with the passed in parameter values. and not have to worry about any errors regarding mismatched DataContext s:
Collapse Collapse Copy Code
IMember newMember = memberService. the output of SelectAll() will be cached for 5 minutes.

so for this example. This probably comes with a bit of a performance overhead.DataContext. I've read in quite a few places that serializing LINQ to SQL entities to XML is not possible without some trickery.
Using Dependency Injection to map to alternate data contexts As mentioned in the beginning of this article. On another note. we have to use the BuildEntity<T> method of the EntityServiceFactory to wire up the dependencies for each object. all we have to do is construct the EntityServiceFactory with the name of the container. apart from the BuildEntity performance overhead. I've just implemented IXmlSerializable and custom serialized these objects. }
calls BuildEntity for each item returned from the data store.aspx[6/24/2010 2:36:53 PM]
. The XDataContext manages identity seeding and incrementing as well as tracking additions and deletions.DataContext. Using the EntityServiceFactory 's CreateEntity<T> method to create an entity automatically wires up the entity's dependencies with the IDataContext so that Save() and Delete() can be called. I've defined a second container in the configuration file which is exactly the same as the SQL container. they don't have their dependencies setup.com/KB/linq/LinqAndUnity2. I didn't create custom entities since the LINQ to SQL entities are quite simple to begin with and already take care of the entity relationships.
Points of interest
The "nice to have" methods such as Delete() and Save() that now exist on these entities also come with a catch.Decoupled LINQ to SQL framework . the IDataContext is mapped to this XDataContext instead of the LINQ to SQL DataContext . entities.BuildEntity<T>(x)). To use this other container. In order to get this working. the SelectAll() method:
Collapse Collapse Copy Code
public virtual List<T> SelectAll() { List<T> entities = (from x in Factory. is licensed under The Code Project Open License (CPOL)
About the Author
Shannon Deminick Shannon Deminick is the Technical Director of The Farm Digital.SubmitChanges(). along with any associated source code and files. I've created a test data context called XDataContext which stores data in XML files instead of a database.CodeProject factory. return entities.codeproject.
Occupation: Web Developer Company: Location: The Farm Digital Australia
Member
http://www. Best to download the source to see what is actually going on!
References
Here's more info on the technologies used: Dependency Injection with Unity Policy Injection Logging Application Block LINQ to SQL
License
This article. a Sydney based digital services agency.ForEach(x => Factory. however. this may come at a cost. It was also quite interesting trying to get around Microsoft's LINQ to SQL class structure to implement mock services without using some sort of type mocking library. Considering there may be hundreds or thousands of rows.GetITable<T>() select x). For example. when these entities are returned from a data source. However. However.
Conclusion
I thought this was quite a fun exercise to show off some really cool technologies.ToList(). there's negligible overhead as compared to running normal LINQ to SQL with a lot of iterations.

Win2003. PocketPC2002. Vista.6 KB
Introduction
How do I use disconnected entities with LINQ to SQL? Every developer that has used LINQ to SQL has asked this question in dismay.NET3.aspx[6/24/2010 2:37:22 PM]
. and multiple data contexts? All are normal questions when building a solid framework.000 members and growing! (19. Windows (WinXP. PLINQO makes it very simple to detach LINQ to SQL entities.699 19 times
Go!
Platforms.49 Rating: 5.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace
Detaching from a DataContext
To ensure that an entity is completely detached. LINQ. Here is a look at the Product entity and all of its dependencies in the DBML designer. Win7).CodeProject
7. JSON. Smith. we need to examine each child entity. . Frameworks & Libraries » LINQ » General
License: The Code Project Open License (CPOL)
LINQ to SQL . Dev. Architect. child entity list and every property that is delay or lazy loaded. Win2008. We are going to walk you through how this was implemented in PLINQO by showing how to manually add the detach functionality using LINQ to SQL.
Wanted
RackPalooza – Technical Job Fair at Rackspace Corporate Headquarters View this and other fine jobs on the Job Board.0. LINQ to SQL does not like to let go of its entities and does not see a reason that an entity should be disconnected from its context. .NET3.5). encapsulation. A RowVersion column of type timestamp has been added to each table in the Petshop database.0).Detach Entities
By Eric J. WinMobile6.Detach Entities .
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms. Where is the detach? How do I use these entities with services.codeproject.139.com/KB/linq/linq-to-sql-detach. Shannon Davidson Easily detach entities when using LINQ to SQL
Announcements
Write an iPhone Tutorial. LINQ to SQL uses the RowVersion column to perform an optimistic concurrency check and will not allow you to attach entities as modified to the datacontext without a RowVersion column or setting UpdateCheck to Never (default is Always ) on each entity property in the DBML.220.LINQ to SQL . Popularity: 3. We are going to walk through the process of implementing detach on the Product entity. Design Revision: Posted: Views: Bookmarked: 5 (See All) 12 Jul 2009 13.
Getting Setup
We will use the Petshop database to demonstrate detaching LINQ to SQL entities. WinMobile2003. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Articles / Quick Answers Articles / Quick Answers
Advanced Search Add to IE Search
5 votes for this article.182 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Lounge
C# (C#3. By default.
Download source .NET (.00 out of 5
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now. Frameworks & Libraries ATL MFC STL WTL COM / COM+ .
http://www.

A check to verify whether this event is being handled or not lets me know if the entity is attached to a datacontext . a check is made to verify that the entity is attached to a context. Also. this check eliminates the possibility of circular references causing any stack overflow issues.NET Dev Library ASP.
Now that the event handlers have been removed. but LINQ to SQL entities participate in the DataContext 's change notification through their PropertyChanged and PropertyChanging event handlers. PropertyChanging = null. To detach the Product entity.CodeProject
Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
Services
Product Catalog Job Board CodeProject VS2008 Addin
The Product entity contains all the different scenarios we have come across when detaching entities. Item is a child entity list and we have configured Descn to delay or lazy load. So. lists and the properties that are delay loaded. This means that the PropertyChanged and PropertyChanging events manage the attachment to the DataContext . changes for the entity are no longer being tracked. This might be considered a bit of a hack.codeproject.LINQ to SQL .
http://www. we aren't done detaching the Product entity. lists and the delay loaded properties. To implement the detach for all the child entities. we must create an
abstract base class called LinqEntityBase from which all the entities inherit. we will do that now. PropertyChanged = null. Category is a child entity. We will add the following method to the Product partial class:
Collapse Collapse Copy Code
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . no work needs to be done.Detach Entities . However. select "View Code" and a partial class is created for the Product entity. right click the Product entity in the LINQ to SQL designer.
Collapse Collapse Copy Code
if (null == PropertyChanging) return.
If the entity is not attached to a datacontext . We must detach all of its child entities.aspx[6/24/2010 2:37:22 PM]
.com/KB/linq/linq-to-sql-detach. If the entity is attached to a datacontext . then the event handlers for the PropertyChanging and PropertyChanged events are removed. The LINQ to SQL's DataContext tracks objects using the INotifyPropertyChanging and INotifyPropertyChanged interfaces. the first thing we need to do is create a Product partial class. PropertyChanged = null.
Collapse Collapse Copy Code
PropertyChanging = null. } }
First.NET 4 Web Hosting
partial class Product { public override void Detach() { if (null == PropertyChanging) return. To create the partial class for the Product entity.

Linq.
Collapse Collapse Copy Code
entity. return new System. the entity is detached and returned as the target of a new EntityRef instance. each entity requires its own Detach method specific to that entity.Data.HasLoadedOrAssignedValue || entity.EntitySet<TEntity> Detach<TEntity> (System.Data. Once we determine the entity has been loaded.Data. }
We must first determine if the entity has been loaded. Again.Detach Entities . The HasLoadedOrAssignedValue method tells us whether the entity has been loaded or not and we can avoid any lazy loading of entities. newSet.
Collapse Collapse Copy Code
partial class Product : LinqEntityBase
So.EntitySet<TEntity> set.Entity. // copy list and detach all entities var list = set.com/KB/linq/linq-to-sql-detach. detach_Products).Detach().Data. The ItemList property on the Product entity is an EntitySet .Linq. If the entity has not been loaded.Data.ToList().ForEach(t => t.codeproject. }
http://www. list.
Collapse Collapse Copy Code
partial class Category : LinqEntityBase { public override void Detach() { if (null == PropertyChanging) return.EntityRef<TEntity>(entity._Products = Detach(this. } }
All child lists for an entity must be detached as well.Data. this. the property is set to a new empty instance of EntityRef . We have implemented a Detach() method on each entity similar to the process we are using for the Product entity. Each of the other entities in the Petshop.Data. We will add the following method that specifically detaches a child entity to our LinqEntityBase class.Entity. onRemove).Assign(list). now let's look at what we need to do to detach a child entity.CodeProject
Collapse Collapse Copy Code
public abstract partial class LinqEntityBase { public abstract void Detach().Linq.
This line calls the Detach implementation that is specific in this case to the Category entity._Products. PropertyChanged = null. We have already seen the Detach() for the Petshop Product entity. PropertyChanging = null. return newSet. }
Since we will implement some base methods to take advantage of reuse that will need to use the Detach() method from each entity. Each ItemList in the EntitySet must be detached and the following method is needed to accomplish this. So.EntityRef<TEntity> Detach<TEntity> (System.dbml will need a detach specific to that entity. Product will now inherit LinqEntityBase .Linq. Action<TEntity> onRemove) where TEntity : LinqEntityBase { if (set == null || !set.aspx[6/24/2010 2:37:22 PM]
.Linq. Action<TEntity> onAdd.Linq.Data. The trick here is to not trigger any loading of entities.
Collapse Collapse Copy Code
protected static System.Entity == null) return new System.Detach().Detach()).
Collapse Collapse Copy Code
protected static System.LINQ to SQL .EntitySet<TEntity>(onAdd.EntityRef<TEntity>(). onRemove).EntitySet<TEntity>(onAdd. entity. an abstract detach method will be needed in the LinqEntityBase class.HasLoadedOrAssignedValues) return new System. attach_Products.Linq.EntityRef<TEntity> entity) where TEntity : LinqEntityBase { if (!entity.Entity). var newSet = new System.Linq.

child entity sets and delay loaded properties. HasLoadedOrAssignedValue is used to determine if the list has been loaded and avoids lazy loading the list._Items.
Collapse Collapse Copy Code
using using using using
System. this.Linq. we can complete the Product Detach method by adding a Detach call for the Category .Repositories { public class OrderRepository { public static Order Get(int orderId) { Order order = null._Descn).Data.Generic. detach_Items). this. System.Collections.
Any delay loaded properties also hold a connection to the datacontext and must be detached and uses the third and last Detach method we will need in the base class. check to see if the object has been loaded._Category). any delay loaded properties will need to be detached. PropertyChanged = null.
Collapse Collapse Copy Code
protected static System. We have setup a simple OrderRepository to get and save Orders.
namespace Detach. I have configured the
Descn property of the Product entity to be delay loaded. } }
Using Detach
One way to take advantage of detach and reattaching Linq to SQL entities is to use the repository pattern. ItemList and Desc property. Lastly. attach_Items. return new System.Link<T>). return a new instance of Link with the value of the object as the target of the instance. Each item in the ItemList must be detached and copied to a new EntitySet that is not attached to a datacontext .FirstOrDefault(o => o.Orders.Data._Descn = Detach(this._Items = Detach(this.Link<T>(value.aspx[6/24/2010 2:37:22 PM]
. order.
http://www. otherwise. By updating the DBML.Detach()._Category = Detach(this. if not return a default instance.HasLoadedOrAssignedValue) return default(System. using (var context = new PetshopDataContext()) { order = context.Linq. } return order. PropertyChanging = null.OrderId == orderId).Linq.codeproject.CodeProject
As we mentioned before.com/KB/linq/linq-to-sql-detach.LINQ to SQL .Detach Entities . System.Web.Value). Below is the complete Product Detach() method:
Collapse Collapse Copy Code
partial class Product : LinqEntityBase { public override void Detach() { if (null == PropertyChanging) return.Data. this. System.Linq.Link<T> Detach<T>(System.Data.Link<T> value) { if (!value. Now that we have added the necessary base methods for detaching the child entities.Linq. }
As has been the pattern.

Courier = "DHL". This means you do not have to worry about what is needed to detach your entities.BillToLastName = "Adams". order. order = OrderRepository. PLINQO has already taken care of it.BillCity = "Westfield".ShipToFirstName = "Gomez". order. order = OrderRepository. order. order.BillState = "NJ".
Collapse Collapse Copy Code
Order order = new Order().ShipState = "NJ". is licensed under The Code Project Open License (CPOL)
About the Authors
Eric J.BillAddr1 = "0001 Cemetery Lane". order. context. So.Detach(). order. order. DONE!
History
12 th July.DateTime.Orders. else context.codeproject. The entities are completely disconnected which means you are free to use the entities anywhere you like without worrying about the datacontext . The PLINQO detach takes all precautions when detaching from the datacontext and ensures an entity can be used in a disconnected manner. order. as the object graph gets more complicated.BillZip = "07090". The ability to use LINQ to SQL entities disconnected from the datacontext opens up many opportunities for encapsulation and reuse. it can be tricky to ensure that the entity is completely detached.LINQ to SQL .Save(order). PLINQO figures out all the necessary child objects. order. } return order.com/KB/linq/linq-to-sql-detach.ShipCity = "Westfield". order = OrderRepository. order.BillToFirstName = "Gomez".UserId = "gadams". The code below interacts with the repository and is not concerned with datacontext s or maintaining a connection to the database.Detach Entities .InsertOnSubmit(order).Orders.CodeProject } public static Order Save(Order order) { using (var context = new PetshopDataContext()) { if (order.TotalPrice = 0. along with any associated source code and files.Locale = "blah".aspx[6/24/2010 2:37:22 PM]
. order.Get(2).ShipToLastName = "Adams". order. Smith
Occupation: Architect Company: Location: CodeSmith Tools.ShipZip = "07090". lists and delay loaded properties that need to be detached and makes sure the proper detach methods for those entities are executed when detaching an entity from the datacontext .Attach(order. 2009: Initial post
License
This article.BillCountry = "US". order. order.
Conclusion
As you can see.ShipCountry = "NJ".Now. order = OrderRepository. order. order. The ability to detach makes the repository pattern possible with LINQ to SQL. order. true).Save(order).UserId = "gadams2". } } }
As you can see. order. it is a bit of work to detach an entity from a datacontext .OrderId > 0) context.SubmitChanges(). each of these methods uses its own datacontext. order.AuthorizationNumber = 1.ShipAddr1 = "0001 Cemetery Lane". order. order. LLC United States
http://www. order.TotalPrice = 150. order. There is no need to pass one datacontext around as a parameter or hold it in a module level variable. PLINQO generates detach methods similar to the one we just built for the Product entity for each entity.OrderDate = System. Also. how do you detach entities when using PLINQO? Call the Detach method.Save(order).

codeproject.com/KB/linq/linq-to-sql-detach.LINQ to SQL . FAQ Noise Tolerance
Medium Medium
Search
Layout
Normal Normal
Per page
25 25
Update
http://www.Detach Entities .CodeProject
Member
Shannon Davidson
Occupation: Architect Company: Location: CodeSmith Tools United States
Member
Article Top
Sign Up to vote for this article
You must Sign In to use this message board.aspx[6/24/2010 2:37:22 PM]
.

Insert(TKey key.--> <module> <!--Module mapping include module’s types.--> <methodlist> <!--Type’s method list. we define the input data. If this element is omitted. IntPtr lparam)
Our company uses the Dotfuscator tool. </fieldlist> </type>
You will notice that the obfuscated name is always placed in an optional <newname> element. Here is the call stack content example:
Collapse Collapse Copy Code
Services
Product Catalog Job Board CodeProject VS2008 Addin
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . its Community Edition is shipped with Visual Studio.
http://www.NET Dev Library ASP.ArgumentException Stack: at System.Timer.. The other big problem is that there is a huge number of XML elements with the same obfuscated name. had a chance to use this technology in practice.Collections.--> <mapping> <!--Mapping information.--> </method> . at last. maybe. Use LINQ to XML – this is a convenient and easy way to handle XML data.Timer.Forms.NativeWindow. To achieve our search goal. we should analyze a parent element and detect if it is a <type> element.Generic. so anyone can test this name resolving tool on their own code. Usually. The obfuscation map is an XML file whose structure looks like this:
Collapse Collapse Copy Code
<dotfuscatorMap version="1. For example.a() at ne. The UI should be as simple as possible – I think that simple tasks should not require complex user manipulations. Boolean add) at ne.NET 4 Web Hosting
Type: System.Forms.aspx[6/24/2010 2:38:27 PM]
. IntPtr wparam. Let’s move to the concrete steps.CodeProject
Windows Workflow Foundation Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
Things get worse when you need some syntax highlighting. and fields that have the obfuscated name ‘a’.1"> <header /> <!--Provides timestamp and version information.--> <newname>obfuscated_name</newname> <!--Obfuscated field name (Optional).Windows. System.ThrowArgumentException(ExceptionResource resource) at System. you should manually analyze the parent XML elements.--> <field> <signature>System. Int32 msg.--> .c(Object A_0. But.Forms.Callback(IntPtr hWnd.c(ErrorListEventArgs A_0) at ne. we search for the “<newname>a</newname>” string – this will find all the types.Button</signature> <!--Field signature..ThrowHelper. TValue value.OnTick(EventArgs e) at System.c(IError A_0) at ne...Windows.--> <type /> <!--Type name and members mapping.Forms.--> <newname>obfuscated_name</newname> <!--Obfusctaed type name (Optional). <type/> <!--Other type name and members mapping.com/KB/linq/DotfuscatorMapSeeker.--> <method> <signature>void(object. or even more – XML tree parsing/navigation.--> <name>ModuleName. </methodlist> <fieldlist> <!--Types filed list.
Task definition
Facing these problems. EventArgs A_1) at System.Dictionary.Leveraging LINQ to XML: Querying an obfuscation map . and to identify their type.--> </module> </mapping> <statistics /> <!--Some obfuscation statistics. methods.--> <newname>obfuscated_name</newname> <!--Obfusctaed method name (Optional).. the main factor was that I. we need to find a type with the obfuscated name “a”.c.Windows.TimerNativeWindow.--> <name>field_name</name> <!--Original field name.--> <name>method_name</name> <!--Original method name.--> </dotfuscatorMap>
The <type> element structure is the following:
Collapse Collapse Copy Code
<type> <name>type_name</name> <!--Original type name.Windows.codeproject. I decided to help our support team by automating the name resolving process.--> </field> .WndProc(Message& m) at System.dll</name> <!--Original module name. Obfuscation maps have the same format for all obfuscator editions. then the object uses its original name.EventArgs)</signature> <!--Method signature. we should define the user input. First. Next. There are about several thousand results in complex projects. The automation task requirements were the following: It should be a tool that allows finding original Class or Class member names based on an obfuscated name..

Descendants("type") // Declare name element variable.
map. it is not the most
effective solution.
The types collection is filtered by matching the type’s child element <newname> content with the passed obfuscated name. This collection includes child elements. grandchildren elements.aspx[6/24/2010 2:38:27 PM]
.CodeProject
Thus.Elements("module"). meaning that the actual access to the underlying XML will be performed when you first access the Descendants result rather then when you call this function. This method has an overload that allows filtering the output collection by specifying the matching element name filter. To avoid possible NRE.Element("newname"). For example.codeproject. var found = from type in types // Filter type elements by obfuscatedName matching. which returns all the <type> elements from the XElement content.
http://www. and etc. I prefer simplicity of the Descendants function.
This code will search types with obfuscated or original name matching obfuscatedName .Descendants("type") will scan the whole XML tree for the specified element type. This is an easy one:
Collapse Collapse Copy Code
// Construct the XElement to access map file.Where(t => t.xml").
Depending on the XML content. But for this application. This can also be done using the Where extension method with the lambda expression:
Collapse Collapse Copy Code
var found = types. where name.Elements("mapping"). var types = map. I used this overload to filter out all elements except the <type> . the <newname> element is optional.Leveraging LINQ to XML: Querying an obfuscation map .Load("sample. the filter name must have it too.
As stated before. Using direct element navigation that reduces the whole XML scan will be more productive. Note: The filter name should be a fully qualified name. a user usually uses two parameters: the first parameter is an obfuscation map file path. First. // Define the search criteria. XElement map = XElement. I’ve changed LINQ query to the following:
Collapse Collapse Copy Code
var found = from type in map. Let’s do it. it means that if the filtered elements have a namespace.Element("newname") ?? type.Element("newname").Element("name") // Filter type elements by obfuscatedName matching.Descendants("type") call.Descendants() . this expression can give us ten times performance boost than the Descendants call. The Descendants() method returns a plain collection of the descendant XML elements. we need to enlist all types from the map file. where type. According to the requirement of UI simplicity.Value == obfuscatedName select type.
The main operation here is the map. let name = type.Value == obfuscatedName select type. we can use such an expression:
Collapse Collapse Copy Code
var types = map.Elements("type"). I implement this using a LINQ query:
Collapse Collapse Copy Code
string obfuscatedName = "a". so Element("newname") returns null when the type is not obfuscated. Note: Keep in mind that Descendants uses deferred execution. but the simplest one. and need to find matches with the obfuscated name. if we write map. and the second parameter is an obfuscated name. Now we have all the <type> elements.Value == obfuscatedName). There is also one more (implicit) parameter – a search result type (type/method/field). we will get all XML elements enumeration from the map document. but we will try to infer this parameter from the second.Descendants("type"). I think this is enough. So.com/KB/linq/DotfuscatorMapSeeker.
Type name search
The first task is an original type name search using an obfuscated name.

This new variable is an anonymous type that consists of a current <type> element and a <name> /<newname> element.
The whole query can be represented in C# as:
Collapse Collapse Copy Code
IEnumerable<xelement> found = map.codeproject.MyInternalClass” name is presented by a “MyClass/MyInternalClass” string value.
After that. which (in conjunction with the anonymous type projection) will give us some performance penalty.WriteLine("Module: Console.Element("name")). {0}".g. TypeName = type.Descendants("type").
As we can see.'.” on “/” in the obfuscatedName variable to allow a match:
Collapse Collapse Copy Code
obfuscatedName = obfuscatedName.CodeProject
The let keyword introduces a new variable name that holds a <newname> element or a <name> element in case no <newname> element is present. where (type. type. output it to the console:
Collapse Collapse Copy Code
foreach (var type in types) { Console.aspx[6/24/2010 2:38:27 PM]
. so I rewrite the query to the following:
Collapse Collapse Copy Code
var found = from type in map.Value }.Type).Replace('.Name. Name = type.WriteLine().
At last. Something like that:
Collapse Collapse Copy Code
new { Type = type. Name = type. }
{0}".Element("newname") ?? type. there is the second Select function call. and you are welcome to post any questions or suggestions.
Summary
That’s it. Thanks for your time. along with any associated source code and files. Where(tn => tn.. I will step deeper into the LINQ queries by providing Fields and Methods name resolving solutions. Select(type => new { Type = type.Element("name") }. In the next part.com/KB/linq/DotfuscatorMapSeeker. type.WriteLine("Type: Console. is licensed under The Code Project Open License (CPOL)
About the Author
Alexander Yegorov
Occupation: Technical Lead Company: Location: Devart (www. We just need to replace “.
The next thing to do is to process complex type names.Element("name").Value == obfuscatedName select type.Element("name").Parent.Leveraging LINQ to XML: Querying an obfuscation map .ModuleName).Element("newname") ?? type. '/'). We have found types providing the obfuscated name.’.TypeName). the “MyClass.Element("newname") }). Select(tn => tn. In XML.Descendants("type") // Filter type elements by obfuscatedName matching.devart. we provide anonymous type projection that will help us to process the search results in C#:
Collapse Collapse Copy Code
var types = from type in found select new { ModuleName = type.com) Ukraine
http://www.Value. you can process the search result as you wish. e. for example.Value == obfuscatedName).
License
This article. these names are separated by ‘/’ instead of ‘.

Task definition
As previously defined, the user interface should be as simple as possible. So I decided to limit the UI with two editors. The first is used to provide the obfuscation map file name and the other one is for user search requests. Let's take a look at three possible user inputs used as search criteria: 1. a - this is, definitely, a search request for the obfuscated type "a ". 2. a.b - this is less clear - it could be a complex type name "a.b ", or a field named "b " of type "a ". We can't distinguish these cases, so we will search for both - a type and a field. 3. a.b(System.String, int) - this is, definitely, a method search request with a signature (string, int) . Note: The complete method signature contains a result type, but since .NET doesn't support overloads based on a result type, we don't need it. The member search task can be divided in two steps: 1. Type search. 2. Member search based on first step results. We already have a code to complete the first step. To use it, we just need to provide the obfuscated type name. Thus, a list of more detailed steps is: Detect what kind of input is used; Parse the input to separate type names and a member name; Parse the member name to acquire the method signature; Search for the types with the parsed type names; Search for the field or method among the found type members that match the parsed member name.

Member name search
To separate a type name from a member name, we can split the input string using a "." separator and take the last part as a member name. But as we can see dots also present in a method signature, I decided to replace the signature's dots with something else that allows me to use a dotted type name split. The code below replaces "." with "/" within the method signature, if any:
Collapse Collapse Copy Code

This code detects the signature presence by searching for the "(" symbol and replaces all "." with "/" inside the signature using the indexed Select extension method. Now, we can split the obfuscated name apart by simply calling the obfuscatedName.Split('.') method. The last substring can be the method signature or the field name. The method signature requires some additional parsing to detect the argument types, and the equality routine that will compare the signatures. I implemented this logic in the Signature class. To achieve code unity, I also represent Field search request using the Signature class - so any type member search request will be the Signature instance. Signature has three main members:

bool IsMethod - indicates if this is a method signature. bool MemberName - the name of the type member. bool SygEquals(string sig) - check if the passed signature string equals to the instance.
I will not provide the Signature class implementation details, because they are quite trivial and you can get them from the code sample at the start of the article. There are two kinds of types be to searched: The first is a complete type that covers the whole search string (e.g., type name "a.b" input is "a.b") The other one is an incomplete type that partially covers an input string except the last name element (e.g., Type name "a" input is "a.b"). This type is required for member search as we have defined earlier. Note: One more thing that I want to show is how to declare an anonymous type array before you fill it with data. For example, you want to declare an anonymous type array and fill it with data depending on some conditions and then process this array. In such a case, I use the next pattern:

Such an approach allows you not to define named types when you don't need them. I use this pattern to define an array of types to search with a flag that indicates if this type is a complete one.
Collapse Collapse Copy Code

The join operation used here allows me to provide an easy filtering upon the typeNames array. Anonymous type projection will be done for the query result. This projection contains the found type element and the flag indicating type completeness. To get all the found types, we can use a simple expression:
Collapse Collapse Copy Code

types.Where(t => t.IsComplete);

Note: LINQ queries use deferred execution basis; it means that the execution of a query is deferred until the moment you access the data, and even more - a query will be executed each time you access it. The next code demonstrates this behavior:
Collapse Collapse Copy Code

To avoid redundant executions, you can cache the query result by using the .ToArray() or .ToList() extension methods. I intend to use the result of the types query both in the complete type search and the type member search, so in the worst case, the types query will be executed twice. To avoid this, I cache the query result using the .ToArray() function. Now, when we have found the types, we can proceed to the type members search. The straight approach is to use the nested select :
Collapse Collapse Copy Code

Leveraging LINQ to XML: Querying an obfuscation map: Part 2 - CodeProject var members = // For each incoplete types from type in types where !type.IsComplete // Select all type methods that has matched name and signature. from member in type.TypeElement.Element("methodlist").Elements("method") where member.Element("newname").Value == signature.MemberName && signature.SygEquals(member.Element("signature").Value) select member;

Here, we select methods from incomplete types and filter them by matching the Signature objects. This query will be compiled to something like this:
Collapse Collapse Copy Code

Here, we can see the SelectMany call with an anonymous type projection. In the general case, each type element from the types array contains a collection of methods, so it looks like a two-dimensional collection that consists of types where each type holds a methods collection. The SelectMany call flattens this two-dimension collection to one-dimension, and then we filter it. This is a general case, but in our case, we will have a collection of types with collections of one method at most (because of our search filter). So, we have a redundant SelectMany call and an anonymous type projection that will impact the performance. We can't fix this issue using a clear LINQ syntax, but we can combine LINQ with extension methods and lambda expressions to achieve the desired result:
Collapse Collapse Copy Code

Here, I have combined LINQ select with the SingleOrDefault extension method call that allows me to remove the SelectMany call and the anonymous type projection. The last Where method call filters out the default values from the result - this will give us an empty enumeration if nothing is found. Here is what it will be compiled to:
Collapse Collapse Copy Code

you can process the found members as you wish. {0}". Now. e. but this is out of the article scope. member. for example. The first is that it is hard to provide code decomposition because of anonymous types that can't be used as method parameters. There are still many things to do.. for example. along with any associated source code and files. The other one is that LINQ query debugging is quite difficult (but thanks to LINQ Pad. the application can process the whole call stack and can retrieve its de-obfuscated version.WriteLine("Type: Console. output them to the console:
Collapse Collapse Copy Code
foreach (var member in membersPrj) Console.WriteLine("Member: Console.TypeName). All others are not so noticeable.Leveraging LINQ to XML: Querying an obfuscation map: Part 2 . and that the article material will bring you some new and useful experience that you can apply in your practice.WriteLine(). is licensed under The Code Project Open License (CPOL)
About the Author
Alexander Yegorov
Occupation: Technical Lead Company: Location: Devart (www. I hope you enjoyed reading. a command line or something like this.com/KB/linq/DotfuscatorMapSeeker_p2. {0}". it's handy to have some external API. }
{ {0}". member.CodeProject }. not as hard as it can be).g. This article describes my first experience of a practical use of the LINQ to SQL technology.
There is no difference in the method and field search due to the unified Signature class solution. also.MemberName). so I generalized this approach for both the fields and methods search.
The complete solution can be downloaded from the link at the top of the article.
Summary
That's all.WriteLine("Module: Console.codeproject.
License
This article.ModuleName). so I don't think them worth to be mentioned here. member.com) Ukraine
Member
http://www. I should also mention some issues that I have faced during development.devart.aspx[6/24/2010 4:03:39 PM]
.

Frameworks & Libraries ATL MFC STL WTL COM / COM+ .NET.
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now.CodeProject
7. For this example I was working on a web page that shows user roles and their permissions within my applictation.
Wanted
RackPalooza – Technical Job Fair at Rackspace Corporate Headquarters View this and other fine jobs on the Job Board. LINQ.aspx[6/24/2010 6:22:28 PM]
. Popularity: 0.00 out of 5 A Technical Blog article.139. Design Revision: 4 (See All) Posted: 14 Apr 2010 Updated: 15 Apr 2010 Views: 684 Bookmarked: 0 times Technical Blog
Go!
License: The Code Project Open License (CPOL)
Using LINQ To Filter By Object Type and Then Quantify
By Joe Rattz Using LINQ To Filter By Object Type and Then QuantifyEvery so often I run across a case where LINQ comes in so handy that I just want to post a quick article sharing some particular usage of it.0.Using LINQ To Filter By Object Type and Then Quantify .
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms.478 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Beginner
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Lounge
C# (C#3. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Articles / Quick Answers Articles / Quick Answers
Advanced Search Add to IE Search
1 vote for this technical blog. ASP. View entire blog here. All-Topics.00 Rating: 5.codeproject. .
Using LINQ To Filter By Object Type and Then Quantify
Every so often I run across a case where LINQ comes in so handy that I just want to post a quick article sharing some particular usage of it. C#4.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace
http://www.com/Articles/72227/Using-LINQ-To-Filter-By-Object-Type-and-Then-Quant. Below is a photo showing how the display looked before my added LINQ code. For this example I was working on a web page that shows user roles and their permissions within my appli
Announcements
Write an iPhone Tutorial. Dev.000 members and growing! (17.NET. Architect.0).

I decided I would filter the DataSet prior to assinging the GridView's DataSource property and calling its DataBind method. foreach (DataRow dr in ds.CodeProject
Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
My web application has other roles not listed in that image. my permissions have been filtered. The latter would be more efficient because I could stop checking once the first column had a true value. Please notice that the first column of the GridView will contain a string for the description of the permission and each subsequent column will contain a bool specifying if that role is approved for the permission.
Next comes my method to actually filter the unwanted rows. If I add another role to my application there will be another column added here and I wouldn't want to have to modify this code to accommodate the additional role. Just like that.DataSource = ds. Last I call the Any operator and provide a lambda expression returning true if any of the items is set to true. I then call the DataRow's Delete method if I got a false back from the LINQ query.codeproject. It's just that simple. The data is being displayed in an ASP. these permissions were irrelevant for these roles. Why not just use the Any operator? If any of the elements in the row's array of items is true. Here is the code.
Collapse Collapse Copy Code
private void FilterRows(DataSet ds) { bool someRoleHasPermission.OfType<bool>(). Using the Any operator instead of the All operator allows the code to stop once an item is true. It would have been very easy to enumerate through each row. but again. I then set the GridView's DataSource property and call the DataBind method.NET GridView that has been bound to a DataSet. if (!someRoleHasPermission) { dr.com/Articles/72227/Using-LINQ-To-Filter-By-Object-Type-and-Then-Quant. Nothing of note so far.Rows) { someRoleHasPermission = dr. Displaying them would only confuse my users and invite support questions. LINQ can make the code so much simpler.ItemArray.Using LINQ To Filter By Object Type and Then Quantify . Just call the OfType operator on the array of items. If every column is set to false. FilterRows(ds). I could use the All operator and make sure they are all set to false. This is why there are some permissions that none of the listed roles are approved for such as "Allow Access To Site/User/Role Maintenance". I then call the FilterRows method that will delete all the rows from the DataSet for which no role has approval for that row's permission. Here is one last image showing the GridView after calling the FilterRows method:
http://www. GetMyRoleData is just a dummy method to acquire the needed data and I do not show it in this example. if any column is true. I wanted to omit any rows from the display where at least one of the roles wasn't approved for it. After all. Very simple. this would be less efficient and require every item to be checked.DataBind(). ItemArray. then I don't want to delete the row. Alternatively. } } }
So. One thing not obvious from the image is that the number of roles is driven by the database and is not static. and then enumerate through each column of the row. I then call the OfType operator on the row's ItemArray to return only those items that are a bool. RoleComparisonView. For this page. RoleComparisonView.NET 4 Web Hosting
DataSet ds = GetMyRoleData(). This can easily be overcome though with the OfType operator. There is one last complication and that is the fact that the first column of each DataRow is a string for the permission description and not a bool for a role. Below is my code to call the method to filter the DataSet.aspx[6/24/2010 6:22:28 PM]
. Or. But instead of enumerating through each column and checking its value.Tables[0]. I enumerate through each DataRow in the DataTable's Rows collection.Delete(). To omit the rows. don't delete the row. which I did.NET Dev Library ASP. delete the row.
Collapse Collapse Copy Code
Services
Product Catalog Job Board CodeProject VS2008 Addin
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . and specify to only return elements of type bool.Any(b => b == true).

CodeProject
As you can see.com/Articles/72227/Using-LINQ-To-Filter-By-Object-Type-and-Then-Quant. LINQ can make so many chores just that much more simple.aspx[6/24/2010 6:22:28 PM]
. is licensed under The Code Project Open License (CPOL)
About the Author
Joe Rattz Author: Pro LINQ: Language Integrated Query in C# 2008 Co-author: Pro LINQ: Language Integrated Query in VB 2008
Occupation: Technical Lead Company: Location: Genuine Parts Company United States
Member
Article Top
Sign Up to vote for this article
http://www.Using LINQ To Filter By Object Type and Then Quantify .
License
This article. along with any associated source code and files.codeproject.

0). View entire blog here.codeproject.com/Articles/72475/LINQ-Single-vs-First. . On the other hand. Either because it “returned” not only your car or you happen to have more than one car in that parking lot. you want the SINGLE one specific car that’s yours. if you go to the parking lot to get your car. You go to the taxi line and catch the FIRST one.
License
http://www.39 Rating: 5. Imagine you want to catch a taxi. If your “query” “returns” more than one car.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace Windows Communication Foundation Windows Presentation Foundation
I've witnessed and been involved in several discussions around the correctness or usefulness of the Single method in the LINQ API. The most common argument is that you are querying for the first element on the result set and an exception will be thrown if there’s more than one element. Frameworks & Libraries ATL MFC STL WTL COM / COM+ . First . The First method should be used instead. you can only drive one car at once and you'll need to refine your “query”.0).LINQ: Single vs. the documentation for First states that it returns the first element of a sequence of arbitrary length.NET (.5. When you use the Single method in your code you are asserting that your query will result in a scalar result instead of a result set of arbitrary length. it actually returns THE single. . because it doesn't throw if the result set has more than one item.NET3. LINQ. In either case.00 out of 5 A Technical Blog article. Popularity: 2.
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms.CodeProject
7.0. First
Announcements
Write an iPhone Tutorial.
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now. Although the documentation for Single states that it returns a single.NET4. First
By Paulo Morgado LINQ: Single vs. specific element of a sequence of values. specific element of a sequence of ONE value. On the other hand.478 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Beginner
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Lounge
C# (C#3.000 members and growing! (17. no matter how many are there. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Articles / Quick Answers Articles / Quick Answers
Go!
Advanced Search Add to IE Search
3 votes for this technical blog.aspx[6/24/2010 6:25:01 PM]
.139.
Wanted
(LAMP) Software Engineer at Adknowledge View this and other fine jobs on the Job Board. it’s an exception. C#4. Dev Revision: 4 (See All) Posted: 13 Apr 2010 Views: 882 Bookmarked: 1 time Technical Blog
License: The Code Project Open License (CPOL)
LINQ: Single vs.

0).CreateDatabase method I've mentioned earlier uses SQL scripts to create the database and the tables inside it.CreateDatabase method only supports creating new databases. The same goes for live servers that need to be updated with a new product version. no complicated SQL scripts needed Simpler maintenance of a single model instead of two
Background
The LINQ to SQL Framework provides a simple and convenient way to communicate with databases. every feature that another member adds that requires a database structure change. Frameworks & Libraries ATL MFC STL WTL COM / COM+ . LINQ. streamline database changes between your team members Easier product installations and upgrades. SQL. ADO. This drawback may really slow a team down.exe) is a tool that provides database structure synchronization from a LINQ to SQL model. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Advanced Search Add to IE Search
8 votes for this article. Architect. or delete the database entirely and invoke the CreateDatabase method again.44 Rating: 4. forces the other members to run various scripts or perform manual changes on their own database.943 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Lounge
C# (C#3. The DataContext. It analyzes a LINQ to SQL model. when each member of the team has his own database for testing. Dev Revision: Posted: Updated: Views: Bookmarked:
Articles / Quick Answers Articles / Quick Answers
Platforms.codeproject.CodeProject
7.92 out of 5
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now. using simple C# classes and LINQ. This means that in order to modify an existing LINQ to SQL model over an existing database.NET. Frameworks & Libraries » LINQ » General
License: The Microsoft Public License (Ms-PL)
LINQ to SQL Database Synchronizer
By Dan-el Khen An open source utility that synchronizes your database structure with a LINQ to SQL model. Visual-Studio (VS2008).16 KB
Wanted
Senior Applications Developer-Microsoft at Wolters Kluwer-MediRegs View this and other fine jobs on the Job Board. .584 62 times
Go!
Announcements
Write an iPhone Tutorial.5). SQL-Server.com/KB/linq/LINQ_to_SQL_Database_Sync.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation Libraries Windows Powershell
Main Benefits
Single click O\R model synchronization Better team productivity. This tool uses the SQL Server Management Objects (SMO) library instead. I've stumbled upon a major drawback: The DataContext.140. compares it to an existing database structure and finally applies all of the necessary changes to the database structure. Windows.
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms.
http://www. It is important to note that this tool never deletes any data or structure. it simply adds missing tables. While working with it.aspx[6/24/2010 7:05:38 PM]
.NET (. without writing any sophisticated SQL commands.NET3. Popularity: 4. columns and indexes and modifies existing columns.
Open code in an online IDE CodePlex Project Download source .
4 (See All) 1 Jun 2009 1 Jun 2009 12.LINQ to SQL Database Synchronizer .
Introduction
LINQ to SQL Database Synchronizer (csdb. one must apply the structure changes manually on the database.399 members and growing! (18.60. It supports both creating new databases and updating existing ones.

Now we have all the data we need. and locating the corresponding database objects. So let's start off by loading the LINQ to SQL model:
Collapse Collapse Copy Code
Services
Product Catalog Job Board CodeProject VS2008 Addin
var asm = Assembly. } //Now we can synchronize the table columns.g..NET Dev Library ASP.aspx) DataContext class. and the other is the database model.g.exe Optional.microsoft. simply specify an assembly and a type name of a LINQ to SQL DataContext class and run it.MyDataContext. the tool will update the existing database structure.LoadFrom(Options. //Load an assembly file var type = asm. //Find the table if (table == null) //If the table //doesn't exist { table = new Table(Database. out schemaName. }
Review this in an online IDE here and here.Tables[tableName. and it's simply a matter of iterating the LINQ to SQL model.GetType(Options.Add(table). Now. we simply create them.com/en-us/library/bb425822.aspx[6/24/2010 7:05:38 PM]
.TableName. the tool will create the database if it doesn't exist.GetTables()) { string schemaName. out tableName).exe /assembly:[AssemblyFile] /type:[TypeName] /autocreate /autoupdate /cs:[ConnectionString] /dbfilename:[DatabaseFilename] /assembly:[AssemblyFile] /type:[TypeName] The assembly filename that contains the LINQ to SQL (http://msdn.one is the LINQ to SQL model. //Parse the //connection string var server = new Server(sb.TypeName. //Get the database
Review this in an online IDE here and here. schemaName). The type name of the LINQ to SQL (http://msdn.Tables. If they don't exist.: bin\debug\MyApp.
/autocreate /autoupdate
http://www. string tableName. we'll load the database model using SMO and the connection string we have. //Load the LINQ to SQL mapping //model from the specified type
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . any class that inherits from the DataContext class will be processed. //Connect to the //database server var db = server. the latest version of it can be found at the Microsoft SQL Server 2008 Feature Pack download page.: MyApp.NET 4 Web Hosting
Review this in an online IDE here and here.exe). //Split the schema //and table name var table = Database.microsoft.DataSource). When specified.com/en-us/library/bb425822.
Prerequisites
This tool uses SQL Server Management Objects (SMO). true).com/KB/linq/LINQ_to_SQL_Database_Sync. schemaName].
Using the Code
This tool works as a simple command line tool (*.GetModel(type).aspx) DataContext class. tableName. ParseFullTableName(mt.
Collapse Collapse Copy Code
foreach (MetaTable table4 in model.AssemblyFile). e.msi
Usage
This tool is used like any standard command line tool.CodeProject
LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
Inside the Code
We basically have two models we want to synchronize . with the following syntax:
Collapse Collapse Copy Code
csdb.
Collapse Collapse Copy Code
var sb = new SqlConnectionStringBuilder(ConnectionString). if not provided..codeproject. When specified.msi SharedManagementObjects. //Create the table Database. //Find the DataContext class //using reflection var model = new AttributeMappingSource().InitialCatalog]. e. but here are the direct links: SQLSysClrTypes.Databases[sb.LINQ to SQL Database Synchronizer . otherwise we verify their definition. your database structure will be synchronized by this model.

The connection string of the database to be synchronized. you can add a column. Run the SyncMyNorthwindDb. An online IDE. In this way. along with any associated source code and files. etc. allow null s on a column.bat batch file to create the database Run the Samples project To change the LINQ to SQL model and synchronize the database: Modify the LINQ to SQL model by modifying the MyNorthwind.\SQLEXPRESS.
Points of Interest
I think that this tool answers a basic need when using the LINQ to SQL Framework. Read the command line arguments from an external text file. To run the sample: Install the prerequisites .exe" /type:"MyApp. Build the Samples project to reflect your changes. Create a batch file that executes this tool. and include it in your project. modifying the database is as simple as adding a property.
Running the Sample
The sources include a sample project with a LINQ to SQL model of the well known Northwind database. change the data type of a column. run this batch file as a post-build step in your project file.dbml file using Visual Studio. SharedManagementObjects. In the early stages of development.msi.SQLSysClrTypes.
Collapse Collapse Copy Code
csdb.LINQ to SQL Database Synchronizer ..
License
This article. is licensed under The Microsoft Public License (MsPL)
About the Author
Dan-el Khen Dan-El is a co-founder of CodeRun.Initial Catalog=MyDb. add a table. if not provided.MyDataContext class located in the MyApp..aspx[6/24/2010 7:05:38 PM]
.exe /assembly:"bin\debug\MyApp.CodeProject /cs:[ConnectionString] Optional.msi Open the solution and build it Run the Samples\SyncMyNorthwindDb.
Occupation: Architect Member Company: Location: CodeRun United States
http://www. We use it extensively at CodeRun in both development and deployment.codeproject.com/KB/linq/LINQ_to_SQL_Database_Sync.
@[Arguments Filename]
Example
This statement will synchronize (create and update) the MyDb database in the local machine's SqlExpress instance using the MyApp.bat batch file to synchronize your database. the default connection string will be used if it exists in your settings.MyDataContext" /autocreate /autoupdate /cs:"Data Source=.Integrated Security=True"
Recommendations
Specify both the /autocreate and /autoupdate options for maximum automation.exe assembly.

4.CodeProject
Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
2. like this:
Collapse Collapse Copy Code
IEnumerable<Product> products =
http://www.NET Dev Library ASP. set.sln file in Visual Studio.codeproject. Read the file into an IEnumerable<T> using the CsvContext object's Read method. Create a CsvFileDescription object. In your project. 5. OutputFormat = "C")] public decimal Price { get. Create a CsvContext object:
Collapse Collapse Copy Code
CsvContext cc = new CsvContext(). Compile the solution. } [CsvColumn(FieldIndex = 3. and with both US and international (Dutch) dates and numbers. with these elements: a. Although this example only uses properties. set. set. It will look like this:
Collapse Collapse Copy Code
CsvFileDescription inputFileDescription = new CsvFileDescription { SeparatorChar = '.'. You'll need that file to use the library in your own projects. how it should be written to an output file. 3. b.aspx[6/24/2010 7:14:47 PM]
. You'll find that the sources are organized in a solution. 3. etc. you could read into an IEnumerable<Product> . set. The optional CsvColumn attribute allows you to specify whether a field/property is required. 2. The file will be read into an IEnumerable<T> . } [CsvColumn(FieldIndex = 4)] public string Country { get. where T is a data class that you define. } }
With this definition. class Product { [CsvColumn(Name = "ProductName". Open the LINQtoCSV\Sources\Source. using System.both CSV and tab delimited. The data records read from the file will be stored in objects of this data class. 6. CanBeNull = false. Project LINQtoCSV is the actual library.NET 4 Web Hosting
using LINQtoCSV. tab. set. Import the LINQtoCSV namespace at the top of the source file where you'll be reading the file:
Collapse Collapse Copy Code
using LINQtoCSV.
Services
Product Catalog Job Board CodeProject VS2008 Addin
Quick Start
Reading from a file
1. } [CsvColumn(FieldIndex = 5)] public string Description { get. and a lot more (full details). whether the first record in the file holds column names. d.
This allows you to specify what character is used to separate data fields (comma. c.dll file in the Sources\LINQtoCSV\bin directory. etc. Just make sure your fields/properties are public.LINQ to CSV library . Project SampleCode has the sample code shown in this article.
4. FirstLineHasColumnNames = true }. Full details are available here. } [CsvColumn(FieldIndex = 2. and initialize it with details about the file that you're going to read.com/KB/linq/LINQtoCSV.dll you generated during Installation. the library methods will recognize simple fields as well. This will produce a LINQtoCSV. add a reference to the LINQtoCSV. The directory TestFiles within the TestConsoleApplication project contains test files .). The code is heavily documented. Project TestConsoleApplication is a working console application that exercises most of the features of the library.
It is this object that exposes the Read and Write methods you'll use to read and write files. OutputFormat = "dd MMM HH:mm:ss")] public DateTime LaunchDate { get. FieldIndex = 1)] public string Name { get. You could define a data class along these lines:
Collapse Collapse Copy Code
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts .

p. such as a List<T> . // or . OutputFormat = "dd MMM HH:mm:ss")] public DateTime LaunchDate { get. IEnumerable<Product> products = cc. } [CsvColumn(FieldIndex = 5)] public string Description { get. }
To make it easier to get an overview. set. CsvContext cc = new CsvContext().Description }.. Make sure the data is stored in an object that implements IEnumerable<T> . you can also use simple fields. p. OutputFormat . The Write method will happily use an anonymous type for T .Read<Product>("products. Import the LINQtoCSV namespace at the top of the source file where you'll be writing the file:
Collapse Collapse Copy Code
using LINQtoCSV..codeproject. p. 1.. var productsByName = from p in products orderby p. } [CsvColumn(FieldIndex = 2. set. 3. foreach (Product item in products) { .com/KB/linq/LINQtoCSV. 2..csv into the variable products .'. you'll see an example of this. class Product { [CsvColumn(Name = "ProductName". You can now access products via a LINQ query. // or .. OutputFormat = "C")] public decimal Price { get. here is the code again that reads from a file.) are available here. Details for all CsvColumn properties (CanBeNull . } [CsvColumn(FieldIndex = 3. inputFileDescription).LINQ to CSV library . set. The Write method takes a IEnumerable<T> and writes each object of type T in the IEnumerable<T> as a data record to the file.csv". add a reference to LINQtoCSV. you obviously won't define T yourself. In your project. which is of type IEnumerable<Product> .Name select new { p. // Data is now available via variable products. Although this example only uses properties.CodeProject cc.Name. } }
The optional CsvColumn attribute allows you to specify such things as what date and number formats to use when writing each data field. The definition of your data class could look like this:
Collapse Collapse Copy Code
using LINQtoCSV.LaunchDate.. a foreach loop. CanBeNull = false.Read<Product>("products.Name select new { p. etc. using System.
4.
This reads the file products.csv".Name.aspx[6/24/2010 7:14:47 PM]
. p. etc. }
You'll find this same code in the SampleCode project in the sources. In that case. but now in one go:
Collapse Collapse Copy Code
CsvFileDescription inputFileDescription = new CsvFileDescription { SeparatorChar = '. p.Price.dll. inputFileDescription). set. foreach (Product item in products) { .
Writing to a file
This is very similar to reading a file.Description }. } [CsvColumn(FieldIndex = 4)] public string Country { get.. FirstLineHasColumnNames = true }.Price.:
Collapse Collapse Copy Code
var productsByName = from p in products orderby p.
http://www. p. set. so you can write the output of a LINQ query right to a file. 7.. Later on.. or the IEnumerable<T> returned by the Read method.. FieldIndex = 1)] public string Name { get.LaunchDate.

Write( products2. but now in one go:
Collapse Collapse Copy Code
List<Product> products2 = new List<Product>().
This writes the Product objects in the variable products2 to the file "products2. // Fill the list with products // .LaunchDate. The Write method then writes those objects to the file products-Netherlands. "products2.Price.com/KB/linq/LINQtoCSV. Here is the code again that writes a file. Invoke the Write method exposed by the CsvContext object to write the contents of your IEnumerable<T> to a file:
Collapse Collapse Copy Code
cc.
http://www..csv".Country == "Netherlands" select new { p. // tab delimited FirstLineHasColumnNames = false.Write( productsNetherlands. and Description . p.csv". CsvContext cc = new CsvContext(). // LINQ query returning IEnumerable of anonymous type // into productsNetherlands var productsNetherlands = from p in products where p. Create a CsvFileDescription object. outputFileDescription). "products2..Description }. CsvContext cc = new CsvContext().
Here. outputFileDescription). Price . // Write contents of productsNetherlands to file cc.. "products-Netherlands. CsvFileDescription outputFileDescription = new CsvFileDescription { SeparatorChar = '\t'.csv". a LINQ query selects all products for "Netherlands" from the variable products . LaunchDate .. and initialize it with details about the file you will be writing..
Writing an IEnumerable of anonymous type
If you have a LINQ query producing an IEnumerable of anonymous type.codeproject.LINQ to CSV library . // no column names in first record FileCultureName = "nl-NL" // use formats used in The Netherlands }.csv".aspx[6/24/2010 7:14:47 PM]
... p.Name. p. // no column names in first record FileCultureName = "nl-NL" // use formats used in The Netherlands }.
5.CodeProject
Collapse Collapse Copy Code
List<Product> products2 = new List<Product>().
6. Create a CsvContext object:
Collapse Collapse Copy Code
CsvContext cc = new CsvContext().
7. along these lines:
Collapse Collapse Copy Code
CsvFileDescription outputFileDescription = new CsvFileDescription { SeparatorChar = '\t'.. // Fill the list with products // . outputFileDescription).Write( products2. // tab delimited FirstLineHasColumnNames = false.csv. and returns an IEnumerable holding objects of some anonymous type that has the fields Name . writing that IEnumerable to a file is no problem:
Collapse Collapse Copy Code
CsvFileDescription outputFileDescription = new CsvFileDescription { . }. cc.

string fileName) Write<T>(IEnumerable<T> values. CsvFileDescription fileDescription) Write<T>(IEnumerable<T> values. which you then pass to the Read or Write method. then update the file. the file is not yet opened. A CsvFileDescription object has these properties:
SeparatorChar QuoteAllFields FirstLineHasColumnNames EnforceCsvColumnAttribute
http://www. Each time you retrieve a new object from the Enumerator (for example. CsvFileDescription fileDescription)
Some interesting facts about these overloads: None of the overloads return a value. while looping through a foreach ). However. using the default values for the CsvFileDescription properties. When you close the Enumerator (for example. When the Enumerator is retrieved from the IEnumerable<T> (for example. Overloads that do not take a CsvFileDescription object simply create one themselves. to get the IEnumerable<T> .LINQ to CSV library . the compiler will generate a parameterless constructor for you. If using a stream. This prevents lengthy parameter lists. when starting a foreach loop). TextWriter stream) Write<T>(IEnumerable<T> values. such as whether the first record contains column names. CsvFileDescription fileDescription) Read<T>(StreamReader stream) Read<T>(StreamReader stream. Those that take a file name read the data from the file. Overloads that take a stream write the data to the stream. CsvFileDescription fileDescription)
Some interesting facts about these overloads: Each overload returns an IEnumerable<T> . This means that: If reading from a file.com/KB/linq/LINQtoCSV. when a foreach ends or when you break out of it).
Deferred Reading
Here is how the Read overloads implement deferred reading: When you invoke the Read method (which returns an IEnumerable<T> ). the file is closed.
CsvFileDescription
The Read and Write methods need some details about the file they are reading or writing.aspx[6/24/2010 7:14:47 PM]
. no data is read yet.CodeProject
CsvContext. Overloads that take a stream read the data from the stream. the stream is left unchanged. If using a file. T must have a parameterless constructor. Unlike the Read method.Write Overloads
Write<T>(IEnumerable<T> values. a new record is read from the file or stream. Write does not require that T has a parameterless constructor. the file is opened for reading. If using a stream.codeproject.Read Overloads
Read<T>(string fileName) Read<T>(string fileName. the stream is rewound (seek to start of the stream). If you do not define a constructor for T . and allows you to use the same details for multiple files. You only need to call Read once at the beginning. you put those details in an object of type CsvFileDescription . TextWriter stream. using the default values for the CsvFileDescription properties. then access the IEnumerable<T> again in a foreach loop to pick up the new data. As shown in the Reading from a file and Writing to a file examples. Those that take a file name write the data to the file. etc. see the section on deferred reading. The file can be updated in between accesses.
CsvContext. string fileName. You could access the IEnumerable<T> in a foreach loop. the file will be open for reading while you're accessing the IEnumerable<T> in a foreach loop. Overloads that do not take a CsvFileDescription object simply create one themselves.

csv".for example.aspx[6/24/2010 7:14:47 PM]
.
FirstLineHasColumnNames
Type: Default:
bool true
Applies to: Reading and Writing Example:
Collapse Collapse Copy Code
fd. // use tab delimited file CsvContext cc = new CsvContext().
EnforceCsvColumnAttribute
Type: Default:
bool false
Applies to: Reading and Writing Example:
http://www.'
Applies to: Reading and Writing Example:
Collapse Collapse Copy Code
CsvFileDescription fd = new CsvFileDescription().CodeProject
FileCultureName TextEncoding DetectEncodingFromByteOrderMarks MaximumNbrExceptions SeparatorChar
Type: Default:
char
'. // first record does not have column headers
When reading a file. Write surrounds all data fields with quotes. This would be a comma for CSV files. "file.Write(data. When true .QuoteAllFields = true. // forces quotes around all fields
When false . to avoid confusion . When writing a file. fd. tells Read whether to interpret the data fields in the first record in the file as column headers.
The character used to separate fields in the file.com/KB/linq/LINQtoCSV. cc.SeparatorChar = '\t'. or a '\t' for a tab delimited file. except for white space characters or the double quote (").codeproject.
QuoteAllFields
Type: Default:
bool false
Applies to: Writing only Example:
Collapse Collapse Copy Code
fd. when the field contains the SeparatorChar or a line break.LINQ to CSV library . Write only puts quotes around data fields when needed. tells Write whether to write column headers as the first record of the file.FirstLineHasColumnNames = false. fd). You can use any character you like.

DetectEncodingFromByteOrderMarks
Type: Default:
bool true
Applies to: Reading only Example:
Collapse Collapse Copy Code
fd. spreadsheets) that access your files. as shown in the example. if you write files with the Euro symbol. Write only writes the contents of public fields and properties with the [CsvColumn] attribute. However.DetectEncodingFromByteOrderMarks = false. // use Unicode character encoding
If the files that you read or write are in English. And. there is no need to set TextEncoding . if your system uses French-Canadian (fr-CA).UTF8
Applies to: Reading and Writing Example:
Collapse Collapse Copy Code
fd. if you use languages other than English. // only use fields with [CsvColumn] attribute
When true .com/KB/linq/LINQtoCSV. the library uses the current language/country setting on your system. The default normally works fine.FileCultureName = "en-US". By default. the way the characters in your files are encoded may be an issue. all public fields and properties are used.codeproject. Specifically. and to tell Write how to write dates and numbers to the file. You will want to make sure that the encoding used by the library matches the encoding used by any other programs (editors. 23 May 2008 is 5/23/2008 in the United States (en-US) and 23/5/2008 in Germany (de-DE). Use the FileCultureName field to tell Read how to interpret the dates and numbers it reads from the file. // suppress encoding detection
Related to TextEncoding . the library uses that culture unless you override it with FileCultureName .
TextEncoding
Type: Default:
Encoding Encoding. The library uses the same culture names as the . Read only reads data fields into public fields and properties with the [CsvColumn] attribute.
http://www.Unicode. you may need to use Unicode encoding.
FileCultureName
Type: Default:
string
current system setting
Applies to: Reading and Writing Example:
Collapse Collapse Copy Code
fd.EnforceCsvColumnAttribute = true.CodeProject
Collapse Collapse Copy Code
fd. ignoring all other fields and properties. When false . // use US style dates and numbers
Different cultures use different ways to write dates and numbers.aspx[6/24/2010 7:14:47 PM]
.NET "CultureInfo " class (full list of names). So.LINQ to CSV library .TextEncoding = Encoding.

com/KB/linq/LINQtoCSV.MaximumNbrExceptions = -1. To not have any limit and read the entire file no matter how many exceptions you get. set
AggregatedException to -1. Otherwise.
CanBeNull
Type: Default:
bool true
Copy Code
Applies to: Reading only
Collapse Collapse
[CsvColumn(CanBeNull = false)] public DateTime LaunchDate { get. Use the Name property to specify another name for the data field. set. }
The Read and Write methods normally assume that the data fields in the file have the same names as the corresponding fields or properties in the class.
For details about aggregated exceptions.
CsvColumn Attribute
As shown in the Reading from a file and Writing to a file examples. Use of the CsvColumn attribute is optional. you can decorate the public fields and properties of your data class with the CsvColumn attribute to specify such things as the output format for date and number fields.CodeProject
Tells Read whether to detect the encoding of the input file by looking at the first three bytes of the file.codeproject. They will then simply use the defaults shown with each CsvColumn property below. }
http://www. set. those methods will look at all public fields and properties in the data class.
MaximumNbrExceptions
Type: Default:
int
100
Applies to: Reading only Example:
Collapse Collapse Copy Code
fd. The CsvColumn attribute has these properties:
Name CanBeNull NumberStyle OutputFormat FieldIndex Name
Type: Default:
string
Name of the field or property
Applies to: Reading and Writing Example:
Collapse Collapse Copy Code
[CsvColumn(Name = "StartDate")] public DateTime LaunchDate { get. // always read entire file before throwing AggregatedException
Sets the maximum number of exceptions that will be aggregated into an AggregatedException .LINQ to CSV library . it uses the encoding given in the TextEncoding property. see the error handling section. As long as the EnforceCsvColumnAttribute property of the CsvFileDescription object you pass into Read or Write is false .aspx[6/24/2010 7:14:47 PM]
.

}
Allows you to determine what number styles are allowed in the input file (list of options). Unfortunately though.
NumberStyle
Type: Default:
NumberStyles NumberStyles. Field and properties without FieldIndex get written last. In order to accept hexadecimal numbers that do not start with 0x. all styles are permitted.MaxValue
Applies to: Reading only Example:
Collapse Collapse Copy Code
[CsvColumn(FieldIndex = 1)] public DateTime LaunchDate { get.
OutputFormat
Type: Default:
string
"G"
Applies to: Writing only Example:
Collapse Collapse Copy Code
[CsvColumn(OutputFormat = "dd MMM yy")] public DateTime LaunchDate { get. Read needs to look at the order of the data fields in the data records to match them with the fields and properties in the data class. then the Read method generates a MissingRequiredFieldException exception. The FieldIndex s do not have to start at 1. }
Lets you set the output format of numbers and dates/times.The Write method uses the FieldIndex of each field or property to figure out in what order to write the data fields to the output file.NET framework does not provide a way to reliably retrieve that order from the class definition. set. set. the . So.
FieldIndex
Type: Default:
bool Int32. set. that's easy . Writing .
http://www. but in slightly different ways.aspx[6/24/2010 7:14:47 PM]
. The Read and Write methods will simply assume that a field/property comes before some other field/property if its FieldIndex is lower. The default "G" format works well for both dates and numbers most of the time. }
This property is used for both reading and writing. use NumberStyles. By default. in random order.The Read method needs to somehow associate data fields in the input file with field and properties in the data class. However. If the file has column names in the first record. Reading .HexNumber)] public DateTime LaunchDate { get.LINQ to CSV library .CodeProject
If false .HexNumber .Any
Applies to: Reading of numeric fields only Example:
Collapse Collapse Copy Code
[CsvColumn(NumberStyle = NumberStyles. and a record in the input file does not have a value for this field or property. except for one special case.Read simply matches the column names with the names of the fields and properties in the data class. as shown in the example.com/KB/linq/LINQtoCSV. if the file does not have column names in the first record.codeproject. you have to specify which field/property comes before which field/property by giving the fields and properties a CsvColumn attribute with the FieldIndex property. They don't have to be consecutive.

the Exception class exposes the Data property. the final value written to the output file will be "11/23/08" if you use US dates (FileCultureName is set to "en-US"). The description for each exception (further below) shows what information is stored in the Data property. with the list of exceptions in its Data["InnerExceptionsList"] property. etc.com/KB/linq/LINQtoCSV. it does not throw an exception right away. To show how to use the Data property. the field written to the file will be "23 Nov 08". but also when you access the IEnumerable<T> that is returned by the Read method. the final result depends on the language/country of the file. with the list of exceptions aggregated so far. as set in the
FileCultureName property of the CsvFileDescription object. in the example above. it first processes column names. set. but "23/11/08" if you use German dates (FileCultureName is set to "de-DE"). Aggregating exceptions When the Read method detects an error while reading data from a file. This allows you to fix all problems with an input file in one go.LINQ to CSV library . double .aspx[6/24/2010 7:14:47 PM]
.NET class Exception . if LaunchDate is 23 November 2008
and you specify the short date format:
Collapse Collapse Copy Code
[CsvColumn(OutputFormat = "d")] public DateTime LaunchDate { get. You can limit the number of exceptions that get aggregated this way by setting the MaximumNbrExceptions property of the CsvFileDescription object that you pass to the Read method. Then. while they provide error messages targeted at humans via the Message property. they throw an exception with all information you need to solve the problem. When the limit is reached. the AggregatedException is thrown right away. if LaunchDate is 23 November 2008. Deferred reading Keep in mind that due to deferred reading.CodeProject
When writing a date/time or number field. By default. If something goes wrong during that preliminary stage. So. Retrieving error information In addition to such properties as StackTrace and Message .codeproject. after it has processed the file. it throws an exception right away. Format Codes for DateTime Standard Numeric Format Strings Custom Numeric Format Strings
Error Handling
Exception
LINQtoCSVException BadStreamException CsvColumnAttributeRequiredException DuplicateFieldIndexException RequiredButMissingFieldIndexException ToBeWrittenButMissingFieldIndexException NameNotInTypeException MissingCsvColumnAttributeException TooManyDataFieldsException TooManyNonCsvColumnDataFieldsException MissingFieldIndexException MissingRequiredFieldException WrongDataFormatException AggregatedException
When the Read and Write methods detect an error situation. etc. The Read and Write methods use that property to provide exception information in a way that is easy for your code to read. with the given OutputFormat . With many formats. all exceptions are derived from the . the Write method first determines the type of the field (DateTime . As you would expect. decimal . Example The following code reads a file and processes exceptions. So. but stores it in a list of type List<Exception> . MaximumNbrExceptions is set to 100. it throws a single exception of type AggregatedException . Not all exceptions get aggregated! Before Read starts reading data from a file. it includes
http://www. }
Then. CsvColumn attributes.) and then calls the ToString method for that type. you can get exceptions not only when you invoke the Read method. instead of one by one.

Message). // Actual FieldIndex that the two fields have in common int commonFieldIndex = Convert. or does not support Seek ....Data["FieldName2"]).in this case "Product" string typeName = Convert..Data["Index"]).. However.ToString(dfie..ToString(dfie. if EnforceCsvColumnAttribute is false .. // Do some processing with this information // . while they do not have a FieldIndex . } catch(Exception e) { ShowErrorMessage(e.. If there are no column names in the file. The stream has to support Seek . that implies that fields or properties without the CsvColumn attribute can also be used to accept data..
CsvColumnAttributeRequiredException
This exception exposes the same properties as Exception ..
Collapse Collapse Copy Code
public static void ShowErrorMessage(string errorMessage) { // show errorMessage to user // .. } } catch(DuplicateFieldIndexException dfie) { // name of the class used with the Read method .LINQ to CSV library . foreach (Exception e in innerExceptionsList) { ShowErrorMessage(e.. } }
BadStreamException
This exception exposes the same properties as Exception .... then Read relies on the FieldIndex of each field or property in the data class to match them with the data fields in the file.csv". // Inform user of error situation ShowErrorMessage(dfie.com/KB/linq/LINQtoCSV.aspx[6/24/2010 7:14:47 PM]
.This exception exposes the same properties as Exception ..Read<Product>("products. Thrown when a stream is passed to Read ..CodeProject
some special processing for the DuplicateFieldIndexException .Data["TypeName"]). inputFileDescription).thrown when the Read and Write methods detect two fields or properties with the same FieldIndex . otherwise it cannot be rewound when the IEnumarable returned by Read is accessed. // Names of the two fields or properties that have the same FieldIndex string fieldName = Convert.. } public static void ReadFileWithExceptionHandling() { try { CsvContext cc = new CsvContext().Message).. which is either null . plus these additional properties: Property Type Description
Data["TypeName"] string Name of the class with the offending fields/properties Data["FieldName"] string Fields or properties with a duplicate FieldIndex Data["FieldName2"]
http://www. // Do data processing // . Thrown when the CsvFileDescription object that has been passed to Read has both FirstLineHasColumnNames and EnforceCsvColumnAttribute set to false .Message)..codeproject.Data["FieldName"]).. IEnumerable<Product> products = cc..ToString(dfie.
DuplicateFieldIndexException
Additional Properties .ToInt32(dfie. CsvFileDescription inputFileDescription = new CsvFileDescription { MaximumNbrExceptions = 50 // limit number of aggregated exceptions to 50 }.Data["InnerExceptionsList"]. } catch(AggregatedException ae) { // Process all exceptions generated while processing the file List<Exception> innerExceptionsList = (List<Exception>)ae. string fieldName2 = Convert.

codeproject.
MissingCsvColumnAttributeException
Additional Properties . and one of the column names in the first record in the file does not match a field or property. When that happens. and it finds a field or property that doesn't have a FieldIndex . When Read reads the column names from the first record.
TooManyDataFieldsException
Additional Properties . even though only fields and properties with a CsvColumn attribute can be used. it throws a NameNotInTypeException . plus these additional properties: Property Type Description
Data["TypeName"] string Name of the class with the offending field/property Data["FieldName"] string Field or property without CsvColumn attribute Data["FileName"] string Name of the input file
The Read method may throw this exception when it is given a CsvFileDescription with both FirstLineHasColumnNames and EnforceCsvColumnAttribute as true . it would be impossible for some other program to reliably process the file.This exception exposes the same properties as Exception .aspx[6/24/2010 7:14:47 PM]
. plus these additional properties: Property Type Description
Data["TypeName"] string Name of the data class
http://www. when the Write method is given a CsvFileDescription with FirstLineHasColumnNames as false .
RequiredButMissingFieldIndexException
Additional Properties . one of those column names may match a field or property that doesn't have a CsvColumn attribute. plus these additional properties: Property Type Description
Data["TypeName"] string Name of the class missing the field/property Data["FieldName"] string Field or property that isn't found Data["FileName"] string Name of the input file
If the Read method is given a CsvFileDescription with FirstLineHasColumnNames as true . otherwise it cannot be read from the file.LINQ to CSV library . each required field (CanBeNull attribute set to false ) must have a FieldIndex attribute. plus these additional properties: Property Type Description
Data["TypeName"] string Name of the class with the offending field/property Data["FieldName"] string Field or property without FieldIndex
When there are no column names in the first record in the file (FirstLineHasColumnNames is false ). If that order were random. it throws a ToBeWrittenButMissingFieldIndexException .
ToBeWrittenButMissingFieldIndexException
Additional Properties .
NameNotInTypeException
Additional Properties .CodeProject
Data["Index"]
int
Common FieldIndex
Thrown when two or more fields or properties have the same FieldIndex . you will want to make sure that the data fields appear in each line in a well defined order.This exception exposes the same properties as Exception . So.This exception exposes the same properties as Exception .com/KB/linq/LINQtoCSV.This exception exposes the same properties as Exception . plus these additional properties: Property Type Description
Data["TypeName"] string Name of the class with the offending field/property Data["FieldName"] string Field or property without FieldIndex
When writing a file without column names in the first record.This exception exposes the same properties as Exception . Read throws a MissingCsvColumnAttributeException .

plus these additional properties: Property Type Description
Data["TypeName"] string Name of the data class Data["LineNbr"] int Line with offending field Data["FileName"] string Name of the input file
If there are no column names in the first record of the input file (Read is given a CsvFileDescription with FirstLineHasColumnNames as false ).codeproject. plus these additional properties: Property Type Description Name of the class with the required field/property Name of the required field/property Line where missing field should have been Name of the input file
Data["TypeName"] Data["FieldName"] Data["LineNbr"] Data["FileName"]
string string int string
Thrown when a record from the input file does not have a value for a required field or property (CanBeNull property of the CsvColumn attribute set to false ). Difference between null and empty string Empty strings and strings consisting of only white space need to be surrounded by quotes. When a record in the input file has more data fields than there are fields and properties in the data class with a FieldIndex . followed by a string with three spaces.. and a record in the input file has more data fields than there are fields and properties with the CsvColumn attribute.LINQ to CSV library .def abc. These input lines both have the data fields "abc". null.This exception exposes the same properties as Exception . then Read relies on the FieldIndex of the fields and properties in the data class to match them with the data fields in the file.This exception exposes the same properties as Exception .
MissingFieldIndexException
Additional Properties .aspx[6/24/2010 7:14:47 PM]
. followed by "def":
http://www. followed by "def":
Collapse Collapse Copy Code
abc. a TooManyNonCsvColumnDataFieldsException is thrown.def
and this line has the data fields "abc". and "def":
Collapse Collapse Copy Code
abc. plus these additional properties: Property Type Description
Data["TypeName"] string Name of the data class Data["LineNbr"] int Line in the input file with an excess data field Data["FileName"] string Name of the input file
When only fields or properties that have a CsvColumn attribute are used (Read is given a CsvFileDescription with EnforceCsvColumnAttribute as true ).
TooManyNonCsvColumnDataFieldsException
Additional Properties . then a MissingFieldIndexException is thrown.def
While this line has the data fields "abc".com/KB/linq/LINQtoCSV.CodeProject
Data["LineNbr"] int Line in the input file with an excess data field Data["FileName"] string Name of the input file
Thrown when a record in the input file has more data fields than there are public fields and properties in the data class. so they are recognized as something other than null .
MissingRequiredFieldException
Additional Properties . followed by the empty string.This exception exposes the same properties as Exception ."". .

codeproject. ASP.
License
This article.def
WrongDataFormatException
Additional Properties . plus these additional properties: Property Type Description Name of the class with the field/property Name of the field/property The offending data value Line with offending data value Name of the input file
Data["TypeName"] Data["FieldName"] Data["FieldValue"] Data["LineNbr"] Data["FileName"]
string string string int string
Thrown when a field has the wrong format.369 out of 154. Ranked #4. C. SQL Server 2000 and 2005. CSS. is licensed under The Code Project Open License (CPOL)
About the Author
Matt Perdeck Technical skills used in last 2 years: AJAX. Linux.356 (top 3%) at rentacoder. Australia. VB. JavaScript (5 years).This exception exposes the same properties as Exception .5 and 2. HTTP. The Netherlands. embedded low level multi tasking software. Dutch national.aspx[6/24/2010 7:14:47 PM]
. Passionate about producing reliable and maintainable software that makes the client happy.25. PERL.com/KB/linq/LINQtoCSV. SNA. XML. Email: mperdeck (at) gmail (dot) com
Occupation: Web Developer Location: Australia
http://www. PHP. X. along with any associated source code and files. C. GMAT score in top 2%. plus these additional properties: Property Type Description
Data["TypeName"] string Name of the data class used by Read Data["FileName"] string Name of the input file Data["InnerExceptionsList"] List<Exception> List of Exception s
Used to aggregate exceptions generated while reading a file (more details). POP3.LINQ to CSV library . a numeric field with the value "abc". Other technical skills include: Visual C++ (5 years). InsiteCreation CMS. For example.NET 3. developing web sites.This exception exposes the same properties as Exception . with Australian permanent residence.
Member
Graduated with BSc Computer Science in 1987 from University of Technology Twente.com.NET).
AggregatedException
Additional Properties . Currently on contract with a major international publishing house until 1 August 2008. Slovakia and Thailand. SMTP. Loves developing software. CGI."
". TCP/IP.CodeProject
Collapse Collapse
Copy Code
abc.0 (C#. MySQL. XHTML. Has worked in The Netherlands.

as described below. Popularity: 3. the code generation will be invoked automatically in order to create the . You will need Visual Studio 2008 and Reegenerator installed. not attempt to create better LINQ to SQL code.
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms. With this tool. The solution contains a project named CustomizeDesigners that contains the code generators. or right-click on it and invoke "Run Custom Tool". Each scenario will be discussed in more detail below. for each scenario.
http://www. you define in the solution a separate project that holds code generators that is not part of the build. easy to use. therefore.cs") dependent on the DBML file.399 members and growing! (19. As with basically all the other designers.aspx[6/24/2010 7:15:26 PM]
. and trigger the code generation by saving a DBML file. There is a shortcoming though. The customization is completely transparent to the standard LINQtoSQL designer. and a project named Business containing the DBML files and the database that is used as a source for the LINQtoSQL designer.88 out of 5
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now. you simply set a breakpoint in a code generator code in the RenderersLibrary project and start a debug session. I will just show you how to customize and enrich the generated code in a matter of minutes. To debug.
Posted: 6 Dec 2008 Views: 9. you need to edit and save a DBML file.
Using the Code
Download the zip file from the link. it's easy to crank up a demo.
Download source code . but once you get in real life situations. It is fast.LINQtoSQL: Customize the Code Generated by the Designer . Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Articles / Quick Answers Articles / Quick Answers
Go!
Advanced Search Add to IE Search
5 votes for this article.41 Rating: 4. You then attach the LINQtoSQL project items (. it works.cs that is dependent on the DBML file.dbml) to the code generation classes defined in that project. Extract it. there are three Scenario folders that host the DBML and the code generators. and will be implemented based on a free Visual Studio tool named Reegenerator. .com/KB/linq/CustomizeLINQtoSQL. and you will still be using it for modeling your classes and data access. In both the Business and RenderersLibrary projects.NET.
Wanted
Software Engineers at Segula Technologies View this and other fine jobs on the Job Board. In order to trigger the code generation. I will. You cannot customize the generated code.206 KB
Introduction
LINQ to SQL is used to manipulate data in a SQL Server database. LINQ.140. and great in lots of aspects. Load the same solution in the second instance.CodeProject
7.Designer.436 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
C#.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation
Brief Explanations
We can split the problem into three scenarios depending on how different the desired generated code is from what LINQtoSQL generates. and load the solution file from the extracted folder into Visual Studio. Visual Studio ships with the LINQtoSQL designer which is a very powerful tool to generate LINQ. Frameworks & Libraries ATL MFC STL WTL COM / COM+ . You will need to click on the plus sign that sits on the left side of the DBML project item in order to expand its children. you always want that little bit more.Designer. Dev
Lounge
Platforms. The session will invoke a second instance of Visual Studio. That extra bit is always something that depends on the problem.814 Bookmarked: 24 times
Announcements
Write an iPhone Tutorial. When you use the LINQtoSQL designer and save a file. The generated code will be saved as project items (generally ". Frameworks & Libraries » LINQ » General
License: The Code Project Open License (CPOL)
LINQtoSQL: Customize the Code Generated by the Designer
By radusib Customize the code generated by the LINQtoSQL designer.codeproject.

CodeProject
Libraries Windows Powershell LINQ Azure General Programming Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
To inspect what a DBML project item is attached to.GetString(resultsAsBytes). we invoke the standard code generator to retrieve the code generated by Microsoft's designer. The image below shows how your working environment will look like:
Services
Product Catalog Job Board CodeProject VS2008 Addin
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts . string results = System.Default. manipulate it the way we want.NET 4 Web Hosting
Scenario 1 .GetCurrent(). you can manipulate it by adding code to it.. return results. save it. return new Generators. // transform the string. grab the resulting code as a string. // return the string to be saved into the . edit Business\Scenario1\CustomersLINQ.NET Dev Library ASP..Principal. To test. public override Generators. // Generation date: {0} // Generated by: {1} // ------------------------------------------------------{2}".com/KB/linq/CustomizeLINQtoSQL.Text.ToString("yyyy-MM-dd hh:mm").dbml in the Business project. we most likely want to simply invoke the LINQtoSQL default code generator.Format(@"// ------------------------------------------------------// Automatically generated.aspx[6/24/2010 7:15:26 PM]
.cs file.Small Changes
In this case. and open Business\Scenario1\CustomersLINQ.codeproject. byte[] resultsAsBytes = base. // get Microsoft's generated code as string.DateTime. The code to achieve the desired modification is remarkably simple:
Collapse Collapse Copy Code
public class SmallChangesToDefaultLINQtoSQL : Generators. /// </summary> public const string CustomToolName = "MSLinqToSQLGenerator". // AddAttribute(results). expand its children.LINQtoSQL: Customize the Code Generated by the Designer .WindowsIdentity.RunOtherCustomTool(CustomToolName). or replace certain portions of it with string manipulation routines or Regular Expressions.RenderResults Render() { // execute the default custom tool.cs. System.Designer. } }
As you can see in the code above. You can see that the file contains extra code added to
http://www.". right click on it and click "Attach Renderers. and then send the modified code back to Visual Studio. results). The DBML file located in the Scenario1 folder in the business project is linked with the CustomizeDesigners\Scenario1\CustSmallChangesToDefaultLINQtoSQL class. string modifiedResults = AddSomething(results).RenderResults(modifiedResults).Name.Designer. System. Once you have it. } private string AddSomething(string results) { results = string.CodeRenderer { /// <summary> /// Default custom tool for dbml files.Security.Now.Encoding.

AddAttribute . The Business\Scenario2\CustomersLINQ..Name %>".LINQtoSQL: Customize the Code Generated by the Designer .aspx[6/24/2010 7:15:26 PM]
.Now.ProjectItem. %> call just under the namespace. CustomizeDesigners\Scenario2\AddHelloMethodToTables.Dbml" %> // // // // // // ------------------------------------------------------Automatically generated with Kodeo's Reegenerator Generator: CustomizeDesigners.ToString("yyyy-MM-dd hh:mm") %> Generated by: <%= System. } } <%/ Method %>
As opposed to ASPX. load the same solution in the second Visual Studio instance. you can define render methods that help you break down your code generation work into more manageable units.Name %> { public string Hello() { return "Hello from table <%= table.rgt code generator template looks like:
Collapse Collapse Copy Code
<%@ Template Language="C#" ClassName="AddHelloMethodToTables" %> <%@ Import Namespace="System" %> <%@ Import Namespace="CustomizeDesigners.Designer.0\Xml\Schemas\DbmlSchema. For properly building a code generator for the DBML file. This calls a function that is manually written in the code-behind.Principal. but you want extra code. that contains manually written code. and two . Business\Scenario2\CustomersLINQ.cs is the code representation of the template.cs file is generated by CustomizeDesigners\Scenario2\AddHelloMethodToTables.
Scenario 2 .GetCurrent(). you are fine with what Microsoft generates.CodeProject
Microsoft's code.cs files get created/updated when you save it.Scenario2.Name %> -------------------------------------------------------
namespace <%= base.cs template code-behind looks like:
Collapse Collapse Copy Code
public partial class AddHelloMethodToTables { /// <summary> /// The database element as deserialized from the dbml file.xsd.dbml is linked to two code generators (right click on it and click "Attach Renderers.xsd supposing that you installed Visual Studio in the default location. The DBML file schema can be found at C:\Program Files\Microsoft Visual Studio 9.Type. set a breakpoint in the Render method.xsd to see the serializable classes.rgt.WindowsIdentity.CodeNamespace %> { <% RenderTables(). The easiest way to achieve this is to link the DBML file to two code generators. The first one invokes Microsoft's code generator. The example code generator has another method. %> } <%@ Method Name="RenderTable" %> <%@ Parameter Name="table" Type="Table" %> partial class <%= table." to inspect).rgt. it forms a class that will be invoked when the DBML file gets saved.exe to create serializable classes from schemas.cs exactly the same as per Scenario1. Expand the children project items for DbmlSchema. that shows how to do a slightly more complex manipulation. Examples of extra code could be adding a method to each class generated from a table. The second code generator deserializes the DBML file into such a class and then uses it to generate what is needed.Security.Hello.rgt. There is also a code-behind for it. You can use Regular Expressions if the desired changes are more complex. and perform the same steps as you do when you test.rgt code generator that invokes the same mechanism as xsd. you need to be ble to deserialize it into a serializable class instance. This schema has been added to the CustomizeDesigners project to Dbml\DbmlSchema.cs file is generated by CustomizeDesigners\Scenario2\SmallChangesToDefaultLINQtoSQL.AddMethodToTables Generation date: <%= System. /// </summary>
http://www. To debug. Also note the <% RenderTables().Significant Additions
In this scenario. or creating SQL scripts for the database objects. Together with the code-behind. This is a code generator template file that looks pretty much like an ASPX page.Type.com/KB/linq/CustomizeLINQtoSQL.Designer. The Scenario2\AddHelloMethodToTables.codeproject. This is achieved by having a schema in the code generators project that creates serializable classes from it. and you might want to slightly alter the results as per Scenario 1. The second one generates the extra code. The CustomizeDesigners\Scenario2\AddHelloMethodToTables.. It is linked with the CustomizeDesigners\XsdRenderers. The Business\Scenario2\CustomersLINQ.DateTime.cs. CustomizeDesigners\Scenario2\AddHelloMethodToTables. launch a debug session.

if Microsoft's code was close to good enough for you. and inspect the content of the two children cs files. set a breakpoint in the PreRender method.CodeProject Database _database = null. /// <summary> /// Takes place before the render. save it. You can use the designer for something completely different and unrelated to LINQtoSQL as long as the designer does a good enough job for what you are trying to achieve. edit Business\Scenario2\CustomersLINQ. Business\Scenario3\CustomersLINQ. is licensed under The Code Project Open License (CPOL)
About the Author
radusib
Occupation: Architect Member Location: New Zealand
http://www. foreach (Table table in this. and Stored Procedures used by the DBML file. your problem would fall under Scenario 1 or 2. // deserializes the dbml file into the private property._database. In itself.ProjectItem).Table) { RenderTable(table).rgt does a good enough job at replicating Microsoft's code generator. /// </summary> private void RenderTables() { if (this. load the same solution in the second Visual Studio instance.rgt. launch a debug session. } } }
The DBML file is deserialized into the _database property before the code generation takes place. Deserializes the dbml file into _database. Here are some examples: You can have your own LINQtoSQL customizations that magically get created when you use the designer. or you simply want to use the designer for something else (e..com/KB/linq/CustomizeLINQtoSQL.PreRender(). create pure business objects that know nothing about LINQtoSQL). CustomizeDesigners\Scenario3\DbmlRenderer.LINQtoSQL: Customize the Code Generated by the Designer .g.Complete Rewrite
This scenario applies when you are completely unhappy with what the designer generates for you._database = DbmlSchemaFactory. taking advantage of an already made designer. To debug. and you can do the testing and the debugging the same way as per the previous scenarios.Create<Database>(base.
Final Note
The possibilities are limitless on what you can now do with the LINQtoSQL designer. and perform the same steps as you do when you test.codeproject. To test it. it calls the RenderTable(Table table) that is defined in the template. Its purpose is to be an example and provide the familiarity and structure to start with for building your heavily customized code generator. /// </summary> public override void PreRender() { base. along with any associated source code and files. Please note the implementation of RenderTables which gets called from the template file. like SQL scripts for the tables. this. In its turn. it has little value as. expand its children.Table == null) return.aspx[6/24/2010 7:15:26 PM]
.dbml is linked with CustomizeDesigners\Scenario3\DbmlRenderer. You can generate something different.dbml.
Scenario 3 .
License
This article. } /// <summary> /// Renders all the tables._database.

especially LINQ to Objects.0).Query<Person>("SELECT * FROM this ORDER BY age").
17 (See All) 10 Nov 2009 12 Nov 2009 7. Dynamic typing has a lot of advantages especially in increasingly large.97 out of 5
1 2 3 4 5
The Daily Insider
The Odds Are Increasing That Microsoft's Business Will Collapse Daily IT news: Signup now.139 KB
Introduction
Have you ever wanted to execute simple SQL SELECT statements against in-memory collections and lists? Well. Anything that reduces code lines is a big plus for maintainability and quality. complex. The combination of dynamic and static typing within C# may be a powerful new addition. The result is support for runtime query evaluations like:
Collapse Collapse Copy Code
Wanted
Software Engineers at Segula Technologies View this and other fine jobs on the Job Board. .5 (with the addition of System. and have always thought that IDispatchEx never really got a chance to show its real potential outside of Microsoft apps before COM went out of style.NET Framework Win32/64 SDK & OS Vista API Vista Security Cross Platform Game Development Mobile Development Windows CardSpace Windows Communication Foundation Windows Presentation Foundation Windows Workflow Foundation Libraries Windows Powershell LINQ Azure General Programming
var result = source.codeproject. Dev Revision: Posted: Updated: Views: Bookmarked:
Articles / Quick Answers Articles / Quick Answers
Platforms. Tuple<string.NET and C#. var result3 = source. and distributed systems.Expressions ) and the dynamic typing features of C# 4. but my fingers just won't start a query statement with any other word than SELECT . var result2 = source. Avg(Age) AS AverageAge FROM this GROUP BY Address").NET and C# to take on some features of a Duck Typed language.CodeProject
7.NET 3. I've found that while the Enumerable extension methods are pretty easy to follow and work with.NET (. My personal short comings aside. C# gets a dynamic keyword and access to the Dynamic Language Runtime. C#/. The real reason for this article is that I've always wanted to write a runtime evaluation engine. string>("SELECT DISTINCT address FROM this")
I'm a big fan of LINQ. It will be interesting to see how all of this plays out over time.Linq.54 Rating: 4. I should switch to Python. Architect. What came out the other end lets you take something like this:
Collapse Collapse Copy Code
http://www. double>>( "SELECT Address. Family>( "SELECT Address. Win an iPad Local Government Windows Azure Competition Monthly Competition Print Friendly Share
Search
Discuss Report
Advanced Search Add to IE Search
13 votes for this article. Popularity: 5. So I gave it a whirl.Query<Person.NET4. As I've used LINQ more and more in both production and hobby code. Microsoft has been adding dynamic typing features to .aspx[6/24/2010 7:35:21 PM]
.NET 4. Perhaps I've been at this too long. namely those "almost SQL" statements are still tightly coupled to an application's static structure. and this article presents a mechanism to do that utilizing LINQ and runtime generated and compiled Lambda Expressions.257 31 times
Go!
Announcements
Write an iPhone Tutorial.com/KB/linq/QueryAnything.0 have provided the right set of tools.Query<Person.436 online)
Email
Password
Sign in
Join
Join
Remember me?
Lost your password?
Home
Articles
Quick Answers
Message Boards
Job Board
Catalog
Help!
Lounge
C# (C#4. I've found that LINQ has a more practical limitation. I have.0 is adding some interesting features: the F# Tuple type has been made part of the BCL. but C# pays the bills.Dynamically evaluated SQL LINQ queries .0). Perhaps. I continue to trip on the "almost SQL" inline LINQ C# keywords. LINQ. Code that might be tens of lines long using a foreach iteration and evaluation can often be shortened to 1 or 2 lines.140. But I digress. or it might end up being a Frankenstein with the worst features of both approaches. . Luckily.399 members and growing! (19.
Download source code . var result4 = source. Frameworks & Libraries » LINQ » General
License: The Code Project Open License (CPOL)
Dynamically evaluated SQL LINQ queries
By Don Kackman Extension methods to evaluate plain text SQL queries against IEnumerable collections.Query<Person. I was a big fan of the VARIANT back in the day. and the BCL gets an ExpandoObject allowing even statically typed languages like VB. Frameworks & Libraries ATL MFC STL WTL COM / COM+ .
Articles
Desktop Development Web Development Mobile Development Enterprise Systems Database Multimedia Languages Platforms. Avg(Age) FROM this GROUP BY Address").

NET 4 Web Hosting
var result = source. It also owes some inspiration to the Dynamic LINQ example that Microsoft published as part of the VS2008 Samples collection.com/KB/linq/QueryAnything.returns an IEnumerable<T>
The SQL string is immediately turned into a Query<.5.NET 4. such as: The Tuple type The dynamic keyword
ExpandObject
The upshot is that you won't be able to play with the attached code using VS.aspx[6/24/2010 7:35:21 PM]
. Much of the underlying code is the same (or at least started it out the same) especially in the area of SQL parsing using the GOLD parser.
Using the Code
The entry point in the API is a small set of extension methods that extend IEnumerable<T> and take a SQL statement in the form of a string . and some of the new features of C# and . one for enumerable queries).Age) > 40 select new { Address = g. and is largely the same as it was in the previous article.0. Caveat Emptor The attached code and project was created with Microsoft Visual Studio 2010 Beta 2.> derived object (one for scalar queries.codeproject. AverageAge = g. The responsibilities of the Query classes are: Parse the SQL text into a parse tree Convert the parse tree into expression trees Convert and cache the expression trees into lambda functions that can be used to evaluate the data sets The parsing happens in the base class.Average(p => p.NET Dev Library ASP.NET 3.Dynamically evaluated SQL LINQ queries . object> and that was also how the data was returned to the caller.NET 2008 or . A limitation in that previous code was that the data being evaluated had to be in the form of an IDictionary<string. That article describes the basic parsing and evaluation infrastructure used.Average(p => p.
Services
Product Catalog Job Board CodeProject VS2008 Addin
and replace it with something like this:
Collapse Collapse Copy Code
Feature Zones
Product Showcase The SQL Zone WhitePapers / Webcasts .CodeProject
Graphics / Design Development Lifecycle General Reading Third Party Products Mentor Resources
var result = from p in source group p by p.Age) }. dynamic>("SELECT Address.returns a single scalar value (like an int ) Query .Address into g where g.
http://www.Key. There are two sets of overloads:
QueryScalar .Query<Person. Avg(Age) AS AverageAge FROM this GROUP BY Address HAVING AverageAge > 40")
Background
The basis for this article begins with a previous installment: Taking LINQ to SQL in the Other Direction.

the this entry in the join chain portion of the FROM clause is merely a placeholder.Empty<TResult>().CreateFunction<TSource>(). It refers to the same this that is passed to the extension methods or the IEnumerable<T> passed to the Evaluate methods. The type parameters passed to the Query methods indicate both the type contained in the enumerable and the type to create and return.Add("phillip"). A SELECT query can return a subset of the input data without a type transformation. TResult> (this IEnumerable<TSource> enumerable. TResult>().CreateSelector<TSource. IEnumerable<string> result2 = l.CreateEvaluator<TResult>().Query<string>("SELECT * FROM this WHERE Length > 3").
Also.HavingClause.Columns.aspx[6/24/2010 7:35:21 PM]
. l.Dynamically evaluated SQL LINQ queries . if (SyntaxNode. and UPDATE abilities may come later.CreateGroupBySelector<TSource. if (Having != null) result = result. l. else result = GroupBySelect(source).GroupByClause == null) Select = SyntaxNode.Distinct(). Or queries can return completely transformed types that are aggregations of the input data: SELECT name. It can return a subset of the fields from the input data type: SELECT name. if (SyntaxNode. age FROM this . if (SyntaxNode. that capability isn't present.Query<string>("SELECT * FROM this WHERE value() = 'don'").OrderByClause != null) OrderBy = SyntaxNode.Where<TSource>(Where). if (OrderBy != null) source = OrderBy(source). TResult>(Select).CreateEvaluator<TSource>().codeproject. INSERT . if (Select != null) result = source.CodeProject
Creating the evaluation functions is a matter of determining which parts of a SQL query are present and generating a lambda for each one:
Collapse Collapse Copy Code
protected override void OnCompile() { if (SyntaxNode.OrderByClause. In order to support both querying a type's properties and its value. Avg(age) FROM this GROUP BY name . else GroupBySelect = SyntaxNode.
Collapse Collapse Copy Code
public static IEnumerable<TResult> Query<TSource. return result. I'd like to add support for joins across multiple collections. This allows queries such as the following to be differentiated:
Collapse Collapse Copy Code
List<string> l = new List<string>(). IEnumerable<string> result = l.Select<TSource.WhereClause != null) Where = SyntaxNode.HavingClause != null) Having = SyntaxNode.
with a resulting evaluation method that executes each lambda in turn:
Collapse Collapse Copy Code
public override IEnumerable<TResult> Evaluate(IEnumerable<TSource> source) { if (Where != null) source = source. It can return a single value in situations like SELECT Avg(Age) FROM this . DELETE . with queries such as SELECT * FROM this WHERE Age = 40 . string sql)
http://www.com/KB/linq/QueryAnything. }
Some Notes About the SQL Syntax
The SQL syntax is based on the SELECT portions of SQL 89's DML. but at the moment.Distinct) return result. if (SyntaxNode. TResult>(). In a future update.Where<TResult>(Having). l.Add("don").
Points of Interest
The most challenging part of this was creating result selectors. there is a value() function built in. SQL return types are polymorphic depending on the query.Add("kackman").WhereClause. IEnumerable<TResult> result = Enumerable.

both TSource and TResult will be the same.PropertyOrField(param.Parameter(typeof(TSource). the selector is a simple identity lambda:
Collapse Collapse Copy Code
public static Expression<Func<TSource. There is an overload for this case that only takes one type parameter.PropertyOrField(arg. "arg"). memberExpression)). arg). A special selector is created for the Tuple type which has a constructor that takes an argument for each constituent property. IEnumerable<int> result = source.GetInts().codeproject. If TSource or TResult has Property and property . public static Expression<Func<TSource. IEnumerable<string> resultFields) { ParameterExpression arg = Expression.GetPeople().aspx[6/24/2010 7:35:21 PM]
. TResult>>(arg. sourceFields. There are three approaches to doing this. param).
In this case.. } var init = Expression. int i = 0. In this case. int>("SELECT Age FROM this WHERE age > 40"). TResult>>(init. TResult>>( Expression. return Expression. }
Multiple Properties Returning multiple properties requires creating new instances of TResult and populating them with the result data. TResult>(string propertyName) { ParameterExpression param = Expression. }
Single Properties For selecting single properties from the source type. foreach (string field in resultFields) { MemberInfo member = typeof(TResult). TResult>> CreateMemberInitSelector<TSource. "arg"). MemberExpression memberExpression = Expression. The order of fields in the Select statement must match the order of arguments in the constructor declaration. the AS keyword can be used to map the two. bindings. a lambda is created that returns the value of that property from each source object. address AS location FROM this") . public static Expression<Func<TSource. "item").Lambda<Func<TSource. In the example below.Parameter(typeof(TSource). // loop through all of the result fields and generate an expression // that will assign it from the source fields of the param var bindings = new List<MemberAssignment>(). TResult>> CreateIdentitySelector<TSource.Add(Expression.Parameter(typeof(TSource). This doesn't require the creation and initialization of new objects:
Collapse Collapse Copy Code
IEnumerable<Person> source = TestData.
http://www. TResult> (IEnumerable<string> sourceFields.. TResult>> CreateSinglePropertySelector<TSource.ElementAt(i++)). return Expression.Query<Person. arg). if the source property name is not the same as the result property. it is expected that the return type has read/write properties for each of the fields in the SELECT statement.Bind(member.com/KB/linq/QueryAnything.Lambda<Func<TSource. propertyName). IEnumerable<int> result = source. . }
As a side note: all property names are evaluated in a case insensitive fashion.Dynamically evaluated SQL LINQ queries . In most cases.New(typeof(TResult)). bindings).Query<int>("SELECT * FROM this WHERE value() > 3"). exceptions will be thrown..Lambda<Func<TSource.MemberInit(Expression.GetPropertyOrField(field). the Person class has a property named Address . while OtherPerson has Location :
Collapse Collapse Copy Code
var result = source..Query<Person.CodeProject
Subselection For a query that will return the same type as passed in. OtherPerson>("SELECT name. return Expression.
Collapse Collapse Copy Code
IEnumerable<int> source = TestData. TResult>() { ParameterExpression arg = Expression.

. public static Expression<Func<TSource. // the values that will intialize a TResult ConstructorInfo constructor = typeof(TResult).Add(Expression.codeproject. Type[] constructorTypes) { ParameterExpression arg = Expression. bindings). to cache the delegate for each aggregate at compile time for later invocation during evaluation.GetMethod( "Add". field)).ElementAt(i++)). string>>("SELECT name.Lambda<Func<TSource.Parameter(typeof(TSource). keyLambdaArg). since it would seem to mesh more with a dynamically typed environment. bindings). var bindings = fields.PropertyOrField(arg. TResult>>( Expression. // the constructor for a new TResult NewExpression _new = Expression. TResult> (IEnumerable<string> fields. It is via this mechanism that this API goes from statically to dynamically typed.Parameter(typeof(TSource). and this interface is used to populate the return objects.PropertyOrField(keyLambdaArg.Lambda<Func<TSource. Avg(Age) AS AverageAge FROM this GROUP BY Address") . ExpandoObject implements IDictionary<string.Parameter(typeof(IEnumerable<TSource>).. address FROM this") . "arg"). Family>( "SELECT Address. Expression. Tuple<string. TResult>> CreateExpandoSelector<TSource. GroupByCall . // create an object to cache some state for the result selector GroupByCall<TSource.ElementInit(addMethod. return Expression. object> to store the set of dynamically assigned properties. TResult> (IEnumerable<string> sourceFields. public static Expression<Func<IEnumerable<TSource>. I expected them not to be. } var expando = Expression.PropertyOrField(arg. public static Expression<Func<TSource. It took me a while to wrap my head around how to do this without calculating and caching some intermediate state for each aggregate.Constant(field). new Type[] { typeof(string). "arg"). sourceFields. MethodInfo addMethod = typeof(IDictionary<string.Query<Person. Expression. TResult>>(_new.Query<Person. groupByField). Type keyType.CodeProject
Collapse Collapse Copy Code
var result = source. }
Grouping By far the most challenging selector was GROUP BY .
Collapse Collapse Copy Code
var result = source. bindings..New(constructor.ListInit(expando. object>). "group"). I created a type. In the end. // loop through all of the result fields and generate an expression that will // add a new property to the result expando object using its IDictionary interface var bindings = new List<ElementInit>().. IEnumerable<TResult>>> CreateGroupBySelector<TSource.. "keyLambdaArg").Query<Person. foreach (string field in resultFields) { MemberExpression memberExpression = Expression.Convert(memberExpression. // for each aggregate in the query create a lambda expression // and add it to the cache foreach (AggregateNode aggregate in aggregates)
http://www.
Collapse Collapse Copy Code
var result = source. IEnumerable<string> resultFields) { ParameterExpression arg = Expression. address FROM this") . I don't know if that's good or bad.aspx[6/24/2010 7:35:21 PM]
. }
Dynamic and Expando Another special selector is created when dynamic is specified as TResult . typeof(object) }). arg). int i = 0. dynamic>("SELECT age. but for some reason.Parameter(typeof(TSource). TResult> groupingCall = new GroupByCall<TSource. In this case. IEnumerable<AggregateNode> aggregates) { // create the key selector // the parameter passsed to the keySelector var keyLambdaArg = Expression.. TResult>(groupByField).GetConstructor(constructorTypes). // the grouped subset passed to resultSelector var group = Expression. This involves not only a type transformation between TSource and TResult but the calculation of properties on the return type as opposed to only assignment.Select(field => Expression. typeof(object)))). var keyLambda = Expression.Dynamically evaluated SQL LINQ queries . you will always get a collection of ExpandoObject s back.Lambda( Expression. Something I noticed about the ExpandoObject is that its property names are case sensitive.com/KB/linq/QueryAnything. arg).New(typeof(ExpandoObject)). TResult>(string groupByField. return Expression. TResult>> CreateConstructorCallSelector<TSource.

Lambda(groupingFunc. }
Conclusion
The attached unit tests contain plenty of examples for different combinations of the above. keyLambda.
Occupation: Team Leader Company: Location: Starkey Laboratories United States
Article Top
Sign Up to vote for this article
You must Sign In to use this message board.
History
Initial upload . key.Call(Expression.codeproject. data).Lambda<Func<IEnumerable<TSource>. you find it useful or at the very least interesting. data. "key"). group). keyType. is licensed under The Code Project Open License (CPOL)
About the Author
Don Kackman The first computer program I ever wrote was in BASIC on a TRS-80 Model I and it looked something like: 10 PRINT "Don is cool" 20 GOTO 10
Member
It only went downhill from there. "data"). group).Add(aggregate.Compile()). resultSelectorLambda). new Type[] { typeof(TSource). Expression. Hopefully. group). I've had a lot of fun with this code thus far. groupingCall. var resultSelectorLambda = Expression. "GroupingFunc".aspx[6/24/2010 7:35:21 PM]
.GroupBy var data = Expression. but most of the basic syntax of SQL SELECT should work.Dynamically evaluated SQL LINQ queries . var groupingFunc = Expression.Aggregates.Alias. } // create the call to the result selector var key = Expression. and am planning on updating it with additional functionality. typeof(TResult) }. // the input data var groupByExpression = Expression.Parameter(typeof(IEnumerable<TSource>). new Type[] { keyType }.Constant(groupingCall). typeof(TSource). FAQ
Search
http://www.com/KB/linq/QueryAnything. along with any associated source code and files.11/11/2009.Parameter(keyType. key. IEnumerable<TResult>>>(groupByExpression.
License
This article.Lambda(aggregateExpression.Call(typeof(Enumerable). "GroupBy". // package all of that up in a call to Enumerable.GetCallExpression( typeof(IEnumerable<TSource>).CodeProject { var aggregateExpression = aggregate. group). // create the lambda return Expression.