Programming.NET

Linquify

| by

Introduction

Linquify by Primary Objects is a C# .NET business class generator for LINQ to SQL and the Entity Framework, helping to support rapid development of .NET and ASP .NET web application data layers. Linquify is free for use under the LGPL.

Linquify installs as a Visual Studio 2008/2010/2012 addin, which allows you to select your LINQ to SQL dbml file or Entity Framework edmx file and automatically generate a set of easy to use business classes. The generated classes allow you to support a complete 3-tier software architecture and simple handling of database objects, with full support for LINQ queries. Customizations to the generated classes can be easily made within the generated user partial classes.

Download Linquify v1.6
Add-in Install Instructions

Linquify C# ASP .NET Business Class Generator for LINQ to SQL and the Entity Framework

Linquify Key Features

  • Visual Studio 2008/2010/2012 Addin for easy generation.
  • Compatible with LINQ to SQL and the Entity Framework.
  • Compatible with SQL Server, MySQL, Oracle, and any other compatible database platform (uses the Entity Framework default provider or LINQ2SQL with DbLinq).
  • Automatic generation of Types library, containing business classes, and supporting 3-tier architecture.
  • Simple Load(), Save(), Delete() methods for making database calls.
  • Full support for LINQ queries.
  • Full support for passing DTO objects within Session and between postbacks.
  • Optimized SQL query execution.
  • Includes generated partial classes for customizing business objects.

A Quick Glance at Using Linquify

Linquify is a complete business class solution for LINQ to SQL or Entity Framework data context management. When using LINQ to SQL out of the box, one would need to make a series of complex calls to obtain a data context, save the object, and manage the data layer code. LINQ to SQL classes can also be difficult to pass within an ASP .NET web application’s Session. Linquify simplifies the database object management with easy-to-use business objects, allowing you to perform functionality as follows: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
// Instantiate a Linquify business object. 
Person person = new Person();

// Select and Update. 
person.Load(10); 
person.FirstName = "John"; 
person.Save(); 

// Update again. 
person.LastName = "Doe"; 
person.Save(); 

// Insert new person with auto-incremented ID (works for Guids too). 
person = new Person(null, "John", "Doe"); 
person.Save(); 

// Insert new person with ID 10, explicit insert.
person = new Person(10, "John", "Doe");
person.Insert();

// Select and Delete the person with ID = 10 using the Where clause. 
person.WhereClause.ID = 10; 
List<Person> personList = person.Find(); 
foreach (Person aPerson in personList) 
{ 
   aPerson.Delete(); 
} 

// Select the first 2 people with the last name containing 'Doe' using the Where clause. 
person.WhereClause.MyWhere = "WHERE LastName LIKE '%Doe%'"; 
person.WhereClause.MyTop = "TOP 2"; 
List<Person> personList = person.Find(); 
foreach (Person aPerson in personList) 
{ 
   ... 
} 

// Create your own custom method in the partial class to query using LINQ. 
public partial class Person 
{ 
   public static Person GetTestPerson(int id) 
   { 
      using (HelloWorld.DB.PersonContext context = new HelloWorld.DB.PersonContext()) 
      { 
         var result = from p in context.Person 
                      where p.ID == id 
                      select p; 

         return new Person(new BaseTypes.Person(result.ToList()[0])); 
      } 
   } 
} 

// Calling custom method from partial class. 
Person person = Person.GetTestPerson(10); 
person.LastName "Doe Jr."; 
person.Save(); 

// Binding to a GridView with all people is easy. 
myGridView.DataSource = person.ToList(); 
myGridView.DataBind();

As you can see from the above code, the business classes make working with LINQ to SQL and the Entity Framework data contexts a breeze. You have the option to utilize the built-in Where clause on each business class or use LINQ syntax for safer and expressive querying.

Linquify also supports all primary key types, including Int, Guid, auto-incrementing, etc, when inserting new objects to the database. Simply pass a null primary key to default to the auto-generated or auto-incremented value, as setup in the database table.

Linquify generates user partial classes, allowing you to easily extend the business objects with custom methods and custom LINQ queries, without affecting re-generation of business classes when the database table changes.

Embedding Business Objects

The Linquify business classes include overridable methods OnSave(), OnSaveComplete(), OnDelete(), OnDeleteComplete(), OnInitializeComplete(), allowing you to embed other business objects and load data as needed. Extending and customizing the business objects can be done in the automatically generated partial classes. For example: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// Embedding an Address business class within Person, forming a complete entity. 
public partial class Person 
{ 
   public Address Address { get; set; } 

   protected override void OnInitializeComplete() 
   { 
      // Load the address. 
      if (AddressId != null && AddressId != Guid.Empty) 
      { 
         Address = new Address(); 
         Address.Load(AddressId.GetValueOrDefault()); 
      } 

      base.OnInitializeComplete(); 
   } 

   protected override void OnSave() 
   { 
      if (Address != null) 
      { 
         // Save the address. 
         Address.Save(); 

         // Fill in our foreign key. 
         AddressId = Address.AddressId; 
      } 

      base.OnSave(); 
   } 
}

Transactions

Linquify transactions can be created by wrapping calls within the same LINQ2SQL or Entity Framework database context, as shown in the example below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using (HelloWorld.DB.PersonContext context = new HelloWorld.DB.PersonContext()) 
{
     Person person = new Person();
     person.SetTransactionContext(_context);

     foreach (Person p in person.ToList())
     {
          p.Age++;
          p.Save();
     }

     Address address = new Address(null, "123 Main St.", "Newark", "NJ", 12345);
     address.SetTransactionContext(_context);
     address.Save();

     person.Load(123);
     person.AddressId = address.AddressId;
     person.Save();

     person.Commit();
}

With the overview out of the way, let’s jump into a quick-start tutorial for using Linquify in your own project.

Linquify with LINQ to SQL - Quick Start Tutorial

1. Create a new visual Studio 2008 project (Console Application to make things simple).

2. Right-click the Solution (top-most item in the Solution Explorer) and select Add->New Project.

3. In the “Add New Project” window, select “Class Library” and name it: HelloWorld.DB

4. Click OK to add the new project HelloWorld.DB to your solution.

5. Delete the file Class1.cs from the HelloWorld.DB project, as we will not be needing it.

6. Right-click the HelloWorld.DB project and select Add->New Item.

7. In the “Add New Item” window, select “LINQ to SQL Classes” and name it: HelloWorldContext.dbml
This will be your LINQ2SQL data context.

8. Using the Server Explorer, drag your desired tables into the Object Relational Designer and save the file.

9. Select the LINQ2SQL file HelloWorldContext.dbml by highlighting it.

10. Click Tools->Linquify to launch the generator.

11. In the generator, enter the namespace: HelloWorld.Types.GeneratedTypes

12. Checkmark the option to “Use connection string specified in web.config” and enter the name: MyConnectionStringName

13. Click Continue to generate the business classes.
A new project will be added to your solution called HelloWorld.Types. You should be able to compile your solution at this point.

14. To begin using the generated types, add a reference to your main project, HelloWorld, by right-clicking the References folder and selecting “Add Reference”. Click the Projects tab. Select HelloWorld.Types and click OK.
You’ll also need to add a reference to System.Data.Linq, if you have not yet done so.

15. In your Program.cs file, add the using statement: using HelloWorld.Types.GeneratedTypes;

16. Add a new item to your main project, Application Configuration File (or web.config if using a web application).

17. In the App.config file, add your connection string with the name you specified in Linquify:

1
2
3
<connectionStrings> 
   <add name="MyConnectionStringName" connectionString="server=123.456.789.0; database=yourdatabase;uid=user;pwd=password;"/> 
</connectionStrings>

18. We’re done! You can now use the generated types with their native business class methods or with LINQ.

Linquify with the Entity Framework - Quick Start Tutorial

1. Repeat steps 1 - 6 above with a new Visual Studio solution.

2. In the “Add New Item” window, select “ADO.NET Entity Data Model” and name it: HelloWorldContext.edmx
This will be your Entity Framework data context.

3. In the Entity Framework setup wizard, select Generate From Database and click Next.
Select to include sensitive data in connection string (your option) and click Next.
Leave a checkmark on the option Save Entity Connection Settings in App.Config, and click Next.
Choose your desired tables from the database and click Finish.
Save and close the Entity Framework designer.

4. Select the Entity Framework file HelloWorldContext.edmx by highlighting it.

5. Click Tools->Linquify to launch the generator.

6. In the generator, enter the namespace: HelloWorld.Types.GeneratedTypes

7. Remove the checkmark from “Use connection string specified in web.config”, as we will use the default Entity Framework connection string.

8. Click Continue to generate the business classes.
A new project will be added to your solution called HelloWorld.Types. You should be able to compile your solution at this point.

9. To begin using the generated types, add a reference to your main project, HelloWorld, by right-clicking the References folder and selecting “Add Reference”. Click the Projects tab. Select HelloWorld.Types and click OK.
You’ll also need to add a reference to System.Data.Entity and HelloWorld.DB, if you have not yet done so.

10. In your Program.cs file, add the using statement: using HelloWorld.Types.GeneratedTypes;

11. Add a new item to your main project, Application Configuration File (or web.config if using a web application).

12. In the App.config file, copy the connection string from the HelloWorld.DB App.Config (generated by the Entity Framework):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<connectionStrings> 
   <add name="HelloWorldEntities" connectionString="metadata=res://*/HelloWorld.csdl|
        res://*/HelloWorld.ssdl|res://*/HelloWorld.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=123.456.789.0;Initial Catalog=
        yourdatabase;Persist Security Info=True;User ID=user;Password=password;
        MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /> 
</connectionStrings> ```

**13**. We're done! You can now use the generated types with their native business class methods or with LINQ.  

## Using Linquify and LINQ to SQL with MySQL or Oracle

If you are using the Entity Framework, Linquify supports all database platforms via the default Entity Framework database provider, and no additional changes are needed. Simply select your EDMX file and run Linquify, following the steps in the tutorial above.

If you are using LINQ2SQL, Linquify can support LINQ to SQL with a MySQL or Oracle database by generating the LINQ to SQL classes using DbLinq, an opensource LINQ2SQL generator. You'll need to download a copy of [DbLinq](http://code2code.net/DB_Linq/) (direct download [DbLinq binaries](temp/DbLinq-bin.html)) and, for MySql, you'll need to download the [ADO .NET MySql Connector](http://dev.mysql.com/downloads/connector/net/).  

To use DbLinq to generate the LINQ to SQL class for MySQL, follow these steps:  

**1**. Run the DbLinq command-line generator against your database as follows:  

dbmetal /server:123.456.789.0 /user:username /password:password /provider:MySql /database:yourdatabase /language:C#  

**2**. Rename the generated file to contain .designer.cs in it. In a new Visual Studio solution, follow the steps from the previous tutorial to create an empty HelloWorld.DB class library project and add the generated class.designer.cs file to your HelloWorld.DB project (this will take the place of your LINQ to SQL generated context.dbml file).  

**3**. We need to tweak/hack some items in the DbLinq generated file to work with Linquify and LINQ to SQL, but it's quite straight-forward. Inside class.designer.cs (your generated DbLinq file), rename the generated class to contain the word DataContext at the end and change the base class to the DbLinq MySql implementation, as follows:

public partial class MyDatabaseDataContext : DbLinq.MySql.MySqlDataContext
{
…
}

1
2
3

**4**. Add the following two using statements to the top of the DbLinq generated class, directly after the existing using statements:


using System.Configuration;
using MySql.Data.MySqlClient;
1
2
3

**5**. Remove the two existing constructors in the DbLinq generated class and replace it with the following (change class name and connection string name to suit your project):


public HelloWorldDataContext() : base(new MySqlConnection(ConfigurationManager.
ConnectionStrings[“MyConnectionStringName”].
ConnectionString))
{
}
1
2
3

**6**. Enclose the entire DbLinq generated class in a namespace, as follows:


namespace HelloWorld.DB
{
using System;
using System.Data;
using System.Data.Linq.Mapping;
using System.Diagnostics;
using System.Reflection;
using DbLinq.Data.Linq;
using DbLinq.Vendor;

…

public partial class MyDatabaseDataContext : DbLinq.MySql.MySqlDataContext
{
…

1
2
3
4
5
6
7

**7**. In the HelloWorld.DB project, add a Reference to System.Data.Linq, DbLinq, DbLinq.MySql, and MySql.Data (from the ADO .NET MySQL Connector link above).  

**8**. Select the HelloWorld.DB project and click Tools->Linquify, enter the name HelloWorld.Types.GeneratedTypes as the namespace.  

**9**. In the generated project, HelloWorld.Types, modify the file GeneratedTypes/BaseTypes/BusinessBaseType.cs to replace "DataContext" with "DbLinq.MySql.MySqlDataContext", as follows:


public abstract class BusinessBaseType<TEntity, TContext>
where TEntity : class, new()
where TContext : DbLinq.MySql.MySqlDataContext, new()
1
2
3

Also comment out the following line in the constructor:


//Context.DeferredLoadingEnabled = false;
1
2
3

Also change the using statement at the top of the file from using System.Data.Linq to using DbLinq.Data.Linq, as follows:


using DbLinq.Data.Linq;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

**10**. In the generated project, HelloWorld.Types, add a reference to DbLinq and DbLinq.MySql.

**11**. Add a new item to your main project, Application Configuration File (or web.config if using a web application).  

**12**. In the App.config file, add a connection string for your MySQL database using the same you entered for the connection string in the Linquify dialog.  

**13**. You're done! Back in your main project, add a reference to HelloWorld.Types, DbLinq, and DbLinq.MySql and you can now begin accessing the business objects in the same way as the SQL Server tutorial above, including LINQ queries.  

## Additional Tutorials

See also:
[Populating a DropDownList with Linquify and the Entity Framework in C# ASP .NET](/2010/01/18/populating-a-dropdownlist-with-linquify-and-the-entity-framework-in-c-asp-net/)  
[Creating Silverlight Web Applications with Linquify, LINQ to SQL, WCF](/2009/12/28/creating-silverlight-web-applications-with-linquify-linq-to-sql-wcf/)

## Linquify Project Architecture and File Descriptions

The above solution architecture offers 3-tiers: The user interface HelloWorld, the Types library HelloWorld.Types, and the database layer HelloWorld.DB. All database calls from the user interface go through your business layer and/or the Types library. This separation of layers allows easy inclusion in a variety of architectural patterns, such as the Repository pattern or Manager classes as needed. A detailed description of the Linquify project files are shown below:  

#### Project HelloWorld

This is your main program and user interface layer. This can be a console application or ASP .NET web application. This project will reference your business layer library or the generated Types library to access the database. Program logic and control stems from this project.  

#### Project HelloWorld.DB

This is your database layer project. This project contains your LINQ to SQL DBML file, your Entity Framework EDMX file, or your DbLinq generated LINQ to SQL file. This project provides a clear layer of separation from your user interface, business layer, and Types library. Note, while an app.config file may be generated from the ORM tools and placed in this project, you will need to copy the values in the app.config over to your main HelloWorld project so it can access the file and connection strings, as the main project runs in the context of the main project's folder.  

#### Project HelloWorld.Types

This is your Types library, containing business objects to represent the database layer. This project is automatically generated by Linquify and contains the files needed to access the database or extend your business objects. The files generated are as follows:  

HelloWorld.Types  
- GeneratedTypes  
- BaseTypes  
- UserTypes  
Person.cs  

#### Folder BaseTypes

This folder contains the lowest level business objects, next to the database, for accessing database calls and LINQ or Entity Framework connectivity. These classes contain private members for tracking dirty flags on each class property (to know which fields to update on SQL queries etc). You should never modify files in this folder. Any time you re-generate with Linquify, these files will be overwritten.  

#### Folder UserTypes

This folder contains light-weight business objects (or Data Transfer Objects DTO), available to the user for manipulating the database. These light-weight business objects can be passed throughout your application, in the web application Context, Session, business class methods, etc., and provide the main database access for your solution. You should never modify files in this folder. Any time you re-generate with Linquify, these files will be overwritten.  

#### Class Person.cs

This is a partial class for your light-weight business object (DTO). These files are automatically generated by Linquify, the first time ran, and allow you to customize the business object functionality to add custom data access methods and extend your business class. The following overriddable methods exist:  

OnSave(): occurs before the Save method of the business object completes  
OnSaveComplete(): occurs after the Save method of the business object has completed  
OnDelete(): occurs before the Delete method of the business object completes  
OnDeleteComplete(): occurs after the Delete method of the business object has completed  
OnInitializeComplete(): occurs after the Load method of the business object has completed  

Using these overridable methods, you can extend the functionality of the business classes to embed related business objects (see the Person and Address example above) or to add your own custom methods, LINQ code, and business rules.

## Full Support for Entity Framework Foreign Keys

Linquify 1.6 and above includes full support for foreign keys with both LINQ to SQL and the Entity Framework. Note, the the Entity Framework in .NET 3.5 has limitations with foreign keys, as they are not included in the default Entity Framework generated schemas. Linquify resolves this by including foreign keys in the generated type classes and mapping them directly to the Entity Framework foreign key relation objects. This gives you support for getting and setting foreign keys with the Entity Framework with no hassle, allowing you to write code with the Entity Framework, as follows:


Person person = new Person();
person.PersonId = 100; // Assign a primary key for this person
person.AddressId = 30; // Assign a foreign key for this person’s address, AddressId
person.Save(); // The person will be saved with a foreign key value for AddressId
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<a name="MANUALINSTALL"></a>
## Manually Installing the Add-in for Visual Studio 2008 / 2010 / 2012

The Linquify installer should create a Visual Studio 2008/2010 add-in automatically upon installing. However, if you do not see the Linquify toolbar option under the Tools menu in Visual Studio, follow these steps to create the add-in manually.  

**1**. Run Visual Studio and click the menu Tools->Add-in Manager. If you see an available add-in for Linquify, checkmark the box and click OK. You can now click Tools->Linquify to run the generator. If you did not see a Linquify add-in option inside the Addin Manager, follow the steps below to manually install the add-in.  

**To install in Visual Studio 2008 you will need to create an add-in file, as follows:**  

**1**. Create the following file in Notepad:  
C:\Users\your_user_name\Documents\Visual Studio 2008\Addins\Linquify.Addin  

**2**. Containing the following text:

Microsoft Visual Studio Macros

9.0

Microsoft Visual Studio

9.0

Linquify

Linquify by Primary Objects - Business Class Generator

C:\Program Files (x86)\Linquify\Linquify.dll

SQLToLINQGen.Connect

0

1

0


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

**3**. Change the <Assembly> path above to match your "Program Files" folder.  

On 32-bit computers this will be:  
&lt;Assembly&gt;C:\Program Files\Linquify\Linquify.dll&lt;/Assembly&gt;

On 64-bit computers this will be:  
&lt;Assembly&gt;C:\Program Files (x86)\Linquify\Linquify.dll&lt;/Assembly&gt; 

**4**. Exit Visual Studio if it is running. Run Visual Studio 2008 and the addin should appear.  

**To install in Visual Studio 2010 you will need to create an add-in file, as follows:**  

**1**. Create the following file in Notepad:  
C:\Users\your_user_name\Documents\Visual Studio 2010\Addins\Linquify.Addin  

**2**. Containing the following text:

Microsoft Visual Studio Macros

10.0

Microsoft Visual Studio

10.0

Linquify

Linquify by Primary Objects - Business Class Generator

C:\Program Files (x86)\Linquify\Linquify.dll

SQLToLINQGen.Connect

0

1

0


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

**3**. Change the <Assembly> path above to match your "Program Files" folder.  

On 32-bit computers this will be:  
&lt;Assembly&gt;C:\Program Files\Linquify\Linquify.dll&lt;/Assembly&gt;

On 64-bit computers this will be:  
&lt;Assembly&gt;C:\Program Files (x86)\Linquify\Linquify.dll&lt;/Assembly&gt;

**4**. Exit Visual Studio if it is running. Run Visual Studio 2010 and the addin should appear.  

**To install in Visual Studio 2012 RC you will need to create an add-in file, as follows: **  

**1**. In Windows, create the folder "Addins" at:

C:\Users\[YOUR_USERNAME]\Documents\Visual Studio 2012\Addins

**2**. In Visual Studio 2012 RC, click Tools->Options->Enviornment->Add-in Security

**3**. Click the Add button and enter the path you created from step 1 above. Click OK to save the path.

**4**. Exit Visual Studio.

**5**. Create the following file in Notepad:  
C:\Users\[YOUR_USERNAME]\Documents\Visual Studio 2012\Addins\Linquify.Addin  

Containing the following text:

Microsoft Visual Studio Macros

11.0

Microsoft Visual Studio

11.0

Linquify

Linquify by Primary Objects - Business Class Generator

C:\Program Files (x86)\Linquify\Linquify.dll

SQLToLINQGen.Connect

0

1

0


`

6. Start Visual Studio 2012 RC. Click Tools and you should see the Linquify icon at the top of the menu.

License and Usage

Writing and re-writing data access methods is boring and prone to error. This was the driving force behind the development of Linquify. We find that the business classes greatly reduce the time required to get an ASP .NET web application project off the ground, moving past the details of setting up data access methods, and immediately beginning work on logic and UI. We hope the Linquify tool can help other .NET developers as well.

Linquify v1.6 is free to use. The included template classes are licensed under the LGPL v2.1, giving sufficiently flexible permissions to allow the use of Linquify in both open source and commercial projects.

About the Author

Linquify and associated article(s) were written by Kory Becker, .NET software architect and founder of Primary Objects, a software and web application development company.

Share