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))); }