preface
It is difficult for Dapper to deal with multiple table free association, group query, anonymous query and other application scenarios. It often needs to write SQL statements by hand (or use tools to generate SQL configuration files). Imagine a project where the entire DAL layer is jammed with SQL statements, which is a natural disaster for later maintenance. The framework in API design to draw on the maximum extent of the EntityFramework writing, clean entity, silky add delete change check, robust navigation attributes, in addition to support the chain query (point mark), query expression, aggregation query and so on. At the entity mapping transformation level, Emit is used to dynamically build binding instructions for maximum performance close to the native level.
XFramework window
- Native.NET syntax, zero learning cost
- Support LINQ query, Ramda expression
- Support batch add, delete, change, check and multiple table associated update
- Support SqlServer, MySql, Postgre, Oracle,.net Core
- The biggest highlight, truly support one to one, one to many navigation attributes. This is something few existing open source ORMs dare say they support
- The entity field type need not be the same as the database type
- Supports temporary table and table variable operations
- Provides native ADO operation
- Other highlights, you’ll see if you use them
performance
Take a look at the performance comparison with EntityFramework. Different machine configurations may result in different results, just for reference. It is important to note that the EntityFramework uses AsNoTracking, otherwise there would be no comparison
Functional specifications
1. Entity definition
- If the class has TableAttribute, the name specified by TableAttribute is used as the table name; otherwise, the class name is used as the table name
- You can specify the ColumnAttribute feature for entity fields to specify the mapping between entity fields and table fields. If the parameter to be deleted or updated is an entity, you must use [Column(IsKey = true)] to specify the primary key of the entity
- ForeignKeyAttribute Specifies a foreign key. For a one-to-many foreign key, the type must be IList or List
- Columnattribute. DataType Specifies the table field type. SQLSERVER is used as an example. System.String corresponds to nvARCHAR by default. For vARCHAR, specify [Column(DbType= dbtype.ansiString)].
'[Table(Name =" Bas_Client")]' public partial Class Client '{' /// <summary>' /// initializes a new instance of <see cref="Client"/> class `/// </summary>` `public Client()` `{` `this.CloudServerId = 0; ` `this.Qty = 0; ` `this.HookConstructor(); ` `} ` ` / / / < summary > ` ` initialization / / / < see which = "Client" / > new instance of the class ` ` / / / < summary > ` ` public Client (Client model) ` ` {` `this.CloudServerId = 0; ` `this.Qty = 0; ` `this.HookConstructor(); ` `}` `/// <summary>` `/// clientid` `/// </summary>` `[Column(IsKey = true)]` `public virtual int ClientId { get; set; }` `/// <summary>` `/// activedate` `/// </summary>` `public virtual Nullable<DateTime> ActiveDate { get; set; } '/// remark' '/// </summary>' '[Column(Default = "' Default')] ' 'public virtual string remark {get; set; }` `[ForeignKey("CloudServerId")]` `public virtual CloudServer CloudServer { get; set; }` `[ForeignKey("ClientId")]` `public virtual List<ClientAccount> Accounts { get; set; } ` ` / / / < summary > ` ` / / / constructor hook ` ` / / / < summary > ` ` partial void HookConstructor (); ` ` `}Copy the code2. Context definition
'1 SQLSERVER: var context = new SqlDbContext(connString); MySQL: var context = new MySqlDbContext(connString); Postgre: var context = new NpgDbContext(connString); 4 Oracle: var context = new OracleDbContext(connString); `Copy the code3. Anonymous type
'//// anonymous class' var guid = guid.newguid (); ` `var dynamicQuery =` `from a in context.GetTable<TDemo>()` `where a.DemoId <= 10` `select new` `{` `DemoId = 12,` 'DemoCode = a.democode,' 'DemoEnum = Model.State.Complete,// enumeration type support' '}; ` `var result = dynamicQuery.ToList(); ` ` / / dots ` ` dynamicQuery = context ` `. GetTable < TDemo > () ` `. Where (a = > a. d. emoId < = 10) ` `. Select (a = > new ` ` {` ` DemoId = 12, ' 'DemoCode = a.democode,' 'DemoEnum = Model.State.Complete,// enum type support' '}); ` `result0 = dynamicQuery.ToList(); `Copy the code4. All fields
'// Date,DateTime,DateTime2 Supports'' var query = ' 'from a in context.gettable <TDemo>() where a.emoid <= 10&&a.emodate > sDate && a.DemoDateTime >= sDate && a.DemoDateTime2 > sDate` `select a; ` `var result1 = query.ToList(); GetTable<TDemo>().Where(a => a.emoid <= 10&&a.emodate > sDate &&a.emodateTime >= sDate && a.DemoDateTime2 > sDate); ` `result1 = query.ToList(); `Copy the code5. Specify fields
Query = from a in context.gettable <TDemo>() where A.demoid <= 10 select new TDemo {' DemoId = (int)a.DemoId,` `DemoCode = (a.DemoCode ?? "N001")` `}; ` `result1 = query.ToList(); Query = context.GetTable<TDemo>().Where(a => a.democode! = a.DemoId.ToString() && a.DemoName ! = a.DemoId.ToString() && a.DemoChar == 'A' && a.DemoNChar == 'B')` `.Select(a => new TDemo` `{` `DemoId = a.DemoId,` 'DemoCode = a.demooname == "Zhang SAN"? }); ` `result1 = query.ToList(); `Copy the codeconstructors
Those of you who have used the EntityFramework know that if you want to query a given field using a constructor, you can’t honestly redefine a new entity, Otherwise, an exception “The entity or complex type cannot be constructed in a LINQ to Entities query” will be thrown in your face. This use of the XFramework framework is designed to take the pain out of breathing! ~
'// constructor' var query = ' 'from a in context.gettable < model.demo >()' 'where a.modelid <= 10' select new model.demo (a); ` `var r1 = query.ToList(); ` `//SQL=>` `//SELECT` `//t0.[DemoId] AS [DemoId],` `//t0.[DemoCode] AS [DemoCode],` `//t0.[DemoName] AS [DemoName],` ` / /... ` `//FROM [Sys_Demo] t0` `//WHERE t0.[DemoId] <= 10` `query =` `from a in context.GetTable<Model.Demo>()` `where a.DemoId <= 10` `select new Model.Demo(a.DemoId, a.DemoName); ` `r1 = query.ToList(); `Copy the code7. Paging query
'// paging query' // 1. Instead of querying the contents of the first page, you must first OrderBy and then paging, OFFSET... The Fetch Next paging statement requires Order Derby '// 2. The parameters in the Order Derby expression must be the same as the variable names in the Query, as in a. Query = from a in context.gettable <TDemo>() ' 'orderby A.democode' 'select a; ` `query = query.Skip(1).Take(18); ` `result1 = query.ToList(); ` ` / / dots ` ` query = context ` `. GetTable < TDemo > () ` `. OrderBy (a = > a. d. emoCode) ` `. Skip (1) ` `. Take (18); ` `result1 = query.ToList(); `Copy the code8. Filter conditions
` / / filter ` ` query = the from a context in GetTable < TDemo > () ` ` where a. d. emoName = = "D0000002" | | a. d. emoCode = = "D0000002 `" `select a; ` `result1 = query.ToList(); ` ` / / dots ` ` query = context. GetTable < TDemo > () Where (a = > a. d. emoName = = "D0000002" | | a. d. emoCode = = "D0000002"); ` `result1 = query.ToList(); ` `query = context.GetTable<TDemo>().Where(a => a.DemoName.Contains("004")); ` `result1 = query.ToList(); ` `query = context.GetTable<TDemo>().Where(a => a.DemoCode.StartsWith("Code000036")); ` `result1 = query.ToList(); ` `query = context.GetTable<TDemo>().Where(a => a.DemoCode.EndsWith("004")); ` `result1 = query.ToList(); `Copy the code9. More conditions
` / / support query conditions ` ` / / divisional nvarchar, varchar, the date and datetime, datetime2 field type ` ` / / support string manipulation = > Trim | TrimStart | TrimEnd | ToString | Length` `int m_byte = 9; ` `Model.State state = Model.State.Complete; ` `query = from a in context.GetTable<TDemo>()` `where` `a.DemoCode == "002" &&` `a.DemoName == "002" &&` `a.DemoCode.Contains("TAN") && // LIKE '%%'` `a.DemoName.Contains("TAN") && // LIKE '%%'` `a.DemoCode.StartsWith("TAN") && // LIKE 'K%'` `a.DemoCode.EndsWith("TAN") && // LIKE '%K'` `a.DemoCode.Length == 12 && // LENGTH` `a.DemoCode.TrimStart() == "TF" &&` `a.DemoCode.TrimEnd() == "TF" &&` `a.DemoCode.TrimEnd() == "TF" &&` `a.DemoCode.Substring(0) == "TF" &&` `a.DemoDate == DateTime.Now &&` `a.DemoDateTime == DateTime.Now &&` `a.DemoDateTime2 == DateTime.Now &&` `a.DemoName == (` `a.DemoDateTime_Nullable == null ? "NULL" : "NOT NULL") && // the teradata expression ' 'a.time == (a.time?? A. d. emoCode) && / / binary expression ` ` new [] {1, 2, 3}. The Contains (a. d. emoId) && / / IN (1, 2, 3) ` ` new List < int > {1, 2, 3}. The Contains (a. d. emoId) && / / IN (1, 2, 3) ` ` new List < int > (_demoIdList). The Contains (a. d. emoId) && / / IN (1, 2, 3) ` ` a. d. emoId = = new List<int> { 1, 2, 3} [0] && / / IN (1, 2, 3) ` ` _demoIdList. The Contains (a. d. emoId) && / / IN (1, 2, 3) ` ` a. d. emoName = = _demoName && ` ` a. d. emoCode = = (a.DemoCode ?? "CODE") &&` `new List<string> { "A", "B", "C" }.Contains(a.DemoCode) &&` `a.DemoByte == (byte)m_byte &&` `a.DemoByte == (byte)Model.State.Complete ||` `a.DemoInt == (int)Model.State.Complete ||` `a.DemoInt == (int)state ||` `(a.DemoName == "STATE" && a.DemoName == "REMARK")// OR Select 'a' from 'a'; ` `result1 = query.ToList(); `Copy the code10. The DataTable and the DataSet
`// DataTable` `query = from a in context.GetTable<TDemo>()` `orderby a.DemoCode` `select a; ` `query = query.Take(18); ` `var result3 = context.Database.ExecuteDataTable(query); ` `// DataSet` `var define = query.Resolve(); ` `List<DbCommandDefinition> sqlList = new List<DbCommandDefinition> { define, define, define }; ` `var result4 = context.Database.ExecuteDataSet(sqlList); `Copy the code11. Inline query
`// INNER JOIN` `var query =` `from a in context.GetTable<Model.Client>()` `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId` `where a.ClientId > 0` `select a; ` `var result = query.ToList(); Query = context.gettable < model.client >().join (context.gettable < model.cloudServer >(), a => a.CloudServerId, b => b.CloudServerId, (a, b) => a)` `.Where(a => a.ClientId > 0); ` `result = query.ToList(); `Copy the code- Leftist writers query
Note that the second left association, which uses constants as the associative key, translates to something like this:
`SELECT ***`
`FROM [Bas_Client] t0`
`LEFT JOIN [Sys_CloudServer] t1 ON t0.[CloudServerId] = t1.[CloudServerId] AND N'567' = t1.[CloudServerCode]`
`WHERE t1.[CloudServerName] IS NOT NULL`
Copy the codeDo you see any familiar smells, brother Dei?
`// LEFT JOIN` `query =` `from a in context.GetTable<Model.Client>()` `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId into u_b` `from b in u_b.DefaultIfEmpty()` `select a; ` `query = query.Where(a => a.CloudServer.CloudServerName ! = null); ` `result = query.ToList(); ` `// LEFT JOIN` `query =` `from a in context.GetTable<Model.Client>()` `join b in context.GetTable<Model.CloudServer>() on new { a.CloudServerId, CloudServerCode = "567" } equals new { b.CloudServerId, b.CloudServerCode } into u_b` `from b in u_b.DefaultIfEmpty()` `select a; ` `query = query.Where(a => a.CloudServer.CloudServerName ! = null); ` `result = query.ToList(); `Copy the code13. Right-click to search
The syntax for left and right associations is the same, except that DefaultIfEmpty(true) is overloaded to denote the right association.
`// RIGHT JOIN` `query =` `from a in context.GetTable<Model.CloudServer>()` `join b in context.GetTable<Model.Client>() on a.CloudServerId equals b.CloudServerId into u_b` `from b in u_b.DefaultIfEmpty(true)` `where a.CloudServerName == null` `select b; ` `result = query.ToList(); `Copy the code14. The Union query
Our Union query supports pagination after Union operation
Var q1 = context.gettable < model.client >().Where(x => x.clientid == 0); ` `var q2 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0); ` `var q3 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0); ` `var query6 = q1.Union(q2).Union(q3); ` `var result6 = query6.ToList(); ` `result6 = query6.Take(2).ToList(); ` `result6 = query6.OrderBy(a => a.ClientId).Skip(2).ToList(); ` `query6 = query6.Take(2); ` `result6 = query6.ToList(); ` `query6 = query6.OrderBy(a => a.ClientId).Skip(1).Take(2); ` `result6 = query6.ToList(); `Copy the code- Navigation attribute
'// Simpler assignment method' '// Applicable scenarios: Query = 'from a in context.gettable < model.client >()' select new model.client (a) '{' `CloudServer = a.CloudServer,` `LocalServer = new Model.CloudServer` `{` `CloudServerId = a.CloudServerId,` `CloudServerName = a.LocalServer.CloudServerName` `}` `}; ` `result = query.ToList(); `Copy the code16. One-to-one one-to-many navigation
'// 1:1 relationship, 1: N Relation ' 'query =' 'from a in context.gettable < model.client >()' 'where a.clid > 0' 'orderby A.clid' 'select new Model.Client(a)` `{` `CloudServer = a.CloudServer,` `Accounts = a.Accounts` `}; ` `result = query.ToList(); `Copy the code17. Include syntax
EntityFramework includes syntax, we also have, and is a solid solid one-time load!!
'// Include syntax' 'query =' 'context' '.gettable < model.client >() ' '.Include(a => A.loudServer); ` `result = query.ToList(); '// Include, Query = ' 'from a in context' '.gettable < model.client >() ' '.Include(a => A.ccounts) ' '.Include(a => A.ccounts) ' '. a.Accounts[0].Markets)` `.Include(a => a.Accounts[0].Markets[0].Client)` `where a.ClientId > 0` `orderby a.ClientId` `select a; ` `result = query.ToList(); Query = ' 'from a in context' '.gettable < model.client >() ' '.Include(a => A.ccounts) ' '.Include(a => A.ccounts) ' '. a.Accounts[0].Markets)` `.Include(a => a.Accounts[0].Markets[0].Client)` `where a.ClientId > 0` `orderby a.ClientId` `select a; ` `query = query` `.Where(a => a.ClientId > 0 && a.CloudServer.CloudServerId > 0)` `.Skip(10)` `.Take(20); ` `result = query.ToList(); ` `query =` `from a in context` `.GetTable<Model.Client>()` `.Include(a => a.CloudServer)` `.Include(a => a.Accounts)` `where a.ClientId > 0` `select a; ` `query = query.OrderBy(a => a.ClientId); ` `result = query.ToList(); '// Include syntax query primary/secondary relationship < note: Var query1 = ' 'from a in' 'context' '.gettable < model.client >() ' '.include (a =>) ' a.CloudServer)` `.Include(a => a.Accounts)` `.Include(a => a.Accounts[0].Markets)` `.Include(a => a.Accounts[0].Markets[0].Client)` `group a by new { a.ClientId, a.ClientCode, a.ClientName, a.CloudServer.CloudServerId } into g` `select new Model.Client` `{` `ClientId = g.Key.ClientId,` `ClientCode = g.Key.ClientCode,` `ClientName = g.Key.ClientName,` `CloudServerId = g.Key.CloudServerId,` `Qty = g.Sum(a => a.Qty)` `}; ` `query1 = query1` `.Where(a => a.ClientId > 0)` `.OrderBy(a => a.ClientId)` `.Skip(10)` `.Take(20)` `; ` `var result1 = query1.ToList(); `Copy the code18. Group query
`var query2 =` `from a in context.GetTable<Model.Client>()` `group a by a.ClientId into g` `select new` `{` `ClientId = g.Key,` `Qty = g.Sum(a => a.Qty)` `}; ` `query2 = query2.OrderBy(a => a.ClientId).ThenBy(a => a.Qty); `Copy the code19. Aggregate functions
`1 var result1 = query2.Max(a => a.ClientId); ` `2 var result2 = query2.Sum(a => a.Qty); ` `3 var result3 = query2.Min(a => a.ClientId); ` `4 var result4= query2.Average(a => a.Qty); ` `5 var result5 = query2.Count(); `Copy the code20. Group paging
Var query8 = ' 'from a in context.gettable < model.client >()' 'where a.clientName == "TAN"' 'group a by new { a.ClientId, a.ClientName } into g` `where g.Key.ClientId > 0` `orderby new { g.Key.ClientName, g.Key.ClientId }` `select new` `{` `Id = g.Key.ClientId,` `Name = g.Min(a => a.ClientId)` `}; ` `query8 = query8.Skip(2).Take(3); ` `var result8 = query8.ToList(); `Copy the code21. The subquery
'// Force to subquery' 'query =' 'from a in context.gettable < model.client >()' 'join B in context.gettable < model.cloudserver >() on a.CloudServerId equals b.CloudServerId into u_c` `from b in u_c.DefaultIfEmpty()` `select a; ` `query = query.OrderBy(a => a.ClientId).Skip(10).Take(10).AsSubQuery(); ` `query = from a in query` `join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId` `select a; ` `result = query.ToList(); `Copy the code22. Any query
`// Any` `var isAny = context.GetTable<Model.Client>().Any(); ` `isAny = context.GetTable<Model.Client>().Any(a => a.ActiveDate == DateTime.Now); ` `isAny = context.GetTable<Model.Client>().Distinct().Any(a => a.ActiveDate == DateTime.Now); ` `isAny = context.GetTable<Model.Client>().OrderBy(a => a.ClientId).Skip(2).Take(5).Any(a => a.ActiveDate == DateTime.Now); ` `//SQL=>` `//IF EXISTS(` `// SELECT TOP 1 1` `// FROM[Bas_Client] t0` `// WHERE t0.[ActiveDate] = '2018-08-15 14:07:09.784' '//) SELECT 1 ELSE SELECT 0Copy the code23. Delete a single file
Var demo = new TDemo {DemoId = 1}; ` `3 context.Delete(demo); ` `4 context.SubmitChanges(); `Copy the code24. Delete them in batches
` / / 2. The WHERE condition bulk Delete ` ` context. Delete < TDemo > (a = > a. d. emoId = = 2 | | a. d. emoId = = | 3 | a. d. emoName = = "N0000004"); ` `var qeury =` `context` `.GetTable<TDemo>()` `.Where(a => a.DemoId == 2 || a.DemoId == 3 || a.DemoName == "N0000004"); Context. Delete<TDemo>(qeury); ` `context.SubmitChanges(); `Copy the code25. Delete multiple table associations
Var query1 = ' 'from a in context.gettable < model.client >()' 'join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId` `join c in context.GetTable<Model.ClientAccountMarket>() on new { b.ClientId, b.AccountId } equals new { c.ClientId, c.AccountId }` `where c.ClientId == 5 && c.AccountId == "1" && c.MarketId == 1` `select a; ` `context.Delete<Model.Client>(query1); Query Query Query var query2 = ' 'from a in context.gettable < model.client >()' 'join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId` `where a.CloudServer.CloudServerId == 20 && a.LocalServer.CloudServerId == 2` `select a; ` `context.Delete<Model.Client>(query2); ` ` / / 4. Associated batch delete Query ` ` var query3 = ` ` from a context in GetTable < Model. The Client > () ` ` where a.C loudServer. CloudServerId = = 20 && a.LocalServer.CloudServerId == 2` `select a; ` `context.Delete<Model.Client>(query3); Var sum = ' 'from a in context.gettable < model.clientAccount >()' 'where a.clientid <= 20' `group a by new { a.ClientId } into g` `select new Model.Client` `{` `ClientId = g.Key.ClientId,` `Qty = g.Sum(a => a.Qty)` `}; ` `var query4 =` `from a in context.GetTable<Model.Client>()` `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId` `join c in context.GetTable<Model.CloudServer>() on a.CloudServerId equals c.CloudServerId` `join d in sum on a.ClientId equals d.ClientId` `where a.ClientId > 10 && a.CloudServerId < 0` `select a; ` `context.Delete<Model.Client>(query4); `Copy the code26. Single update
`var demo = context` `.GetTable<TDemo>()` `.FirstOrDefault(x => x.DemoId > 0); DemoName = "001'.N"; ` `context.Update(demo); ` `context.SubmitChanges(); `Copy the code27. Batch updates
Update<TDemo>(x => new TDemo ' '{' DemoDateTime2 = datetime.utcnow,') `DemoDateTime2_Nullable = null,` `//DemoTime_Nullable = ts` `}, x => x.DemoName == "001'.N" || x.DemoCode == "001'.N"); ` `context.SubmitChanges(); `Copy the code28. Multiple table associated update
It also supports updating the values of table B fields back to table A. To be clear, Oracle and Postgre do not support this SAO operation.
` / / 3. Associated Query batch update ` ` var Query = ` ` from a context in GetTable < Model. The Client > () ` ` where a.C loudServer. CloudServerId! = 0` `select a; ` `context.Update<Model.Client>(a => new Model.Client` `{` `Remark = "001.TAN"` `}, query); Query = ' 'from a in context.gettable < model.client >()' 'join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId` `join c in context.GetTable<Model.ClientAccount>() on a.ClientId equals c.ClientId` `where c.AccountId == "12"` `select a; ` `context.Update<Model.Client, Model.CloudServer>((a, b) => new Model.Client` `{` `CloudServerId = b.CloudServerId,` `Remark = "001.TAN"` `}, query); ` `context.SubmitChanges(); `Copy the code29. Subquery updates
'// subquery update' 'var sum =' 'from a in context.gettable < model.clientAccount >()' 'where A.clientid > 0' 'group a by new { a.ClientId } into g` `select new Model.Client` `{` `ClientId = g.Key.ClientId,` `Qty = g.Sum(a => a.Qty)` `}; ` `if (_databaseType == DatabaseType.SqlServer || _databaseType == DatabaseType.MySql)` `{` `var uQuery =` `from a in context.GetTable<Model.Client>()` `join b in sum on a.ClientId equals b.ClientId` `where a.ClientId > 0 && b.ClientId > 0` `select a; ` `context.Update<Model.Client, Model.Client>((a, b) => new Model.Client { Qty = b.Qty }, uQuery); Update field values are not supported from subquery var uQuery = ' 'from a in context.gettable < model.client >()' `join b in sum on a.ClientId equals b.ClientId` `where a.ClientId > 0 // b.ClientId > 0` `select a; ` `context.Update<Model.Client>(a => new Model.Client { Qty = 9 }, uQuery); ` `}` `context.SubmitChanges(); `Copy the code30. The column with autoincrement is added
Var demo = new TDemo {' 'DemoCode = "D0000001",' 'DemoName = "N0000001"}; ` `context.Insert(demo); ` `context.SubmitChanges(); ` `var demo2 = new TDemo` `{` `DemoCode = "D0000002",` `DemoName = "N0000002"` `}; ` `context.Insert(demo2); ` `var demo3 = new TDemo` `{` `DemoCode = "D0000003",` `DemoName = "N0000003",` `}; ` `context.Insert(demo3); ` `context.Insert(demo); ` `context.SubmitChanges(); `Copy the code31. Batch add
Batch new translation of SQL without parameters, just pure SQL text. SQLSERVER students can try SqlDbContext.BulkCopy if they want to be faster.
INSERT INTO VALUES(),(),()... Syntax. Context.Delete<TDemo>(x => x.demoid > 1000000); ` `demos = new List<TDemo>(); ` `for (int i = 0; i < 1002; i++)` `{` `TDemo d = new TDemo` `{` `DemoCode = "D0000001",` `DemoName = "N0000001"` `}; ` `demos.Add(d); ` `}` `context.Insert<TDemo>(demos); ` `context.SubmitChanges(); `Copy the code32. Associated query is added
'// subquery increment' 'var sum =' 'from a in context.gettable < model.clientAccount >()' 'where A.clientid > 0' 'group a by new { a.ClientId } into g` `select new Model.Client` `{` `ClientId = g.Key.ClientId,` `Qty = g.Sum(a => a.Qty)` `}; ` `sum = sum.AsSubQuery(); ` `maxId = context.GetTable<Model.Client>().Max(x => x.ClientId); ` `nextId = maxId + 1; ` `var nQuery =` `from a in sum` `join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId into u_b` `from b in u_b.DefaultIfEmpty()` `where b.ClientId == null` `select new Model.Client` `{` `ClientId = SqlMethod.RowNumber<int>(x => a.ClientId) + nextId,` `ClientCode = "ABC3",` `CloudServerId = 11,` `State = 3,` `Qty = a.Qty,` `}; ` `context.Insert(nQuery); `Copy the code33. Add, delete and modify data at the same time
'context.update < model. Client>(x => new model. Client' '{' ClientName = "monte 3"'}, x => x. clientid == 3); ` `var query =` `from a in context.GetTable<Model.Client>()` `where a.ClientId == 1` `select 5; ` `context.AddQuery(query); ` `List<int> result1 = null; ` `context.SubmitChanges(out result1); ' 'context.Update< model. Client>(x => new model. Client' '{' ClientName = "monte 4"'}, x => x. clientid == 4); ` `query =` `from a in context.GetTable<Model.Client>()` `where a.ClientId == 1` `select 5; ` `context.AddQuery(query); ` `var query2 =` `from a in context.GetTable<Model.Client>()` `where a.ClientId == 1` `select 6; ` `context.AddQuery(query2); ` `result1 = null; ` `List<int> result2 = null; ` `context.SubmitChanges(out result1, out result2); `Copy the code34. Load multiple lists at once
**** ' 'var query3 =' 'from a in context.gettable < model.client >()' 'where a.clientid >= 1&&a.clientid <= 10` `select 5; ` `var query4 =` `from a in context.GetTable<Model.Client>()` `where a.ClientId >= 1 && a.ClientId <= 10` `select 6; ` `var tuple = context.Database.ExecuteMultiple<int, int>(query3, query4); ` `query3 =` `from a in context.GetTable<Model.Client>()` `where a.ClientId >= 1 && a.ClientId <= 10` `select 5; ` `query4 =` `from a in context.GetTable<Model.Client>()` `where a.ClientId >= 1 && a.ClientId <= 10` `select 6; ` `var query5 =` `from a in context.GetTable<Model.Client>()` `where a.ClientId >= 1 && a.ClientId <= 10` `select 7; ` `var tuple2 = context.Database.ExecuteMultiple<int, int, int>(query3, query4, query5); `Copy the code35. Transaction operations
Borrowing from the EntityFramework, the framework also supports the ability to start a transaction itself, or to start a transaction from another context and then use it within the framework.
` / / transaction 1. Context independent transaction ` ` try ` ` {` ` using (var transaction = context. Database. BeginTransaction ()) ` ` {` ` var result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId <= 10); ' 'context.update < model. Client>(x => new model. Client' '{' ClientName = "transaction 1"'}, x => x.clientid == result.clientid); ` `context.SubmitChanges(); ` `result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId); ' 'context.update < model. Client>(x => new model. Client' '{' ClientName = "transaction 2"'}, x => x.clientid == result.clientid); ` `context.SubmitChanges(); ` `result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId); '//throw new Exception(" pretend Exception "); ` `//transaction.Rollback(); ` `transaction.Commit(); ` `} ` `} ` ` finally ` ` {` ` / / open after the transaction must be explicitly release resources ` ` context. The Dispose (); IDbTransaction Transaction2 = null; ` `IDbConnection connection = null; ` `try` `{` `connection = context.Database.DbProviderFactory.CreateConnection(); ` `connection.ConnectionString = context.Database.ConnectionString; ` `if (connection.State ! = ConnectionState.Open) connection.Open(); ` `transaction2 = connection.BeginTransaction(); ` ` / / specify the Transaction ` ` context. The Database. The Transaction = transaction2; ` `var result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId <= 10); ' 'context.update < model. Client>(x => new model. Client' '{' ClientName = "transaction 3"'}, x => x.clientid == result.clientid); ` `context.SubmitChanges(); ` `result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId); ' 'context.update < model. Client>(x => new model. Client' '{' ClientName = "transaction 4"'}, x => x.clientid == result.clientid); ` `result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId); ' 'string SQL = @"UPDATE Bas_Client SET ClientName = N' transaction 5' WHERE ClientID=2; UPDATE Bas_Client SET ClientName = N' transaction 6' WHERE ClientID=3; ; ` `context.AddQuery(sql); ` `context.SubmitChanges(); ` `transaction2.Commit(); ` `}` `catch` `{` `if (transaction2 ! = null) transaction2.Rollback(); ` `throw; ` `}` `finally` `{` `if (transaction2 ! = null) transaction2.Dispose(); ` `if (connection ! = null) connection.Close(); ` `if (connection ! = null) connection.Dispose(); ` `context.Dispose(); ` ` `}Copy the code36. The table variables
SQLSERVER children shoes look over, you want to cool this framework can give ~
` / / declaration form variable ` ` var typeRuntime = TypeRuntimeInfoCache. GetRuntimeInfo < SqlServerModel. JoinKey > (); ` `context.AddQuery(string.Format("DECLARE {0} [{1}]", typeRuntime.TableName, typeRuntime.TableName.TrimStart('@'))); ` `List<SqlServerModel.JoinKey> keys = new List<SqlServerModel.JoinKey>` `{` `new SqlServerModel.JoinKey{ Key1 = 2 },` `new SqlServerModel.JoinKey{ Key1 = 3 },` `}; Insert<SqlServerModel.JoinKey>(keys); Var query = ' 'from a in context.gettable < model.client >()' 'join b in context.GetTable<SqlServerModel.JoinKey>() on a.ClientId equals b.Key1` `select a; ` `context.AddQuery(query); List< model. Client> result = null; ` `context.SubmitChanges(out result); `Copy the codeconclusion
XFramework has been used in many of our company’s core projects, completely replacing Dapper + DbHelper’s data persistence solution. From the initial support only SQLSERVER to support MySQL, Postgre and Oracle, more than a month to stay up late to adhere to, the sour feeling can only be experienced. Your love and support is the biggest power for me to run all the way on the road of open source. But I believe that this is absolutely a humanized, temperature data persistence framework!!
Postscript: This document was actually posted on Blogpark two years ago, and found on Github todaywebsyncThis one key to send a number of platform documents, especially take this document to play, do not like the light spray.
- Riz.x framework is now fully open source, following the Apache2.0 protocol, hosted address:
- Yards cloud: gitee.com/TANZAME/Riz…
- GitHub:github.com/TANZAME/Riz…
Technical exchange Group :816425449