Archive of November 2020

PHP array_diff array_intersect function c# alternative

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

  • array2.Except(array1)
  • array1.Except(array2)
  • array1.Intersect(array2)

Reference

MySQL: Delete All Duplicate Rows Except the Earliest One in One SQL

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.

.NET Core 3.1 Swagger UI Tweaks

  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();
    

  1. 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);
           });
      

SQLSugar: How to implement Count(FieldName) and GROUP BY in SQLSugar

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

Elasticsearch Nest with Logstash 神奇故障一则 ”返回了搜索结果,但所有字段都是null“

这与 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。