在 Postgres 使用 TransactionScope 似乎很空易就會發生 Transaction Abort 的情況。所以必需自行實做 retry 的機制

            int tries = 0;

            while (tries < Su.PgSql.maxTransactionAborted)

            {

                try

                {

                    using (var scop = new System.Transactions.TransactionScope(TransactionScopeAsyncFlowOption.Enabled))

                    {

                        var context = ShopBandContext.Context;



                        updateCount = await context.UpdateAsync<MarketDiscount>(dto, Sc.ModifyInfo,

                            onlyColumns: "StartAt,EndAt,NewPrice,FrontEndMemo,BackEndMemo");



                        scop.Complete();

                        break; //這個 Break 很重要

                    }

                }

                catch (Exception ex)

                {

                    // 我發現因為是非同步模式，所以要檢查很多層的 InnerException

                    if (tries < Su.PgSql.maxTransactionAborted

                        && ((ex.InnerException != null && ex.InnerException is Npgsql.PostgresException && ((Npgsql.PostgresException)ex.InnerException).SqlState == "40001")

                            || (ex.InnerException.InnerException != null && ex.InnerException.InnerException is Npgsql.PostgresException && ((Npgsql.PostgresException)ex.InnerException.InnerException).SqlState == "40001")))

                    {

                        //隨機休息 10 ~ 20 ms, 等另一個 job 完工

                        System.Threading.Thread.Sleep(Su.MathUtil.Random.Next(10, 20));

                        tries++;

                    }

                    else

                    {

                        throw;

                    }

                }

            }

.net 6 在 model 中上傳日期字串時，如果遇到 "The JSON value could not be converted to System.Nullable`1[System.DateTime]"  這個錯誤，解決方法如下:

1. 安裝套件: Microsoft.AspNetCore.Mvc.NewtonsoftJson

2. 在 program.cs 中，原來來的

builder.Services.AddControllers()

改為 (其 options 的部份非必要)

builder.Services.AddControllers()
    .AddNewtonsoftJson(options =>
     {
         options.SerializerSettings.ContractResolver = new Su.CamelCaseContractResolver();
     });

找了很久，原來就在 梨子給的範例裡。

假設有兩個 expression: e1, e2

            var combineBody = Expression.AndAlso(e1.Body, Expression.Invoke(e2, e1.Parameters[0]));

            var finalExpression = Expression.Lambda<Func<TestClass, bool>>(combineBody, e1.Parameters).Compile();

同理，把上面的 AndAlso 換成 OrElse 就可以用 Or 合併。

即使只有兩行，還是不太可能背起來，所以當然要來做一下擴充

    public static class ExpressionExtension

    {

        public static Expression<Func<T, bool>> AndAlso<T>(this Expression<Func<T, bool>> e1, Expression<Func<T, bool>> e2)

        {

            var combineE = Expression.AndAlso(e1.Body, Expression.Invoke(e2, e1.Parameters[0]));



            return Expression.Lambda<Func<T, bool>>(combineE, e1.Parameters);

        }



        public static Expression<Func<T, bool>> OrElse<T>(this Expression<Func<T, bool>> e1, Expression<Func<T, bool>> e2)

        {

            var combineE = Expression.OrElse(e1.Body, Expression.Invoke(e2, e1.Parameters[0]));



            return Expression.Lambda<Func<T, bool>>(combineE, e1.Parameters);

        }

    }

使用範例:

            Expression<Func<Market, bool>> e1 = e => e.Is_Deleted == isDelete;



            Expression<Func<Market, bool>> e2 = e => string.IsNullOrEmpty(marketNo) || e.MarketNo.ToUpper().Contains(marketNo.ToUpper());



            return new

            {

                andMarkets = Ds.PageContext.ShopBandContext.Markets.Where(e1.AndAlso(e2)).ToList(),

                orMarkets = Ds.PageContext.ShopBandContext.Markets.Where(e1.OrElse(e2)).ToList(),

            };

 再補兩個擴充, 可以把多個 Expression 用 AndAlso 或 OrElse 串在一起:

        public static Expression<Func<T, bool>> OrElseAll<T>(this IEnumerable<Expression<Func<T, bool>>> exps)

        {

            if (exps.Count() == 1)

            {

                return exps.First();

            }



            var e0 = exps.First();



            var orExp = exps.Skip(1).Aggregate(e0.Body, (x, y) => Expression.OrElse(x, Expression.Invoke(y, e0.Parameters[0])));



            return Expression.Lambda<Func<T, bool>>(orExp, e0.Parameters);

        }



        public static Expression<Func<T, bool>> AndAlsoAll<T>(this IEnumerable<Expression<Func<T, bool>>> exps)

        {

            if (exps.Count() == 1)

            {

                return exps.First();

            }



            var e0 = exps.First();



            var orExp = exps.Skip(1).Aggregate(e0.Body, (x, y) => Expression.AndAlso(x, Expression.Invoke(y, e0.Parameters[0])));



            return Expression.Lambda<Func<T, bool>>(orExp, e0.Parameters);

        }

使用範例:

            Expression<Func<Market, bool>> q = e =>

            e.Is_Deleted == "N"

            && (string.IsNullOrEmpty(marketNo) || e.MarketNo.ToLower().Contains(marketNo.ToLower()))

            && (string.IsNullOrEmpty(isCombination) || isCombination != "Y" || e.TypeEnum == 200);



            if (!string.IsNullOrEmpty(name))

            {

                var nameList = name.Split(',').Select(e => e.Trim())

                    .Where(e => !string.IsNullOrEmpty(e));



                if (nameList.Any())

                {

                    q = q.AndAlso(nameList

                        .Select(s => (Expression<Func<Market, bool>>)(e => e.Name.ToLower().Contains(s.ToLower())))

                        .OrElseAll());

                }

            }

另外在 google，整理了 stackoverflow 幾篇文章之後得到的另一個方法，比較複雜, 不過可以讓人理解一下 Expression 比較底層的東西，也留下來參考一下。

    internal class MergeTool : ExpressionVisitor

    {

        private readonly ParameterExpression _parameter;



        protected override Expression VisitParameter(ParameterExpression node)

        {

            return base.VisitParameter(_parameter);

        }



        internal MergeTool(ParameterExpression parameter)

        {

            _parameter = parameter;

        }



        public static Expression<Func<T, bool>> MergedExpression<T>(Expression<Func<T, bool>> e1, Expression<Func<T, bool>> e2)

        {

            ParameterExpression param = Expression.Parameter(typeof(T));



            BinaryExpression MergeBody = Expression.AndAlso(e1.Body, e2.Body);



            var ReplacedBody = (BinaryExpression)new MergeTool(param).Visit(MergeBody);



            return Expression.Lambda<Func<T, bool>>(ReplacedBody, param);

        }

    }



使用時要 Compile



            var mergedExpression = MergeTool.MergedExpression(e1, e2);



            var list = testList.Where(mergedExpression.Compile());

-- 自動更新某個欄位

CREATE OR REPLACE FUNCTION before_market_update ()
RETURNS TRIGGER AS 
$$
BEGIN
    NEW.LMDT = LocalTimeStamp;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER updt_market
  BEFORE UPDATE
  ON "Market"
  FOR EACH ROW
  EXECUTE PROCEDURE before_market_update();

在 Program.cs 由註冊的 Filter 會在 Action 或 Controller 註冊的 Filter 之前執行。

IOrderedFilter 也無法改變執行順序哦。

使用 android device 時，遇到 "若要建立此專案，必須安裝下列工作負載: maccatalyst" 這個錯誤



In Terminal, install the latest public build of .NET MAUI:

dotnet workload install maui --source https://api.nuget.org/v3/index.json

dotnet workload install android

安裝後要重新啟動 Visual Studio

這篇文章中的回答提到 https://ithelp.ithome.com.tw/questions/10209520

多核使用時CPU的運作頻率會被限制，因此當使用多執行續進行大資料量處理時(先不考慮硬碟速度)對每個CPU核心的效率會下降，因此為了進行多核處理進行資料分割的成本考量會有不小的影響。

 

從github抓取應用程式
https://github.com/g13579112000/ShopeeApiV2

若網站是使用 SVN update 方式更新網站，為了防止被外部讀取到 /.svn/  目錄內容
要在 web.config 片段加上以下內容

<configuration>

<system.webServer>

     <security>

         <requestFiltering>

            <hiddenSegments>

             <add segment=".svn" />

            </hiddenSegments>

         </requestFiltering>

     </security>

</system.webServer>

</configuration>

git 也是一樣的方式
可以參考此網址
https://www.petefreitag.com/item/823.cfm
 

--建立 table (要記得改 XXX)
CREATE TABLE IF NOT EXISTS public.table_monitor
(
    table_name text COLLATE pg_catalog."default" NOT NULL,
    update_at timestamp without time zone NOT NULL DEFAULT now(),
    update_count bigint NOT NULL DEFAULT 0,
    CONSTRAINT table_monitor_pkey PRIMARY KEY (table_name)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.table_monitor
    OWNER to XXX;

-- 這一段只要執行一次
CREATE OR REPLACE FUNCTION public.table_monitor_trigger_fnc()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
     Update public.table_monitor Set update_count = update_count + 1, update_at = now()  Where table_name = TG_TABLE_NAME; 
RETURN NEW;
END;
$BODY$;

ALTER FUNCTION public.table_monitor_trigger_fnc()
    OWNER TO XXX;

-- 對於被監控的 table, 要執行以下的指令，記得 monitor_table_name 要改為 table 的名字(要符合大小寫)
INSERT INTO public.table_monitor (table_name, update_at, update_count) VALUES ('monitor_table_name', '2000-01-01', 0) ON CONFLICT DO NOTHING;

CREATE OR REPLACE TRIGGER table_monitor_trigger
    AFTER INSERT OR DELETE OR UPDATE 
    ON monitor_table_name
    FOR EACH STATEMENT
    EXECUTE FUNCTION public.table_monitor_trigger_fnc();

監控用的程式碼:

public static void UpdateTableCache()

{

    //標記自已是正在執行的 Thread. 讓舊的 Thread 在執行完畢之後, 應該會自動結束. 以防舊的 Thread 執行超過 10 秒.

    CurrentThreadId = System.Threading.Thread.CurrentThread.ManagedThreadId;



    try

    {

        //確認自已是正在執行的 Thread, 重覆執行. (另一個 Thread 插入執行)

        while (CurrentThreadId == System.Threading.Thread.CurrentThread.ManagedThreadId)

        {

            LastUpdateDate = DateTime.Now;



            foreach (var dbId in CachedDbs)

            {

                var sql = @"select * from table_monitor";



                var dt = Su.PgSql.DtFromSql(sql, dbId);



                foreach (DataRow row in dt.Rows)

                {

                    string changeId = row["update_count"].DBNullToDefault();

                    string CacheKey = TableCacheKey(dbId, row["table_name"].DBNullToDefault());

                    ObjectCache cache = MemoryCache.Default;



                    string OldValue = (string)cache[CacheKey];



                    if (OldValue == null)

                    {

                        cache.Set(CacheKey, changeId, DateTime.MaxValue);

                    }

                    else

                    {

                        if (changeId != OldValue)

                        {

                            cache.Remove(CacheKey);

                            cache.Set(CacheKey, changeId, DateTime.MaxValue);

                        }

                    }

                }

            }



            //每兩秒檢查一次

            System.Threading.Thread.Sleep(2000);

        }

    }

    catch (Exception)

    {

        //依經驗, 只要 DB 能通, 這裡幾乎不會有問題, 所以這裡暫時不處理, 未來有問題時可以考慮寫入文字檔比較好.

    }

}