Entity Framework——使用SQL語句查詢

使用SQL語句查詢

使用SQL語句查詢比使用Linq更加靈活，查詢效率也可以更高。EntityFramework支援直接使用SQL語句查詢，使用DbContext.Database. SqlQuery< TElement >函數查詢。該函數的文檔介紹如下：

//     Creates a raw SQL query that will return elements of the given generic type.
//     The type can be any type that has properties that match the names of the columns
//     returned from the query, or can be a simple primitive type. The type does not
//     have to be an entity type. The results of this query are never tracked by the
//     context even if the type of object returned is an entity type. Use the System.Data.Entity.DbSet`1.SqlQuery(System.String,System.Object[])
//     method to return entities that are tracked by the context. As with any API that
//     accepts SQL it is important to parameterize any user input to protect against
//     a SQL injection attack. You can include parameter place holders in the SQL query
//     string and then supply parameter values as additional arguments. Any parameter
//     values you supply will automatically be converted to a DbParameter. context.Database.SqlQuery<Post>("SELECT
//     * FROM dbo.Posts WHERE Author = @p0", userSuppliedAuthor); Alternatively, you
//     can also construct a DbParameter and supply it to SqlQuery. This allows you to
//     use named parameters in the SQL query string. context.Database.SqlQuery<Post>("SELECT
//     * FROM dbo.Posts WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
//
// 參數:
//   sql:
//     The SQL query string.
//
//   parameters:
//     The parameters to apply to the SQL query string. If output parameters are used,
//     their values will not be available until the results have been read completely.
//     This is due to the underlying behavior of DbDataReader, see http://go.microsoft.com/fwlink/?LinkID=398589
//     for more details.
//
// 類型參數:
//   TElement:
//     The type of object returned by the query.
//
// 傳回結果:
//     A System.Data.Entity.Infrastructure.DbRawSqlQuery`1 object that will execute
//     the query when it is enumerated.
public DbRawSqlQuery<TElement> SqlQuery<TElement>(string sql, params object[] parameters);
           

從介紹中我們可以知道，如果查詢資料隻有一列時，我可以直接使用int，string，DateTime等類型作為TElement類型查詢，查詢的結果會以一個list傳回。

 如果查詢有多列，查詢結果會傳回一個TElement類型的list，每一列資料會與TElement中的一個屬性對應，對應的方式為，列名與屬性名相同。如以下查詢結果會與相應的類對應：

為了使用使用SQL語句進行查詢，可以先為查詢建立相應的類，然後查詢。這種方法比較麻煩，因為我們可能隻是想簡單的查詢幾個資料，但查詢還要我們先建立相應的類才行。為了減少類的建立，我們可以使用匿名類，但匿名類類型建立就比較麻煩，而且匿名類要使用反射機制，效率也不高。

為了更好的使用SQL語句查詢，可以先用模闆建立幾個查了類，每次查詢直接套用模闆就行，這樣即可省去建立類，查詢效率也很高。查詢類如下：

public class Query2<T1, T2>
    {
        public T1 Q1 { get; set; }

        public T2 Q2 { get; set; }

        public Query2()
        {
        }
    }

    public class Query3<T1, T2, T3>
    {
        public T1 Q1 { get; set; }

        public T2 Q2 { get; set; }

        public T3 Q3 { get; set; }

        public Query3()
        {
        }
    }

    public class Query4<T1, T2, T3, T4>
    {
        public T1 Q1 { get; set; }

        public T2 Q2 { get; set; }

        public T3 Q3 { get; set; }

        public T4 Q4 { get; set; }

        public Query4()
        {
        }
    }

    public class Query5<T1, T2, T3, T4, T5>
    {
        public T1 Q1 { get; set; }

        public T2 Q2 { get; set; }

        public T3 Q3 { get; set; }

        public T4 Q4 { get; set; }

        public T5 Q5 { get; set; }

        public Query5()
        {
        }
    }
           

每個類對應查詢需要的列數，這裡我隻建立了4個，實際項目中我們可以先多建立好幾個，用的時候直接用就行。

使用查詢類的例子如下：

class Program
    {
        class TableItem
        {
            public int ID { get; set; }
            public string S1 { get; set; }
            public int I1 { get; set; }
            public DateTime D1 { get; set; }
        }

        class MyContex : DbContext
        {
            public const string connectstr = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=EFQuery;Integrated Security=True;";

            public DbSet<TableItem> TableItems { get; set; }

            public MyContex() :
                base(connectstr)
            {   }
        }

        static void Main(string[] args)
        {
            using (var db = new MyContex())
            {

                //db.TableItems.Add(new TableItem() { S1="a", I1= 1, D1 = new DateTime(2018, 04, 01) });
                //db.TableItems.Add(new TableItem() { S1 = "a", I1 = 2, D1 = new DateTime(2018, 04, 02) });
                //db.SaveChanges();

                var sql = "select S1 As Q1, COUNT(I1) As Q2, sum(I1) As Q3 from TableItems group by S1";
                var query = db.Database.SqlQuery<Query3<string, int, int>>(sql);
                Console.WriteLine($"Query SQL: {query.ToString()}");

                foreach (var item in query.ToList())
                {
                    Console.WriteLine($"S1={item.Q1},  Count={item.Q2},  Sum={item.Q3}");
                }
            }
        }     
    }
           

資料庫中的資料：

結果：

看到查詢類，也許你會想用系統的Tuple類進行查詢，但Tuple類不能用作查詢，兩個原因：1.是Tuple缺少預設無參數構造函數；2.是Tuple類缺少Set屬性，是以不适合。