EF的基本函数

string esql = "select value it from WanceMISEntities.Intern as it";
            ObjectQuery<Intern> query = MISData.CreateQuery<Intern>(esql).Where("it.InternID=='1'") ;//可以加上where条件

            foreach (var item in query)
            {
                Console.WriteLine("实习生ID:{0}",item.InternID);
            }
            string esql1 = "select  it.InternID ,(case when it.InternID=='1' then '优秀' end) as Dis from WanceMISEntities.Intern as it";//注意要用数组集合的形式来查询不能用整体来查询
            ObjectQuery<DbDataRecord> query1 = MISData.CreateQuery<DbDataRecord>(esql1);//转化为数组类型
            foreach (DbDataRecord item in query1)
            {
                Console.WriteLine("{0},{1}",item["InternID"].ToString(),item["Dis"].ToString());
            }
            string esql2 = "select value count(it.InternID) from WanceMISEntities.Intern as it";
            ObjectQuery<int> query2 = MISData.CreateQuery<int>(esql2);//普通类型
           
            foreach (int item in query2)
            {
                Console.WriteLine(item);
            }

            string esql3 = "select value it  from WanceMISEntities.Intern as it where it.InternID=@v1";
            ObjectParameter v1 = new ObjectParameter("v1", "1");
            ObjectQuery<Intern> query3 = MISData.CreateQuery<Intern>(esql3,v1);
            Console.WriteLine(query3.CommandText);//CommandText属性得到esql字符串
            Console.WriteLine(query3.ToTraceString());//ToTraceString()打印调试信息
            foreach (Intern item in query3)
            {
                Console.WriteLine(item.InternID);
            }
            //如果有中文字段需要使用[]将字段括起来如：Intern.[学校]
            //orderby,Select(射影),和where条件类型
            ObjectQuery<Intern> intern = MISData.Intern.Top("1");//Top(count)集合的前n个元素
            foreach (Intern item in intern)
            {
                Console.WriteLine(item.InternID);
            }
            //Skip(keys,count)跳过集合的前n个元素, keys : 用于排序的字段 ,count : 要跳过的记录个数
            //分页 Skip,Top一起使用 ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5").Top("3");
            //GroupBy(keys,projection)后可加HAVING
            //Include(path) 加载关联数据,参数为实体的[导航属性]的字串,调用Include("导航属性")后,关联数据会加载,这样就不用在[实体.导航属性]上调用Load()方法
            //var r = context.DBItem.Include("DBItemList");
            //IN 在集合中 如：IN {1,2,3}"
            //EXISTS string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )";
            //Union (合集) 连接不同集合 UNION --自动过滤相同项 UNION ALL --两个集合的相同项都会返回
            //EXCEPT (左并集) 从集合中删除其与另一个集合中相同的项
            //intersect （交集）获取不同集合的相同项
            //ANYELEMENT 集合中的第一个 string esql = "ANYELEMENT(select value it from myContext.DBItemList as it where it.ItemID == 'a') ";
            //OVERLAPS 两个集合是否有相交部份 string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID=='b' ) OVERLAPS (select value it from myContext.DBItemList as it where it.ItemID == 'a' || it.ItemID=='b')";
            //Set 去掉重复项
            //Avg 平均值
            //BigCount 个数(long)
            //Count 个数(int)
            //联合使用
            //string esql = "SELECT Max(it.ItemValue) as Max , Min(it.ItemValue) as Min FROM myContext.DBItemList as it";

            //ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);
            //foreach (DbDataRecord r in query)
            //{
            //    Console.WriteLine("Max:{0},Min:{1}", r["Max"], r["Min"]);
            //}
            //Abs绝对值
            //Round 随机数
            //Concat ( string1, string2) 字符串连接
            //IndexOf( string1, string2) 字符串位置查找
            //Length ( string ) 字符串长度
            //Reverse ( string ) 字符串反转
            //ToLower( string ) 大写转小写
            //ToUpper( string ) 小写转大写
            //Trim( string ) 去两端空格
            //LTrim( string ) 去左端空格  RTrim( string ) 去右端空格
            //Left ( string, length) 左端截取
            //Right ( string, length) 右端截取
            //Substring ( string, start, length) 两端截取
            //CASE语句 CASE WHEN THEN ELSE END 例：string esql = "select it.ItemID, it.ItemValue ,(Case when it.ItemValue =1 then '差' when it.ItemValue between 2 and 4 then '好' else '其他' end) as ItemValueRemarks from myContext.DBItemList as it";
            //Guid类型产生随机数
            //REF string esql = "SELECT REF(it).ItemID FROM DBItem as it";
            //CAST 类型转换 例string esql = "select value CAST(it.ItemValue as System.String) from myContext.DBItemList as it";
            //OFTYPE OFTYPE ( expression, [ONLY] test_type )
            //使用NET的数据类型 string esql = "using System;select value CAST(it.ItemValue as String) from myContext.DBItemList as it";