public ActionResult Index()
{
NewsEntities news = new NewsEntities();
//select * from NewsInfo
#region 排序
var query = from s in news.NewsInfo
orderby s.ID descending //ascending
select new { id = s.ID, name = s.Name, auter = s.Auter }; //构造属性
foreach (var item in query)
{
int id = item.id;
string name = item.name;
}
#endregion
#region where条件 + top
//var query1 = from s in news.NewsInfo
// where s.ID >= 1 && s.ID <= 6
// select s;
var query1 = (from s in news.NewsInfo
where s.Name.StartsWith("啊") //EndsWith : name like '%啊' ,Contains : name like '%啊%' , StartsWith : name like '啊%'
select s).Take(3); //take() 类似top
foreach (var item in query1)
{
int id = item.ID;
string name = item.Name;
}
#endregion
#region 函数
var query2 = from s in news.NewsInfo
group s by new { s.ID, s.Time } into tab
select tab;
//int? sum = query3.Sum(f => f.Key.ID);
//System.DateTime? maxid = query3.Max(f => f.Key.Time);
foreach (var item in query2)
{
var id = item.Key.ID;
var time = item.Key.Time;
//统计一组数据记录数
int count = item.Count();
//计算一组的平均ID
double? avg = item.Average(f => f.ID);
//计算ID之和
int? sum = item.Sum(f => f.ID);
//统计当前最大 最小的ID数
int? maxid = item.Max(f => f.ID);
int? minid = item.Min(f => f.ID);
}
#endregion
#region skip(跳过指定前几行) + take(再获取前几行)
var query3 = (from s in news.NewsInfo
orderby s.ID ascending
select s).Skip(2).Take(3); //take() 类似top
foreach (var item in query3)
{
int id = item.ID;
string name = item.Name;
}
#endregion
#region linq 分页操作
//当前页码
int pageindex = 1;
//每页数量
int pagesize = 2;
var list = (from s in news.NewsInfo
orderby s.ID ascending
select s).Skip((pageindex - 1) * pagesize).Take(pagesize);
#endregion
#region 链表查询(查询数据里的所有男女性别 并输出男女性别数量)
var list1 = from ss in
(
from s in news.NewsInfo
join g in news.GTable on s.ID equals g.ID
select new
{
id = s.ID,
name = s.Name,
gsex = g.Sex
}
)
group ss by ss.gsex into tab
select tab;
foreach (var item in list1)
{
var Class = item.Key;
var count = item.Count();
}
#endregion
#region 子表查询(用b表的ID查询a表的name列)
var zi = from b in news.GTable
select new
{
id = b.ID,
name = (
from a in news.NewsInfo
where b.ID == a.ID
select a.Name
).FirstOrDefault()
};
foreach (var item in zi)
{
var name = item.id;
var gname = item.name;
}
#endregion
return View();
}