Entity Framework - Entity SQL

Entity SQL 是與儲存體無關的查詢語言，與 SQL 類似。 Entity SQL 可讓您查詢實體資料 (無論以物件形式或在表格式資料表中)。

支援語法可以參考: Entity SQL Reference
常用語法範例:

select
T-SQL select * from course
Entity SQL select value c from courses as c

select where
T-SQL select title from course where course.departmentId=4
Entity SQL select c.title from courses as c where c.departmentId=4

select where in
T-SQL select * 
from course 
where departmentid in (
    select departmentid
    from department
    where name in ('Economics','English')
)
Entity SQL select value c
from courses as c
where c.departmentId in {
    select value d.departmentId
    from departments as d
    where d.name in
        {'Economics','English'}
}
// 取得符合條件之departmentIdList
ObjectQuery<int> DepartmentIdQuery
    = schoolContext.CreateQuery<int>(@"
select value d.departmentId
from departments as d
where d.name in {'Economics','English'}
");
// 將departmentIdList轉成{id,id,id,id}格式
// ,以靜態方式串入Entity SQL
ObjectQuery<Course> PersonQuery =
    schoolContext.CreateQuery<Course>(
            string.Format(@"
select value c
from courses as c
where c.departmentId in {{{0}}}
",string.Join(","
    ,DepartmentIdQuery.ToArray())));

outer join
T-SQL select d.name,c.title
from course c,department d
where c.departmentid=d.departmentid
Entity SQL select d.name,c.title
from courses as c
CROSS JOIN departments as d
where c.departmentId=d.departmentId

left join
T-SQL select s.studentid,
s.grade,
       p.lastname+' '+p.firstname as fullname
from studentgrade s
left join person p
on p.personid=s.studentid
Entity SQL select s.studentid,
s.grade,
       p.lastname+' '+p.firstname fullname
from studentgrades as s
left outer join people as p
on p.personid=s.studentid


count/group by/having
T-SQL select studentid,count(*) as num
from studentgrade 
group by grade,studentid
Entity SQL select s.studentid,count(s.grade) as num
from studentgrades as s
group by s.grade,s.studentid
having count(s.grade) > 0
-- 預設連=0都會顯示,所以需要用having>0方式替除
Posted by

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

橫式廣告