QQs'Note

dotnet tool install --global dotnet-ef

dotnet ef migrations add QQsInitialCreate

├───MyProduct.API
│   └───startup.cs
└───MyProduct.Models
    └───migrations

cd MyProduct.Models
dotnet ef migrations add QQsInitialCreate --startup-project "D:\QQsWorkspace\MyProduct.API"

dotnet ef database update

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}
// 从模型中排除的类型
[NotMapped]
public class BlogMetadata
{
    public DateTime LoadedFromDatabase { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasKey(b => b.BlogId)
        .HasName("PrimaryKey_BlogId");
}

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .HasForeignKey(p => p.BlogUrl)
            .HasPrincipalKey(b => b.Url);
    }
}

var blog = _context.Blogs.Single(blog=>blog.Author=="QQs") // 此处关联属性Posts为null
...
var posts = _context.Entry(blog).Collection(blog => blog.Posts).Query().Where(post => post.Title.Contains("敏感")).ToList() // 此时blog对象的Posts属性被填充（仅过滤结果）

_context.Entry(blog).Collection(blog => blog.Posts).Load()

await using (var context = new BloggingContext())
{
    var blog = await context.Blogs.Include(b => b.Posts).FirstAsync();
    var post = new Post { Title = "Intro to EF Core" };

    blog.Posts.Add(post);
    await context.SaveChangesAsync();
}

await using (var context = new BloggingContext())
{
    var blog = new Blog { Url = "http://blogs.msdn.com/visualstudio" };
    var post = await context.Posts.FirstAsync();

    post.Blog = blog;
    await context.SaveChangesAsync();
}

// create
context.Add(new Student{
    FirstName="Jack",
    SurName="Ma"
});
context.SaveChanges();
// select
var MaYun = context.Students
            .Where(s => s.FirstName == GetName()).ToList();
// update
MaYun.Age=8;
context.Update(MaYun)
context.SaveChanges()
// delete
context.Remove(MaYun);
context.SaveChanges()

// create
context.Add(new Student{
    FirstName="Jack",
    SurName="Ma"
});
context.SaveChangesAsync();
// select
var MaYun = context.Students
            .Where(s => s.FirstName == GetName()).ToListAsync();
// update
MaYun.Age=8;
context.SaveChangesAsync()
// delete
context.Remove(MaYun);
context.SaveChangesAsync()

try
{
    _context.Add(newBlog);
    await _context.SaveChangesAsync();
}
catch (Microsoft.EntityFrameworkCore.DbUpdateException dbEx)
{
    // TODO the result description is not properly rigorous.
    if (null != dbEx.InnerException
        && dbEx.InnerException.Message.Contains("constraint"))
    {
        System.Console.WriteLine("Blog exists;");
    }
    else
    {
        // other error handle
    }
    _context.Remove(newBlog);
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<BloggingContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DBConnection")));
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<PicnicContext>(
        options => options.UseSqlServer(
            Configuration.GetConnectionString("DBConnection"),
            providerOptions => providerOptions.EnableRetryOnFailure()));
}

using (var dbContext = new CategoryEntities())
{
    dbContext.Configuration.LazyLoadingEnabled = true; // 默认是true，针对导航属性
        var categoryList = dbContext.Set<Category>().Where(p => p.CategoryId == 3);
        // 只会在数据库里面查询Category表，不会查询ProductDetail表
        foreach(var category in categoryList)
        {
            Console.WriteLine("CategoryId:"+category.CategoryId+ ",CategoryName:"+category.CategoryName);
            // 这时才会去数据库查询ProductDetail表
            foreach (var product in category.ProductDetails)
            {
                Console.WriteLine("ProductName:"+product.ProductName);
            }
        }
}

using (var dbContext = new CategoryEntities())
{
    dbContext.Configuration.LazyLoadingEnabled = false; // 不延迟加载,不会再次查询了
    var categoryList = dbContext.Set<Category>().Where(p => p.CategoryId == 3);
    // 只会在数据库里面查询Category表，不会查询ProductDetail表
    foreach (var category in categoryList)
    {
        Console.WriteLine("CategoryId:" + category.CategoryId + ",CategoryName:" + category.CategoryName);
        // 这时不会去数据库查询了，所以用户全是空的
        foreach (var product in category.ProductDetails)
        {
            Console.WriteLine("ProductName:" + product.ProductName);
        }
    }
}

// 显示加载
using (var dbContext = new CategoryEntities())
{
    // 不延迟加载，指定Include，一次性加载主表和从表的所有数据
    var categoryList = dbContext.Set<Category>().Include("ProductDetails").Where(p => p.CategoryId == 3);
    foreach (var category in categoryList)
    {
        Console.WriteLine("CategoryId:" + category.CategoryId + ",CategoryName:" + category.CategoryName);
        // 不会再查询
        foreach (var product in category.ProductDetails)
        {
            Console.WriteLine("ProductName:" + product.ProductName);
        }
    }
}

 public void ConfigureServices(IServiceCollection services)
{
    ...
    services.AddDbContext<DataContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DataContext"), conf=>
            {
                conf.UseHierarchyId();
            }
        ));
    ...
}

public class Group
{
    public Guid ID { get; set; }
    public string Name { get; set; }
    public HierarchyId GroupLevel { get; set; }
    
    public ICollection<Group> Children { get; } = new List<Group>();
}

public async Task<List<Group>> GetChildrenByGroupIDAsync(Guid groupID)
{
    Group self = await _context.Groups.FindAsync(groupID);
    List<Group> groups = await _context.Groups
        .Where(g => g.GroupLevel.IsDescendantOf(self.GroupLevel))
        .ToListAsync();

    return groups; //.FindAll(g => g.ID != groupID);
}

 public void ConfigureServices(IServiceCollection services)
{
    ...
    services.AddDbContext<DataContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DataContext"), conf=>
            {
                conf.EnableRetryOnFailure();
            }
        ));
    ...
}

List<customers> _customers = (from a in db.customers select a).ToList();
var _dataToWebPage = _customers.Skip(50).Take(50);

services.AddDbContext<MyDBContext>(options => {
    options.UseSqlServer(Configuration.GetConnectionString("MyDBContext"), conf => {
        conf.UseHierarchyId();
        conf.EnableRetryOnFailure();
    });
    options.LogTo(System.Console.WriteLine);
});

options.LogTo(Console.WriteLine, LogLevel.Information;

[HttpPost]
[Route("api/[controller]/create")]
public async Task<Result> Create([FromBody] QModel model){
    ...
    try{
        _context.Add(model);
        await _context.SaveChangesAsync();
    }catch(Exception ex){
        // ...
    }
}

services.AddDbContext<MyDBContext>(
    options => options.UseSqlServer(Configuration.GetConnectionString("MyDBContext"),
                                conf => { conf.UseHierarchyId();
                                    conf.EnableRetryOnFailure();
                                }), ServiceLifetime.Transient);

public class GroupsController : ControllerBase
{
    private readonly MyDBContext _context;
    private IGroupTreeService _groupTreeService;

    public GroupsController(MyDBContext context, IGroupTreeService groupTreeService)
    {
        _context = context;
        _groupTreeService = groupTreeService;
    }
    ...
}

public interface IWorker
{
    void DoWork(Func<MyDbContext> dbFactory);
}

public class WorkerRunner
{
    private readonly DbContextOptions<MyDbContext> _dbOptions;

    private readonly List<IWorker> _workers;

    public WorkerRunner(DbContextOptions<MyDbContext> dbOptions, List<IWorker> workers)
    {
        _dbOptions = dbOptions;
        _workers = workers;
    }

    public void RunWorkers()
    {
        using (var context = new MyDbContext(_dbOptions))
        {
            using (var tran = context.Database.BeginTransaction())
            {
                foreach (var worker in _workers)
                    worker.DoWork(() =>
                    {
                        // This won't work
                        var db = new MyDbContext(_dbOptions);
                        // And this one will even throw exception when used with in-memory database (during unit testing)
                        db.Database.UseTransaction(tran.GetDbTransaction());
                        return context;
                    });

                tran.Commit();
            }
        }
    }
}

[Table("User")]
public class User{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string Id { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .Property(e => e.Role)
        .HasDefaultValue(1);
}

sqlcmd -S .\SqlExpress
NET START/PAUSE/CONTINUE/STOP/ MSSQLSERVER

create table [ent].[tabletemp](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

create schema ent

ALTER SCHEMA ent TRANSFER OBJECT::dbo.table1;  

EXEC storedProcedure1 @param='01'

CREATE TABLE [dbo].[Group](
	[ID] [uniqueidentifier] NOT NULL,
	[CreateTime] [datetime2](7) NULL,
	[Name] [nvarchar](80) NULL,
	[Valid] [bit] NOT NULL,
	[UpdateTime] [datetime2](7) NULL,
	[Comment] [nvarchar](500) NULL,
    CONSTRAINT [AK_Group_Name] UNIQUE ([Name])
) ON [PRIMARY]

select * from table1 where name='abc' collate Chinese_PRC_CS_AI_WS 

SQL_Latin1_General_CP1_CS_AI
Latin1_General_CS_AI

SELECT SERVERPROPERTY(N'Collation')

SELECT * from ::fn_helpcollations()

STUFF ( character_expression , start , length , character_expression )

SELECT CAST(t1.num AS varchar) from t1;
SELECT CONVERT(varchar, t1.num) from t1;

ALTER TABLE dbo.Table1 DROP CONSTRAINT PK_Table1 // 移除原主键
ALTER TABLE dbo.Table1 DROP COLUMN SerialNumber // 移除列
ALTER TABLE dbo.Table1 ADD ID uniqueidentifier NOT NULL default newID()

select  * from t_dw CONNECT BY PRIOR id = parentID START WITH id='dw001'

-- 根节点 /
update t_dw set orgLvl=HierarchyID::GetRoot() where parentID is null
-- 子树   /1/,/2/
update t_dw set orgLvl=HierarchyID::Parse('/1/') where name='dw1'
update t_dw set orgLvl=HierarchyID::Parse('/2/') where name='dw2'
-- 叶    /1/3/
update t_dw set orgLvl=HierarchyID::Parse('/1/1/') where name='dw1-a'

insert t_dw (id,name,ParentID,orgLvl) 
values(newid(),'dw1-b','xxxxxxxxxxxxxxx',
HierarchyID::Parse('/1/').GetDescendant(CAST('/1/1/' AS hierarchyid), NULL))

SELECT CAST('/1/2/' AS hierarchyid).GetLevel() -- 结果：2

SELECT name, orgLvl.ToString()
FROM t_dw
WHERE orgLvl.IsDescendantOf(CAST('/1/' AS hierarchyid)) = 1

SELECT name, orgLvl.ToString()
FROM t_dw
WHERE orgLvl.GetAncestor(2) = HierarchyID::Parse('/1/')

DECLARE @CurrentNode hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid 
select  @CurrentNode=orgLvl from t_dw where name='dw_x'; -- /1/1/
select  @OldParent=orgLvl from t_dw where name='dw_old'; -- /1/
select  @NewParent=orgLvl from t_dw where name='dw_new'; -- /3/
UPDATE t_dw  
SET OrgNode = @CurrentNode.GetReparentedValue(@OldParent, @NewParent)   
WHERE OrgNode = @CurrentNode ; -- /3/1/ 
GO  

select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount] 
from sys.tables as t, sysindexes as i 
where t.object_id = i.id and i.indid <=1

; with cte(rownum)as(
    select row_number () over(partition by [Col1], [Col2] order by Col3) from [table]
)
delete from cte where rownum > 1

select @@version

SELECT id, first_name, last_name, phone, birth_date, sex
 FROM persons 
 WHERE id = 10

res = db.execSql(sql);
name = res[0]["FIRST_NAME"];

p = Person.get(10);
name = p.first_name;

result = await _context.employees.Where(employee => employee.level == highlvl)
    .Include(employee => employee.department)
    .ThenInclude(department => department.bills).ToListAsync();

sudo apt-get install vsftpd

service vsftpd restart

yum install git gcc gcc-c++ make automake autoconf libtool pcre pcre-devel zlib zlib-devel openssl-devel wget vi -y

apt install git gcc g++ make automake autoconf libtool libpcre3 libpcre3-dev zlib1g zlib1g-dev  libssl-dev wget vi

Function LCIDtoTAG 
    ${case} "1036"
    Push "fr_FR"
    ${break}
    ${case} "1049"
    Push "ru_RU"
    ${break}
    ${case} "1041"
    Push "ja_JP"
    ${break}
    ${case} "2052"
    Push "zh_CN"
    ${break}
    ${default}
    Push "en_US"
    ${break}
    ${endswitch}
FunctionEnd
Section
    Call LCIDtoTAG
    Pop $0
SectionEnd

StrCmp $1 "" +1 +2
DetailPrint "parameter is empty"

DetailPrint "parameter is not empty"

!ifndef TARGETDIR
!ifdef NSIS_UNICODE
!define TARGETDIR "..\binU"
!else
!define TARGETDIR "..\bin"
!endif
!endif

!addplugindir "${TARGETDIR}"

npm install @ngx-translate/core @ngx-translate/http-loader --save

import {TranslateModule} from '@ngx-translate/core';

export function createTranslateLoader(http: HttpClient) {
  return new TranslateHttpLoader(http, './assets/i18n/', '.json');
}
@NgModule({
    imports: [
        BrowserModule,
        HttpClientModule,
        TranslateModule.forRoot({
            loader: {
            provide: TranslateLoader,
            useFactory: createTranslateLoader,
            deps: [HttpClient]
      },
      defaultLanguage: 'en'
        })
    ],
    bootstrap: [AppComponent]
})
export class AppModule { }

export class AppComponent {
    constructor(translate: TranslateService) {
        // this language will be used as a fallback when a translation isn't found in the current language
        translate.setDefaultLang('en');
         // the lang to use, if the lang isn't available, it will use the current loader to get them
        translate.use('en');
    }
}

<p>{{"sayHi" | translate}}</p>

<p>{{'sayHiWithParameter' | translate:userObj}}</p>

<p>{{ 'ROLES.' + role | uppercase | translate }}</p>

src
├───assets
│   ├───i18n
│   │       en.json
│   │       fr.json
│   │       zh.json

{
    "introductionHeader":"你好",
    "ROLE":{
        "ADMIN": "管理员"，
        "USER": "用户"
    },
    "sayHiWithParameter":"你好，{{pride}}的{{lastname}}}先生"
}

translate.get('HELLO', {value: 'world'}).subscribe((res: string) => {
    console.log(res);
    //=> 'hello world'
});

msgid ""
msgstr ""
"MIME-Version: 1.0\n"
"Content-Type: text/plain; charset=UTF-8\n"
"Content-Transfer-Encoding: 8bit\n"
"Plural-Forms: nplurals=1; plural=0;\n"
"X-Language: zh_CN\n"
"X-Qt-Contexts: true\n"

#: ../scanflow/res/qml/Dialog/AboutDialog.qml:25
msgctxt "AboutDialog|"
msgid "About"
msgstr "关于"

#: ../scanflow/res/qml/Dialog/AboutDialog.qml:52
msgctxt "AboutDialog|"
msgid "Product Version"
msgstr "产品版本"

<h1>{{ "AboutDialog|.About" |translate}}</h1>

msgctxt "ErrorMessage|"
msgid ""
"Application preferences have been damaged. Reinstall the application to "
"solve the problem."
msgstr ""
"Předvolby aplikace byly poškozeny. Problém vyřešíte opětovnou instalací "
"aplikace."
====>
{
    ...
    "ErrorMessage|":{
        msgctxt:"ErrorMessage|",
        msgid:"Application preferences have been damaged. Reinstall the application to solve the problem.",
        msgstr:["Předvolby aplikace byly poškozeny. Problém vyřešíte opětovnou instalací aplikace."]
    }
}

getValue(target, key) {
    /** @type {?} */
    let keys = typeof key === 'string' ? key.split('.') : [key];
    key = '';
    do {
        key += keys.shift();
        if (isDefined(target) && isDefined(target[key]) && (typeof target[key] === 'object' || !keys.length)) {
            target = target[key];
            key = '';
        }
        else if (!keys.length) {
            target = undefined;
        }
        else {
            key += '.';
        }
    } while (keys.length);
    return target;
}

public getTranslation(lang: string): Observable<any> {
    return this._http
        .get(`${this._prefix}/${lang}${this._suffix}`, { responseType: 'text' })
        .pipe(
            map((contents: string) => this.parse(contents)));
}

/**
* Parse po file
* @param contents
* @returns {any}
*/
public parse(contents: string): any {
    let translations: { [key: string]: object | string } = {};

    const po = gettext.po.parse(contents, 'utf-8');
    if (!po.translations.hasOwnProperty(this.domain)) {
        return translations;
    }

    Object.keys(po.translations)
        .forEach(domain => {
            if (domain.length === 0) { return; }
            if (po.translations[domain].msgstr) { // there is no msgctxt
                translations[domain] = po.translations[domain].msgstr;
            } else {
                // context
                translations[domain] = {};
                Object.keys(po.translations[domain]).forEach(key => {
                    let translation: string | Array<string> = po.translations[domain][key].msgstr.pop();
                    if (translation instanceof Array && translation.length > 0) {
                        translation = translation[0];
                    }
                    if (key.length > 0 && translation.length > 0) {
                        translations[domain][key] = translation;
                    }
                });
            }
        });
    return translations;
}

import { TranslatePoHttpLoader } from './edited-po-loader';
export function createTranslateLoader(http: HttpClient) {
	return new TranslatePoHttpLoader(http, 'assets/i18n', '.po');

import {MissingTranslationHandler, MissingTranslationHandlerParams} from '@ngx-translate/core';

export class MyMissingTranslationHandler implements MissingTranslationHandler {
    handle(params: MissingTranslationHandlerParams) {
        return 'some value';
    }
}
@NgModule({
    imports: [
        BrowserModule,
        TranslateModule.forRoot({
            missingTranslationHandler: {provide: MissingTranslationHandler, useClass: MyMissingTranslationHandler},
            useDefaultLang: false
        })
    ],
    providers: [

    ],
    bootstrap: [AppComponent]
})
export class AppModule { }

npx source-map-explorer dist/main.js --no-border-checks