protected override void Execute(NativeActivityContext context)
{
// get the ambient Entity Framework object context
ObjectContext efObjectContext = context.Properties.Find(ObjectContextScope.ObjectContextPropertyName) as ObjectContext;
if (efObjectContext == null)
{
throw new ValidationException("Entity Framework Object Context not found");
}
ObjectQuery <TResult> query;
// setup parameters
if (this.parameters.Count() > 0)
{
ObjectParameter[] objectParameters = new ObjectParameter[this.Parameters.Count()];
int i = 0;
foreach (KeyValuePair <string, Argument> item in this.Parameters)
{
objectParameters[i] = new ObjectParameter(item.Key, item.Value.Get(context));
i++;
}
query = efObjectContext.CreateQuery <TResult>(EntitySql.Get(context), objectParameters);
}
else
{
query = efObjectContext.CreateQuery <TResult>(EntitySql.Get(context));
}
// set the result value
Result.Set(context, query.ToList <TResult>());
}
public ObjectQuery <TEntity> DoQuery(Expression <Func <TEntity, bool> > EvalPredicate)
{
var Query = (ObjectQuery <TEntity>)ObjectContext.CreateQuery <TEntity>("[ + typeof(TEntity).Name + ]")
.Where(EvalPredicate);
return(Query);
}
public static IQueryable <T> CreateQuery <T>(this DbContext ctx, String esql, params Object[] parameters)
{
ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
String resql = ParametersRegex.Replace(esql, "@p${x}");
return(octx.CreateQuery <T>(resql, parameters.Select((elm, index) => new ObjectParameter(String.Format("p{0}", index), elm)).ToArray()));
}
/// <summary>
/// 获取我管理的人员
/// </summary>
/// <param name="EmpLoyeeID"></param>
/// <returns></returns>
public List <Sys_Employee> GetMyManagerEmpLoyees(int?EmpLoyeeID, ref string keyList)
{
HHL_WeddingEntities ObjWeddingDataContext = new HHL_WeddingEntities();
using (EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <int> ObjKeyList = new List <int>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
var MyDepartmentList = ObjWeddingDataContext.Sys_Department.Where(C => C.DepartmentManager == EmpLoyeeID);
List <Sys_Department> ObjDepartmentList = new List <Sys_Department>();
foreach (var ObjDepartment in MyDepartmentList)
{
ObjDepartmentList.AddRange(ObjDataContext.CreateQuery <Sys_Department>("Select VALUE c from HHL_WeddingEntities.Sys_Department as c where c.IsDelete =false and c.SortOrder Like '%" + ObjDepartment.SortOrder + "%'").ToList <Sys_Department>());
}
foreach (var Objitem in ObjDepartmentList)
{
ObjKeyList.Add(Objitem.DepartmentID);
}
var ObjEmpLoyeeList = this.GetByDepartmetnKeysList(ObjKeyList.ToArray());
foreach (var item in ObjKeyList)
{
keyList += item.ToString() + ",";
}
return(ObjEmpLoyeeList.Where(C => C.Status == 0).ToList());
}
}
/// <summary>
/// 获取我管理的人员
/// </summary>
/// <param name="EmpLoyeeID"></param>
/// <returns></returns>
public List <Sys_Employee> GetMyManagerEmpLoyee(int?EmpLoyeeID)
{
HHL_WeddingEntities ObjWeddingDataContext = new HHL_WeddingEntities();
using (EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <int> ObjKeyList = new List <int>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
var MyDepartmentList = ObjWeddingDataContext.Sys_Department.Where(C => C.DepartmentManager == EmpLoyeeID);
List <Sys_Department> ObjDepartmentList = new List <Sys_Department>();
foreach (var ObjDepartment in MyDepartmentList)
{
ObjDepartmentList.AddRange(ObjDataContext.CreateQuery <Sys_Department>("Select VALUE c from HHL_WeddingEntities.Sys_Department as c where c.SortOrder Like '%" + ObjDepartment.SortOrder + "%'").ToList <Sys_Department>());
}
////猜测是否为部门主管
//if (ObjDepartment.DepartmentManager == ObjEmpLoyee.EmployeeID)
//{
//Department ObjDepartmentBLL=new Department();
// var DepartmetnList = ObjDataContext.CreateQuery<Sys_Department>("Select VALUE c from HHL_WeddingEntities.Sys_Department as c where c.SortOrder Like '%" + ObjDepartment.SortOrder + "%'").ToList<Sys_Department>();
foreach (var Objitem in ObjDepartmentList)
{
//ObjKeyList.Add(Convert.ToInt32(Objitem.Parent));
ObjKeyList.Add(Objitem.DepartmentID);
}
var ObjEmpLoyeeList = this.GetByDepartmetnKeysList(ObjKeyList.ToArray());
return(ObjEmpLoyeeList.Where(C => C.Status == 0).ToList());
}
}
/// <summary>
/// 获取我管理的人员
/// </summary>
/// <param name="EmpLoyeeID"></param>
/// <returns></returns>
public static string GetMyManagerEmpLoyee(int?EmpLoyeeID, string KeyList, bool IsTrue = false)
{
HHL_WeddingEntities ObjWeddingDataContext = new HHL_WeddingEntities();
using (EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <int> ObjKeyList = new List <int>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
var MyDepartmentList = ObjWeddingDataContext.Sys_Department.Where(C => C.DepartmentManager == EmpLoyeeID && C.Status == 0);
List <Sys_Department> ObjDepartmentList = new List <Sys_Department>();
foreach (var ObjDepartment in MyDepartmentList)
{
ObjDepartmentList.AddRange(ObjDataContext.CreateQuery <Sys_Department>("Select VALUE c from HHL_WeddingEntities.Sys_Department as c where c.SortOrder Like '%" + ObjDepartment.SortOrder + "%'").ToList <Sys_Department>());
}
foreach (var Objitem in ObjDepartmentList)
{
ObjKeyList.Add(Objitem.DepartmentID);
}
var ObjEmpLoyeeList = new Employee().GetByDepartmetnKeysList(ObjKeyList.ToArray(), IsTrue).Where(C => C.Status == 0).ToList();
foreach (var ObjItem in ObjEmpLoyeeList)
{
KeyList += ObjItem.EmployeeID.ToString() + ",";
}
return(" {" + (KeyList.Trim(',') + "," + EmpLoyeeID).Trim(',') + "} ");
}
//return EmpLoyeeID.ToString();
}
public string GetColumnName(DbContext dbContext, PropertyInfo propertyInfo)
{
Type modelClassType = propertyInfo.DeclaringType;
string entitySQL = string.Format("SELECT a.{0} FROM {1}.{2} AS a", propertyInfo.Name, dbContext.GetContainer().Name, dbContext.GetEntitySetName(modelClassType));
string traceString = objectContext.CreateQuery <DbDataRecord>(entitySQL, new ObjectParameter[0]).ToTraceString();
return(SqlParser.ExtractColumnName(traceString));
}
public IQueryable <TEntity> GetQuery <TEntity>() where TEntity : class
{
var entityName = GetEntityName <TEntity>();
var q = ObjectContext.CreateQuery <TEntity>(entityName);
//return ObjectContext.CreateQuery<TEntity>(entityName);
return(q);
}
public static IQueryable <T> CreateQuery <T>(this ObjectContext context)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
string name = GetEntitySet(context, typeof(T)).Name;
return(context.CreateQuery <T>(string.Format("[{0}]", name)));
}
public TEntity GetByKey(int id)
{
string eSql = string.Format("SELECT VALUE ent FROM {0} AS ent WHERE ent.Id=@id", EntitySetName);
var objectQuery = objContext.CreateQuery <TEntity>(eSql, new ObjectParameter("id", id));
if (objectQuery.Count() > 0)
{
return(objectQuery.First());
}
throw new Exception("Not found");
}
private void ObjQuery_Click(object sender, EventArgs e)
{
//string con = ef.Database.Connection.ConnectionString;
ObjectContext context = (new EcoFarmEntities() as IObjectContextAdapter).ObjectContext;
//context.DefaultContainerName = "EcoFarmEntities";
ObjectParameter param = new ObjectParameter("Parameter", comboBox1.SelectedItem.ToString());
string command = "SELECT VALUE p FROM Position as p WHERE p.Position1=@Parameter";
ObjectQuery <Position> objQuery = context.CreateQuery <Position>(command, param);
var result = objQuery.ToList();
dataGridView1.DataSource = result;
}
private void button3_Click(object sender, EventArgs e)
{
ObjectContext context = (new EcoFarm_DBEntities() as IObjectContextAdapter).ObjectContext;
//context.DefaultContainerName = "EcoFarm_DBEntities";
ObjectParameter parameter = new ObjectParameter("supplier", SupplierCB.SelectedItem.ToString());
string query = "SELECT VALUE i FROM Invoices as i WHERE i.Supplier=@supplier";
ObjectQuery <Invoice> objectQuery = context.CreateQuery <Invoice>(query, parameter);
var res = objectQuery.ToList();
dt.DataSource = res;
}
public static object Paginate2 <T>(ObjectContext context, int totalRecords, HttpRequestBase req, string[] columns, int page, int rows, string sidx, string sord)
{
int pageIndex = Convert.ToInt32(page) - 1;
int pageSize = rows;
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
int offset = pageIndex * pageSize;
var tmp = buildWhere2 <T>(req.Params, columns);
string where = (string)tmp[0];
ObjectParameter[] vals = (ObjectParameter[])tmp[1];
//string sql_stmt = String.Format(@"SELECT VALUE b FROM (SELECT VALUE a FROM {0} AS a WHERE {1} ORDER BY a.{2} {3} ) as b ORDER BY b.{2} skip {4} limit {5}",
string sql_stmt = String.Format(@"SELECT VALUE a FROM {0} AS a WHERE {1} ORDER BY a.{2} {3}",
typeof(T).Name, where, sidx, sord, offset, rows);
var queryx = context.CreateQuery <T>(sql_stmt, vals);
var query = queryx.OrderBy("")
.Take(10)
.Skip(10);
//System.Diagnostics.Debug.WriteLine((ObjectQuery<T>)query.ToTraceString());
var items = query.ToArray();
//var items = query.Execute(MergeOption.NoTracking).ToArray();
var i = 0;
var jsonData = new
{
total = totalPages,
page = page,
records = totalRecords,
rows = (
from item in items
select new
{
id = "row" + i++.ToString(),
cell = from col in columns
select myFormat(typeof(T).GetProperty(col).GetValue(item, null))
}).ToArray()
};
return(jsonData);
}
private ObjectQuery <TEntity> GetQuery <TEntity>() where TEntity : class, IEdgarEntity
{
string key = typeof(TEntity).Name;
IObjectContextAdapter adapter = (IObjectContextAdapter)Context;
ObjectContext objectContext = adapter.ObjectContext;
// 1. we need the container for the conceptual model
EntityContainer container = objectContext.MetadataWorkspace.GetEntityContainer(
objectContext.DefaultContainerName, System.Data.Entity.Core.Metadata.Edm.DataSpace.CSpace);
// 2. we need the name given to the element set in that conceptual model
string name = container.BaseEntitySets.Where((s) => s.ElementType.Name.Equals(key)).FirstOrDefault().Name;
// 3. finally, we can create a basic query for this set
ObjectQuery <TEntity> query = objectContext.CreateQuery <TEntity>("[" + name + "]");
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="EmployeeID"></param>
/// <returns></returns>
public List <Sys_Department> GetMyManagerDepartment(int EmployeeID)
{
using (EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <int> ObjKeyList = new List <int>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
var MyDepartmentList = ObjEntity.Sys_Department.Where(C => C.DepartmentManager == EmployeeID);
List <Sys_Department> ObjDepartmentList = new List <Sys_Department>();
foreach (var ObjDepartment in MyDepartmentList)
{
ObjDepartmentList.AddRange(ObjDataContext.CreateQuery <Sys_Department>("Select VALUE c from HHL_WeddingEntities.Sys_Department as c where c.SortOrder Like '%" + ObjDepartment.SortOrder + "%' and c.IsDelete=false").ToList <Sys_Department>());
}
return(ObjDepartmentList);
}
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="GetWhere">条件</param>
/// <param name="OrderByColumn">排序字段</param>
/// <param name="GroupBy">排序方式(倒序 Or 正序)</param>
/// <returns></returns>
public static List <T> GetByParameters(string GetWhere, string OrderByColumn, OrderType GroupBy = OrderType.Asc)
{
using (EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <ObjectParameter> ObjListparmeter = new List <ObjectParameter>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
string sql = "Select VALUE c from HHL_WeddingEntities." + typeof(T).Name + " as c where 1=1 " + GetWhere + " order by c." + OrderByColumn + " " + GroupBy.ToString();
List <T> ObjList = new List <T>();
var ObjReturnList = ObjDataContext.CreateQuery <T>(sql);
ObjList = ObjReturnList.ToList();
ObjEntityconn.Close();
return(ObjList);
}
}
public void EntitySQLObjectQuery()
{
using (var context = new DemoEntities())
{
//var categories = context.Categories;
var sql = @"SELECT VALUE C
FROM DemoEntities.Categories As C
WHERE C.CategoryId =1";
ObjectContext oContext = (context as IObjectContextAdapter).ObjectContext;
ObjectQuery <Category> categories = oContext.CreateQuery <Category>(sql);
Category category = categories.FirstOrDefault <Category>();
}
}
/// <summary>
/// Linq to Entity
/// </summary>
private static void FunWithEntitySQL()
{
using (AutoLotEntities context = new AutoLotEntities())
{
ObjectContext newContext = ((IObjectContextAdapter)context).ObjectContext;
//构建一个包含Entity SQL语法的字符串
string query = "SELECT VALUE car FROM AutoLotEntities.Cars " + "AS car WHERE car.Color = 'black'";
var blackCars = newContext.CreateQuery <Car>(query);
foreach (var item in blackCars)
{
Console.WriteLine(item);
}
}
}
ObjectQuery <T> CreateObjectQuery <T>(ObjectQuery <T> source)
{
var parameters = source.Parameters
.Select(p => new ObjectParameter(p.Name, p.ParameterType)
{
Value = p.Value
})
.ToArray();
var query = targetObjectContext.CreateQuery <T>(source.CommandText, parameters);
query.MergeOption = source.MergeOption;
query.Streaming = source.Streaming;
query.EnablePlanCaching = source.EnablePlanCaching;
if (TargetProvider == null)
{
TargetProvider = ((IQueryable)query).Provider;
}
return(query);
}
internal static ObjectQuery <T> ConstructQuery <T>(ObjectContext context,
string entitySetQueryExpression,
string commandText,
ObjectParameter[] commandParameters)
{
string queryExpression;
ObjectParameter[] queryParameters;
if (!string.IsNullOrEmpty(commandText))
{
queryExpression = commandText;
queryParameters = commandParameters;
}
else
{
queryExpression = entitySetQueryExpression;
queryParameters = QueryBuilderUtils.EmptyObjectParameters;
}
return(context.CreateQuery <T>(queryExpression, queryParameters));
}
public void Verify_querying_database_with_ObjectQuery_works()
{
const string commandText =
"SELECT VALUE C FROM NorthwindEntities.Customers AS C WHERE C.CustomerID LIKE @CustomerID";
using (var context = new ObjectContext(NorthwindEntitiesConnectionString))
{
var query = context.CreateQuery <Customer>(commandText, new ObjectParameter("CustomerID", "A%"));
var caseIdx = 0;
foreach (var customer in query)
{
Assert.Equal(expectedResults[caseIdx].Name, customer.CompanyName);
Assert.Equal(expectedResults[caseIdx].Location, customer.Location.AsText());
caseIdx++;
}
Assert.Equal(4, caseIdx);
}
}
protected List <T> FindInStore <T>(Tuple <string, object>[] keyValues, params Expression <Func <T, object> >[] includes)
where T : class
{
ObjectContext objectContext = ((IObjectContextAdapter)this.unitOfWork.DbContext).ObjectContext;
ObjectSet <T> objectSet = objectContext.CreateObjectSet <T>();
string quotedEntitySetName = string.Format(
CultureInfo.InvariantCulture,
"{0}.{1}",
this.QuoteIdentifier(objectSet.EntitySet.EntityContainer.Name),
this.QuoteIdentifier(objectSet.EntitySet.Name));
var queryBuilder = new StringBuilder();
queryBuilder.AppendFormat("SELECT VALUE X FROM {0} AS X WHERE ", quotedEntitySetName);
var entityKeyValues = this.CreateEntityKey(objectSet.EntitySet, keyValues).EntityKeyValues;
var parameters = new ObjectParameter[entityKeyValues.Length];
for (var i = 0; i < entityKeyValues.Length; i++)
{
if (i > 0)
{
queryBuilder.Append(" AND ");
}
var name = string.Format(CultureInfo.InvariantCulture, "p{0}", i.ToString(CultureInfo.InvariantCulture));
queryBuilder.AppendFormat("X.{0} = @{1}", this.QuoteIdentifier(entityKeyValues[i].Key), name);
parameters[i] = new ObjectParameter(name, entityKeyValues[i].Value);
}
IQueryable <T> query = objectContext.CreateQuery <T>(queryBuilder.ToString(), parameters);
foreach (var include in includes)
{
query = query.Include(include);
}
return(query.ToList());
}
// GetDataByPower(T ObjEntity, int? EmployeeID, int? ChannelID, ObjectParameter[] ObjParameterList)
/// <summary>
/// 根据复杂条件获取数据
/// </summary>
/// <param name="ObjEntity">需要的实体</param>
/// <param name="ObjParameterList">参数集合</param>
///OrderByCloumname排序字段
/// <returns></returns>
public static List <T> GetDataByWhereParameter(List <PMSParameters> ObjParameterList, string OrderByCloumname, int PageSize, int PageIndex, out int SourceCount, int Sort = 1)
{
string WhereStr = GetWhere(ObjParameterList);
string CountSql = " Select count(*) from " + typeof(T).Name + " as c where 1=1 " + WhereStr.Replace("{", "(").Replace("}", ")").Replace("System.Boolean", "bit").Replace("False", "'0'").Replace("True", "'1'");
//CountSql = CountSql.Replace("System.DateTime", "date");
CountSql = CountSql.Replace("cast(", "").Replace("as System.DateTime)", "");
HHL_WeddingEntities ObjContex = new HHL_WeddingEntities();
var SourctcountResualt = ObjContex.Database.SqlQuery <int>(CountSql, new List <ObjectParameter>().ToArray()).First();
SourceCount = SourctcountResualt;
using (
EntityConnection ObjEntityconn = new EntityConnection("name=HHL_WeddingEntities"))
{
List <ObjectParameter> ObjListparmeter = new List <ObjectParameter>();
ObjectContext ObjDataContext = new ObjectContext(ObjEntityconn);
PageIndex = PageIndex - 1;
ObjListparmeter.Add(new ObjectParameter("skip", PageIndex * PageSize));
ObjListparmeter.Add(new ObjectParameter("limit", PageSize));
List <T> ObjList = new List <T>();
OrderType GroupBy = OrderType.Desc;
if (Sort == 2)
{
GroupBy = OrderType.Asc;
}
string RunSql = "Select VALUE c from HHL_WeddingEntities." + typeof(T).Name + " as c where 1=1 " + WhereStr + " order by c." + OrderByCloumname + " " + GroupBy.ToString() + " SKIP @skip LIMIT @limit ";
var ObjReturnList = ObjDataContext.CreateQuery <T>(RunSql, ObjListparmeter.ToArray());
ObjList = ObjReturnList.ToList();
ObjEntityconn.Close();
return(ObjList);
}
}
static void Querying()
{
using (var ctx = new ProjectsContext())
{
//id
var bigProject = ctx.Projects.Find(1);
//LINQ
var usersInProjectWithLINQ = (from r in ctx.Resources
from p in ctx.Projects
where p.Name == "Big Project" &&
r.ProjectResources.Select(x => x.Project).Contains(p)
select r).ToList();
var projectsByName = ctx.Projects.Where(x => x.Name == "Big Project").ToList();
var customersWithoutProjects = ctx.Customers.Where(c => c.Projects.Any() == false).ToList();
//or
var resourcesKnowingVBOrCS = ctx.Technologies.Where(t => t.Name == "VB.NET" || t.Name == "C#").SelectMany(x => x.Resources).Select(x => x.Name).ToList();
//grouping
var resourcesGroupedByProjectRole = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Role = x.Role, Resource = x.Resource.Name }).GroupBy(x => x.Role).Select(x => new { Role = x.Key, Resources = x }).ToList();
//grouping and counting
var projectsByCustomer = ctx.Projects.GroupBy(x => x.Customer).Select(x => new { Customer = x.Key.Name, Count = x.Count() }).ToList();
//top 10 customers having more projects in descending order
var top10CustomersWithMoreProjects = ctx.Projects.GroupBy(x => x.Customer.Name).Select(x => new { x.Key, Count = x.Count() }).OrderByDescending(x => x.Count).Take(10).ToList();
//grouping by date part and counting
var countOfProjectsByMonth = ctx.Projects.GroupBy(x => EntityFunctions.CreateDateTime(x.Start.Year, x.Start.Month, 1, 0, 0, 0)).Select(x => new { Month = x.Key, Count = x.Count() }).ToList();
//group and count the days between two dates
var projectsGroupedByDurationDays = ctx.Projects.Where(x => x.End != null).GroupBy(x => EntityFunctions.DiffDays(x.Start, x.End.Value)).Select(x => new { Duration = x.Key, List = x }).ToList();
//order by extension method
var technologiesSortedByName = ctx.Technologies.OrderBy("Name").ThenBy("TechnologyId").ToList();
//create a base query
var projectsQuery = from p in ctx.Projects select p;
//add sorting
var projectsSortedByDateQuery = projectsQuery.OrderBy(x => x.Start);
//execute and get the sorted results
var projectsSortedByDateResults = projectsSortedByDateQuery.ToList();
//add paging
var projectsWithPagingQuery = projectsQuery.OrderBy(x => x.Start).Take(5).Skip(0);
//execute and get the first 5 results
var projectsWithPagingResults = projectsWithPagingQuery.ToList();
//add a restriction
var projectsStartingAWeekAgoQuery = projectsQuery.Where(x => x.Start >= EntityFunctions.AddDays(DateTime.Today, -7));
//execute and get the projects that started a week ago
var projectsStartingAWeekAgoResults = projectsStartingAWeekAgoQuery.ToList();
//eager load properties
var resourcesIncludingTechnologies = ctx.Resources.Include(x => x.Technologies).ToList();
var projectsIncludingCustomers = ctx.Projects.Include("Customer").ToList();
//distinct
var roles = ctx.Resources.SelectMany(x => x.ProjectResources).Where(x => x.Resource.Name == "Ricardo Peres").Select(x => x.Role).Distinct().ToList();
//check existence
var existsProjectBySomeCustomer = ctx.Projects.Any(x => x.Customer.Name == "Some Customer");
//count
var numberOfClosedProjects = ctx.Projects.Where(x => x.End != null && x.End < DateTime.Now).Count();
//average
var averageProjectDuration = ctx.Projects.Where(x => x.End != null).Average(x => EntityFunctions.DiffDays(x.Start, x.End));
//sum
var sumProjectDurationsByCustomer = ctx.Projects.Where(x => x.End != null).Select(x => new { Customer = x.Customer.Name, Days = EntityFunctions.DiffDays(x.Start, x.End) }).GroupBy(x => x.Customer).Select(x => new { Customer = x.Key, Sum = x.Sum(y => y.Days) }).ToList();
//return the resources and project names only
var resourcesXprojects = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Resource = x.Resource.Name, Project = x.Project.Name }).ToList();
//return the customer names and their project counts
var customersAndProjectCount = ctx.Customers.Select(x => new { x.Name, Count = x.Projects.Count() }).ToList();
//subquery
var usersKnowingATechnology = (from r in ctx.Resources where r.Technologies.Any(x => (ctx.Technologies.Where(t => t.Name == "ASP.NET")).Contains(x)) select r).ToList();
var usersKnowingATechnology2 = (from r in ctx.Resources where r.Technologies.Any(x => (from t in ctx.Technologies where t.Name == "ASP.NET" select t).Contains(x)) select r).ToList();
//contains
var customersToFind = new String[] { "Some Customer", "Another Customer" };
var projectsOfCustomers = ctx.Projects.Where(x => customersToFind.Contains(x.Customer.Name)).ToList();
//spatial
var location = DbGeography.FromText("POINT(41 8)");
var area = DbGeography.MultiPointFromText("MULTIPOINT(53.095124 -0.864716, 53.021255 -1.337128, 52.808019 -1.345367, 52.86153 -1.018524)", 4326);
/*var pointInsideArea = ctx.Venues.Where(x => area.Intersects(x.Location)).ToList();
*
* var venuesAndDistanceToLocation = (from v in ctx.Venues
* orderby v.Location.Distance(location)
* select new { Venue = v, Distance = v.Location.Distance(location) }).ToList();*/
//Entity-SQL
ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
//filtering
var usersInProjectWithESQL = octx.CreateQuery <Resource>("SELECT VALUE pr.Resource FROM ProjectResources AS pr WHERE pr.Project.Name = @name", new ObjectParameter("name", "Big Project")).ToList();
//contains
var usersKnowingATechnologyESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r WHERE EXISTS (SELECT VALUE t FROM Technologies AS t WHERE t.Name = @name AND r IN t.Resources)", new ObjectParameter("name", "ASP.NET")).ToList();
//flatten
var userTechnologiesESQL = octx.CreateQuery <Technology>("FLATTEN(SELECT VALUE r.Technologies FROM Resources AS r)").ToList();
//paging
var pagedResourcesESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP 5 LIMIT(5)").ToList();
//paging with parameters
var pagedResourcesWithParametersESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP @skip LIMIT(@limit)", new ObjectParameter("skip", 5), new ObjectParameter("limit", 5)).ToList();
//top
var lastProjectESQL = octx.CreateQuery <Project>("SELECT VALUE TOP(1) p FROM Projects AS p ORDER BY p.Start DESC").SingleOrDefault();
//between
var projectsStartingInADateIntervalESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start BETWEEN @start AND @end", new ObjectParameter("start", DateTime.Today.AddDays(-14)), new ObjectParameter("end", DateTime.Today.AddDays(-7))).ToList();
//in
var projectsStartingInSetOfDatesESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start IN MULTISET(DATETIME '2013-12-25 0:0:0', DATETIME '2013-12-31 0:0:0')").ToList();
//projection
var projectNameAndDurationESQL = octx.CreateQuery <Object>("SELECT p.Name, DIFFDAYS(p.Start, p.[End]) FROM Projects AS p WHERE p.[End] IS NOT NULL").ToList();
//count
var numberOfClosedProjectsESQL = octx.CreateQuery <Int32>("SELECT VALUE COUNT(p.ProjectId) FROM Projects AS p WHERE p.[End] IS NOT NULL AND p.[End] < @now", new ObjectParameter("now", DateTime.Now)).Single();
//group
var customersAndProjectCountIndicatorESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, COUNT(p.Name) FROM Projects AS p GROUP BY p.Customer").ToList();
//case
var customersAndProjectRangeESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, CASE WHEN COUNT(p.Name) > 10 THEN 'Lots' ELSE 'Few' END AS Amount FROM Projects AS p GROUP BY p.Customer").ToList();
if (customersAndProjectRangeESQL.Any() == true)
{
var r = customersAndProjectRangeESQL.OfType <IExtendedDataRecord>().First();
var nameIndex = r.GetOrdinal("Name");
var name = r.GetString(nameIndex);
}
//max number of days
var maxDurationESQL = octx.CreateQuery <Int32?>("SELECT VALUE MAX(DIFFDAYS(p.Start, p.[End])) FROM Projects AS p WHERE p.[End] IS NOT NULL").SingleOrDefault();
//string contains
var technologiesContainingNetESQL = octx.CreateQuery <String>("SELECT VALUE t.Name FROM Technologies AS T WHERE CONTAINS(t.Name, '.NET')").ToList();
//SQL
var projectFromSQL = ctx.Projects.SqlQuery("SELECT * FROM Project WHERE Name = @p0", "Big Project").ToList();
//stored procedure
var projectFromProcedure = ctx.Projects.SqlQuery("SELECT * FROM dbo.GetProjectById(@p0)", 1).SingleOrDefault();
var result = ctx.Database.ExecuteSqlCommand("UPDATE Project SET [End] = null WHERE ProjectId = {0}", 100);
//current date and time
var now = ctx.Database.SqlQuery <DateTime>("SELECT GETDATE()").Single();
var model = ctx.Database.SqlQuery(typeof(Byte[]), "SELECT Model FROM __MigrationHistory").OfType <Object>().Single();
//call function
var serverTimestamp = ctx.ExecuteScalar <DateTime>("SELECT GETDATE()");
//update records
var updateCount = ctx.ExecuteNonQuery("UPDATE ProjectDetail SET Budget = Budget * 1.1 WHERE ProjectId = {0}", 1);
//extensions
var projectsBetweenTodayAndBeforeToday = ctx.Projects.Between(x => x.Start, DateTime.Today.AddDays(-1), DateTime.Today).ToList();
//projects with 10 to 20 resources
var projectsWithTwoOrThreeResources = ctx.Projects.Select(x => new { x.Name, ResourceCount = x.ProjectResources.Count() }).Between(x => x.ResourceCount, 10, 20).ToList();
//extension method
var soundex = ctx.Projects.Select(x => x.Name.Soundex()).ToList();
//first level cache
var user = ctx.Resources.Local.SingleOrDefault(x => x.Technologies.Any(y => y.Name == "ASP.NET"));
//no caching
var technologies = ctx.Technologies.AsNoTracking().ToList();
}
}
public static List <TU> GetBy <T, TU>(this ObjectContext ctx, Expression <Func <T, bool> > exp, Expression <Func <T, TU> > columns)
where T : class
where TU : class
{
return(ctx.CreateQuery <T>($"[{typeof (T).Name}]").Where(exp).Select(columns).ToList());
}
public static IQueryable <TEntity> GetTable <TEntity>(this ObjectContext context)
{
return(context.CreateQuery <TEntity>("[" + context.GetEntitySet <TEntity>().Name + "]"));
}
/// <summary>
/// ESQL查询
/// </summary>
/// <param name="query">ESQL语句</param>
/// <param name="parameter">参数(可选)</param>
/// <returns></returns>
/// <remarks>可用.Execute(MergeOption.AppendOnly)执行查询</remarks>
public ObjectQuery <TEntity> GetQuery(string query, params ObjectParameter[] parameter)
{
return(_objectContext.CreateQuery <TEntity>(query, parameter));
}
public void Verify_querying_database_with_ObjectQuery_works()
{
const string commandText =
"SELECT VALUE C FROM NorthwindEntities.Customers AS C WHERE C.CustomerID LIKE @CustomerID";
using (var context = new ObjectContext(NorthwindEntitiesConnectionString))
{
var query = context.CreateQuery<Customer>(commandText, new ObjectParameter("CustomerID", "A%"));
var caseIdx = 0;
foreach (var customer in query)
{
Assert.Equal(expectedResults[caseIdx].Name, customer.CompanyName);
Assert.Equal(expectedResults[caseIdx].Location, customer.Location.AsText());
caseIdx++;
}
Assert.Equal(4, caseIdx);
}
}
/// <summary>
/// Metodo generico que retorna todas las entidades del tipo TEntity
/// </summary>
/// <param name="entitySetName">
/// El nombre del EntitySet en el modelo.
/// </param>
/// <typeparam name="TEntity">
/// La entidad a cargarse desde la base de datos.
/// </typeparam>
/// <returns>Retorna un conjunto de TEntity.</returns>
public IQueryable <TEntity> Query(string entitySetName)
{
return(_ctx.CreateQuery <TEntity>("[" + entitySetName + "]"));
}
private ObjectQuery <T> CreateQuery(ObjectContext context)
{
return(context.CreateQuery <T>(EntitySetName));
}
/// <summary>
/// Returns an <see cref="IQueryable{T}"/>.
/// </summary>
/// <typeparam name="T">Generic Argument of the returned <see cref="IQueryable{T}"/>.</typeparam>
/// <returns>Returns an <see cref="IQueryable{T}"/>.</returns>
public IQueryable <T> Get <T>() where T : class
{
return(objectContext.CreateQuery <T>(string.Format("[{0}]", typeof(T).Name)));
}
