How to transform lambdas expressions into sql commands?

Question

How to transform lambdas expressions into sql commands?

Navigation

#1 by (1 votes)

2

How can I manipulate expressions to help automate the creation of SQL statements like Linq to SQL?

So: using EntityFramework I can make queries such as:

var livros = contexto.Livros
   .Where(livro => livro.SaldoEstoque > 0)
   .OrderBy(livro => livro.Preco);

livros.ToList(); // nesse momento a query é executada

How can I use this type of feature to automate my queries?

Example:

Let's say I have the following class:

public abstract GenericDAL<TModel> where T : class
{
    private SqlConnection _connection = null;
    public string TableName { get; set; }
    private string query;

    public GenericDAL()
    {
        _connection = new SqlConnection(Config.ConnectionString);
        TableName = "Nome da Tabela";
    }

    public GenericDAL<TModel> Query()
    {
        query = "";
        query = "select * from " + TableName;
        return this;
    }

    public GenericDAL<TModel> OrderBy(Expression<Func<TModel, object>> byProp)
    {
        // como adicionar a expressão à query?
        return this;
    }

    public List<TModel> ToList()
    {
        // executa a query e cria a lista
    }
}

And then, you can do this:

var dao = new GenericDAL<Livro>();
dao.Query().OrderBy(livro => livro.Preco).ToList();

sql c# linq-to-sql

asked by anonymous 10.11.2014 / 23:24

1 answer

Download dialog does not appear Convert string into parameter for arrayToDataTable (varString)

score 1 · Answer 1

Just following a pattern for the names at the time of creating the lambda expression and doing the following code I already managed to get a good result.

public GenericDAL<TModel> OrderBy(Expression<Func<TModel, object>> byProp)
    {
        query += " order by " + byProp.Body.ToString();
        return this;
    }

Example:

    public static void Main(string[] args)
    {
        var dao = new GenericDAL<Livro>();
        dao.Query().OrderBy(Livro => Livro.Nome);
        Console.WriteLine(dao.ToString());
        Console.ReadKey();
    }

Result:

select * from Livro order by Livro.Nome

Nothing difficult.

I've found a way that makes the result even better. So:

    public GenericDAL<TModel> OrderBy(Expression<Func<TModel, object>> byProp)
    {
        var expression = byProp.Body as dynamic;
        var termOfExpression = expression.Expression.Name;

        query += " order by " + byProp.Body.ToString().Replace(termOfExpression + ".", "");
        return this;
    }

The command looks like this:

select * from Livro order by Nome

Even though I use lambda expression as something like:

dao.Query.OrderBy(x => x.Nome);