ITDevSpace - Development Tips & Tricks

Left outer join extension method is based on How to: Perform Left Outer Joins article. [Test] public void Test() { var list1 = new List< int >() { 1, 2, 3, 4, 5}.Select(x=> new {a=x, b=x*x}); var list2 = new List< int >() { 1, 2, 3, 4 }.Select(x => new { a = x, b = x * x * x }); var innerJoin = list1.Join(list2, x => x.a, y => y.a, (x, y) => new {a = x.a, b = x.b, c = y.b}); innerJoin.ForEach(Console.WriteLine); var leftOuterJoin = list1.LeftOuterJoin(list2, x => x.a, y => y.a, (x, y) => new { a = x.a, b = x.b, c = (y != null ) ? y.b : ( int ?) null }); leftOuterJoin.ForEach(Console.WriteLine); } public static class Extensions { public static IEnumerable<TResult> LeftOuterJoin<TOuter, TInner, TKey, TResult>( this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOute...

[Test] public void TestDataTableToEnumeration() { var dt = new DataTable(); dt.Columns.Add(" Id ", typeof ( int )); dt.Columns.Add(" Name ", typeof ( string )); dt.Columns.Add(" Value ", typeof ( double )); for ( int i = 0; i < 5; i++) { var row = dt.NewRow(); row[" Id "] = i; row[" Name "] = " Name " + i; row[" Value "] = i + 0.1d; dt.Rows.Add(row); } var data = dt.AsEnumerable().Select(row => new { Id = ( int ) row[" Id "], Name = ( string ) row[" Name "], Value = ( double ) row[" Value "] }).Where(x => x.Id < 3); foreach (var row in data) { Console.WriteLine(" {0},{1},{2} ", row.Id, row.Name, row.Value); } }

public void TestLinqPivot() { var before = new []{ new {Ticker=" FB ", Type=" BID ", Value=1}, new {Ticker=" FB ", Type=" ASK ", Value=11}, new {Ticker=" IBM ", Type=" BID ", Value=2}, new {Ticker=" IBM ", Type=" ASK ", Value=22} }; Console.WriteLine(" Ticker,Type,Value "); foreach (var item in before) { Console.WriteLine(" {0},{1},{2} ", item.Ticker, item.Type, item.Value); } var after = before.GroupBy(x => x.Ticker).Select( x => new { Ticker = x.Key, BID = x.Where(y => y.Type.Equals(" BID ")).Sum(y => y.Value), ASK = x.Where(y => y.Type.Equals(" ASK ")).Sum(y => y.Value) }); Console.WriteLine(); Console.WriteLine(" Ticker,BID,ASK "); foreach (var item in after) { Console.WriteLine(" {0},{1},{2} ", ite...

public void TestGroupBy() { var people = new [] { new { Name=" John ", City=" London ", Side=" South ", Age=20}, new { Name=" John ", City=" London ", Side=" North ", Age=55}, new { Name=" Eli ", City=" London ", Side=" North ", Age=39}, new { Name=" Anna ", City=" NY ", Side=" North ", Age=23}, new { Name=" Marc ", City=" NY ", Side=" South ", Age=51}, new { Name=" Julie ", City=" NY ", Side=" South ", Age=67}, }.ToList(); var ageByCitySide = people .GroupBy(p => new {p.City, p.Side}) .Select(r => new {r.Key.City, r.Key.Side, AverageAge = r.Average(p => p.Age)}) .OrderBy(p=>p.AverageAge); foreach (var p in ageByCitySide) { Console.WriteLine(" {0},{1} AvgAge:{2} ",p.City,p.Side,p.AverageAge); } var youngOldBy...

This is type safe version of ExecuteQuery method using anonymous object as a template public static class DataContextExtensions { public static IEnumerable<T> ExecuteQuery<T>( this DataContext ctx, string query, T template, DbParameter[] parameters = null ) where T : class { using (DbCommand cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = query; if (parameters != null ) cmd.Parameters.AddRange(parameters); ctx.Connection.Open(); using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (rdr.Read()) { object [] values = new object [rdr.FieldCount]; for ( int i = 0; i < rdr.FieldCount; i++) { if (!rdr.IsDBNull(i)) { if (rdr.GetFieldType(i) == typeof (Decimal)) //special case if you need it? { values[i] = Convert.ToDouble(rdr.GetDecimal(i)); } ...

This is an extension method that returns enumeration of dynamic objects based on SQL query ResultSet. public static class DataContextExtensions { public static IEnumerable<dynamic> ExecuteQuery( this DataContext ctx, string query, DbParameter[] parameters = null ) { using (DbCommand cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = query; if (parameters != null ) cmd.Parameters.AddRange(parameters); ctx.Connection.Open(); using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (rdr.Read()) { dynamic row = new DynamicRow(); for ( int i = 0; i < rdr.FieldCount; i++) { row[rdr.GetName(i)] = rdr[i]; } yield return row; } } } } } //DynamicRow class is similiar to ExpandoObject but with addition of indexer public class DynamicRow : DynamicObject { private readonly Dictionary< strin...

Tutorial on using Functional Programming (FP) techniques Query Composition using Functional Programming Techniques in C# 3.0

namespace Test { public static class Extensions { //ForEach IEnumerable extension public static void ForEach<T>( this IEnumerable<T> source, Action<T> action) { foreach (var item in source) { action(item); } } } class Person { public string Name { get ; set ; } public string City { get ; set ; } } class Account { public string Name { get ; set ; } public string AccountName { get ; set ; } public double Amount { get ; set ; } public List< string > Cards { get ; set ; } } [TestFixture] public class TestFunc { private List<Person> people; private List<Account> accounts; [TestFixtureSetUp] public void SetUp() { people = new List<Person>(); people.Add( new Person(){Name=" J...