How to check the consistency of memory data, dynamic express is a big help

1: Background

1. Tell a story

Remember that when the last company did a full memory project, because some key tables would be fully poured into the memory when the program was started up, but with the passage of time, data differences occasionally occurred in the synchronization between the memory and the database, accompanied by the bug s reported by the operation. It is very simple to check the data integrity of the database. Just write some sql to verify it directly, But verifying the data in memory is very troublesome, because you can't directly check the memory collection in production like writing sql. What should you do? In order to facilitate the demonstration of the problem, the previous demonstration code:

    class Program
    {
        static void Main(string[] args)
        {
            var tradeList = new List<Trade>()
            {
                new Trade(){TradeID=1, TradeTitle="Transaction 1", Created=Convert.ToDateTime("2020/8/1"), CustomerID=1},
                new Trade(){TradeID=2, TradeTitle="Transaction 2", Created=Convert.ToDateTime("2020/8/5"),CustomerID=2},
                new Trade(){TradeID=3, TradeTitle="Transaction 3", Created=Convert.ToDateTime("2020/8/10"), CustomerID=3}
            };
        }
    }

    class Trade
    {
        public int TradeID { get; set; }

        public string TradeTitle { get; set; }

        public DateTime Created { get; set; }

        public int CustomerID { get; set; }
    }

The tradelist above is a collection in memory. Now there is a problem. I want to query whether the records of CustomerID in (1,2,10) & & created < = '2020-08-01' in the trade table are consistent with the tradelist in memory.

It's too easy to verify with sql. Write sql directly in the query analyzer, as shown in the following figure:

How to verify on the UI?

2: Find a solution

1. Customize advanced query on UI

This is also the easiest thing to think of. Use multiple if to overlay query conditions, as shown in the following code:

        static void Main(string[] args)
        {
            var tradeList = new List<Trade>()
            {
                new Trade(){TradeID=1, TradeTitle="Transaction 1", Created=Convert.ToDateTime("2020/8/1"), CustomerID=1},
                new Trade(){TradeID=2, TradeTitle="Transaction 2", Created=Convert.ToDateTime("2020/8/5"),CustomerID=2},
                new Trade(){TradeID=3, TradeTitle="Transaction 3", Created=Convert.ToDateTime("2020/8/10"), CustomerID=3}
            };

            IEnumerable<Trade> query = tradeList;

            //UI
            var queryCustomerIDList = new List<int>() { 1, 2, 10};
            var queryCreated = "2020-08-01";

            if (queryCustomerIDList.Count > 0)
            {
                query = query.Where(m => queryCustomerIDList.Contains(m.CustomerID));
            }
            if (string.IsNullOrEmpty(queryCreated))
            {
                query = query.Where(m => m.Created <= Convert.ToDateTime(queryCreated));
            }

            //Final results
            var list = query.ToList();
        }

It seems that the problem can be solved, but don't you think it's too inflexible to use if superposition? If the customer is in a bad mood and comes with a TradeID between 1 and 10, does the above code have to add a TradeID judgment? It's too much trouble. We have to continue to find more flexible postures.

2. Use DataTable

Haha, you should not underestimate DataTable because it directly supports sql query. The flexibility should not be underestimated. The last code says:

        static void Main(string[] args)
        {
            var tradeList = new List<Trade>()
            {
                new Trade(){TradeID=1, TradeTitle="Transaction 1", Created=Convert.ToDateTime("2020/8/1"), CustomerID=1},
                new Trade(){TradeID=2, TradeTitle="Transaction 2", Created=Convert.ToDateTime("2020/8/5"),CustomerID=1},
                new Trade(){TradeID=3, TradeTitle="Transaction 3", Created=Convert.ToDateTime("2020/8/10"), CustomerID=3}
            };

            var table = CopyToDataTable(tradeList);

            var query = table.Select("CustomerID in (1,2,10) and Created <= '2020-08-01' and TradeID >= 1 and TradeID <= 10")
                            .Select(m => new Trade()
                            {
                                TradeID = Convert.ToInt32(m[0]),
                                TradeTitle = Convert.ToString(m[1]),
                                Created = Convert.ToDateTime(m[2]),
                                CustomerID = Convert.ToInt32(3)
                            }).ToList();
        }

        public static DataTable CopyToDataTable<T>(IEnumerable<T> array)
        {
            var ret = new DataTable();
            foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                ret.Columns.Add(dp.Name);
            foreach (T item in array)
            {
                var Row = ret.NewRow();
                foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                    Row[dp.Name] = dp.GetValue(item);
                ret.Rows.Add(Row);
            }
            return ret;
        }

Is it very powerful to directly insert textual sql into DataTable, and you can write whatever kind of sql you want. Of course, theory belongs to theory. I certainly won't play like this in my scene. After all, there are tens of millions of trade lines in memory. Turning into DataTable is not digging a hole for yourself. Is there any other way?

3. Use expression tree

I think many people will give up when they see the expression tree. Although this thing is very powerful, it is too complex. It will disassemble your query statement into nodes in the tree to build a very complex tree structure. In fact, the sql statement parsing of DataTable also constructs a parsing tree in memory, so this thing is too anti-human. For example, if you want to build a query with I > 5, you need the following hard coding, It's still very simple. Complex will make you vomit blood.

            ParameterExpression param = Expression.Parameter(typeof(int), "i");
            ConstantExpression constExp = Expression.Constant(5, typeof(int));
            BinaryExpression greaterThan = Expression.GreaterThan(param, constExp);
            Expression<Func<int, bool>> f = Expression.Lambda<Func<int, bool>>(greaterThan, param);
            Func<int, bool> mydelegate = f.Compile();
            Console.WriteLine(mydelegate(5));

As can be seen from the figure, 5 > 5 = false is no problem. Since the expression tree can solve such scenarios, you should think that whether the open source community is similar to the encapsulated ExpressionTree development package? To tell you the truth, there are...

4. DynamicExpresso development kit

Good open source method, github address: https://github.com/davideicar... , this thing realizes the dynamic conversion of textual C# statements into delegate. What does this sentence mean? You can take a look at this picture:

As can be seen from the above figure, you can write some textual C# statements, and then convert them into executable delegate after being processed by DynamicExpresso. If you don't understand it, I'll show it in code, as shown in the following figure:

Where: 30 = 5 * 8 / 2 + 10, the key point is that the mathematical expression here is text. With this idea, can I also express the query conditions of tradeList in text, as shown in the following code:

            var interpreter = new Interpreter();

            interpreter.Reference(typeof(System.Linq.Enumerable));

            interpreter.SetVariable("arr", new int[] { 1, 2, 10 });

            string whereExpression = "(trade.CustomerID == 1 || trade.CustomerID==2 || trade.CustomerID==10) && " +
                                     "trade.Created <= Convert.ToDateTime(\"2020-08-01\") &&" +
                                     "trade.TradeID >= 1 && " +
                                     "trade.TradeID <=10";

            Func<Trade, bool> queryFunc = interpreter.ParseAsDelegate<Func<Trade, bool>>(whereExpression, "trade");

            var list = tradeList.Where(queryFunc).ToList();

            var i = Enumerable.Contains(new int[] { 1, 2, 3 }, 3);

The problem is solved. It's still perfect 🙂🙂🙂

3: Summary

In general, with DynamicExpresso, I can throw textual C# statements directly to Where conditions, and I can flexibly retrieve them. It perfectly solves the problem of querying tradelist data distribution in memory. Of course, there are still many statements that are not supported by DynamicExpresso, but they are all in the process of improvement. I look forward to your support and contributions.

Tags: C#

Posted by katuki on Sun, 22 May 2022 20:26:38 +0300