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