62,046
社区成员
发帖
与我相关
我的任务
分享
string TypeTableName = "Source";
string TypeTableValue = "id";
string TypeTableText = "Title";
string QueryTableName = "AskReservation";
string QueryTypeForeignKey = "SourceId";
string QueryTableTimeField = "Addtime";
var QueryFilter = " " + QueryTableTimeField + " between '" + startdate + "' and '" + enddate + "' and companyId in(" + string.Join(",", companys) + ") and groupid in (" + string.Join(",", groups) + ") ";
var sql = " declare @tsql nvarchar(4000) " +
" set @tsql = '' " +
" select @tsql+= ',sum(case " + QueryTypeForeignKey + " when '+cast(" + TypeTableValue + " as nvarchar(20))+' then 1 else 0 end) as '+" + TypeTableText + "+' ' from " + TypeTableName + " where " + TypeTableValue + " in (select " + QueryTypeForeignKey + " from " + QueryTableName + " where " + QueryFilter + " group by " + QueryTypeForeignKey + ") " +
" set @tsql = 'select convert(char(10)," + QueryTableTimeField + ",120) as 日期' +@tsql + ' from " + QueryTableName + " where " + QueryFilter.Replace("'", "''") + " group by convert(char(10)," + QueryTableTimeField + ",120) order by convert(char(10)," + QueryTableTimeField + ",120) desc '" +
" exec(@tsql) ";
List<ExpandoObject> objects = ExecuteTSqlResults(sql,new Entities());
/// <summary>
/// 执行自定义查询语句,并返回动态对象
/// </summary>
/// <param name="tsql">查询语句</param>
/// <param name="context">执行上下文</param>
/// <returns>语句执行结果</returns>
public List<ExpandoObject> ExecuteTSqlResults(string tsql, ObjectContext context)
{
List<ExpandoObject> lists = new List<ExpandoObject>();
EntityConnection entityConnection = (EntityConnection)context.Connection;
DbConnection storeConnection = entityConnection.StoreConnection;
DbCommand command = storeConnection.CreateCommand();
command.CommandText = tsql;
bool openingConnection = command.Connection.State == ConnectionState.Closed;
if (openingConnection) { command.Connection.Open(); }
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ExpandoObject t = new ExpandoObject();
for (int i = 0; i < reader.FieldCount; i++)
{
var dt = (IDictionary<string, object>)t;
if (!dt.Keys.Contains(reader.GetName(i)))
{
dt.Add(new KeyValuePair<string, object>(reader.GetName(i), reader.GetValue(i)));
}
else
{
int k = 1;
while (dt.Keys.Contains(string.Format("{0}({1})", reader.GetName(i), k)))
{
k++;
}
dt.Add(new KeyValuePair<string, object>(string.Format("{0}({1})", reader.GetName(i), k), reader.GetValue(i)));
}
}
lists.Add(t);
}
}
if (openingConnection && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); }
return lists;
}
//依据IQueryable数据源构造一个查询
IQueryable<Customer> custs = db.Customers;
//组建一个表达式树来创建一个参数
ParameterExpression param =
Expression.Parameter(typeof(Customer), "c");
//组建表达式树:c.ContactName
Expression selector = Expression.Property(param,
typeof(Customer).GetProperty("ContactName"));
Expression pred = Expression.Lambda(selector, param);
//组建表达式树:Select(c=>c.ContactName)
Expression expr = Expression.Call(typeof(Queryable), "Select",
new Type[] { typeof(Customer), typeof(string) },
Expression.Constant(custs), pred);
//使用表达式树来生成动态查询
IQueryable<string> query = db.Customers.AsQueryable()
.Provider.CreateQuery<string>(expr);
//使用GetCommand方法获取SQL语句
System.Data.Common.DbCommand cmd = db.GetCommand(query);
Console.WriteLine(cmd.CommandText);