Click here to Skip to main content
Click here to Skip to main content

Who Needs LINQ Anyway ? Build Queries with Intellisense Support in C#

, 5 Jun 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
This article explains how one could implement something that looks like LINQ in the current versions of the .NET Framework.

Introduction

As a C# developer, do you like to write your queries in SQL or in C# ? This article provides the solution...

Take a look at the following screen... The query (Access SQL) is completely built from C# code, and this is done with minimal effort.

In this article, I will show you how you can develop your own database query language in C#, without needing LINQ.

Background

A while ago, I needed an application framework to develop an application for one of my customers. At that time, there was a big buzz about the SubSonic project[^], so I decided to check it out.
While it was actually a step in the right direction, it felt to me as if it just was not sufficient, so I got very -a little bit too much - ambitious and decided to write my own within one week [^].
As you can imagine, doing it in one week was not possible, but I got very close.

Using the Code

This article shows a single item of my own framework that uses an approach I have not seen before. Therefore I think it might be useful to show other people how you can build code like this. This code will allow you to use C# syntax with intellisense to build your queries.

An example of what is possible with this approach (CST is the customers table and ORD is the orders table):

DB db = new DB(); 
string sql = Query<Customer>
.Select(db.CST.Id, db.CST.LastName,db.CST.FirstName) 
  .From(db.CST,db.ORD) 
 .Where(db.CST.FirstName >= "C") 
   // customer older than 20 years 
   .And(db.CST.DateOfBirth <= DateTime.Today.AddYears(-20)) 
      .And(db.CST.Id == db.ORD.Customer) // join orders and customers 
   .And(db.ORD.OrderDate >= DateTime.Today) // orders in the future ? 
.SQL; 
Console.WriteLine(sql); 
Console.ReadLine(); 

What Do I Need to do to Get this Functionality in my Own Application?

Either do it yourself or get some kind of code generator to implement the following code:

The DB class: implement all tables together with its columns inheriting from QrySrc and instancing a QryEl for each column. The datatype of the column is not relevant here.

// this class is autogenerated from the database in my own framework
class DB
{
    public class Customers : QrySrc
    {
        public QryEl Id;
        public QryEl FirstName;
        public QryEl LastName;
        public QryEl DateOfBirth;

        public Customers(string t,string a):base(t,a)
        {
            Id = new QryEl(this.Alias, "ID");
            FirstName = new QryEl(this.Alias, "FNAME");
            LastName = new QryEl(this.Alias, "LNAME");
            DateOfBirth = new QryEl(this.Alias, "BIRTHDATE");
        }
    }
    public Customers CST = new Customers("Customers", "CST");

    public class Orders : QrySrc
    {
        public QryEl OrderDate;
        public QryEl Customer;
        //...

        public Orders(string t, string c):base(t,c)
        {
            OrderDate = new QryEl(c, "ORDERDATE");
            Customer = new QryEl(c, "CUSTOMERID");
            //...
        }
    }

    public Orders ORD = new Orders("Orders", "ORD");
}

The Model class: This would be the class containing all classes contained within the model. The instance of the model should be loaded from a datareader based on the SQL that the query generates. I personally prefer to use reflection to load the values. In the downloadable example, this stuff is not implemented.

// this class is also autogenerated from the database in my own framework
    class Model
    {
        public class Customer
        {
            public int ID;
            public string FirstName;
            public string LastName;
            public DateTime DateOfBirth;
        }

        public class Order
        {
            //...
        }
    }

After that, you can use the query functionality in all it's glory, without using LINQ...

So How Does It Work?

With a whole lot of Bow-Chika-Bow-Wow-Chika_Bow® Smile | :)

The whole system is based on the following 3 classes:

The Query<T> Class

This is the class that offers the query functionality itself; it groups select/from/where functionalities and should implement a datareader. This class is always initialized using a Query<T>.Select(columns).From(tables).Where(expression).And/Or(Expression)...

  • T: A class type that will be instanced and used for mapping the results from the datareader. In this example code, the implementation of this part is left to the reader as an exercise.
  • columns: A series of QryEl class instances (DB.Table.Column)
  • tables: A series of QrySrc class instances (DB.Table)
  • expression: One or more QryEl instances together with an operator
    ( DB.Table.column <<operator>> [ DB.Table.column | object ] )

The QrySrc Class

This is the equivalent of a table in a database. It should be initialized with the tablename (for example "Customer") and table alias (for example "CST").

The QryEl Class

This can be any SQL expression you define, but most of the time this will be table columns and criteria. This is where all the magic happens: by overloading all default operators, and converting them to a string instead of executing them, we automatically get our SQL.

That's all folks !!!

Epilogue

The source code for this article is by no means a complete framework; it is just a quick mockup to show you how one small part of the framework works.

Some information for the more curious people regarding my complete framework (warning: shameless plug).

My own framework is a work in progress. Some features:

  • It is written in ASP.NET
  • It uses a buildprovider to automatically derive the class model from the database.
    The database needs to be modelled following a few easy rules, and automatically the built model also supports Parent/Child relationships and inheritance/late binding/updating only changed fields,logging, security, ...
  • The framework also offers a grid- and detailsview that is built on the fly, using dropdownbuttons, calendar controls, etc...
  • The model tracks updates, and posts only changed fields back to the database...
  • ...

Maybe I might release the source of the full framework to the community in the future, but I will not promise anything...

Voting

Please give your vote for this article. If you vote, let me know what you like and dislike, so I can improve this in the future!!

History

  • 2007-06-05: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tom Janssens
Founder Core bvba
Belgium Belgium
Tom Janssens, owner of Core, a software and consultancy company.
Father of two sons named Quinten & Matisse, and married to a beautiful woman named Liesbeth.
 
Blog: http://tojans.me
Github: http://github.com/ToJans
Twitter: http://twitter.com/ToJans
LinkedIn: http://www.linkedin.com/in/tomjanssens

Comments and Discussions

 
GeneralVery Nice PinmemberDecodedSolutions.co.uk2-Jun-10 23:01 
GeneralSimple is good! Pinmembercyberpuff12-Jun-07 6:11 
GeneralRe: Simple is good! PinmemberTom Janssens13-Jun-07 6:10 
GeneralRe: Simple is good! Pinmembercyberpuff13-Jun-07 8:24 
GeneralRe: Simple is good! [modified] PinmemberTom Janssens16-Jun-07 23:56 
GeneralDifferentiation PinmemberBWattsCO6-Jun-07 7:25 
AnswerRe: Differentiation PinmemberTom Janssens6-Jun-07 9:25 
GeneralRe: Differentiation PinmemberBill Seddon8-Jun-07 10:49 
GeneralRe: Differentiation PinmemberTom Janssens9-Jun-07 3:32 
GeneralRe: Differentiation Pinmemberggeurts11-Jun-07 23:12 
GeneralRe: Differentiation PinmemberBill Seddon11-Jun-07 23:53 
GeneralRe: Differentiation Pinmemberggeurts12-Jun-07 0:58 
QuestionInteresting approach... PinmemberMike Doyon5-Jun-07 5:15 
AnswerRe: Interesting approach... PinmemberTom Janssens5-Jun-07 5:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 5 Jun 2007
Article Copyright 2007 by Tom Janssens
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid