linq2db.EntityFrameworkCore 介紹

linq2db.EntityFrameworkCore 是一個ef core的插件，對linq文法的擴充

對複雜的sql都有很好的支援，他是基于linq2db （provided by LINQ To DB）

如果你使用了linq2db的文法擴充那麼你必須使用下面的方法進行查詢

// ToLinqToDB是必須的
 var temp = qry.ToLinqToDB().ToList();
           

下面是 linq2db 的冰山一角

JOIN

1. InnerJoin

var qry = from t1 in db.T
           from t2 in db.T2.InnerJoin(m => m.T1Id == t1.Id)
           

2.LeftJoin

var qry = from t1 in db.T
           from t2 in db.T2.LeftJoin(m => m.T1Id == t1.Id)
           

3.RightJoin

var qry = from t1 in db.T
           from t2 in db.T2.RightJoin(m => m.T1Id == t1.Id)
           

SUM

// 相比于原來linq，簡潔了很多。
var qry = from t1 in db.T
          from t2 in db.T2.LeftJoin(m => m.T1Id == t1.Id)
          select Sql.Ext.Sum(t1.Type == "A" ? t1.Number * (t1.SalePrice-t2.OriginalPrice) : 0).ToValue();

           

CountExt

//我要查t2中不重複的 t1的Id有多少個
var qry = from t1 in db.T
          from t2 in db.T2.LeftJoin(m => m.T1Id == t1.Id)
          where t1.some==''
          group new {t1,t2} by t2.some into g
          select new 
          {
              //相當于sql Count(distinct t2.T1Id)
              Number = g.CountExt(m => m.t2.T1Id, Sql.AggregateModifier.Distinct)
          }
           

對于一些sql函數的支援

DatePart

var qry = from t1 in db.T
                    where t1.SaleDate > beginTime 
                    group t1 by Sql.DatePart(Sql.DateParts.Month, t1 .SaleDate) into g
                    select new
                    {
                        Month = g.Key,
                        FlowAmount = g.Sum(m => m.SaleWay == "A" ? Sql.Abs(m.Number * m.SalePrice) : 0) -
                                     g.Sum(m => m.SaleWay == "B" ? Sql.Abs(m.Number * m.SalePrice) : 0)
                    };
           

當然還有更多的擴充方法，分别位于

包含于 Sql ， Sql.Ext，AnalyticFunctions 中

linq2db文檔 : https://linq2db.github.io/index.html

當然還有批量更新的操作

如果是需要使用，那麼最好再程式開始時運作以下代碼

//因為他是幂等的 ，是以可以多次運作
LinqToDBForEFTools.Initialize();
           

以下代碼都是從github上抄下來的。

// fast insert big recordsets
ctx.BulkCopy(new BulkCopyOptions {...}, items);

// query for retrieving products that do not have duplicates by Name
var query =
	from p in ctx.Products
	from op in ctx.Products.LeftJoin(op => op.ProductID != p.ProductID && op.Name == p.Name)
	where Sql.ToNullable(op.ProductID) == null
	select p;

// insert these records into the same or another table
query.Insert(ctx.Products.ToLinqToDBTable(), s => new Product { Name = s.Name ... });

// update these records by changing name based on previous value
query.Update(prev => new Product { Name = "U_" + prev.Name ... });

// delete records that matched by query
query.Delete();