Beispiel #1
0
 public void HourMinuteSecond()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(
             @"SELECT c.DateBegan, Hour(c.DateBegan), Minute(c.DateBegan), Second(c.DateBegan)
                 FROM Companies AS c WHERE c.Id=1");
         foreach (DbDataRecord record in q)
         {
             Assert.Equal(5, record[1]);
             Assert.Equal(18, record[2]);
             Assert.Equal(23, record[3]);
         }
     }
 }
Beispiel #2
0
 public void ToUpperToLowerReverse()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(
             @"SELECT ToUpper(c.Name),ToLower(c.Name),
             Reverse(c.Name) FROM Companies AS c WHERE c.Id=1");
         foreach (DbDataRecord r in q)
         {
             Assert.Equal("HASBRO", r[0]);
             Assert.Equal("hasbro", r[1]);
             Assert.Equal("orbsaH", r[2]);
         }
     }
 }
Beispiel #3
0
 public void ListContains2In()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         int[] Ages = new int[] { 8, 9, 10 };
         var   q    = from e in ctx.Products
                      where Ages.Contains(e.MinAge)
                      orderby e.Name
                      select e;
         var sql = q.ToString();
         st.CheckSql(sql,
                     @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, 
   `Extent1`.`CreatedDate` FROM `Products` AS `Extent1` WHERE `Extent1`.`MinAge` IN ( 8,9,10 )
   ORDER BY `Extent1`.`Name` ASC");
     }
 }
Beispiel #4
0
 public void YearMonthDay()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(
             @"SELECT c.DateBegan, Year(c.DateBegan), Month(c.DateBegan), Day(c.DateBegan)
                 FROM Companies AS c WHERE c.Id=1");
         foreach (DbDataRecord record in q)
         {
             Assert.Equal(1996, record[1]);
             Assert.Equal(11, record[2]);
             Assert.Equal(15, record[3]);
         }
     }
 }
Beispiel #5
0
        public void CurrentDateTime()
        {
            DateTime current = DateTime.Now;

            using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
            {
                var context = ((IObjectContextAdapter)ctx).ObjectContext;
                ObjectQuery <DateTime> q = context.CreateQuery <DateTime>("CurrentDateTime()");
                foreach (DateTime dt in q)
                {
                    Assert.Equal(current.Year, dt.Year);
                    Assert.Equal(current.Month, dt.Month);
                    Assert.Equal(current.Day, dt.Day);
                    // we don't check time as that will be always be different
                }
            }
        }
Beispiel #6
0
 public void JoinOnRightSideAsDerivedTable()
 {
     using (DefaultContext ctx = st.GetDefaultContext())
     {
         var q = from b in ctx.Books
                 join a in ctx.ContractAuthors
                 on b.Author.Id equals a.Author.Id
                 where b.Pages > 300
                 select b;
         string sql      = q.ToString();
         var    expected = @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`PubDate`, `Extent1`.`Pages`, `Extent1`.`Author_Id`
                 FROM `Books` AS `Extent1` INNER JOIN `ContractAuthors` AS `Extent2` ON (`Extent1`.`Author_Id` = 
                 `Extent2`.`Author_Id`) OR ((`Extent1`.`Author_Id` IS  NULL) AND (`Extent2`.`Author_Id` IS  NULL))
                 WHERE `Extent1`.`Pages` > 300";
         st.CheckSql(sql, expected);
     }
 }
Beispiel #7
0
        public void TimeType()
        {
            using (DefaultContext ctx = st.GetDefaultContext())
            {
                TimeSpan birth = new TimeSpan(11, 3, 2);

                Child c = new Child();
                c.ChildId   = "ABC";
                c.Name      = "first";
                c.BirthTime = birth;
                c.Label     = Guid.NewGuid();
                ctx.Children.Add(c);
                ctx.SaveChanges();

                Child d = ctx.Children.Where(x => x.ChildId == "ABC").Single();
                Assert.Equal(birth, d.BirthTime);
            }
        }
Beispiel #8
0
        public void SimpleDeleteAllRows()
        {
            using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
            {
                Assert.True(ctx.Products.Count() > 0);

                foreach (Product p in ctx.Products)
                {
                    ctx.Products.Remove(p);
                }
                ctx.SaveChanges();

                Assert.Equal(0, ctx.Products.Count());
            }
            // set the flag that will cause the setup to happen again
            // since we just blew away a table
            st.NeedSetup = true;
        }
Beispiel #9
0
        public void SimpleDeleteRowByParameter()
        {
            using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
            {
                int total   = ctx.Products.Count();
                int cntLeft = ctx.Products.Where(b => b.MinAge >= 18).Count();
                // make sure the test is valid
                Assert.True(total > cntLeft);

                foreach (Product p in ctx.Products.Where(b => b.MinAge < 18).ToList())
                {
                    ctx.Products.Remove(p);
                }
                ctx.SaveChanges();
                Assert.Equal(cntLeft, ctx.Products.Count());
                st.NeedSetup = true;
            }
        }
Beispiel #10
0
 public void ComplexListIn()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         int[] Ages = new int[] { 8, 9, 10 };
         var   q    = from e in ctx.Products
                      where (Ages.Contains(e.MinAge) && e.Name.Contains("Hoop")) ||
                      !Ages.Contains(e.MinAge)
                      orderby e.Name
                      select e;
         var sql = q.ToString();
         st.CheckSql(sql,
                     @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
     FROM `Products` AS `Extent1` WHERE ((`Extent1`.`MinAge` IN ( 8,9,10 )) AND 
     (`Extent1`.`Name` LIKE @gp1)) OR (`Extent1`.`MinAge` NOT  IN ( 8,9,10 )) ORDER BY 
     `Extent1`.`Name` ASC");
     }
 }
Beispiel #11
0
 public void SimpleJoin()
 {
     using (DefaultContext ctx = st.GetDefaultContext())
     {
         var q = from b in ctx.Books
                 join a in ctx.Authors
                 on b.Author.Id equals a.Id
                 select new
         {
             bookId     = b.Id,
             bookName   = b.Name,
             authorName = a.Name
         };
         var expected = @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent2`.`Name` AS `Name1`
                 FROM `Books` AS `Extent1` INNER JOIN `Authors` AS `Extent2` ON `Extent1`.`Author_Id` = `Extent2`.`Id`";
         st.CheckSql(q.ToString(), expected);
     }
 }
Beispiel #12
0
        public void IndexOf()
        {
            using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
            {
                var context           = ((IObjectContextAdapter)ctx).ObjectContext;
                ObjectQuery <Int32> q = context.CreateQuery <Int32>(@"IndexOf('needle', 'haystackneedle')");
                foreach (int index in q)
                {
                    Assert.Equal(9, index);
                }

                q = context.CreateQuery <Int32>(@"IndexOf('haystack', 'needle')");
                foreach (int index in q)
                {
                    Assert.Equal(0, index);
                }
            }
        }
 public void CanGroupBySingleColumn()
 {
     using (DefaultContext ctx = st.GetDefaultContext())
     {
         var authors = from a in ctx.Authors
                       group a by a.Age into cgroup
                       select new
         {
             Name  = cgroup.Key,
             Count = cgroup.Count()
         };
         string sql = authors.ToString();
         st.CheckSql(sql,
                     @"SELECT `GroupBy1`.`K1` AS `Age`,  `GroupBy1`.`A1` AS `C1` FROM (SELECT
   `Extent1`.`Age` AS `K1`, COUNT(1) AS `A1` FROM `Authors` AS `Extent1`
   GROUP BY `Extent1`.`Age`) AS `GroupBy1`");
     }
 }
Beispiel #14
0
 void LoadData()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         ctx.Products.Add(new Product()
         {
             Id = 1, Name = "Garbage Truck", Weight = 8.865f
         });
         ctx.Products.Add(new Product()
         {
             Id = 2, Name = "Fire Truck", Weight = 12.623f
         });
         ctx.Products.Add(new Product()
         {
             Id = 3, Name = "Hula Hoop", Weight = 2.687f
         });
         ctx.SaveChanges();
     }
 }
 void LoadData()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         ctx.Products.Add(new Product()
         {
             Name = "Garbage Truck", MinAge = 8
         });
         ctx.Products.Add(new Product()
         {
             Name = "Fire Truck", MinAge = 12
         });
         ctx.Products.Add(new Product()
         {
             Name = "Hula Hoop", MinAge = 18
         });
         ctx.SaveChanges();
     }
 }
Beispiel #16
0
 public void MultipleOrs()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         // 3rd test, using only ||'s
         var q = from e in ctx.Products
                 where e.MinAge == 37 || e.MinAge == 38 || e.MinAge == 39 ||
                 e.MinAge == 40 || e.MinAge == 40 || e.MinAge == 41 ||
                 e.MinAge == 42 || e.MinAge == 43
                 orderby e.Name
                 select e;
         var sql = q.ToString();
         st.CheckSql(sql,
                     @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
   FROM `Products` AS `Extent1` WHERE (((((((37 = `Extent1`.`MinAge`) OR (38 = `Extent1`.`MinAge`)) OR 
   (39 = `Extent1`.`MinAge`)) OR (40 = `Extent1`.`MinAge`)) OR (40 = `Extent1`.`MinAge`)) OR 
   (41 = `Extent1`.`MinAge`)) OR (42 = `Extent1`.`MinAge`)) OR (43 = `Extent1`.`MinAge`)
   ORDER BY `Extent1`.`Name` ASC");
     }
 }
Beispiel #17
0
 public void Round()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(@"
             SELECT p.Id, p.Weight, 
             Round(p.Weight) AS [Rounded Weight],
             Floor(p.Weight) AS [Floor of Weight], 
             Ceiling(p.Weight) AS [Ceiling of Weight] 
             FROM Products AS p WHERE p.Id=1");
         foreach (DbDataRecord r in q)
         {
             Assert.Equal(1, r[0]);
             Assert.Equal(8.865f, (float)r[1]);
             Assert.Equal(9, Convert.ToInt32(r[2]));
             Assert.Equal(8, Convert.ToInt32(r[3]));
             Assert.Equal(9, Convert.ToInt32(r[4]));
         }
     }
 }
Beispiel #18
0
        public void TimestampColumn()
        {
            DateTime now = DateTime.Now;

            using (DefaultContext ctx = st.GetDefaultContext())
            {
                Product p = new Product()
                {
                    Name = "My Product", MinAge = 7, Weight = 8.0f
                };
                ctx.Products.Add(p);
                ctx.SaveChanges();

                p             = ctx.Products.First();
                p.CreatedDate = now;
                ctx.SaveChanges();

                p = ctx.Products.First();
                Assert.Equal(now, p.CreatedDate);
            }
        }
Beispiel #19
0
        public void CommandTimeout()
        {
            MySqlCommand cmd = new MySqlCommand("CREATE FUNCTION spFunc() RETURNS INT BEGIN DO SLEEP(5); RETURN 4; END", st.Connection);

            cmd.ExecuteNonQuery();

            var sb = new MySqlConnectionStringBuilder(st.ConnectionString);

            sb.DefaultCommandTimeout         = 3;
            sb.UseDefaultCommandTimeoutForEF = true;
            using (DefaultContext ctx = new DefaultContext(sb.ToString()))
            {
                var exception = Record.Exception(() =>
                {
                    int val = ctx.Database.SqlQuery <int>(@"SELECT spFunc()").Single();
                });

                Assert.NotNull(exception);
            }
            st.NeedSetup = true;
        }
Beispiel #20
0
 public void Bitwise()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context           = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <Int32> q = context.CreateQuery <Int32>("BitwiseAnd(255,15)");
         foreach (int i in q)
         {
             Assert.Equal(15, i);
         }
         q = context.CreateQuery <Int32>("BitwiseOr(240,31)");
         foreach (int i in q)
         {
             Assert.Equal(255, i);
         }
         q = context.CreateQuery <Int32>("BitwiseXor(255,15)");
         foreach (int i in q)
         {
             Assert.Equal(240, i);
         }
     }
 }
Beispiel #21
0
 public void Trims()
 {
     using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
     {
         var context = ((IObjectContextAdapter)ctx).ObjectContext;
         ObjectQuery <string> query = context.CreateQuery <string>("LTrim('   text   ')");
         foreach (string s in query)
         {
             Assert.Equal("text   ", s);
         }
         query = context.CreateQuery <string>("RTrim('   text   ')");
         foreach (string s in query)
         {
             Assert.Equal("   text", s);
         }
         query = context.CreateQuery <string>("Trim('   text   ')");
         foreach (string s in query)
         {
             Assert.Equal("text", s);
         }
     }
 }
Beispiel #22
0
        public void ConversionToLike()
        {
            // Generates queries for each LIKE + wildcards case and checks SQL generated.
            using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
            {
                // Like 'pattern%'
                var q = from c in ctx.Products
                        where c.Name.StartsWith("B")
                        orderby c.Name
                        select c;
                var sql = q.ToString();
                st.CheckSql(sql,
                            @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, 
            `Extent1`.`CreatedDate` FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1
            ORDER BY `Extent1`.`Name` ASC");

                // Like '%pattern%'
                q = from c in ctx.Products
                    where c.Name.Contains("r")
                    orderby c.Name
                    select c;
                sql = q.ToString();
                st.CheckSql(sql,
                            @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, 
          `Extent1`.`CreatedDate` FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1
          ORDER BY `Extent1`.`Name` ASC");

                // Like '%pattern'
                q = from c in ctx.Products
                    where c.Name.EndsWith("y")
                    orderby c.Name
                    select c;
                sql = q.ToString();
                st.CheckSql(sql,
                            @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
          FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1 ORDER BY `Extent1`.`Name` ASC");
            }
        }
        public virtual bool Setup(Type t)
        {
            if (!NeedSetup)
            {
                return(false);
            }
            NeedSetup = false;

            database = "db-" + t.Name.ToLower();
            if (database.Length > 32)
            {
                database = database.Substring(0, 32);
            }

            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();

            sb.Server             = "localhost";
            sb.Port               = 3306;
            sb.UserID             = "root";
            sb.Pooling            = false;
            sb.AllowUserVariables = true;
            sb.Database           = database;
            ConnectionString      = sb.ToString();

            using (DefaultContext ctx = new DefaultContext(ConnectionString))
            {
                if (ctx.Database.Exists())
                {
                    ctx.Database.Delete();
                }
                ctx.Database.Create();
            }

            Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
            return(true);
        }