static void Main(string[] args) { using (northwindEFEntities db = new northwindEFEntities()) { { string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE SQLite.DatePart('yyyy', o.OrderDate) = 1997;"; ObjectQuery<Orders> query = db.CreateQuery<Orders>(entitySQL); foreach (Orders o in query) { Console.WriteLine(o.ShipPostalCode); } } { var query = from c in db.Customers where c.City == "London" orderby c.CompanyName select c; int cc = query.Count(); foreach (Customers c in query) { Console.WriteLine(c.CompanyName); } } { string scity = "London"; Customers c = db.Customers.FirstOrDefault(cd => cd.City == scity); Console.WriteLine(c.CompanyName); } { DateTime dt = new DateTime(1997, 1, 1); var query = from order in db.Orders where order.OrderDate < dt select order; foreach (Orders o in query) { Console.WriteLine(o.OrderDate.ToString()); } } { Categories c = new Categories(); c.CategoryName = "Test Category"; c.Description = "My Description"; db.AddToCategories(c); db.SaveChanges(); Console.WriteLine(c.CategoryID); c.Description = "My modified description"; db.SaveChanges(); db.DeleteObject(c); db.SaveChanges(); } { Customers cust = new Customers(); cust.CustomerID = "MTMTM"; cust.ContactName = "My Name"; cust.CompanyName = "SQLite Company"; cust.Country = "Netherlands"; cust.City = "Amsterdam"; cust.Phone = "012345677"; db.AddToCustomers(cust); db.SaveChanges(); db.DeleteObject(cust); db.SaveChanges(); } { var query = db.Customers.Where(cust => cust.Country == "Denmark") .SelectMany(cust => cust.Orders.Where(o => o.Freight > 5)); foreach (Orders c in query) { Console.WriteLine(c.Freight); } } { var query = from c in db.Customers where c.Orders.Any(o => o.OrderDate.HasValue == true && o.OrderDate.Value.Year == 1997) select c; foreach (Customers c in query) { Console.WriteLine(c.CompanyName); } } { string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customers.Country <> 'UK' AND o.Customers.Country <> 'Mexico' AND Year(o.OrderDate) = 1997;"; ObjectQuery<Orders> query = db.CreateQuery<Orders>(entitySQL); foreach (Orders o in query) { Console.WriteLine(o.ShipPostalCode); } } { string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE NewGuid() <> NewGuid();"; ObjectQuery<Orders> query = db.CreateQuery<Orders>(entitySQL); foreach (Orders o in query) { Console.WriteLine(o.ShipPostalCode); } } // This query requires SQLite 3.6.2 to function correctly { var query = from p in db.Products where p.OrderDetails.Count(od => od.Orders.Customers.Country == p.Suppliers.Country) > 2 select p; foreach (Products p in query) { Console.WriteLine(p.ProductName); } } } Console.ReadKey(); }
private static int ComplexPrimaryKeyTest() { using (northwindEFEntities db = new northwindEFEntities()) { long orderId = 10248; long productId = 1; int[] counts = { 0, 0 }; // // NOTE: *REQUIRED* This is required so that the // Entity Framework is prevented from opening // multiple connections to the underlying SQLite // database (i.e. which would result in multiple // IMMEDIATE transactions, thereby failing [later // on] with locking errors). // db.Connection.Open(); KeyValuePair<string, object> orderIdPair = new KeyValuePair<string, object>("OrderID", orderId); KeyValuePair<string, object> productIdPair = new KeyValuePair<string, object>("ProductID", productId); ///////////////////////////////////////////////////////////////// OrderDetails newOrderDetails = new OrderDetails(); newOrderDetails.OrderID = orderId; newOrderDetails.ProductID = productId; newOrderDetails.UnitPrice = (decimal)1.23; newOrderDetails.Quantity = 1; newOrderDetails.Discount = 0.0f; newOrderDetails.OrdersReference.EntityKey = new EntityKey( "northwindEFEntities.Orders", new KeyValuePair<string, object>[] { orderIdPair }); newOrderDetails.ProductsReference.EntityKey = new EntityKey( "northwindEFEntities.Products", new KeyValuePair<string, object>[] { productIdPair }); db.AddObject("OrderDetails", newOrderDetails); try { db.SaveChanges(); counts[0]++; } catch (Exception e) { Console.WriteLine(e); } finally { db.AcceptAllChanges(); } try { db.Refresh(RefreshMode.StoreWins, newOrderDetails); counts[0]++; } catch (Exception e) { Console.WriteLine(e); } Console.WriteLine("inserted {0}", counts[0]); ///////////////////////////////////////////////////////////////// newOrderDetails.UnitPrice = (decimal)2.34; newOrderDetails.Quantity = 2; newOrderDetails.Discount = 0.1f; try { db.SaveChanges(); counts[1]++; } catch (Exception e) { Console.WriteLine(e); } finally { db.AcceptAllChanges(); } try { db.Refresh(RefreshMode.StoreWins, newOrderDetails); counts[1]++; } catch (Exception e) { Console.WriteLine(e); } Console.WriteLine("updated {0}", counts[1]); } return 0; }
// // NOTE: Used to test the fix for ticket [ccfa69fc32]. // private static int EFTransactionTest(bool add) { // // NOTE: Some of these territories already exist and should cause // an exception to be thrown when we try to INSERT them. // long[] territoryIds = new long[] { 1, 2, 3, 4, 5, // NOTE: Success 6, 7, 8, 9, 10, // NOTE: Success 1576, 1577, 1578, 1579, 1580, // NOTE: Success 1581, 1730, 1833, 2116, 2139, // NOTE: Fail (1581) 2140, 2141 // NOTE: Skipped }; if (add) { using (northwindEFEntities db = new northwindEFEntities()) { using (TransactionScope scope = new TransactionScope()) { // // NOTE: *REQUIRED* This is required so that the // Entity Framework is prevented from opening // multiple connections to the underlying SQLite // database (i.e. which would result in multiple // IMMEDIATE transactions, thereby failing [later // on] with locking errors). // db.Connection.Open(); foreach (int id in territoryIds) { Territories territories = new Territories(); territories.TerritoryID = id; territories.TerritoryDescription = String.Format( "Test Territory #{0}", id); territories.Regions = db.Regions.First(); db.AddObject("Territories", territories); } try { #if NET_20 db.SaveChanges(false); #else db.SaveChanges(SaveOptions.None); #endif } catch (Exception e) { Console.WriteLine(e); } finally { scope.Complete(); db.AcceptAllChanges(); } } } } else { using (northwindEFEntities db = new northwindEFEntities()) { bool once = false; #if NET_20 // // HACK: We cannot use the Contains extension method within a // LINQ query with the .NET Framework 3.5. // var query = from t in db.Territories orderby t.TerritoryID select t; foreach (Territories territories in query) { if (Array.IndexOf(territoryIds, territories.TerritoryID) == -1) continue; if (once) Console.Write(' '); Console.Write(territories.TerritoryID); once = true; } #else var query = from t in db.Territories where territoryIds.AsQueryable<long>().Contains<long>(t.TerritoryID) orderby t.TerritoryID select t; foreach (Territories territories in query) { if (once) Console.Write(' '); Console.Write(territories.TerritoryID); once = true; } #endif } } return 0; }
// // NOTE: Used to test the UPDATE fix (i.e. the missing semi-colon // in the SQL statement between the actual UPDATE statement // and the follow-up SELECT statement). // private static int UpdateTest() { long[] orderIds = new long[] { 0 }; using (northwindEFEntities db = new northwindEFEntities()) { int[] counts = { 0, 0 }; // // NOTE: *REQUIRED* This is required so that the // Entity Framework is prevented from opening // multiple connections to the underlying SQLite // database (i.e. which would result in multiple // IMMEDIATE transactions, thereby failing [later // on] with locking errors). // db.Connection.Open(); for (int index = 0; index < orderIds.Length; index++) { Orders newOrders = new Orders(); newOrders.ShipAddress = String.Format( "Test Order Ship Address, Index #{0}", index); db.AddObject("Orders", newOrders); try { db.SaveChanges(); counts[0]++; // StoreGeneratedPattern="Identity" orderIds[index] = newOrders.OrderID; // StoreGeneratedPattern="None" newOrders.ShipAddress = String.Format( "New Order Ship Address #{0}", orderIds[index]); // StoreGeneratedPattern="Computed" newOrders.Freight = 1; db.SaveChanges(); counts[1]++; } catch (Exception e) { Console.WriteLine(e); } finally { db.AcceptAllChanges(); } } Console.WriteLine( "inserted {0} updated {1}", counts[0], counts[1]); } return 0; }