pkm的linq to Entities学习1

来源:互联网 发布:数据挖掘原理与应用 编辑:程序博客网 时间:2024/05/20 14:19

数据准备:

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] ONINSERT [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] OFFif 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] ONINSERT [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)


 

            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.score);                }                var stu11 = from s in edm.stuinfo                            where s.CreateTime >= new DateTime(2012, 8, 24, 10, 58, 10)// && s.username == "pkm"//根据时间和姓名查询                            select s;                Console.WriteLine(stu11.Count());//数量,参数可以加条件  s => s.username=="pkm"                foreach (var s in stu11)                {                    Console.WriteLine("name:" + s.username);//输出用户姓名                }                //stuinfo stu01 = stu1.First();//返回集合中第一个(强类型对象)                var cls = (from c in edm.classinfo                           select c.className).Distinct();//distinct                foreach (var c in cls)                {                    Console.WriteLine("classname:" + c);//输出用户姓名                }                var count = edm.stuinfo.Select(s => s.score).Sum();               // count = edm.stuinfo.Sum(s => s.score); //用法2(Min()、Max() 也是一样的用法)                Console.WriteLine("分数之和为:{0}",count);                #endregion            }