Scribbles for my bad memory

If you've got LINQ available to you, you can use Union, Except, Intersect, and Distinct:

• array1.Union(array2).Distinct().ToList()
• array2.Except(array1)
• array1.Except(array2)
• array1.Intersect(array2)

Reference

You want to add a unique index to a table, and unfortunately, there are already many duplicate rows in it. Manually find and delete these rows is time-wasting and error-prone. So why won't we just write one SQL statement and quickly resolve it?

First try, I wrote the following statement, and it won't work:

DELETE FROM PromotionSkus A 
WHERE 
	A.SkuId IN (SELECT SkuId FROM PromotionSkus B GROUP BY B.SkuId HAVING COUNT(B.SkuId) > 1) 
	AND 
	A.Id NOT IN (SELECT MIN(Id) FROM PromotionSkus C GROUP BY C.SkuId HAVING COUNT(C.SkuId) > 1);

AND this one below works!

DELETE FROM PromotionSkus A
WHERE 
	A.Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id, COUNT(SkuId) AS Total FROM PromotionSkus GROUP BY SkuId HAVING Total > 1) AS B)
	AND 
	A.SkuId IN (SELECT SkuId FROM (SELECT SkuId FROM PromotionSkus GROUP BY SkuId HAVING COUNT(SkuId) > 1) AS C);

The reason is well explained in this brilliant article.

Another mysql tip: using mysqldump export a table with one line one row.

mysqldump --databases YourDataBaseName --tables YourTableName --skip-extended-insert

Why do we need that? It is much easier to compare !

1. Present enums as string. Modify your StartUp.cs, add the highlight part below:

            services.AddMemoryCache().AddMvcCore().AddJsonOptions(opts =>
                {
                    opts.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
                })
                .AddDataAnnotationsLocalization();
 

2. Enable XML Comments

   1. Manually add the two lines in PropertyGroup below to the .csproj file:

<PropertyGroup>
  <GenerateDocumentationFile>true</GenerateDocumentationFile>
  <NoWarn>$(NoWarn);1591</NoWarn>
</PropertyGroup>

2. Add the options in the code below 

            services.AddSwaggerGen(options =>
            {
                options.IncludeXmlComments(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"{AppDomain.CurrentDomain.FriendlyName}.xml"), true);
            });

            await Db.Queryable<Entity>().Where(_ => _.SampleFieldName== sampleFieldValue)
                .GroupBy("AnothterFieldNameInStringFormat")
                .Select<ResultModel>("AnotherFieldNameInStringFormat, Count(AnotherFieldNameInStringFormat) Total")
                .ToListAsync();

这与 Nest 默认的字段名映射规则有关。C# 项目字段名往往是 CamelCase，然而 Nest API在创建索引时，默认会将 CamelCase 的字段名，转换为 camelCase 的字段名。当然，查询的时候，它也会精确的按照 camelCase 字段名映射数据。如果你只用 Nest，其实是不会有任何问题的。毕竟 AutoMapper 对字段名大小写是很宽容的。

当我们引入 Logstash 动态更新索引之后，不和谐的一幕出现了。凡是 Logstash 更新的过数据，查询的时候会返回搜索结果，但所有字段的值都是null。这是因为Logstash在更新索引的时候，没有做 CamelCase => camelCase 的字段名转换。

了解了问题产生的原因，解决起来也就容易了。幸运的是，Nest有一个配置参数，可以设置为索引时不做字段名转换。直接上代码

        public static void AddElasticsearch(
            this IServiceCollection services, string connectionUrl, string connectionIndex)
        {
            var defaultIndex = connectionIndex;

            var settings = new ConnectionSettings(new Uri(connectionUrl))
                .DefaultIndex(defaultIndex);
            settings.DefaultFieldNameInferrer(p => p);
            var client = new ElasticClient(settings);

            services.AddSingleton(client);
        }

需要注意的是，代码修改后，别忘记重建索引。否则你会发现原来不正常的（ Logstash更新过的数据）都正常了，原来搜索正常的，现在却所有字段名都变成了null。