在 ADO.NET EF 中使用原始 SQL 自定义查询

Tamas Lorincz

4.33/5 (5投票s)

2014年6月15日

CPOL

4分钟阅读

38626

368

下载 CustomQueriesEntityFramework.zip - 3.4 MB

引言

在这篇文章中，我将解释如何在 ADO.NET 实体框架中使用 原始 SQL 构建和使用自定义对象查询。我不会解释如何创建 ADO.NET 实体框架模型，或将其连接到数据库，网上有很多关于如何做到这一点的文章。因此，这篇文章适合对 ADO.NET 有基本了解的开发人员。

背景

当您处理大量数据库数据时，普通的 LINQ 查询似乎不是最佳选择，因为查询可能不够快。如果一个表中大约有 100,000 条记录，而您不仅想列出，还想根据某个条件在该表中进行搜索，这可能需要很多秒（时间取决于复杂性）。或者您只想做一个特殊的查询，该查询取决于用户交互，有时编写一个 SQL 查询并将其交给实体上下文会更简单——并且可以根据用户交互进行更改。

第一次在 EF 中创建自定义查询时，花费了相当多的时间和研究才找到有用的信息，而那些文章没有详细介绍如何执行这类操作——只是一些基本的东西——而且没有一篇深入解释如何使用这种解决方案。（也许有很好的、详细的文章，只是我找不到。）所以，我想和您分享我学到的关于这方面的内容。我希望您能从中受益。

使用代码

我将使用“数据库优先”配置。

基本解释可以在这里找到

http://msdn.microsoft.com/en-us/data/jj592907.aspx

但我想深入探讨这些内容。

我们将有一个名为*Person*的表。这个人将有来自另一个表的地址。

注意：是的，我知道我犯了一个拼写错误。不是 Nationalty 而是 Nationality... 我的错 :(

数据库中的数据

人员

地址

让我们从这个数据库创建一个 EF 模型。（所有内容都可以在源文件中找到。）

EF 创建的模型类在项目中名为*DbEntities*。

EF 模型向导创建的类

public partial class Address
    {
        public int ID { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string ZIP { get; set; }
        public Nullable<int> PersonID { get; set; }
    
        public virtual Person Person { get; set; }
    }

public partial class Person
    {
        public Person()
        {
            this.Addresses = new HashSet<Address>();
        }
    
        public int ID { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public Nullable<System.DateTime> BirthDate { get; set; }
        public string Email { get; set; }
        public Nullable<int> IDNumber { get; set; }
        public string Nationalty { get; set; }
    
        public virtual ICollection<Address> Addresses { get; set; }
    }

我为测试创建的类名为**EFQueryTest**。您可以在该单个类中找到所有演示查询如何完成的方法。

 class EFQueryTest
    {
        DbEntities entities = new DbEntities();

        ...
        ...
    }

第一次测试

正常查询。

这是最常用的。只需调用实体框架方法和数据集。

 public void NormalQuery()
        {
            Console.WriteLine("List ALL Persons in the database:");
            Console.WriteLine("");

            // call the persons dataset from the Entity model.
            foreach (var person in entities.Persons)
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
                Address personsAddress = person.Addresses.FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

第二次测试

带 LINQ 搜索的正常查询。

 public void NormalQueryWithLinqSearch()
        {
            Console.WriteLine("List ALL Persons in the database who is British:");
            Console.WriteLine("");

            // call the persons dataset from the Entity model, who's nationality is British

            foreach (var person in entities.Persons.Where(x => x.Nationalty == "British"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
                Address personsAddress = person.Addresses.FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

这两次是热身。我猜所有使用过 EF 的人都知道上面显示的这类方法。

使用原始 SQL 创建查询。

根据我上面粘贴的文章，可以这样做

using (var context = new BloggingContext()) 
{ 
    var blogNames = context.Database.SqlQuery<string>( 
                       "SELECT Name FROM dbo.Blogs").ToList(); 
}

如您所见，我们可以设置我们想从 EF 返回的对象类型。
Database.SqlQuery<TElement> --> TElement 代表我们想要返回的对象。

所以，让我们在 EF 模型上使用这个。

我们希望从模型中返回 Person 对象，所以它会像这样

Database.SqlQuery<Person>("SQL QUERY").ToList();

让我们做一个测试！

这里是这段代码片段

public void RawSQLQueryFailing()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query):");
            Console.WriteLine("");
            // call the persons from the Entity model with raw sql query

            foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
                Address personsAddress = person.Addresses.FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

此方法会失败，这非常重要！为什么？

因为我们没有从数据库获取地址！

正如您在 SQL 查询 "SELECT * FROM dbo.Persons" 中看到的，我们只从数据库中选择了 Persons，如果您运行此方法，第一个 Person 的数据将被写入控制台，但当方法到达需要写入 Address 的部分时，它会失败，因为它没有从数据库获取 Address 对象。

我们应该获取 Persons 的地址。可惜这次我们不能使用 JOIN。尽管查询会从数据库获取数据，但不会将其分配给 Person 类中的 Address 对象。

我们可以这样做

public void RawSQLQuery()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query):");
            Console.WriteLine("");
            // call the persons from the Entity model with raw sql

            foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
// here we make a new query and assign the address object
                Address personsAddress = entities.Database.SqlQuery<Address>("SELECT * FROM dbo.Addresses AS a WHERE a.PersonID = " + person.ID).FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

这远非最佳解决方案！我们执行了两个查询，向数据库发了两次不必要的请求。

最好的方法是创建一个新对象，我们可以用它来进行新查询，并将我们想要的任何值从数据库中分配。

    public class CustomQueryObject
    {
        public string PersonFirstname { get; set; }
        public string PersonLastname { get; set; }
        public DateTime PersonBirthdate { get; set; }
        public string PersonNationality { get; set; }
        public string AddressStreet { get; set; }
        public string AddressCity { get; set; }
        public string AddressZIP { get; set; }
    }

SQL 查询将比之前的更复杂

SELECT 
p.Firstname as PersonFirstname,
p.Lastname as PersonLastname,
p.BirthDate as PersonBirthDate,
p.Nationalty as PersonNationality,
a.City as AddressCity,
a.ZIP as AddressZIP,
a.Street as AddressStreet
FROM dbo.Persons as p
LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID

如您所见，与对象属性的关系是 SELECT 部分的指定名称。

让我们看看代码的样子

 public void RawSQLQueryWithCustomObject()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query with custom object):");
            Console.WriteLine("");
            // call the persons dataset from the Entity model with raw sql and custom object

            foreach (var item in entities.Database.SqlQuery<CustomQueryObject>(
                               "SELECT " +
                               " p.Firstname as PersonFirstname," +
                               " p.Lastname as PersonLastname," + 
                               " p.BirthDate as PersonBirthDate," + 
                               " p.Nationalty as PersonNationality," +
                               " a.City as AddressCity," +
                               " a.ZIP as AddressZIP," +
                               " a.Street as AddressStreet" +
                               " FROM dbo.Persons as p" +
                               " LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    item.PersonFirstname,
                                                    item.PersonLastname,
                                                    item.PersonBirthdate.ToShortDateString(),
                                                    item.PersonNationality
                    ));

                Console.WriteLine("Address: {0}, {1} {2} ", item.AddressStreet, item.AddressCity, item.AddressZIP);
            }

            Console.ReadKey();
        }

通过这种解决方案，我们可以使用所有 SQL 函数。

这是另一个小型示例，展示了一个带有 WHERE 子句的简单 SQL 查询。这将获取国籍为英国的所有人员。

public void RawSQLQueryWithCustomObjectWithWhere()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query with custom object with WHERE statement):");
            Console.WriteLine("");
            // call the persons dataset from the Entity model with raw sql and custom object

            foreach (var item in entities.Database.SqlQuery<CustomQueryObject>(
                                      "SELECT " +
                                      " p.Firstname as PersonFirstname," +
                                      " p.Lastname as PersonLastname," +
                                      " p.BirthDate as PersonBirthDate," +
                                      " p.Nationalty as PersonNationality," +
                                      " a.City as AddressCity," +
                                      " a.ZIP as AddressZIP," +
                                      " a.Street as AddressStreet" +
                                      " FROM dbo.Persons as p" +
                                      " LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID"+
                                      " WHERE p.Nationalty = 'British' "))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    item.PersonFirstname,
                                                    item.PersonLastname,
                                                    item.PersonBirthdate.ToShortDateString(),
                                                    item.PersonNationality
                    ));

                Console.WriteLine("Address: {0}, {1} {2} ", item.AddressStreet, item.AddressCity, item.AddressZIP);
            }

            Console.ReadKey();
        }

摘要

通过这种解决方案，您将能够使用所有 SQL 函数，这些函数有时比使用 EntityFramework 函数更流畅、更快。您可以进行正常的 DateTime 比较，而 EF 无法做到。您可以使用 SUM、CONCAT、CONVERT 等 SQL 函数。

如果您觉得这篇文章有帮助，请给文章投票，留言，并随时回复这篇文章。

历史

2014 年 6 月 15 日：初次发布。