How to transform Amble expressions into sql commands?

Asked

Viewed 601 times

2

How can I manipulate expressions to help automate the creation of SQL commands 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 it like this:

var dao = new GenericDAL<Livro>();
dao.Query().OrderBy(livro => livro.Preco).ToList();
c# sql lambda-expressions linq-to-sql
  • 3

    This question is practically "How to create an ORM?". What you’re really trying to do is this?

  • No, I want to make my DAO layer more flexible. The examples are merely illustrative.

  • Without an object-relational mapping, it is impossible to generate a query. You have two options: allow a configurable mapping, or else adopt some kind of convention... something like "the property name is equal to the column name" is a convention. You would also have to define what happens when there are intermediate objects in lambda: dao.OrderBy(x => x.OutroObjeto.Nome)... do you want to support this? Any of the options is already a ORM (a mini-ORM so to say).

  • I already follow a pattern. My columns and tables have the names of the properties and classes. About supporting intermediate objects, I can even see if you are able to do this. But, I would like to know first how to do Orderby. I don’t want ORM, but if you’re going to take some characteristics of what a ORM would be, I don’t care.

  • After all, it’s very simple to do. You can even think of intermediate objects, create other types of methods like Leftjoin and Innerjoin instead of just Order By. You can even create a Where as well.

1 answer

1

Just following a pattern for the names when creating the lambda expression and making 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();
    }

Upshot:

select * from Livro order by Livro.Nome

Not difficult.

I found a way that leaves the result still mellhor. 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 goes like this:

select * from Livro order by Nome

even though I use something like lambda expression:

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

Browser other questions tagged c# sql lambda-expressions linq-to-sql

You are not signed in. Login or sign up in order to post.