DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Transactional Outbox Patterns Step by Step With Spring and Kotlin
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Part 3 of My OCP Journey: Practical Tips and Examples
  • A React Frontend With Go/Gin/Gorm Backend in One Project

Trending

  • Transactional Outbox Patterns Step by Step With Spring and Kotlin
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Part 3 of My OCP Journey: Practical Tips and Examples
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  1. DZone
  2. Data Engineering
  3. Databases
  4. Linqer – a nice tool for SQL to LINQ transition

Linqer – a nice tool for SQL to LINQ transition

Hajan Selmani user avatar by
Hajan Selmani
·
Jan. 24, 11 · News
Like (0)
Save
Tweet
Share
65.94K Views

Join the DZone community and get the full member experience.

Join For Free

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

select * from authors a
where a.city = 'Oakland'

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.

sql Database code style Connection (dance) application Connection pool Strings .NET Data Types

Published at DZone with permission of Hajan Selmani, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Transactional Outbox Patterns Step by Step With Spring and Kotlin
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Part 3 of My OCP Journey: Practical Tips and Examples
  • A React Frontend With Go/Gin/Gorm Backend in One Project

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: