LinQ语法学习

1:复杂查询

 

    private void ShowData()

         {   ///创建LinqDB数据库的数据上下文的实例

                   LinqDBDataContext db = new LinqDBDataContext(LinqSystem.LinqDBConnectionString);

                   ///获取所有角色

                   var result = from ur in db.UserRole

                                                ///联接UserInfo表

                                                join u in db.UserInfo on ur.UserID equals u.ID

                                                ///联接Role表

                                                join r in db.Role on ur.RoleID equals r.ID

                                                where u.ID < 10 && u.Username.Length > 5

                                                select new

                                                {   ///选择用户名称和角色

                                                         Username = u.Username,

                                                         RoleName = r.RoleName

                                                };

                   ///绑定控件数据，并显示数据

                   gvData.DataSource = result;

                   gvData.DataBind();

         }

 

 

2:排序数据

 

private void ShowData()

         {   ///创建LinqDB数据库的数据上下文的实例

                   LinqDBDataContext db = new LinqDBDataContext(LinqSystem.LinqDBConnectionString);

                   ///获取所有角色

                   var result = from u in db.UserInfo

                                               where u.ID < 10 && u.Username.Length > 5

                                                orderby u.Username descending,u.Email descending

                                                select u;

                   ///绑定控件数据，并显示数据

                   gvData.DataSource = result;

                   gvData.DataBind();

         }

 

 

3:聚合查询

 

private void ShowData()

         {   ///创建LinqDB数据库的数据上下文的实例

                   LinqDBDataContext db = new LinqDBDataContext(LinqSystem.LinqDBConnectionString);

                   ///获取所有商品

                   var result = from p in db.Product

                                                select p;

                   ///计算商品的最高价格和最低价格

                   decimal maxPrice = result.Max(p => p.Price.Value);

                   decimal minPrice = result.Min(p => p.Price.Value);

                   ///显示最高价格和最低价格

                   Response.Write("Max Price:" + maxPrice.ToString() + "<br />Min Price:" + minPrice.ToString());

         }

 

 

4:分组查询

 

        private void ShowData()

         {   ///创建LinqDB数据库的数据上下文的实例

                   LinqDBDataContext db = new LinqDBDataContext(LinqSystem.LinqDBConnectionString);

                   ///获取所有商品及其所属用户

                  var result = from p in db.Product

                                                group p by p.UserID into g

                                                orderby g.Key

                                                select new

                                                {   ///获取商品名称和用户名称

                                                         ProdcutName = g.First().Name,

                                                         Username = (from u in db.UserInfo

                                                                                     where u.ID == g.Key

                                                                                     select u).First().Username

                                               };

                   ///绑定控件数据，并显示数据

                   gvData.DataSource = result;

                   gvData.DataBind();

         }

 

 

5.只返回城市=London的

 

var queryLondonCustomers = from cust in customers
where cust.City == "London"
select cust;

 

6.继where扩展

 

where cust.City=="London" && cust.Name == "Devon";

 

where cust.City == "London" || cust.City == "Paris";

 

7.orderby字句按照被排列的类型的默认比较器进行排序,下面是字符默认的从a-z排序  (asc/desc)

 

var queryLondonCustomers3 =
from cust in customers
where cust.City == "London"
orderby cust.Name ascending
select cust;

 

8.若要只选择源元素的一个成员，请使用点运算。在下面的示例中，假定 Customer 对象包含几个公共属性，其中包括名为 City
的字符串。在执行此查询时，此查询将生成字符串输出序列。

 

var query = from cust in Customers
select cust.City;

 

9.若要创建包含源元素的多个属性的元素，可以使用具有命名对象或匿名类型的对象初始值设定项。下面的示例演示如何使用匿名
类型来封装各个 Customer 元素的两个属性：

 

var query = from cust in Customer
select new {Name = cust.Name, City = cust.City};

 

10.下面的示例使用 where 子句（在 C# 中）或 Where 子句（在 Visual Basic 中）来从数组中筛选那些具有特
定长度的字符串

 

string[] words = { "the", "quick", "brown", "fox", "jumps" };
IEnumerable<string> query = from word in words
where word.Length == 3
select word;

foreach (string str in query)
Console.WriteLine(str);//这种方式挺好,可以满足不同需求操作

 

11.下面的示例使用 C# 中的 select 子句或 Visual Basic 中的 Select 子句来映射字符串列表中每个字符串的第一个字母。

 

List<string> words = new List<string>() { "an", "apple", "a", "day" };
var query = from word in words
select word.Substring(0, 1);

foreach (string s in query)
Console.WriteLine(s);

 

12.下面的示例使用多个 from 子句（在 C# 中）或 From 子句（在 Visual Basic 中）来映射字符串列表中每个字符串中的每个单词。

 

List<string> phrases = new List<string>() { "an apple a day", "the quick brown fox" };
var query = from phrase in phrases
from word in phrase.Split(' ')
select word;
foreach (string s in query)
Console.WriteLine(s);

 

 13.LinQ使用sql存储过程(输出参)

 

 

        //使用输出参数
        public static bool addOutparas(string isbn)
        {
            BookDALDataContext dba = cfgHelper.a;
            bool? output = false;
            var result = dba.up_Book_GetOutparams(isbn, ref output);
            return Convert.ToBoolean(output);
        }

14.LinQ实战积累

 

 

 public class BookBLL
    {
        public static bool Add(book b)
        {
            BookDALDataContext dba = cfgHelper.a;
            try
            {         
            dba.book.InsertOnSubmit(b);
            dba.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
           
        }

        public static bool Modify(book b)
        {
            BookDALDataContext dba = cfgHelper.a;
            try
            {
                var obj = dba.book.Single(o => o.ISBN == b.ISBN);

                obj.BanCI = b.BanCI;
                obj.BookName = b.BookName;
                obj.Number = b.Number;
                obj.Press = b.Press;
                obj.PressDate = b.PressDate;
                obj.price = b.price;
                obj.Writer = b.Writer;
                dba.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool Delete(string isbn)
        {
            BookDALDataContext dba = cfgHelper.a;
            try
            {
                var bb = dba.book.Single(o => o.ISBN == isbn);
                dba.book.DeleteOnSubmit(bb);
                dba.SubmitChanges();             
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static book GetBook(string isbn)
        {
            BookDALDataContext dba = cfgHelper.a;
            return dba.book.Where(o => o.ISBN == isbn).Single();
           
        }

       
       

        public static List<book> GetList()
        {
            BookDALDataContext dba = cfgHelper.a;
            return dba.book.ToList();   //428                      
        }

        /*=======Start LinQ=================*/
        //获取所有集合
        public static List<book> GetAllList()
        {
            BookDALDataContext dba = cfgHelper.a;
            return dba.book.ToList();
        }

        //匹配找出版社已"民"开头的
        public static List<book> GetStartWithParams (string t)
        {
            StreamWriter sw = new StreamWriter("log.txt",true);
                       
            BookDALDataContext dba = cfgHelper.a;
                  

        //return dba.book.Where(c => c.Press.StartsWith(t)).First();//就相当于top(1)
            var a = from c in dba.book
                    where c.Press == t
                    select c;

            dba.Log = sw;    

            return a.ToList();

         
           
 
        }

        ////匹配找出版社已民开头的----疑问！！！
        //public static IQueryable GetStartWithParamss(string t)
        //{
        //    BookDALDataContext dba = cfgHelper.a;
        //   //return   dba.book.First(o=>o.ISBN==t);//就相当于top(1)
        //    var b = from u in dba.book
        //            where t == u.ISBN
        //            select new { u.ISBN,u.Number,u.Press,u.price};
        //    return b;
            
        //}

        //寻找出在图书类别表在图书信息表中的Info 这方法还在研究中。。。 研究出了结果这种方式很好
        public static List<book> GetClassByISBN(string isbn)
        {
            BookDALDataContext dba = cfgHelper.a;
            return (from c in dba.book
                    where c.ISBN == isbn
                    select c).ToList();     
        }
        //是上面方法的复杂版,连续三个表用同样的方法！
        public static IQueryable GetDescribeByInfoId(int infoId)
        {
            BookDALDataContext dba = cfgHelper.a;
            var dbo = from c in dba.book
                      from p in c.T_BookInfo                     
                      from q in p.T_BookInfoDad                     
                      where p.BookInfoID == infoId
                      select new { c.ISBN, p.BookInfoID, q.bookInfoID, q.bookDecribe };
            return dbo;                    
        }

        //还可以这样用
        public static string getForeachString()
        {
            string str="";
            BookDALDataContext dba=cfgHelper.a;

            foreach (var p in dba.book)
            {
                foreach (var q in p.T_BookInfo)
                {
                    str+= p.ISBN + "--" + q.BookClass+",";
                }                   

            }
            return str;
            }

        //使用输出参数
        public static bool addOutparas(string isbn)
        {
            BookDALDataContext dba = cfgHelper.a;
            bool? output = false;
            var result = dba.up_Book_GetOutparams(isbn, ref output);
            return Convert.ToBoolean(output);
        }
               

    }