SQLinq

SQLinq allows you to use LINQ (Language INtegrated Query) features of .NET to generate Ad-Hoc SQL Queries at runtime.

This allows for easy generation of ad-hoc SQL code using strongly typed LINQ code in .NET in a similar fashion to writing queries with Entity Framework. However, SQLinq is NOT an ORM. SQLinq is a library for generating ad-hoc, strongly typed SQL code at runtime. The generated SQL code can then be executed against a SQL database using either ADO.NET directly, or with Dapper.NET using the SQLinq Dapper nuget package. One of the benefits of SQLinq is that you can write ad-hoc SQL code at runtime that isn't just strongly typed, but benefits from compile time validation as well.

Nuget Package

http://nuget.org/packages/sqlinq

SQLinq Usage

Step 1: Create your data object in code (like the following examples) that matches the database table or view you want to select from. It can either be a class or interface. You can also name the object and/or its properties differently than the database by using the SQLinqTable and SQLinqColumn attributes to specify their name in the database.

[SQLinqTable("PersonTable")]publicclassPerson{publicGuidID{get;set;}[SQLinqColumn("First_Name")]publicstringFirstName{get;set;}[SQLinqColumn("Last_Name")]publicstringLastName{get;set;}publicintAge{get;set;}}

Step 2: Use LINQ to generate the ad-hoc SQL query necessary.

varquery=fromdinnewSQLinq<Person>()whered.FirstName.StartsWith("C")&&d.Age>18
orderby d.FirstNameselectnew{id=d.ID,firstName=d.FirstName};

Step 3: Generate the SQL code and necessary query parameter key/value pairs.

varqueryResult=query.ToSQL();// get the full SQL codevarsqlCode=queryResult.ToQuery();// get the query parameters necessary to execute the above queryvarsqlParameters=queryResult.Parameters;

Step 4: Create SqlCommand and set the SQL code and Query Parameters

varcmd=newSqlCommand(dbconnection,sqlCode);foreach(varpinsqlParameters){cmd.Parameters.AddWithValue(p.Key,p.Value);}// now execute the command and get the results from the database

SQLinq.Dapper

SQLinq.Dapper is a small helper library that bridges the gap between SQLinq and Dapper dot net to allow for queries to be performed more easily.

SQLinq.Dapper Usage:
Here's a simple example of using SQLinq.Dapper:

IEnumerable<Person>data=null;using(IDbConnectioncon=GetDbConnection()){con.Open();data=con.Query(frompinnewSQLinq<Person>()wherep.FirstName.StartsWith("C")&&p.Age>21
orderby p.FirstNameselectp);con.Close();}// do somthing with the data that was returned

Install SQLinq.Dapper via Nuget
SQLinq.Dapper can also be installed into your project via Nuget!
http://nuget.org/packages/SQLinq.Dapper

Articles

SQLinq: Use LINQ to generate Ad-Hoc, strongly typed SQL queries