Entity FrameWork 笔记（二）4.0中的查询

2010-04-17 16:28 Henry Cui 阅读(5132) 评论(12) 编辑收藏举报

这两天一直在用vs2010来试水，发现Entity FrameWork4.0改进了很多，这篇文章先从查询说起。

工具VS2010+SQL2005

准备

首先我们看下生成完成的EDM图：

编写查询

1）Linq to Entities

在开始我还是想像以前一样用Linq to Entities的方式写一个简单的查询：

using (var ctx = new HJRightPlatEntities())
{
    var projects = from pro in ctx.Projects
                   where pro.ProjectID.Equals("Henllyee")
                   select pro;
    Assert.AreEqual(1, projects.Count());
}

开始编写时，总是获取不到语法的提示，后来才发现是自己没有添加对System.Linq的引用，从而没有Where的扩展方法，真是汗啊。这这里主要是来查询ProjectID为“Henllyee”的Project

下面我们写一个组联的查询：

[TestMethod()]
public void QueryInnerJoinByLinq()
{
    using (var ctx = new HJRightPlatEntities())
    {
        var projects = from pro in ctx.Projects
                       join mo in ctx.Modules
                       on pro.ProjectID equals mo.ProjectID  into moduleList
                       where pro.ProjectID.Equals("Henllyee")
                       select new {
                           ProjectName=pro.ProjectName,
                           ModuleList = moduleList
                       };
        Assert.AreEqual(7, projects.First().ModuleList.Count());
 
    }
}

下面是段左联的查询：

[TestMethod()]
public void QueryLeftJoinByLinq()
{
    using (var ctx = new HJRightPlatEntities())
    {
        var modules = from pro in ctx.Projects
                       join mo in ctx.Modules
                       on pro.ProjectID equals mo.ProjectID into moduleList
                       where pro.ProjectID.Equals("Henllyee")
                       from prom in moduleList.DefaultIfEmpty()
                       select new
                       {
                           ProjectName = pro.ProjectName,
                           ModuleName = prom.ModuleName
                       };
        Assert.AreEqual(7, modules.Count());
 
    }
}

这些都是些基本的Linq 语法，就不再举例子了

2)E-SQL的方式

第一个实例：

[TestMethod()]
public void QueryByESQL()
{
    using (var ctx = new HJRightPlatEntities())
    { 
        var projects = ctx.CreateQuery<Projects>(
            "Select Value p From Projects AS p Where p.ProjectID=@ProjectID",
            new ObjectParameter("ProjectID","Henllyee"));
 
        foreach (var pro in projects)
        {
            Console.WriteLine("ProjectName:{0}",
                pro.ProjectName);
        }
    }
}

这个上面的例子中就跟linq to entities的第一个实例差不多

我们看如何实现左连的实现：

[TestMethod()]
public void QueryLeftJoinByESQL()
{
    using (var ctx = new HJRightPlatEntities())
    {
        var modules = ctx.CreateQuery<Modules>(
            "Select Value m From Modules AS m Where m.Projects.ProjectID=@ProjectID",
            new ObjectParameter("ProjectID","Henllyee")
            );
        foreach (var mo in modules)
        {
            Console.WriteLine("ModuleName:{0} ProjectName:{1}",
                mo.ModuleName,
                mo.Projects.ProjectName);
 
        }
    }
}