public IQueryable <OUT> OptimizeSelect <OUT>(System.Linq.IQueryable <T> linq, String orderByQuery = "")
{
var linqStr = linq.ToTraceQuery();
StringBuilder sb = new StringBuilder();
sb.Append(linqStr.Substring(0, linqStr.IndexOf("[")));
PropertyInfo[] props = typeof(OUT).GetProperties();
PropertyInfo[] propsSource = typeof(T).GetProperties();
var nProp = props.Count();
var propertySb = new StringBuilder();
for (int iProp = 0; iProp < nProp; iProp++)
{
var p = props[iProp];
if (!(propsSource.Count(x => x.Name == p.Name && x.PropertyType == p.PropertyType) > 0))
{
continue;
}
propertySb.Append(p.Name);
propertySb.Append(", ");
}
var propetyStr = propertySb.ToString();
sb.Append(propetyStr.Substring(0, propetyStr.Length - 2));
sb.Append(" ");
sb.Append(linqStr.Substring(linqStr.IndexOf("FROM")));
orderByQuery = " " + orderByQuery + " ";
sb.Append(orderByQuery);
try
{
return(dataContext.Database.SqlQuery <OUT>(sb.ToString()).AsQueryable());
}
catch (Exception e)
{
throw new Exception(e.Message + sb.ToString());
}
}
public IEnumerable <OUT> PagingQuery <OUT>(System.Linq.IQueryable <T> linq, int index, int size, String orderBy = "")
{
var linqStr = linq.ToTraceQuery();
StringBuilder sb = new StringBuilder();
sb.Append(linqStr.Substring(0, linqStr.IndexOf("[")));
PropertyInfo[] props = typeof(OUT).GetProperties();
PropertyInfo[] propsSource = typeof(T).GetProperties();
var nProp = props.Count();
var propertySb = new StringBuilder();
for (int iProp = 0; iProp < nProp; iProp++)
{
var p = props[iProp];
if (!(propsSource.Count(x => x.Name == p.Name && x.PropertyType == p.PropertyType) > 0))
{
continue;
}
propertySb.Append(p.Name);
propertySb.Append(", ");
}
var propetyStr = propertySb.ToString();
sb.Append(propetyStr.Substring(0, propetyStr.Length - 2));
sb.Append(" ");
sb.Append(linqStr.Substring(linqStr.IndexOf("FROM")));
var last = String.Format(" order by {0} OFFSET {1} rows FETCH NEXT {2} ROWs ONLY", orderBy, index * size, size);
sb.Append(last);
try
{
return(dataContext.Database.SqlQuery <OUT>(sb.ToString()).ToList());
}
catch
{
throw new Exception(sb.ToString());
}
}