Пример #1
0
        public void ProjectingObjects()
        {
            using (var context = new PEF())
            {
                String query = "SELECT c, c.Address " +
                               "FROM PEF.Contact AS c " +
                               "WHERE c.FirstName='Robert'";

                ObjectQuery <DbDataRecord> contacts = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contacts)
                {
                    var contact = c[0] as Contact;
                    Console.WriteLine("{0} {1} {2}",
                                      contact.Title.Trim(),
                                      contact.FirstName.Trim(),
                                      contact.LastName);
                    foreach (var a in contact.Address)
                    {
                        Console.WriteLine(" {0}, {1}",
                                          a.Street1.Trim(), a.City);
                    }
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #2
0
        public void QueryContactsLambda()
        {
            using (var context = new PEF())
            {
                //Seleccionar toda la entidad, propiedades, campos.
                //var contacts =
                //    context.Contact.Where(c => c.FirstName == "Robert")
                //.OrderBy((foo) => foo.LastName);

                //Projection, seleccionar propiedades, campos especificos
                var contacts =
                    context.Contact
                    .Where(c => c.FirstName == "Robert")
                    .Select(c => new { c.Title, c.LastName, c.FirstName })
                    .OrderBy((foo) => foo.LastName);



                foreach (var contact in contacts)
                {
                    Console.WriteLine("{0} {1}",
                                      contact.FirstName.Trim(),
                                      contact.LastName);
                }
            }
            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #3
0
        public void ProjectingEntityReference()
        {
            using (var context = new PEF())
            {
                String query = "SELECT a,a.Contact FROM PEF.Address AS a WHERE a.CountryRegion = 'UK'";

                ObjectQuery <DbDataRecord> contacts = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contacts)
                {
                    var contact = c[1] as Contact;
                    Console.WriteLine("{0} {1} {2}",
                                      contact.Title.Trim(),
                                      contact.FirstName.Trim(),
                                      contact.LastName);
                    foreach (var a in contact.Address)
                    {
                        Console.WriteLine(" {0}, {1}",
                                          a.Street1.Trim(), a.City);
                    }
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #4
0
        public void ShapingData()
        {
            using (var context = new PEF())
            {
                String query = "SELECT a,a.Contact FROM PEF.Address AS a WHERE a.CountryRegion = 'Canada'";

                ObjectQuery <DbDataRecord> addresses = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);



                foreach (DbDataRecord item in addresses)
                {
                    var con = (Contact)item["Contact"]; //cast to Contact type
                    Console.WriteLine("LastName: {0} #Addresses: {1}",
                                      con.LastName.Trim(), con.Address.Count());
                    foreach (Address a in con.Address)
                    {
                        Console.WriteLine("....." + a.City);
                    }
                    Console.WriteLine();
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #5
0
        public void UpdateContact()
        {
            using (PEF context = new PEF())
            {
                var contact = context.Contact.First();
                contact.FirstName    = "Julia";
                contact.ModifiedDate = DateTime.Now;
                context.SaveChanges();
            }



            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #6
0
        public void NativeSQL()
        {
            using (var context = new PEF())
            {
                var contact = context.Contact.SqlQuery("SELECT * FROM Contact  WHERE  FirstName = 'Robert'").FirstOrDefault <Contact>();


                Console.WriteLine("{0} {1}",
                                  contact.FirstName.Trim(),
                                  contact.LastName);


                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #7
0
 public void QueryContacts()
 {
     using (var context = new PEF())
     {
         //using System.Data.Objects;
         //ObjectSet<Contact> contacts = context.Contacts;
         var contacts = context.Contact;
         foreach (var contact in contacts)
         {
             Console.WriteLine("{0} {1}",
                               contact.FirstName.Trim(),
                               contact.LastName);
         }
     }
     Console.Write("Press Enter...");
     Console.ReadLine();
 }
Пример #8
0
        // Using the MAX aggregate function in Entity SQL
        public void MAX()
        {
            using (var context = new PEF())
            {
                String query = "SELECT c.LastName, MAX(SELECT VALUE a.PostalCode FROM c.Address AS a) FROM PEF.Contact AS c";

                ObjectQuery <DbDataRecord> contact = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contact)
                {
                    Console.Write("{0}", c[1]);
                }
            }

            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #9
0
        public void UpdateContacts()
        {
            using (PEF context = new PEF())
            {
                var contacts = context.Contact.Include("Address")
                               .Where(c => c.FirstName == "Bobby").ToList();
                var contact = contacts[1];
                contact.FirstName = "Marquitos";
                contact           = contacts[2];
                var address = contact.Address.ToList()[0];
                address.Street1 = "Two Main Street";
                context.SaveChanges();
            }



            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #10
0
        public void GetContactsbyState()
        {
            using (PEF context = new PEF())
            {
                //   Example 7 - 5.Testing the function mapping
                ObjectResult <Contact> results = context.GetContacsbyState("Washington");

                List <Contact> ListaContactos = context.GetContacsbyState("Washington").ToList();

                foreach (var contact in ListaContactos)
                {
                    Console.WriteLine("{0} {1} ", contact.FirstName, contact.LastName);
                }
            }


            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #11
0
        public void OperatorAnyElement()
        {
            using (var context = new PEF())
            {
                String query = "SELECT c.LastName, ANYELEMENT(SELECT VALUE max(a.PostalCode) FROM c.Address AS a) AS MaxPostal FROM PEF.Contact AS c";

                ObjectQuery <DbDataRecord> contact = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contact)
                {
                    var contacto = c[0] as Contact;
                    Console.WriteLine("{0} {1}",
                                      contacto.LastName, contacto.FirstName);
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #12
0
        public void FilterNavigation()
        {
            using (var context = new PEF())
            {
                String query = "Select VALUE c FROM PEF.Contact as c WHERE EXISTS(SELECT a from c.Address as a WHERE a.CountryRegion = 'UK')";

                ObjectQuery <DbDataRecord> contact = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contact)
                {
                    var contacto = c[0] as Contact;
                    Console.WriteLine("{0} {1}",
                                      contacto.LastName, contacto.FirstName);
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #13
0
        //Using the Count aggregate function in Entity SQL
        public void CountAdresses()
        {
            using (var context = new PEF())
            {
                String query = "Select c, COUNT(Select VALUE a.AddressID FROM c.Address as a) FROM PEF.Contact as c";

                ObjectQuery <DbDataRecord> contact = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contact)
                {
                    var s        = c[1];
                    var contacto = c[0] as Contact;
                    Console.WriteLine("{0} {1} {2}",
                                      contacto.LastName, contacto.FirstName, s);
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
        public void QueryContacts()
        {
            using (var context = new PEF())
            {
                var queryString = "SELECT VALUE c " +
                                  "FROM PEF.Contact AS c " +
                                  "WHERE c.FirstName='Robert'";

                //ObjectQuery<Contact> contacts = context.CreateQuery<Contact>(queryString);

                //foreach (var contact in contacts)
                //{
                //    Console.WriteLine("{0} {1}",
                //    contact.FirstName.Trim(),
                //    contact.LastName);
                //}
            }
            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #15
0
        public void InsertAdress()
        {
            using (PEF context = new PEF())
            {
                //  Example 6 - 3.Creating a new address in memory
                var contact = context.Contact.Where(c => c.FirstName == "Robert").First();
                var address = new Address();
                address.Street1 = "TRHEE Main Street";
                address.City    = "Colon";

                address.StateProvince = "VH";
                address.AddressType   = "HOME";
                address.ModifiedDate  = DateTime.Now;
                //join the new address to the contact
                address.Contact = contact;
                context.SaveChanges();
            }
            Console.Write("Press Enter...");
            Console.ReadLine();
        }
Пример #16
0
        public void JOIN()
        {
            using (var context = new PEF())
            {
                String query = "SELECT c.Title,oa.FirstName, oa.LastName, oa.Street1, oa.City, oa.StateProvince" +
                               " FROM PEF.Contact as c " +
                               "JOIN PEF.vOfficeAddresses as oa ON c.ContactID = oa.ContactID";

                ObjectQuery <DbDataRecord> contact = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in contact)
                {
                    var contacto = c[0] as Contact;
                    Console.WriteLine("{0} {1}",
                                      c["Title"].ToString().Trim(), c["LastName"].ToString().Trim());
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #17
0
        public void QueryContacts2()
        {
            using (var context = new PEF())
            {
                //Querying with Object Services and Entity SQL


                var sqlString = "SELECT VALUE c " +
                                "FROM PEF.Contact AS c " +
                                "WHERE c.FirstName = 'Robert'";

                var objctx = (context as IObjectContextAdapter).ObjectContext;

                ObjectQuery <Contact> contact = objctx.CreateQuery <Contact>(sqlString);
                Contact newContact            = contact.First <Contact>();
                Console.WriteLine("{0} {1}",
                                  newContact.FirstName.Trim(),
                                  newContact.LastName);
                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #18
0
        public void FilterSortAnEntityReference()
        {
            using (var context = new PEF())
            {
                String query = "SELECT a,a.Contact.LastName " +
                               "FROM PEF.Address AS a" +
                               " WHERE a.Contact.AddDate > DATETIME'2009-01-1 00:00' " +
                               "ORDER BY a.Contact.LastName";

                ObjectQuery <DbDataRecord> addres = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord c in addres)
                {
                    var a = c[0] as Address;
                    Console.WriteLine("{0} {1}",
                                      a.Contact.LastName, a.Contact.FirstName);
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #19
0
        public void QueryContacts()
        {
            using (var context = new PEF())
            {
                String query = "SELECT c.FirstName,c.LastName, c.Title " +
                               "FROM PEF.Contact AS c " +
                               "WHERE c.FirstName='Robert'";

                ObjectQuery <DbDataRecord> contacts = ((IObjectContextAdapter)context).ObjectContext.CreateQuery <DbDataRecord>(query);

                foreach (DbDataRecord record in contacts)
                {
                    Console.WriteLine("{0} {1} {2}",
                                      record["Title"].ToString().Trim(),
                                      record["FirstName"].ToString().Trim(),
                                      record["LastName"].ToString().Trim());
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
            #endregion
        }
Пример #20
0
        public void LinqToEnttiesInclude()
        {
            using (var context = new PEF())
            {
                //var contacts =
                //from c in context.Contact.Include("Address")
                //where c.LastName.StartsWith("J")
                //select c;

                //Obtener los contactos que tengan cualquiera de sus domicilios en UK
                var contacts =
                    from c in context.Contact.Include("Address")
                    where c.Address.Any((a) => a.CountryRegion == "UK")
                    select c;

                foreach (var contact in contacts)
                {
                    Console.WriteLine("{0} {1} {2} ", contact.FirstName, contact.LastName, contact.Address.Count());
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #21
0
        public void Grouping()
        {
            using (var context = new PEF())
            {
                //var contacts = from c in context.Contact group c by c.Title into mygroup select mygroup;

                //var contacts =
                //from c in context.Contact
                //group c by c.Title into MyGroup
                //orderby MyGroup.Key
                //select new { MyTitle = MyGroup.Key, MyGroup };

                //Chaining Agregates
                //var contacts =
                //from c in context.Contact
                //group c by c.Title into MyGroup
                //orderby MyGroup.Key
                //select new
                //{
                //    MyTitle = MyGroup.Key,
                //    MyGroup,
                //    Max = MyGroup.Max(c => c.AddDate),
                //    Count = MyGroup.Count()
                //};

                //filtering on a group property
                //var contacts =
                //from c in context.Contact
                //group c by c.Title into MyGroup
                //where MyGroup.Count() > 150
                //select new
                //{
                //    MyTitle = MyGroup.Key,
                //    MyGroup,
                //    Count = MyGroup.Count()
                //};

                var contacts =
                    from a in context.Address
                    let c = new
                {
                    a.Contact.FirstName,
                    a.Contact.LastName,
                    a.CountryRegion
                }
                group c by c.CountryRegion into MyGroup
                where (MyGroup.Count() > 150)
                select MyGroup;

                //Example 4 - 30.Filtering related data in a query using projections
                //var contactGraphs = from c in context.Contact
                //                  select new { c, c.Address.Where(a => a.CountryRegion = "UK") };

                foreach (var contact in contacts)
                {
                    //  Console.WriteLine("{0} {1} {2} ", contact.., contact.MyGroup,contact.Count);
                }

                Console.Write("Press Enter...");
                Console.ReadLine();
            }
        }
Пример #22
0
        public void QueryContacts()
        {
            using (var context = new PEF())
            {
                //Retornar un objetos, entidades enteras donde el nombre sea Robert
                //var contacts = from c in context.Contact
                //               where c.FirstName == "Robert"
                //               select c;

                //Projection, obtener propiedades o campos especificos
                //var contacts = from c in context.Contact
                //               where c.FirstName == "Robert"
                //               select new { c.Title, c.FirstName, c.LastName };


                //foreach (var contact in contacts)
                //{

                //    Console.WriteLine("{0} {1} {2}",
                //    contact.Title.Trim(),
                //    contact.FirstName.Trim(),
                //    contact.LastName.Trim()
                //    );
                //}


                //Projection, tipos anonimos
                //            var contacts =
                //from c in context.Contact
                //where c.FirstName == "Robert"
                //let foo = new
                //{
                //    ContactName = new { c.Title, c.LastName, c.FirstName },
                //    c.Address
                //}
                //orderby foo.ContactName.LastName
                //select foo;

                //            foreach (var contact in contacts)
                //            {
                //                var name = contact.ContactName;
                //                Console.WriteLine("{0} {1} {2}: # Addresses {3}",
                //                name.Title.Trim(), name.FirstName.Trim(),
                //                name.LastName.Trim(), contact.Address.Count());
                //            }


                //Projecting into an EntityRef with LINQ to Entities
                //var addresses = from a in context.Address
                //                where a.CountryRegion == "UK"
                //                select new { a, a.Contact };


                //var addresses = from a in context.Address
                //                where a.Contact.AddDate > new System.DateTime(2009,1,1)
                //                orderby a.Contact.LastName
                //                select new
                //                {
                //                   Address = a, a.Contact
                //                };

                ////Accessing the properties of an anonymous type
                //foreach (var address in addresses)
                //{
                //    Console.WriteLine("{0} {1} {2}",
                //    address.Contact.LastName, address.Address.Street1,
                //    address.Address.City);
                //}



                //var contacts = from c in context.Contact
                //               select new { c, Foos = c.Address};

                //foreach (var contact in contacts)
                //{
                //    Console.WriteLine("{0}: Address Count {1} ",
                //    contact.c.LastName.Trim(), contact.Foos.Count);
                //    foreach (var foo in contact.Foos)
                //    {
                //        Console.WriteLine(" City= {0}", foo.City);
                //    }
                //}


                //shaped results
                //var contacts = from c in context.Contact
                //               select new
                //               {
                //                   c.FirstName,
                //                   c.LastName,
                //                   StreetsCities = from a in c.Address
                //                                   select new { a.Street1, a.City }
                //               };

                //foreach (var contact in contacts)
                //{
                //    Console.WriteLine("{0} {1} ",
                //    contact.LastName.Trim(), contact.FirstName);
                //    foreach (var foo in contact.StreetsCities)
                //    {
                //        Console.WriteLine("{0} {1}",foo.Street1 ,foo.City);
                //    }
                //}


                //flatened results, turn the query, look more simple, but the contacts data are duplicated when they have multiple adresses
                var contacts =
                    from a in context.Address
                    orderby a.Contact.LastName
                    select new { a.Contact.LastName, a.Contact.FirstName, a.Street1, a.City };


                foreach (var contact in contacts)
                {
                    Console.WriteLine("{0}{1}{2}{3}", contact.LastName, contact.FirstName, contact.Street1, contact.City);
                }
            }
            Console.Write("Press Enter...");
            Console.ReadLine();
        }