1.8.1. fejezet, inner és outer Join

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
 
namespace JoinProject
{
 
    public class Address
    {
        [XmlElement("Country")]
        public string Country { get; set; }
        [XmlElement("PostalCode")]
        public int PostalCode { get; set; }
        [XmlElement("State")]
        public int State { get; set; }
        [XmlElement("City")]
        public string City { get; set; }
        [XmlElement("Street")]
        public string Street { get; set; }
    }
 
    public class Customer
    {
        [XmlElement("ID")]
        public int ID { get; set; }
        [XmlElement("FirstName")]
        public string FirstName { get; set; }
        [XmlElement("LastName")]
        public string LastName { get; set; }
        [XmlElement("Email")]
        public string Email { get; set; }
        [XmlElement("PhoneNumber")]
        public string PhoneNumber { get; set; }
        [XmlElement("Address")]
        public Address Address { get; set; }
    }
 
    public class Order
    {
        [XmlElement("CustomerID")]
        public int CustomerID { get; set; }
        [XmlElement("ProductID")]
        public int ProductID { get; set; }
        [XmlElement("OrderDate")]
        public DateTime OrderDate { get; set; }
        [XmlElement("DeliverDate")]
        public DateTime? DeliverDate { get; set; }
        [XmlElement("Note")]
        public string Note { get; set; }
    }
 
    public class Product
    {
        [XmlElement("ID")]
        public int ID { get; set; }
        [XmlElement("ProductName")]
        public string ProductName { get; set; }
        [XmlElement("Quantity")]
        public int Quantity { get; set; }
    }
 
    class Program
    {
        private static List<Order> GetOrderList()
        {
            FileStream fstream = new FileStream("c:\\temp\\orders.xml", FileMode.Open);
            XmlSerializer ser = new XmlSerializer(typeof(List<Order>));
            List<Order> result = (List<Order>)ser.Deserialize(fstream);
            fstream.Close();
            return result;
        }
 
        private static List<Customer> GetCustomerList()
        {
            FileStream fstream = new FileStream("c:\\temp\\customers.xml", FileMode.Open);
            XmlSerializer ser = new XmlSerializer(typeof(List<Customer>));
            List<Customer> result = (List<Customer>)ser.Deserialize(fstream);
            fstream.Close();
            return result;
        }
 
        private static List<Product> GetProductList()
        {
            FileStream fstream = new FileStream("c:\\temp\\products.xml", FileMode.Open);
            XmlSerializer ser = new XmlSerializer(typeof(List<Product>));
            List<Product> result = (List<Product>)ser.Deserialize(fstream);
            fstream.Close();
            return result;
        }
 
        private static void generateCustomerSample()
        {
            List<Customer> result = new List<Customer>();            
            Customer cust1 = new Customer();
            cust1.ID = 1;
            cust1.FirstName = "Róbert";
            cust1.LastName = "Koltai";
            Address addr = new Address();
            addr.City = "Budapest";
            addr.Country = "Hungary";
            addr.State = 36;
            cust1.Address = addr;
            result.Add(cust1);
 
            Customer cust2 = new Customer();
            cust2.ID = 2;
            cust2.FirstName = "Róbert";
            cust2.LastName = "Rátonyi";            
            Address addr2 = new Address();
            addr2.City = "Budapest";
            addr2.Country = "Hungary";
            addr2.State = 36;
            cust2.Address = addr;
            result.Add(cust2);
 
            FileStream fstream = new FileStream("c:\\temp\\customers.xml", FileMode.Create);
            XmlSerializer ser = new XmlSerializer(typeof(List<Customer>));
            ser.Serialize(fstream, result);
            fstream.Close();
        }
 
        private static void generateProductSample()
        {
            List<Product> result = new List<Product>();
            Product prod = new Product();
            prod.ID = 1;
            prod.ProductName = "Árvíztűrő tükörfúró gép";
            prod.Quantity = 1;
            result.Add(prod);
            FileStream fstream = new FileStream("c:\\temp\\products.xml", FileMode.Create);
            XmlSerializer ser = new XmlSerializer(typeof(List<Product>));
            ser.Serialize(fstream, result);
            fstream.Close();
        }
 
        private static void generateOrderSample()
        {
            List<Order> result = new List<Order>();
            Order ord = new Order();
            ord.CustomerID = 1;
            ord.ProductID = 1;
            ord.OrderDate = DateTime.Now;
            result.Add(ord);
            FileStream fstream = new FileStream("c:\\temp\\orders.xml", FileMode.Create);
            XmlSerializer ser = new XmlSerializer(typeof(List<Order>));
            ser.Serialize(fstream, result);
            fstream.Close();
        }
 
        static void Main(string[] args)
        {
            generateCustomerSample();
            generateProductSample();
            generateOrderSample();
            var result = from customer in GetCustomerList() group customer by new { customer.Address.Country, customer.Address.State } into customergroup select customergroup;
            foreach (var group in result)
            {
                Console.WriteLine("{0}, {1}", group.Key.Country, group.Key.State);
                foreach (var customer in group) { Console.WriteLine("-- {0}", customer.FirstName + " " + customer.LastName); }
            }
            Console.ReadKey();
 
            var result2 = from order in GetOrderList()
                          join customer in GetCustomerList() on order.CustomerID equals customer.ID
                          select new
                          {
                              Name = customer.FirstName + " " + customer.LastName,
                              Products = GetProductList().Where(product => product.ID == order.ProductID)
                          };
 
            foreach (var orders in result2)
            {
                Console.WriteLine(orders.Name);
                foreach (var product in orders.Products) { Console.WriteLine("-- {0}", product.ProductName); }
            }
            Console.ReadKey();
 
            var customers = from customer in GetCustomerList()
                         join order in GetOrderList() on customer.ID equals order.CustomerID into tmpresult
                            from o in tmpresult.DefaultIfEmpty() select 
                            new {
                                Name = customer.FirstName + " " + customer.LastName,
                                Product = o == null ? "nincs rendelés" : o.ProductID.ToString()
                            };
            foreach (var order in customers)
            {
                Console.WriteLine("{0}: {1}", order.Name, order.Product);
            }
            Console.ReadKey();
        }
    }
}