asp.net linq查询环境搭建

本文是以sqlserver2008为数据库，vs2013为开发工具来介绍的。

要搭建这样一个数据库的操作环境，首先建立一个类库项目

然后在这个类库项目中添加几个类：DBDataContext数据库上下文，继承DbContext（这需要添加EntityFramework和EntityFramework.SqlServer的引用,如果开发工具是vs2013的话，可以在工具菜单下选择扩展和更新，在联机状态下搜索EntityFramework

重启vs后，工具-》库程序包管理器-》程序包管理器控制台，打开控制台，然后：

新增了一个packages.config文件

在web.config文件里面

再打开项目的引用，就会看到EF已经引用进去了。这里一直没注意是重启vs之后这个引用就已经添加到项目中了呢还是通过上面的操作才最终引用成功了。这个以后要注意一下。OK,在类库项目中也将这两个插件引进去吧。

）；DBA数据库访问类，继承DBDataContext；DBAManager数据库控制类。

DBDataContext：

public  class DBDataContext:DbContext
    {
        public DBDataContext()
           : base("defaultDB")//这个defaultDB是配置文件中的数据库连接字符串的名称
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            RegisterModelMapping(modelBuilder);
        }

        protected virtual void RegisterModelMapping(DbModelBuilder modelBuilder)
        {

        }
    }

数据库的连接字符串：由于数据库是放在本机的，所以server等于.，

<connectionStrings>
  <add name="defaultDB" connectionString="Server=.;Database=MRS;uid=sa;pwd=123456" providerName="System.Data.Sqlclient"></add>

 </connectionStrings>

DBA：

public class Dba : DBDataContext
    {
        private const string TbaleArea = "Area";
    }

DBAManager：

public class DBAManager
    {
        protected Dba DataAccessContext = new Dba();
        public DBAManager()
        {

        }
        public DBAManager(Dba context)
        {
            DataAccessContext = context;
        }
    }

剩下的就是建立实体类了，需要查询什么数据就建立一个类，类里面的每个属性值表示数据库表中的一列。比如，数据库里面有一个名为Area的表，那么就建立一个名为Area的类,里面属性的类型与数据库表中的类型一致。

public class Area
{
　　public int AreId{get;set;}
　　public int AreChildNo{set;get;}
　　public int AreParentNo{set;get;}
　　public string AreName{set;get;}
　　public string AreCreaterNo{set;get;}
　　public DateTime AreCreateTime{set;get;}
　　public int AreIsChecked{set;get;}
　　public int AreDelState { set; get; }
}

类中的属性表示数据库表中的列，这里可以不用全部写完，只需要写出需要查询的列就可以。然后在DBA类中添加一条语句：public DbSet<Area> myArea{ get; set; }，然后重写DBA的父类的RegisterModelMapping函数：

protected override void RegisterModelMapping(System.Data.Entity.DbModelBuilder modelBuilder)
{
base.RegisterModelMapping(modelBuilder);
#region 行政区划表
modelBuilder.Entity<Area>().ToTable("Area");//这里的Area表示数据库里面的表名
modelBuilder.Entity<Area>().HasKey(a => a.AreId);
modelBuilder.Entity<Area>().Property(a => a.AreId);
modelBuilder.Entity<Area>().Property(a => a.AreName).IsRequired();
modelBuilder.Entity<Area>().Property(a => a.AreChildNo).IsRequired();
modelBuilder.Entity<Area>().Property(a => a.AreParentNo).IsRequired();
modelBuilder.Entity<Area>().Property(a => a.AreCreaterNo);
modelBuilder.Entity<Area>().Property(a => a.AreCreateTime);
modelBuilder.Entity<Area>().Property(a => a.AreDelState);
modelBuilder.Entity<Area>().Property(a => a.AreIsChecked);
#endregion
}

在DBAManager类中创建用于查询数据的函数：

/// <summary>
        /// 获取所有行政区划
        /// </summary>
        /// <returns></returns>
        public virtual IList<Area> GetAreas(int AreChildNo=0)
        {
            var query = from a in DataAccessContext.myArea select a;

　　　　　　if (AreChildNo> 0)
            {
                query = query.Where(a => a.AreChildNo<= AreChildNo);
            }
            return query.ToList();
        }

然后编译这个类库，同时在网站项目中添加这个类库的引用,同时还要添加EntityFramework.SqlServer.dll和EntityFramework.dll的引用。添加好了之后，新建一个网页，在网页后台代码中查询数据库：

 public partial class WebForm1 : System.Web.UI.Page
    {
        public DBAManager dbam = new DBAManager();
        protected void Page_Load(object sender, EventArgs e)
        {
            IList<Area> Areas = dbam.GetAreas();
        }
    }

 到此，整个查询环境就搭建起来了。整个过程不需要手动的在数据库里面去创建表，也不需要你手动的去建立什么数据库，当程序运行起来后，只要执行了上面的查询操作，系统会自动根据设置在sqlserver里面创建数据库并且创建所有的表，当然如果数据库里面需要什么函数，或者存储过程什么的，能不能自动创建，这个暂时不清楚。还有种情况就是在创建了数据库后，如果修改了模型，比如在area表中想添加一列或删除一列，那么除了需要修改对应的模型外，还要完成code first迁移工作，具体的参照：https://msdn.microsoft.com/zh-cn/data/jj591621。但是这种方式就是在每次修改了模型后，都要做重复的工作，更好的方式应该是修改模型后，在运行的时候，系统自动根据模型的改变来修改数据库结构，并保留数据库中没有变动的数据。

接下来再研究各种常用的查询以及修改删除。

1、单表查询，也就是只在一个表中查询数据：

var query = from a in DataAccessContext.myArea
                        where a.AreId<10 
                        where a.AreParentNo !=0
                        select a;

var query = from a in DataAccessContext.myArea
                        where a.AreId<10 && a.AreParentNo !=0
                        select a;

上面的两段查询代码是等价的，在linq查询中，对于多条件的查询不能用and或or来连接，而应该像上面那样才可以。个人觉得，第二种方式看起来更像sql语句的语法规则。

var query = from a in DataAccessContext.myArea
                        where a.AreId<10 
                        || (a.AreId >=20 && a.AreId<30)
                        orderby a.AreId descending//这是降序
                        select a;

升序是ascending。linq查询除了将查询条件按照上面的方式设置外，还可以这样做：IList<Area> f = query.Where(a => a.AreId % 2 == 0).ToList();如果是多个查询条件的话，这种方式可以这样设置：

IList<Area> f = query.Where(a => a.AreId % 2 == 0 && a.AreId>4).ToList();
或
IList<Area> f = query.Where(a => a.AreId % 2 == 0).Where(b=>b.AreId>4).ToList();//对于这种方式的or条件该怎么设置，暂时还没学会。

模糊查询用Contains函数，eg：where a.AreName.Contains("城")。

总结：通过上面的例子，可以看到，linq的查询条件遵循的语法是C#或VB等编程语言的语法，比如a.AreId 是一个int型的数据，只要用它按照编程语言的语法规则来做运算且运算结果是bool型，那么就可以作为where条件来使用。这样一来，对于一个根本不懂sql语句的菜鸟，查询数据库就不在是问题。

2、多表查询，以最简单的两个表为例。

假如数据库中有这样两个表ActiveCourseDetail和ActiveCourse。这两个表是一个多对一的关系，关联的字段是ACID，两个表都有这个字段。首先还是要建立表的实体类，并在Dba类中映射数据库，最后在DBAManager类中写查询的函数。

var query=from ac in DataAccessContext.myActiveCourse
                      join acd in DataAccessContext.myActiveCourseDetail on ac.ACID equals acd.ACID
                      select ac;

//这个查询语句等于下面的sql语句
select ac.* from ActiveCourse ac ,ActiveCourseDetail acd where ac.ACID=acd.ACID

 如果说，查询的条件需要两个字段分别对应相等，可以如下：

var query=from ac in DataAccessContext.myActiveCourse
                      join acd in DataAccessContext.myActiveCourseDetail on ac.ACID equals acd.ACID
                      where ac.ACIntegral==acd.AcdYuYue
                      select ac;

还可以：

var query = from ac in DataAccessContext.myActiveCourse
                        join acd in DataAccessContext.myActiveCourseDetail
                        on new { a = ac.ACID, b = ac.ACIntegral } equals new { a = acd.ACID, b = acd.AcdYuYue }
                        select ac;

这里面的a和b是随便写的，没有实际的意义。上面的语句查询出来的结果中有重复的数据，为了去掉重复的数据，只保留一个，需要用到Distinct函数。即：query.Distinct().ToList()

上面的查询都是查询的一个表的所有字段，而且查询出来的结果在转换成集合时，还必须指定集合的类型，这是很不方便的。为了对查询的结果能够自由组合，获取我们想要的字段，可以按下面的方式处理：

 var query = from ac in DataAccessContext.myActiveCourse
                        join acd in DataAccessContext.myActiveCourseDetail
                        on new { a = ac.ACID, b = ac.ACIntegral } equals new { a = acd.ACID, b = acd.AcdYuYue }
                        select new {c=ac.ACName,d=ac.ACTeacherID,f=acd.AcdYuYue,g=acd.Remark
                                                     
        };
            var name = query.Distinct().ToList();

上面的语句分别从两个表中各取两个字段来组合成我想要的查询结果，然后将其转换为集合的时候，直接用var来定义接收变量，这样就不用去管具体的数据类型了。这里的变量name就是一个数组集合。下面是name的数据结构，这种数据可以通过转换为jason字符串来读取里面的数据。如果是在jquery中的话，还可以将jason还原为对象。

三个表关联查询：

var query = from ac in DataAccessContext.myActiveCourse
                        join acd in DataAccessContext.myActiveCourseDetail
                        on new { a = ac.ACID, b = ac.ACIntegral } equals new { a = acd.ACID, b = acd.AcdYuYue } 
                        join cus in DataAccessContext.myCustomer
                        on acd.CusID equals cus.CusID//这里要注意，cus.CusID必须位于equals右边
                        select new {c=ac.ACName,d=ac.ACTeacherID,f=acd.AcdYuYue,g=acd.Remark,cusname=cus.CusName};

关于group by 的用法：

 var query = from ac in DataAccessContext.myActiveCourse
                        join acd in DataAccessContext.myActiveCourseDetail
                        on new { a = ac.ACID, b = ac.ACIntegral } equals new { a = acd.ACID, b = acd.AcdYuYue } 
                        join cus in DataAccessContext.myCustomer
                        on acd.CusID equals cus.CusID//这里要注意，cus.CusID必须位于equals右边
                        group ac by new {ac.ACName,ac.ACTeacherID,ac.ACID,acd.Remark} into s
                        select new { c = s.Key.ACName, d = s.Key.ACTeacherID, f = s.Key.Remark, i = s.Key.ACID, ss = s.Sum(t => t.ACIntegral) } ;

SELECT ac.ACName,ac.ACTeacherID,ac.ACID,acd.Remark,sum(ac.ACIntegral) ss
  FROM ActiveCourse ac,activecoursedetail acd,Customer cus
where ac.ACID=acd.ACID and ac.ACIntegral=acd.AcdYuYue and acd.CusID=cus.CusID
group by ac.ACName,ac.ACTeacherID,ac.ACID,acd.Remark,ac.ACIntegral

上面的sql语句和linq语句是等效的。

下面来看看如何实现左连接查询。有时候我们需要查询如下图所示的结果：

var query = from cus in DataAccessContext.myCustomer
                        join i in DataAccessContext.myIntegral on cus.CusVipCardNo equals i.ItgVipCardNo into newt
                        from x in newt.DefaultIfEmpty()
                        orderby cus.CusVipCardNo descending//这是降序
                        select new { a = cus.CusName, b = cus.CusVipCardNo, c = x == null ?null : x.ItgCusName };

这段代码有个地方要特别注意，就是如果x.ItgCusName的在数据库中的类型不是varchar，而是float类型，那么在定义实体类的时候，这个字段的类型必须定义为double，否则上面的语句会报错。

对于一个用惯了sql语句查询的人来说，用linq来处理一些复杂的查询是比较让人头疼的。所幸，在linq中还是可以调用存储过程，这样就将复杂的sql语句封装到存储过程中，然后再在linq查询环境下调用存储过程即可。

linq调用存储过程有两种方式（这是目前我所接触到的）。一种是通过创建一个linq to sql类，后缀是.dbml。创建好了以后，在vs左边的服务器资源管理器中找到你的数据连接（这个连接是在前面搭建linq查询环境的时候就自动配置好了的，它的名称和配置文件中的连接字符串的名称一样），找到你创建的存储过程，将其拖到linq to sql类中，系统会自动的在这个类中创建一个和存储过程名称相同的函数。然后你要调用这个存储过程的时候，只需要创建一个linq to sql类的实例，然后像调用函数一样调用存储过程就可以了。这种方式是非常简单。

下面主要介绍一下另一种方式，这种方式不需要去创建一个linq to sql类。

首先，在sqlserver中创建一个存储过程ABC，它的作用就是查询area表中的所有数据，怎么创建存储过程，在这里就不啰嗦了。然后，在vs中创建一个函数用来调用存储过程：

public void GetAreas()
        {
            string sql = "Execute ABC ";
            IList<Area> a = DataAccessContext.Database.SqlQuery<Area>(sql).ToList();
        }

这里主要是用的 DataAccessContext.Database.SqlQuery来实现存储过程的调用的，这只是一个没有参数的存储过程的调用，而且返回的结果集是用的area表的实体类的集合来接收的，当然如果结果集是多个表的字段自由组合的，那也需要定义一个包含同样字段的类来接收。这也是linq不好的地方，每次查询一个结果集都必须要有相应的类型，而不像sql语句查询那样直接放到dataset中。

调用带有参数的存储过程：

string sql = "Execute SendStateInfoSearch @word,@areaCode,@depCode";
            SqlParameter txt = new SqlParameter { ParameterName = "@word", Value = word };
            SqlParameter area = new SqlParameter { ParameterName = "@areaCode", Value = areaCode };
            SqlParameter dep = new SqlParameter { ParameterName = "@depCode", Value = depCode };
List<BWSStateEntity> results = DataAccessContext.Database.SqlQuery<BWSStateEntity>(sql, txt, area, dep).ToList<BWSStateEntity>();

　　接下来将介绍linq对数据的添加，修改与删除

首先看看如何修改数据：

public void getDB()
        {
            var query = from cus in DataAccessContext.myCustomer
                        where cus.CusName.Contains("高")
                        select cus;
          
            IList<Customer> people = query.ToList();
            Customer p1 = people[0];
            p1.CusRemark = "ffsfasdfa";
            DataAccessContext.myCustomer.Attach(p1);
            DataAccessContext.Entry(p1).State = System.Data.Entity.EntityState.Modified;
            DataAccessContext.SaveChanges();

        }

代码中，首先是要查出需要修改的数据，通过表的实体对象来完成修改，然后调用DataAccessContext的相关方法来完成修改，这里只是修改了数据表中的一行数据，如果遇到需要批量修改数据的情况那就有点悲剧了，简单一点的就是先查出要修改的所有数据，然后用for循环来修改，这实际上根本就不是批量修改。查看了一下网上提供的方法，感觉都太复杂了，咱是简单的人，只做简单的事，所以只好在需要批量修改的情况下直接用存储过程得了。

添加数据：代码和上面的差不多，只不过不需要从数据库查询数据了，需要的是创建一个新的实体对象p1，然后

DataAccessContext.myCustomer.Add(p1);
            DataAccessContext.Entry(p1).State = System.Data.Entity.EntityState.Added;
            DataAccessContext.SaveChanges();

 删除：

var query = from cus in DataAccessContext.myCustomer
                        where cus.CusName.Contains("高")
                        select cus;
          
            Customer people = query.FirstOrDefault();
            DataAccessContext.myCustomer.Attach(people);
            DataAccessContext.Entry(people).State = System.Data.Entity.EntityState.Deleted;
            DataAccessContext.SaveChanges();

在linq环境下直接使用sql语句：

string sql = "select * from Customer where CusName =@name";
            IList<Customer> cus = DataAccessContext.Database.SqlQuery<Customer>(sql, new SqlParameter("@name", "曹露")).ToList();

如果sql条件中有多个参数，可以将这些参数到SqlParameter的数组中，比如：

List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@name", "曹露"));

然后在调用SqlQuery的时候，第二个参数用param.ToArray()。

上面是执行查询语句的方式，如果是执行添加，修改或删除的话，用DataAccessContext.Database.ExecuteSqlCommand(sql,param.ToArray())，它的返回值是一个整数。但是，即使在这里用sql语句来做查询，但如果查询的结果集是几个表中的一部分字段，那么查询出来的结果就是一个匿名类，如果不定义相应的类的话，这个匿名类似乎不好用。