protected void Page_Load(object sender, EventArgs e)
{
string oql = @"
select
a.OrgId, a.OrgCode, --测试：Property，使用Entity别名。期望结果：别名保留，Property替换为Column
OrgName, --测试：Property字。期望结果：Property替换为Column
--测试：CASE语句。期望结果：正确解析，并将里面的Property替换为Column
case a.OrgType when 1 then '组织' when 2 then '合作企业' else '不明物' end as Type,
u.User_Name as CreateBy, --测试：Column，使用Table别名，Column定义了别名。期望结果：全部保留，不做转换
a.CreateDate, a.ModifyBy, a.ModifyDate --测试：Property
from Org a --测试：Entity，定义了别名。期望结果：Entity替换为Table。
inner join SYS_User u --测试：Table，定义了别名。期望结果：全部保留
--测试：Column与Property关联。期望结果：Column保留，Property替换为Column。
--注意：SELECT列表中有一个别名叫做CreateBy，
--所以：a.CreateBy表示Entity Org的属性，需要替换为自段名。如果使用u.User_ID=CreateBy，则CreateBy为别名，这种情况不应当替换
on u.User_ID=a.CreateBy
where
a.OrgType=:ot --测试：命名参数
and u.user_id=? --测试：位置参数
";
using (ISession session = new Magic.Framework.ORM.Session())
{
ObjectQuery query = session.CreateObjectQuery(oql);
query.Attach(typeof(Org), "Org")
.SetValue(":ot", OrgType.Own, "OrgType")
.SetValue(0, 189, "CreateBy")
.SetFirstOffset(4)
.SetLastOffset(7);
DataSet ds = query.DataSet(); //1
/*执行的语句
WITH q AS(
select a.Org_ID,a.Org_Code,Org_Name
,case a.Org_Type when 1 then '组织' when 2 then '合作企业' else '不明物' end as Type
,u.User_Name as CreateBy,a.Create_Date,a.Modify_By,a.Modify_Date
,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
from SYS_Org a
inner join SYS_User u on u.User_ID=a.Create_By
where a.Org_Type=@_p0 and u.user_id=@_p1
)
SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
IList<Org> orgs = query.List<Org>(); //2
/*执行的语句
WITH q AS(
select a.Org_ID,a.Parent_ID,a.Org_Code,a.Org_Name,a.Org_Type,a.Is_Virtual
,a.Is_Root,a.Org_Seq,a.Del_Flag,a.Description,a.Manager,a.Create_Date
,a.Create_By,a.Modify_Date,a.Modify_By
,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
from SYS_Org a inner join SYS_User u on u.User_ID=a.Create_By
where a.Org_Type=@_p0 and u.user_id=@_p1
)
SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
IList<User> users = query.List<User>(); //3
/*执行的语句
WITH q AS(
select u.User_ID,u.User_Name,u.Password,u.Full_Name,u.User_Type
,u.Email,u.Gender,u.Employee_No,u.Ext,u.Mobile,u.Home_Phone
,u.Home_Address,u.Birthday,u.Note,u.Status,u.Org_ID,u.Last_Logon_Time
,u.Create_Time,u.Create_By,u.Modify_By,u.Modify_Time
,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
from SYS_Org a inner join SYS_User u on u.User_ID=a.Create_By
where a.Org_Type=@_p0 and u.user_id=@_p1
)
SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
this.rpDs.DataSource = ds;
this.rpDs.DataBind();
this.rpOrg.DataSource = orgs;
this.rpOrg.DataBind();
this.rpUser.DataSource = users;
this.rpUser.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (ISession session = new Magic.Framework.ORM.Session())
{
//测试1：对ON条件，能够根据别名（t）找到子查询，尝试从子查询中判断CreateBy是否是一个属性名字。
//测试结果应当为true，这个属性需要替换为自段名
string oql = @"
select * from User a inner join
(select distinct CreateBy from Org) t
on t.CreateBy=a.UserId
";
ObjectQuery query = session.CreateObjectQuery(oql);
IList <User> users = query.Attach(typeof(Org), "Org").Attach(typeof(User), "User").List <User>();
/*预期执行的SQL
* select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
* ,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
* ,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
* from SYS_User a
* inner join (select distinct Create_By from SYS_Org) t on t.Create_By=a.User_ID
*/
//测试2：这次子查询里面将CreateBy取了一个别名，所以ON条件中的CreateBy不再需要替换
oql = @"
select * from User a inner join
(select distinct CreateBy as CreateBy from Org) t
on t.CreateBy=a.UserId
";
users = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org").Attach(typeof(User), "User")
.List <User>();
/*预期执行的SQL
* select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
* ,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
* ,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
* from SYS_User a
* inner join (select distinct Create_By as CreateBy from SYS_Org) t on t.CreateBy=a.User_ID
*/
//测试3：子查询里面情况稍微复杂一点，这将影响列名决策的执行流。
//因为测试2中，子查询只有一个table，列名决策遇到这种情况时，如果列名在子查询里面没有定义别名，它就可以肯定是来自这个表的自段或实体的属性
oql = @"
select OrgCode as OrgCode from Org a, User u
where len(a.OrgCode)=? and a.CreateBy=u.UserId and u.UserId=189
";
DataSet ds = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org").Attach(typeof(User), "User")
.SetValue(0, 2, "OrgId")
.DataSet();
/*预期执行的SQL
* select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
* ,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
* ,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
* from SYS_User a
* inner join (
* select c.*,p.Org_Name as ParentName
* from SYS_Org p,SYS_Org as c
* where c.Parent_ID=p.Org_ID
* ) t on t.Create_By=a.User_ID
* where t.ParentName like @_p0 order by t.Org_Code
*/
foreach (DataRow row in ds.Tables[0].Rows)
{
this.Response.Write(row["OrgCode"]);
this.Response.Write("<br />");
}
oql = @"select * from Org where len(OrgCode)=2";
object obj = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org")
.Scalar();
this.Response.Write(obj);
//this.rpUser.DataSource = users;
//this.rpUser.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string oql = @"
select
a.OrgId, a.OrgCode, --测试：Property，使用Entity别名。期望结果：别名保留，Property替换为Column
OrgName, --测试：Property字。期望结果：Property替换为Column
--测试：CASE语句。期望结果：正确解析，并将里面的Property替换为Column
case a.OrgType when 1 then '组织' when 2 then '合作企业' else '不明物' end as Type,
u.User_Name as CreateBy, --测试：Column，使用Table别名，Column定义了别名。期望结果：全部保留，不做转换
a.CreateDate, a.ModifyBy, a.ModifyDate --测试：Property
from Org a --测试：Entity，定义了别名。期望结果：Entity替换为Table。
inner join SYS_User u --测试：Table，定义了别名。期望结果：全部保留
--测试：Column与Property关联。期望结果：Column保留，Property替换为Column。
--注意：SELECT列表中有一个别名叫做CreateBy，
--所以：a.CreateBy表示Entity Org的属性，需要替换为自段名。如果使用u.User_ID=CreateBy，则CreateBy为别名，这种情况不应当替换
on u.User_ID=a.CreateBy
where
a.OrgType=:ot --测试：命名参数
and u.user_id=? --测试：位置参数
";
using (ISession session = new Magic.Framework.ORM.Session())
{
ObjectQuery query = session.CreateObjectQuery(oql);
query.Attach(typeof(Org), "Org")
.SetValue(":ot", OrgType.Own, "OrgType")
.SetValue(0, 189, "CreateBy")
.SetFirstOffset(4)
.SetLastOffset(7);
DataSet ds = query.DataSet(); //1
/*执行的语句
* WITH q AS(
* select a.Org_ID,a.Org_Code,Org_Name
* ,case a.Org_Type when 1 then '组织' when 2 then '合作企业' else '不明物' end as Type
* ,u.User_Name as CreateBy,a.Create_Date,a.Modify_By,a.Modify_Date
* ,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
* from SYS_Org a
* inner join SYS_User u on u.User_ID=a.Create_By
* where a.Org_Type=@_p0 and u.user_id=@_p1
* )
* SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
IList <Org> orgs = query.List <Org>(); //2
/*执行的语句
* WITH q AS(
* select a.Org_ID,a.Parent_ID,a.Org_Code,a.Org_Name,a.Org_Type,a.Is_Virtual
* ,a.Is_Root,a.Org_Seq,a.Del_Flag,a.Description,a.Manager,a.Create_Date
* ,a.Create_By,a.Modify_Date,a.Modify_By
* ,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
* from SYS_Org a inner join SYS_User u on u.User_ID=a.Create_By
* where a.Org_Type=@_p0 and u.user_id=@_p1
* )
* SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
IList <User> users = query.List <User>(); //3
/*执行的语句
* WITH q AS(
* select u.User_ID,u.User_Name,u.Password,u.Full_Name,u.User_Type
* ,u.Email,u.Gender,u.Employee_No,u.Ext,u.Mobile,u.Home_Phone
* ,u.Home_Address,u.Birthday,u.Note,u.Status,u.Org_ID,u.Last_Logon_Time
* ,u.Create_Time,u.Create_By,u.Modify_By,u.Modify_Time
* ,ROW_NUMBER() over(ORDER BY CURRENT_TIMESTAMP) AS _mm_row
* from SYS_Org a inner join SYS_User u on u.User_ID=a.Create_By
* where a.Org_Type=@_p0 and u.user_id=@_p1
* )
* SELECT * FROM q WHERE _mm_row>=@_mm_first AND _mm_row<=@_mm_last
*/
this.rpDs.DataSource = ds;
this.rpDs.DataBind();
this.rpOrg.DataSource = orgs;
this.rpOrg.DataBind();
this.rpUser.DataSource = users;
this.rpUser.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (ISession session = new Magic.Framework.ORM.Session())
{
//测试1：对ON条件，能够根据别名（t）找到子查询，尝试从子查询中判断CreateBy是否是一个属性名字。
//测试结果应当为true，这个属性需要替换为自段名
string oql = @"
select * from User a inner join
(select distinct CreateBy from Org) t
on t.CreateBy=a.UserId
";
ObjectQuery query = session.CreateObjectQuery(oql);
IList<User> users = query.Attach(typeof(Org), "Org").Attach(typeof(User), "User").List<User>();
/*预期执行的SQL
select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
from SYS_User a
inner join (select distinct Create_By from SYS_Org) t on t.Create_By=a.User_ID
*/
//测试2：这次子查询里面将CreateBy取了一个别名，所以ON条件中的CreateBy不再需要替换
oql = @"
select * from User a inner join
(select distinct CreateBy as CreateBy from Org) t
on t.CreateBy=a.UserId
";
users = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org").Attach(typeof(User), "User")
.List<User>();
/*预期执行的SQL
select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
from SYS_User a
inner join (select distinct Create_By as CreateBy from SYS_Org) t on t.CreateBy=a.User_ID
*/
//测试3：子查询里面情况稍微复杂一点，这将影响列名决策的执行流。
//因为测试2中，子查询只有一个table，列名决策遇到这种情况时，如果列名在子查询里面没有定义别名，它就可以肯定是来自这个表的自段或实体的属性
oql = @"
select OrgCode as OrgCode from Org a, User u
where len(a.OrgCode)=? and a.CreateBy=u.UserId and u.UserId=189
";
DataSet ds = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org").Attach(typeof(User), "User")
.SetValue(0, 2, "OrgId")
.DataSet();
/*预期执行的SQL
select a.User_ID,a.User_Name,a.Password,a.Full_Name,a.User_Type,a.Email,a.Gender,a.Employee_No
,a.Ext,a.Mobile,a.Home_Phone,a.Home_Address,a.Birthday,a.Note,a.Status,a.Org_ID
,a.Last_Logon_Time,a.Create_Time,a.Create_By,a.Modify_By,a.Modify_Time
from SYS_User a
inner join (
select c.*,p.Org_Name as ParentName
from SYS_Org p,SYS_Org as c
where c.Parent_ID=p.Org_ID
) t on t.Create_By=a.User_ID
where t.ParentName like @_p0 order by t.Org_Code
*/
foreach (DataRow row in ds.Tables[0].Rows)
{
this.Response.Write(row["OrgCode"]);
this.Response.Write("<br />");
}
oql = @"select * from Org where len(OrgCode)=2";
object obj = session.CreateObjectQuery(oql)
.Attach(typeof(Org), "Org")
.Scalar();
this.Response.Write(obj);
//this.rpUser.DataSource = users;
//this.rpUser.DataBind();
}
}