Linq like predicate to sql string

Question

I want to make a function which should accept linq like expression based on model and property types of models and return a SQL string.

public class MyModel
{
    public int Year { get; set; }

    public string UserName { get; set; }
}

public static class MyControlClass
{
    public static string BuildSQL<TModel>(Func<TModel, bool> whereExpression)
    {
        return $"SELECT * FROM {nameof(TModel)} WHERE {whereExpression}";
    }
}

public main(){
    string sqlQuery = MyControlClass.BuildSQL<MyModel>(m => m.Year == 2021 && UserName == "Tester");
    
    //What I wish sqlQuery to be
    //SELECT * FROM MyModel WHERE Year = 2021 AND UserName = "Tester"
}

Any ideas how to accomplish this? I am aware of POCO and other ORM libraries and I have been trying and searching for more than 3 days now. I just cant find a solution.

If your database supports linq-to-sql you shouldn't build your sql yourself. — Palle Due
– Palle Due, Commented Dec 9, 2021 at 14:36
@PalleDue I wonder how to make something not what I should or "shouldn't" — Secretary Of Education
– Secretary Of Education, Commented Dec 9, 2021 at 14:57
@HenkvanBoeijen do you have any example of mentioned framework to use in my case and get the foreseen result? — Secretary Of Education
– Secretary Of Education, Commented Dec 9, 2021 at 14:58
Well, stated differently, @PalleDue says: you should let an ORM do the job for you. If you are aware of ORM libraries, does that mean you actually tried any of them? — Gert Arnold
– Gert Arnold, Commented Dec 9, 2021 at 15:02

Svyatoslav Danyliv · Accepted Answer · 2021-12-09 15:46:41Z

Solution is not simple at all. And you have to deal with Expression<Func<TModel, bool>> whereExpression

Then you can visit this expression ExpressionVisitor for example this answer and generate SQL.

Or you can save your time with escaping strings, dates, ect., install linq2db and just write:

public class MyModel
{
    public int Year { get; set; }

    public string UserName { get; set; }
}

public static void Main()
{   
    // create options builder
    var builder = new LinqToDbConnectionOptionsBuilder();

    // configure connection string
    var options = builder.UseSqlServer(connectionString);

    using var db = new DataConnection(options);

    var query = db.GetTable<MyModel>().Where(m => m.Year == 2021 && m.UserName == "Tester");

    // will generate SQL
    var sql = query.InlineParameters().ToString();

    // will return list of items
    var items = query.ToList();
}

Collectives™ on Stack Overflow

Linq like predicate to sql string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related