Linqer – a nice tool for SQL to LINQ transition

Almost all .NET developers who have been working in several applications up to date are probably familiar with writing SQL queries for specific needs within the application. Before LINQ as a technology came on scene, my daily programming life was about 60-70% of the day writing code either in the front-end (ASPX, JavaScript, jQuery, HTML/CSS etc…) or in the back-end (C#, VB.NET etc…), and about 30-40% writing SQL queries for specific needs used within the application. Now, when LINQ is there, I feel that the percentage for writing SQL queries got down to about 10% per day. I don’t say it won’t change with time depending what technology I use within the projects or what way would be better, but since I’m writing a lot LINQ code in the latest projects, I thought to see if there is a tool that can automatically translate SQL to LINQ so that I can transfer many queries as a LINQ statements within the code.

Linqer is a tool that I have tested in the previous two weeks and I see it works pretty good. Even I’m not using it yet to convert SQL to LINQ code because I did it manually before I discovered that Linqer could have really helped me, I would recommend it for those who are just starting with LINQ and have knowledge of writing SQL queries.

Let’s pass through several steps so that I will help you get started faster…

1. Go to http://www.sqltolinq.com/ website and download the version you want. There is a Linqer Version 4.0.1 for .NET 4.0 or Linqer Version 3.5.1 for .NET 3.5.

2. Once you download the zip file, extract it and launch the Linqer4Inst.exe then add install location. In the location you will add, the Linqer.exe will be created.

3. Launch the Linqer.exe. Once you run it for first time, the Linqer Connection Pool will be displayed so that you can create connection to your existing Model

Click the Add button

Right after this, the following window will appear

#1 – The name of the connection string you are creating
#2 – Click “…” to construct your connection string using Wizard window

#3 – Chose your language, either C# or VB

#4 – Model LINQ to SQL or LINQ to Entities

Right after you select LINQ to SQL, the options to select the files for the Model will be displayed. In our case I will select LINQ to SQL, and here is the current progress

So, you can select existing model from your application or you can Generate LINQ to SQL Files so that the *.dbml and *.designer.cs will be automatically filled

#5 – At the end, you can chose your context name of the model which will be used when generating the LINQ code

Once you are done, click OK.

You will get back to the parent window filled with all needed info

and click Close.

Note: You can later add additional connections in your Linqer Connections Pool from Tools –> Linqer Connections

In the root folder where your Linqer.exe is placed, now you have Linqer.ini file containing the Connection string settings.

Ok, now lets go to the interesting part.

Lets create one (first) simple SQL query and try to translate it to LINQ statement.

SQL Query

If we add this query to Linqer, here is the result:

So, the LINQ code is similar to the SQL code and is easy to read since it’s simple. Also, if you notice, the tool generates class (you can add class name) with prepared code for using in your project. Perfect!

Now, lets try to translate a query with two joined tables (little bit more complex):

SQL Query

select * from employee
left join publishers
on employee.pub_id = publishers.pub_id
where employee.fname like '%a'

The LINQ generated code is: 

from employee in db.Employee
join publishers in db.Publishers on employee.Pub_id equals publishers.Pub_id into publishers_join
from publishers in publishers_join.DefaultIfEmpty()
where
  employee.Fname.EndsWith("a")
select new {
  employee.Emp_id,
  employee.Fname,
  employee.Minit,
  employee.Lname,
  employee.Job_id,
  employee.Job_lvl,
  employee.Pub_id,
  employee.Hire_date,
  Column1 = publishers.Pub_id,
  Pub_name = publishers.Pub_name,
  City = publishers.City,
  State = publishers.State,
  Country = publishers.Country
}

So, if you can notice the where clause, we said in the SQL query: ... like "%a" and the corresponding LINQ code in C# is ... EndsWith("a"); - Excellent!

And the Class automatically generated by the tool is

public class EmployeePubClass
{
    private String _Emp_id;
    private String _Fname;
    private String _Minit;
    private String _Lname;
    private Int16? _Job_id;
    private Byte? _Job_lvl;
    private String _Pub_id;
    private DateTime? _Hire_date;
    private String _Column1;
    private String _Pub_name;
    private String _City;
    private String _State;
    private String _Country;
    public EmployeePubClass(
        String AEmp_id, String AFname, String AMinit, String ALname,
        Int16? AJob_id, Byte? AJob_lvl, String APub_id, DateTime? AHire_date,
        String AColumn1, String APub_name, String ACity, String AState,
        String ACountry)
    {
        _Emp_id = AEmp_id;
        _Fname = AFname;
        _Minit = AMinit;
        _Lname = ALname;
        _Job_id = AJob_id;
        _Job_lvl = AJob_lvl;
        _Pub_id = APub_id;
        _Hire_date = AHire_date;
        _Column1 = AColumn1;
        _Pub_name = APub_name;
        _City = ACity;
        _State = AState;
        _Country = ACountry;
    }
    public String Emp_id { get { return _Emp_id; } }
    public String Fname { get { return _Fname; } }
    public String Minit { get { return _Minit; } }
    public String Lname { get { return _Lname; } }
    public Int16? Job_id { get { return _Job_id; } }
    public Byte? Job_lvl { get { return _Job_lvl; } }
    public String Pub_id { get { return _Pub_id; } }
    public DateTime? Hire_date { get { return _Hire_date; } }
    public String Column1 { get { return _Column1; } }
    public String Pub_name { get { return _Pub_name; } }
    public String City { get { return _City; } }
    public String State { get { return _State; } }
    public String Country { get { return _Country; } }
}
public class List: List<EmployeePubClass>
{
    public List(Pubs db)
    {
        var query =
            from employee in db.Employee
            join publishers in db.Publishers on employee.Pub_id equals publishers.Pub_id into publishers_join
            from publishers in publishers_join.DefaultIfEmpty()
            where
              employee.Fname.EndsWith("a")
            select new {
              employee.Emp_id,
              employee.Fname,
              employee.Minit,
              employee.Lname,
              employee.Job_id,
              employee.Job_lvl,
              employee.Pub_id,
              employee.Hire_date,
              Column1 = publishers.Pub_id,
              Pub_name = publishers.Pub_name,
              City = publishers.City,
              State = publishers.State,
              Country = publishers.Country
            };
        foreach (var r in query)
            Add(new EmployeePubClass(
                r.Emp_id, r.Fname, r.Minit, r.Lname, r.Job_id, r.Job_lvl,
                r.Pub_id, r.Hire_date, r.Column1, r.Pub_name, r.City, r.State,
                r.Country));
    }
}

Great! We have ready-to-use class for our application and we don't need to type all this code.

Besides this way to generate code, you can in same time use this tool to see the db results

I like this tool because mainly it’s very easy to use, lightweight and does the job pretty straight forward.

You can try the tool and send me feedback using the comments in this blog post.