LinQ语法学习-CSDN博客

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;
            }

}