LINQ

所有的linq 都是从from开始。select或者group by 结束

public class Peson

{

  public  int  age{set;get;} 

}

person p=new List(new Person(){age=10},

          new Person(){age=20} );

var selPer=from P in persons where p.age=20;

foreach(var p in selPer)

 console.writeline(p)

 

dataContext 吧查询语句翻译成sql语句,其有一个日志功能,,可以直接看被翻译过后的sql语句,方便调试

2

我要查找 商品数量>80的商品,我可以直接在加载的时候就查找符合条件的商品中

 DataLoadOptions op=new DataLoadOptions();

 op.LoadWith(p=>p.Mount)

p.AssociateWith(p=>p.Mount.wnere(od=>Mount>80)

ctx.LoadOptions=op;//ctx数据库  p是记录集体的

查询语句

  static void test4_1()
        {
            NorthwindDataContext context = new NorthwindDataContext();
            var result = from s1 in context.Customers
                         where s1.CustomerID.Contains("A")
                         select s1;
            foreach (var item in result)
            {
                Console.WriteLine(item.CustomerID + "," + item.CompanyName);
            }
        }

        static void test101()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                          select new
                          {
                              公司名 = c.CompanyName,
                              地址 = c.Address
                          };
            foreach (var item in result)
            {
                Console.WriteLine("公司名:{0,-36}地址:{1}",item.公司名,item.地址);
            }
        }

        static void test102()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from emp in ctx.Employees
                         select new
                         {
                             姓名 = emp.LastName + emp.FirstName,
                             雇用年 = emp.HireDate.Value.Year
                         };
            foreach (var item in result)
            {
                Console.WriteLine("姓名:{0,-16}地址:{1,-10}", item.姓名, item.雇用年);
            }
        }
        static void test103()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                         select new
                         {
                          ID = c.CustomerID,
                          联系信息 = new
                          {
                              职位 = c.ContactTitle,
                              联系人 = c.ContactName
                          }
                         };
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-10}联系职位:{1,-24}联系人:{2,-10}", item.ID, item.联系信息.职位,item.联系信息.联系人);
            }
        }

        static void test104()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from o in ctx.Orders
                             select new
                             {
                                 订单号 = o.OrderID,
                                 是否超重 = o.Freight > 100 ? "是" : "否"
                             };
            foreach (var item in result)
            {
                Console.WriteLine("订单号:{0,-20}是否超重:{1,-2}", item.订单号, item.是否超重);
            }
        }

        static void test201()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                         where c.Country == "France" && c.ContactTitle == "Owner"
                      select new
                      {
                          国家 = c.Country,
                          城市 = c.City,
                          联络人职位 = c.ContactTitle
                      };   
            foreach (var item in result)
            {
                Console.WriteLine("国家:{0,-10}城市:{1,-10}联络人职位:{2,-10}", item.国家, item.城市,item.联络人职位);
            }
        }


        static void test301()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from emp in ctx.Employees
                         where emp.EmployeeID < 8
                     orderby emp.HireDate.Value.Year descending, emp.FirstName ascending
                     select new
                     {
                         雇用年 = emp.HireDate.Value.Year,
                         姓名 = emp.FirstName
                     };     
            foreach (var item in result)
            {
                Console.WriteLine("雇用年:{0,-10}姓名:{1,-10}", item.雇用年,item.姓名);
            }
        }

        static void test401()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers select c).Skip(10*2).Take(10);
            Console.WriteLine("按照每页10条记录,查询第二页的顾客");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-10}公司名:{1,-10}", item.CustomerID, item.CompanyName);
            }
        }

        static void test501()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                       group c by c.Country into g
                       where g.Count() > 5
                       orderby g.Count() descending
                       select new
                       {
                           国家 = g.Key,
                           顾客数 = g.Count()
                       };
            Console.WriteLine("根据顾客的国家分组,查询顾客数大于5的国家名和顾客数");
            foreach (var item in result)
            {
                Console.WriteLine("国家:{0,-10}顾客数:{1,-10}", item.国家, item.顾客数);
            }
        }
        static void test502()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                         group c by new { c.City, c.Country } into g
                         orderby g.Key.Country, g.Key.City
                         select new
                         {
                             国家 = g.Key.Country,
                             城市 = g.Key.City
                         };
            Console.WriteLine("根据国家和城市分组,查询顾客覆盖的国家和城市");
            foreach (var item in result)
            {
                Console.WriteLine("国家:{0,-16}城市:{1,-10}", item.国家, item.城市);
            }
        }
        static void test601()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();
            Console.WriteLine("查询顾客覆盖的国家");
            foreach (var item in result)
            {
                Console.WriteLine("国家:{0,-16}", item);
            }
        }
        static void test701()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers where c.City.Contains("A") select c).Union
            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
            Console.WriteLine("查询城市是A打头和城市包含A的顾客并按照顾客名字排序");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test801()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers where c.City.Contains("A") select c).Concat
            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

            Console.WriteLine("查询城市是A打头和城市包含A的顾客并按照顾客名字排序,相同的顾客信息不会过滤");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test901()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect
            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

            Console.WriteLine("查询城市是A打头的顾客和城市包含A的顾客的交集,并按照顾客名字排序");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test1001()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = (from c in ctx.Customers where c.City.Contains("A") select c).Except
            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

            Console.WriteLine("查询城市包含A的顾客并从中删除城市以A开头的顾客,并按照顾客名字排序 ");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test1101()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                         where
                             (from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID)
                         select c;
            Console.WriteLine("查询订单数超过5的顾客信息");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test1201()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from c in ctx.Customers
                         where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City)
                         select c;
            Console.WriteLine("查询指定城市中的客户");
            foreach (var item in result)
            {
                Console.WriteLine("编号:{0,-16}公司名:{0,-16}", item.CustomerID, item.CompanyName);
            }
        }

        static void test1301()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from p in ctx.Products
                         join c in ctx.Categories
                         on p.CategoryID equals c.CategoryID
                         select p.ProductName;

            Console.WriteLine("内连接,没有分类的产品查询不到");
            foreach (var item in result)
            {
                Console.WriteLine("产品名:{0,-16}", item);
            }
        }
        static void test1302()
        {
            NorthwindDataContext ctx = new NorthwindDataContext();
            var result = from p in ctx.Products
                         join c in ctx.Categories
                         on p.CategoryID equals c.CategoryID
                         into pro
                         from x in pro.DefaultIfEmpty()
                         select p.ProductName;

            Console.WriteLine("外连接,没有分类的产品也能查询到");
            foreach (var item in result)
            {
                Console.WriteLine("产品名:{0,-16}", item);
            }
        }

 

posted @ 2013-12-30 15:59  live in finally  阅读(177)  评论(0编辑  收藏  举报