PageRenderTime 60ms CodeModel.GetById 7ms app.highlight 28ms RepoModel.GetById 22ms app.codeStats 0ms

/Demo/Linq/Demo/Program.cs

https://github.com/ACher/bltoolkit
C# | 740 lines | 646 code | 94 blank | 0 comment | 20 complexity | db1b93cd51e0780762a8e047fb89aaa3 MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Diagnostics;
  4using System.Linq;
  5using System.Linq.Expressions;
  6
  7using BLToolkit.Data;
  8using BLToolkit.Data.Linq;
  9
 10namespace Linq.Demo
 11{
 12	using DataModel;
 13
 14	static class Program
 15	{
 16		static void Main()
 17		{
 18			DbManager.TraceSwitch = new TraceSwitch("DbManager", "DbManager trace switch", "Info");
 19
 20			FirstTest();
 21			CountTest();
 22			SingleTableTest();
 23			SelectManyTest();
 24			InnerJoinTest();
 25			LeftJoinTest();
 26			AssociationInnerJoinTest();
 27			AssociationCountTest();
 28			AssociationObjectTest();
 29			MultiLevelAssociationTest();
 30			CompareAssociationTest();
 31			GroupByAssociationTest();
 32			InheritanceTest1();
 33			InheritanceTest2();
 34			StringLengthTest();
 35			StringCompareTest();
 36			MathRoundCompare1Test();
 37			MathRoundCompare2Test();
 38			MathRoundCompare3Test();
 39			SimpleSelectTest();
 40			InsertTest1();
 41			InsertTest2();
 42			MultipleInsertTest1();
 43			MultipleInsertTest2();
 44			InsertWithIdentityTest1();
 45			InsertWithIdentityTest2();
 46			UpdateTest1();
 47			UpdateTest2();
 48			UpdateTest3();
 49			SelfUpdateTest();
 50			DeleteTest1();
 51			DeleteTest2();
 52			SqlLengthTest();
 53			SqlRoundCompare1Test();
 54			SqlSelectLengthTest();
 55			SqlSelectLengthAsSqlTest();
 56			RoundToEvenTest();
 57			MethodExpressionTest();
 58			CompiledQueryTest();
 59		}
 60
 61		static void FirstTest()
 62		{
 63			using (var db = new NorthwindDB())
 64			{
 65				var query = db.Employee;
 66
 67				foreach (var employee in query)
 68				{
 69					Console.WriteLine("{0} {1}", employee.EmployeeID, employee.FirstName);
 70				}
 71			}
 72		}
 73
 74		static void CountTest()
 75		{
 76			using (var db = new NorthwindDB())
 77			{
 78				int count = db.Employee.Count();
 79
 80				Console.WriteLine(count);
 81			}
 82		}
 83
 84		static void SingleTableTest()
 85		{
 86			using (var db = new NorthwindDB())
 87			{
 88				var query =
 89					from e in db.Employee
 90					where e.EmployeeID > 5
 91					orderby e.LastName, e.FirstName
 92					select e;
 93
 94				foreach (var employee in query)
 95				{
 96					Console.WriteLine("{0} {1}, {2}", employee.EmployeeID, employee.LastName, employee.FirstName);
 97				}
 98			}
 99		}
100
101		static void SelectManyTest()
102		{
103			using (var db = new NorthwindDB())
104			{
105				var query =
106					from c in db.Category
107					from p in db.Product
108					where p.CategoryID == c.CategoryID
109					select new
110					{
111						c.CategoryName,
112						p.ProductName
113					};
114
115				foreach (var item in query)
116				{
117					Console.WriteLine(item);
118				}
119			}
120		}
121
122		static void InnerJoinTest()
123		{
124			using (var db = new NorthwindDB())
125			{
126				var query =
127					from p in db.Product
128					join c in db.Category on p.CategoryID equals c.CategoryID
129					select new
130					{
131						c.CategoryName,
132						p.ProductName
133					};
134
135				foreach (var item in query)
136				{
137					Console.WriteLine(item);
138				}
139			}
140		}
141
142		static void LeftJoinTest()
143		{
144			using (var db = new NorthwindDB())
145			{
146				var query =
147					from p in db.Product
148					join c in db.Category on p.CategoryID equals c.CategoryID into g
149					from c in g.DefaultIfEmpty()
150					select new
151					{
152						c.CategoryName,
153						p.ProductName
154					};
155
156				foreach (var item in query)
157				{
158					Console.WriteLine(item);
159				}
160			}
161		}
162
163		static void AssociationInnerJoinTest()
164		{
165			using (var db = new NorthwindDB())
166			{
167				var query =
168					from p in db.Product
169					select new
170					{
171						p.Category.CategoryName,
172						p.ProductName
173					};
174
175				foreach (var item in query)
176				{
177					Console.WriteLine(item);
178				}
179			}
180		}
181
182		static void AssociationCountTest()
183		{
184			using (var db = new NorthwindDB())
185			{
186				var query =
187					from p in db.Product
188					select new
189					{
190						p.OrderDetails.Count,
191						p.ProductName
192					};
193
194				foreach (var item in query)
195				{
196					Console.WriteLine(item);
197				}
198			}
199		}
200
201		static void AssociationObjectTest()
202		{
203			using (var db = new NorthwindDB())
204			{
205				var query =
206					from o in db.Order
207					select new Order
208					{
209						OrderID  = o.OrderID,
210						Customer = o.Customer
211					};
212
213				foreach (var item in query)
214				{
215					Console.WriteLine(item);
216				}
217			}
218		}
219
220		static void MultiLevelAssociationTest()
221		{
222			using (var db = new NorthwindDB())
223			{
224				var query =
225					from o in db.OrderDetail
226					select new
227					{
228						o.Product.ProductName,
229						o.Order.OrderID,
230						o.Order.Employee.ReportsToEmployee.Region
231					};
232
233				foreach (var item in query)
234				{
235					Console.WriteLine(item);
236				}
237			}
238		}
239
240		static void CompareAssociationTest()
241		{
242			using (var db = new NorthwindDB())
243			{
244				var query =
245					from o in db.Order
246					from t in db.EmployeeTerritory
247					where o.Employee == t.Employee
248					select new
249					{
250						o.OrderID,
251						o.EmployeeID,
252						t.TerritoryID
253					};
254
255				foreach (var item in query)
256				{
257					Console.WriteLine(item);
258				}
259			}
260		}
261
262		static void GroupByAssociationTest()
263		{
264			using (var db = new NorthwindDB())
265			{
266				var query =
267					from p in db.Product
268					group p by p.Category into g
269					where g.Count() == 12
270					select g.Key.CategoryName;
271
272				foreach (var item in query)
273				{
274					Console.WriteLine(item);
275				}
276			}
277		}
278
279		static void InheritanceTest1()
280		{
281			using (var db = new NorthwindDB())
282			{
283				var query = from p in db.DiscontinuedProduct select p;
284
285				foreach (var item in query)
286				{
287					Console.WriteLine(item);
288				}
289			}
290		}
291
292		static void InheritanceTest2()
293		{
294			using (var db = new NorthwindDB())
295			{
296				var query =
297					from p in db.Product
298					where p is DiscontinuedProduct
299					select p;
300
301				foreach (var item in query)
302				{
303					Console.WriteLine(item);
304				}
305			}
306		}
307
308		static void StringLengthTest()
309		{
310			using (var db = new NorthwindDB())
311			{
312				var query =
313					from c in db.Customer
314					where c.ContactName.Length > 5
315					select c.ContactName;
316
317				foreach (var item in query)
318				{
319					Console.WriteLine(item);
320				}
321			}
322		}
323
324		static void StringCompareTest()
325		{
326			using (var db = new NorthwindDB())
327			{
328				var query =
329					from c in db.Customer
330					where c.ContactName.CompareTo("John") > 0
331					select c.ContactName;
332
333				foreach (var item in query)
334				{
335					Console.WriteLine(item);
336				}
337			}
338		}
339
340		static void MathRoundCompare1Test()
341		{
342			using (var db = new NorthwindDB())
343			{
344				var query =
345					from o in db.Order
346					where Math.Round(o.Freight.Value) >= 10
347					select o.Freight;
348
349				foreach (var item in query)
350				{
351					Console.WriteLine(item);
352				}
353			}
354		}
355
356		static void MathRoundCompare2Test()
357		{
358			using (var db = new NorthwindDB())
359			{
360				var query =
361					from o in db.Order
362					where Math.Round(o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)) >= 10
363					select o.Freight;
364
365				foreach (var item in query)
366				{
367					Console.WriteLine(item);
368				}
369			}
370		}
371
372		static void MathRoundCompare3Test()
373		{
374			using (var db = new NorthwindDB())
375			{
376				var query =
377					from o in db.Order
378					let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)
379					where Math.Round(sum) >= 10
380					select o.Freight;
381
382				foreach (var item in query)
383				{
384					Console.WriteLine(item);
385				}
386			}
387		}
388
389		static void SimpleSelectTest()
390		{
391			using (var db = new NorthwindDB())
392			{
393				var value = db.Select(() => Sql.CurrentTimestamp);
394
395				Console.WriteLine(value);
396			}
397		}
398
399		static void InsertTest1()
400		{
401			using (var db = new NorthwindDB())
402			{
403				var value = db.Employee.Insert(() => new Employee
404				{
405					FirstName = "John",
406					LastName  = "Shepard",
407					Title     = "Spectre",
408					HireDate  = Sql.CurrentTimestamp
409				});
410
411				Console.WriteLine(value);
412
413				db.Employee.Delete(e => e.LastName == "Shepard");
414			}
415		}
416
417		static void InsertTest2()
418		{
419			using (var db = new NorthwindDB())
420			{
421				var value =
422					db
423						.Into(db.Employee)
424							.Value(e => e.FirstName, "John")
425							.Value(e => e.LastName,  "Shepard")
426							.Value(e => e.Title,     "Spectre")
427							.Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
428						.Insert();
429
430				Console.WriteLine(value);
431
432				db.Employee.Delete(e => e.LastName == "Shepard");
433			}
434		}
435
436		static void MultipleInsertTest1()
437		{
438			using (var db = new NorthwindDB())
439			{
440				var value =
441					db.Region
442						.Where(r => r.RegionID > 2)
443						.Insert(db.Region, r => new Region()
444						{
445							RegionID          = r.RegionID + 100,
446							RegionDescription = "Copy Of " + r.RegionDescription
447						});
448
449				Console.WriteLine(value);
450
451				db.Region.Delete(r => r.RegionDescription.StartsWith("Copy Of "));
452			}
453		}
454
455		static void MultipleInsertTest2()
456		{
457			using (var db = new NorthwindDB())
458			{
459				var value =
460					db.Region
461						.Where(r => r.RegionID > 2)
462						.Into(db.Region)
463							.Value(r => r.RegionID,          r => r.RegionID + 100)
464							.Value(r => r.RegionDescription, r => "Copy Of " + r.RegionDescription)
465						.Insert();
466
467				Console.WriteLine(value);
468
469				db.Region.Delete(r => r.RegionDescription.StartsWith("Copy Of "));
470			}
471		}
472
473		static void InsertWithIdentityTest1()
474		{
475			using (var db = new NorthwindDB())
476			{
477				var value = db.Employee.InsertWithIdentity(() => new Employee
478				{
479					FirstName = "John",
480					LastName  = "Shepard",
481					Title     = "Spectre",
482					HireDate  = Sql.CurrentTimestamp
483				});
484
485				Console.WriteLine(value);
486
487				db.Employee.Delete(e => e.EmployeeID == Convert.ToInt32(value));
488			}
489		}
490
491		static void InsertWithIdentityTest2()
492		{
493			using (var db = new NorthwindDB())
494			{
495				var value =
496					db
497						.Into(db.Employee)
498							.Value(e => e.FirstName, "John")
499							.Value(e => e.LastName,  "Shepard")
500							.Value(e => e.Title,     () => "Spectre")
501							.Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
502						.InsertWithIdentity();
503
504				Console.WriteLine(value);
505
506				db.Employee.Delete(e => e.EmployeeID == Convert.ToInt32(value));
507			}
508		}
509
510		static void UpdateTest1()
511		{
512			using (var db = new NorthwindDB())
513			{
514				var value =
515					db.Employee
516						.Update(
517							e => e.Title == "Spectre",
518							e => new Employee
519							{
520								Title = "Commander"
521							});
522
523				Console.WriteLine(value);
524			}
525		}
526
527		static void UpdateTest2()
528		{
529			using (var db = new NorthwindDB())
530			{
531				var value =
532					db.Employee
533						.Where(e => e.Title == "Spectre")
534						.Update(e => new Employee
535						{
536							Title = "Commander"
537						});
538
539				Console.WriteLine(value);
540			}
541		}
542
543		static void UpdateTest3()
544		{
545			using (var db = new NorthwindDB())
546			{
547				var value =
548					db.Employee
549						.Where(e => e.Title == "Spectre")
550						.Set(e => e.Title, "Commander")
551						.Update();
552
553				Console.WriteLine(value);
554			}
555		}
556
557		static void SelfUpdateTest()
558		{
559			using (var db = new NorthwindDB())
560			{
561				var value =
562					db.Employee
563						.Where(e => e.Title == "Spectre")
564						.Set(e => e.HireDate, e => e.HireDate.Value.AddDays(10))
565						.Update();
566
567				Console.WriteLine(value);
568			}
569		}
570
571		static void DeleteTest1()
572		{
573			using (var db = new NorthwindDB())
574			{
575				var value = db.Employee.Delete(e => e.Title == "Spectre");
576
577				Console.WriteLine(value);
578			}
579		}
580
581		static void DeleteTest2()
582		{
583			using (var db = new NorthwindDB())
584			{
585				var value =
586					db.Employee
587						.Where(e => e.Title == "Spectre")
588						.Delete();
589
590				Console.WriteLine(value);
591			}
592		}
593
594		static void SqlLengthTest()
595		{
596			using (var db = new NorthwindDB())
597			{
598				var query =
599					from c in db.Customer
600					where Sql.Length(c.ContactName) > 5
601					select c.ContactName;
602
603				foreach (var item in query)
604				{
605					Console.WriteLine(item);
606				}
607			}
608		}
609
610		static void SqlRoundCompare1Test()
611		{
612			using (var db = new NorthwindDB())
613			{
614				var query =
615					from o in db.Order
616					where Sql.Round(o.Freight) >= 10
617					select o.Freight;
618
619				foreach (var item in query)
620				{
621					Console.WriteLine(item);
622				}
623			}
624		}
625
626		static void SqlSelectLengthTest()
627		{
628			using (var db = new NorthwindDB())
629			{
630				var query =
631					from c in db.Customer
632					select Sql.Length(c.ContactName);
633
634				foreach (var item in query)
635				{
636					Console.WriteLine(item);
637				}
638			}
639		}
640
641		static void SqlSelectLengthAsSqlTest()
642		{
643			using (var db = new NorthwindDB())
644			{
645				var query =
646					from c in db.Customer
647					select Sql.AsSql(Sql.Length(c.ContactName));
648
649				foreach (var item in query)
650				{
651					Console.WriteLine(item);
652				}
653			}
654		}
655
656		static decimal? RoundToEven(decimal? value)
657		{
658			return
659				value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?
660					Sql.Floor(value) :
661					Sql.Round(value);
662		}
663
664		static void RoundToEvenTest()
665		{
666			Expressions.MapMember<decimal?,decimal?>(
667				value => RoundToEven(value),
668				value =>
669					value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?
670						Sql.Floor(value) :
671						Sql.Round(value));
672
673			using (var db = new NorthwindDB())
674			{
675				var query =
676					from o in db.Order
677					let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)
678					where RoundToEven(sum) >= 10
679					select o.Freight;
680
681				foreach (var item in query)
682				{
683					Console.WriteLine(item);
684				}
685			}
686		}
687
688		[MethodExpression("OrderCountExpression")]
689		static int OrderCount(Customer customer, string region)
690		{
691			throw new InvalidOperationException();
692		}
693
694		static Expression<Func<Customer,string,int>> OrderCountExpression()
695		{
696			return (customer, region) => customer.Orders.Count(o => o.ShipRegion == region);
697		}
698
699		static void MethodExpressionTest()
700		{
701			using (var db = new NorthwindDB())
702			{
703				var query =
704					from c in db.Customer
705					select new
706					{
707						sum1 = OrderCount(c, "SP"),
708						sum2 = OrderCount(c, "NM")
709					};
710
711				foreach (var item in query)
712				{
713					Console.WriteLine(item);
714				}
715			}
716		}
717
718		static Func<NorthwindDB,int,IEnumerable<Employee>> _query =
719			CompiledQuery.Compile<NorthwindDB,int,IEnumerable<Employee>>((db, n) =>
720				from e in db.Employee
721				where e.EmployeeID > n
722				orderby e.LastName, e.FirstName
723				select e
724			);
725
726		static void CompiledQueryTest()
727		{
728			using (var db = new NorthwindDB())
729			{
730				var query = _query(db, 5);
731
732				foreach (var item in query)
733				{
734					Console.WriteLine(item);
735				}
736			}
737		}
738	}
739}
740