sql where clause rule in linq

Question

I'm trying to put my SQL rule in Linq , as my rule get generated from query builder and I need to filter my data based on rule , this is my simple example

class Program
{
    static void Main(string[] args)
    {
        PromotionVm lObjPromVm = new PromotionVm();
        for (int i = 1; i <= 5; i++)
        {
            PromotionList lObjPromList = new PromotionList();
            lObjPromList.active_indicator = 1;
            lObjPromList.principle_code = "a" + i;
            lObjPromList.promotion_code = "b" + i;
            lObjPromList.promotion_plan_number = 20 + i;
            lObjPromList.promotion_type_code = 30 + i;
            lObjPromList.start_date = DateTime.Now.AddDays(i);
            lObjPromVm.promotion_list.Add(lObjPromList);
        }

        //var sqlRule= "promotion_type_code = 'expensive' AND Category IN('Food', 'Transportation', 'Shopping') AND(PaymentMode = 'Cash' OR PaymentMode = 'Debit Card' OR(Amount = 35))";
        var sqlRule = "promotion_type_code = '33'";
        //  lObjPromVm.promotion_list.ToDataTable()
        var lOutlut = lObjPromVm.promotion_list.Where(sqlRule);
    }
}

class PromotionVm
{
    public List<PromotionList> promotion_list { get; set; }
    public PromotionVm()
    {
        promotion_list = new List<PromotionList>();
    }
}
    
public class PromotionList
{
    public string principle_code { get; set; }
    public string promotion_code { get; set; }
    public int promotion_plan_number { get; set; }
    public int promotion_type_code { get; set; }
    public DateTime start_date { get; set; }
    public int active_indicator { get; set; }
}

I'm trying to use System.Linq.Dynamic.Core; but not working.

Can anyone suggest how I can filter my data by SQL rules? same question was asked here How to use a string in the linq where clause? but response what is given , its not working .

hi i want to filter based on sql rule which can be anything like sql where clause which i get from front end . for example - //var sqlRule= "promotion_type_code = 'expensive' AND Category IN('Food', 'Transportation', 'Shopping') AND(PaymentMode = 'Cash' OR PaymentMode = 'Debit Card' OR(Amount = 35))"; — Dilip, Jul 13 '21 at 17:02
@charlieface, typo: you're going to want to double up that equals sign before the "33": `public => p.promotion_type_code == "33"` — Flydog57, Jul 13 '21 at 17:10
Sounds like a bad idea, what happens if someone types in `1=1; DROP DATABASE YourDb --` — Charlieface, Jul 13 '21 at 18:53
that we can always check , delete , drop , truncate etc, any of keyword before processing the request . — Dilip, Jul 14 '21 at 05:02
You really don't want to do that, there are always ways around keyword blacklisting, see https://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use — Charlieface, Jul 14 '21 at 10:04

score 0 · Answer 1 · edited Jul 14 '21 at 13:44

0

I was able to solve the problem , I just needed to convert to AsQueryable() .

var sqlRule = "promotion_type_code in (31,33) or (promotion_code=\"b2\")";
var lOutlut = lObjPromVm.promotion_list.AsQueryable().Where(sqlRule);

edited Jul 14 '21 at 13:44

Mohamad Mousheimish

1,641
3
16
48

answered Jul 13 '21 at 17:36

Dilip

697
6
16
35

sql where clause rule in linq

1 Answers1