Build sql query object by lambda

Question

Can anybody advise a lib to build a raw sql code by lambda expression? Do not advise EF and Linq2sql because it doesn't provide a control over query.

Something like this. It's the code from my own library. But I don't wish to support this if there is another good solution for this case.

[Fact]
public void TotalTest()
{
    var countFld = SqlField<Person>.Count(p => p.LastName);
    var select = new SqlSelect<Person>()
        .AddFields(p => p.LastName, p => p.Name)
        .AddFields<Passport>(p => p.Number)
        .AddField(countFld)
        .InnerJoin<Person, Passport>((person, passport) => person.Id == passport.PersonId)
        .Where(SqlFilter<Passport>.From(p => p.Number).IsNotNull().And(p => p.Number).NotEqualTo("3812-808316"))
        .GroupBy(p => p.LastName)
        .Having(SqlFilter<Person>.From<int>(countFld).GreaterThan(2))
        .OrderBy(p => p.LastName);

    var expected =
@"SELECT
    pe.LastName, pe.Name, pa.Number, COUNT(pe.LastName)
FROM
    Person pe
INNER JOIN
    Passport pa ON pe.Id = pa.PersonId
WHERE
    pa.Number IS NOT NULL AND pa.Number <> '3812-808316'
GROUP BY
    pe.LastName
HAVING
    COUNT(pe.LastName) > 2
ORDER BY
    pe.LastName";
    Assert.Equal(expected, select.CommandText);
}

Have you considered Entity Framework? Or do you *actually* need the SQL output? — Bobson, May 29 '16 at 22:26
I'm using Dapper, I need raw sql. And EF sometimes generate a very complex query and it doesn't provide a control over the query I don't like EF and its QueryableProvider — Serg046, May 29 '16 at 22:29
When you're making query building this manual, what is the lambda actually adding for you? Why not just code the TSQL directly? At least then you'll know what you're running... — Marc Gravell, May 29 '16 at 22:29
@MarcGravell, because lambda gives ability to refactor the source code. — Serg046, May 29 '16 at 22:33
@Serg046 being able to rename a column by pressing f2 isn't the end of refactoring; in reality, in any code that has out-of-process pieces (like an RDBMS) you always need to be aware of what you're doing *anyway*. We make *extensive* use of raw SQL in our C#, and refactoring has never been a pain point. Personally, I think the benefits of compiler-based checks in this scenario are over-stated. The reality is the compiler can't check against your RDBMS. — Marc Gravell, May 29 '16 at 22:49
I believe this will help http://stackoverflow.com/questions/18237312/get-sql-query-from-linq-to-sql — Venkatesh Muniyandi, May 29 '16 at 23:30
@MarcGravell, I'm generating db based on entities by FluentMigrator and usually I have some unit tests to check database schema. So I can use compiler to ensure that I have a valid sql. — Serg046, May 30 '16 at 09:43
@VenkateshMuniyandi, please read a question... "Do not advise EF and `Linq2sql` because it doesn't provide a control over query". — Serg046, May 30 '16 at 09:44

Serg046 · Accepted Answer · 2018-06-09T10:15:20.260

I've published the lib to GitHub with nuget package. It contains basic scenarios and will be updated as necessary. Setting fields, where, group by, having, order by, joins, nested queries are already supported.

https://github.com/Serg046/LambdaSql

Example:

var qry = new SqlSelect
(
    new SqlSelect<Person>()
        .AddFields(p => p.Id, p => p.Name)
        .Where(SqlFilter<Person>.From(p => p.Name).EqualTo("Sergey"))
    , new SqlAlias("inner")
).AddFields<Person>(p => p.Name);

Console.WriteLine(qry.ParametricSql);
Console.WriteLine("---");
Console.WriteLine(string.Join("; ", qry.Parameters
    .Select(p => $"Name = {p.ParameterName}, Value = {p.Value}")));

Output:

SELECT
    inner.Name
FROM
(
    SELECT
        pe.Id, pe.Name
    FROM
        Person pe
    WHERE
        pe.Name = @w0
) AS inner
---
Name = @w0, Value = Sergey

Build sql query object by lambda

1 Answers1