pkm的linq to Entities学习1
数据准备：
[sql]
if exists (select * from sysobjects where id = OBJECT_ID('[classinfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [classinfo]
CREATE TABLE [classinfo] (
[id] [bigint] NOT NULL,
[classID] [bigint] NOT NULL,
[className] [nvarchar] (50) NOT NULL,
[stat] [varchar] (2) NOT NULL DEFAULT (1),
[autoid] [bigint] IDENTITY (1, 1) NOT NULL)
ALTER TABLE [classinfo] WITH NOCHECK ADD CONSTRAINT [PK_classinfo] PRIMARY KEY NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [classinfo] ON
INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 1,1,N'计算机一班',N'1',1)
INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 2,2,N'计算机二班',N'1',2)
INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 4,3,N'计算机三班',N'0',3)
INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 5,4,N'计算机四班',N'1',4)
INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 6,5,N'计算机五班',N'1',6)
SET IDENTITY_INSERT [classinfo] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[stuinfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [stuinfo]
CREATE TABLE [stuinfo] (
[id] [bigint] IDENTITY (1, 1) NOT NULL,
[username] [nvarchar] (50) NULL,
[userpwd] [nvarchar] (50) NULL,
[classID] [bigint] NULL,
[score] [numeric] (10,2) NULL,
[age] [int] NULL,
[CreateTime] [datetime] NULL DEFAULT (getdate()))
alter TABLE [stuinfo] WITH NOCHECK ADD CONSTRAINT [PK_stuinfo] PRIMARY KEY NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [stuinfo] ON
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 1,N'001',N'0004',1,123.22,25,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 2,N'002',N'154',2,888.00,21,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 3,N'003',N'555',1,888.00,16,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 4,N'004',N'644',2,85.60,18,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 5,N'pkm',N'123',3,46.00,19,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 6,N'pkm001',N'123',3,45.56,19,N'2012/8/24 10:58:10')
INSERT [stuinfo] ([id],[username],[classID],[CreateTime]) VALUES ( 7,N'2012pkm1',1,N'2012/8/24 10:58:10')
SET IDENTITY_INSERT [stuinfo] OFF
建立3.5以上版本的项目，添加 ADO.NET实体数据模型，连接数据库。（文件后缀为：edmx）
[csharp]
using (var edm = new testEntities())
{
#region linq to entities 内容练习
// /*
ObjectQuery<stuinfo> stu = edm.stuinfo;
IQueryable<stuinfo> stu1 = from s in stu
select s;
//Assert.Greater(stu1.Count(), 0);
Console.WriteLine(stu.ToTraceString());//跟踪输出语句
//where条件选择
stu1 = from s in stu
where s.classID == 3 //选择班级id为3的记录
select s;
//stu1 = stu.Where(s => s.classID==3);
Console.WriteLine(stu1.Count());
foreach (var s in stu1)
{
Console.WriteLine("name:" + s.username);//输出用户姓名
}
//orderby排序
stu1 = (from s in stu
orderby s.id descending //选择班级id为3的记录
select s).Skip(3).Take(3);//跳过3条记录，再取3条记录
//stu1 = stu.OrderByDescending(s => s.id).Skip(3).Take(3);
Console.WriteLine(stu1.Count());
foreach (var s in stu1)
{
Console.WriteLine("name:" + s.username);//输出用户姓名
}
//聚合函数
var maxid = edm.stuinfo.Max(p => p.id);//查询字段中最大的值
Console.WriteLine(maxid);
// */
var query = from s in edm.stuinfo
join c in edm.classinfo
on s.classID equals c.classID //两表关联条件
where s.username == "pkm" //查询条件
select new
{
//格式：列名=列值
id = s.id,
username = s.username,
userpwd = s.userpwd,
className = c.className,
score = s.score >= 60 ? s.score : 0 //少于60分成绩为0
};
foreach (var sc in query)
{
Console.WriteLine("{0} {1} {2} {3}", sc.className, sc.username, sc.userpwd, sc.sc
补充：综合编程 , 其他综合 ,
