/* * Use ObjectQuery(T) Class or ObjectSet<T> on the object context to access data using Entity SQL Builder methods. * Both methods requires an instance of type ObjectContext. As a result, Entity SQL Builder methods are not supported * on objects of type DbContext. */ public static void RunESQLBuilderExample() { using (var context = new AdventureWorksEntities()) { System.Console.WriteLine("\nUsing Entity SQL Builder"); if (context.GetType() == typeof (ObjectContext)) { // An example of ESQL Builder using an ObjectSet<T> on the ObjectContext // var order = context.CreateObjectSet<SalesOrderHeader>("SalesOrderHeader").Where("it.SalesOrderID = 43661"); // System.Console.WriteLine("\nSalesOrderID: {0} \nOrderDate: {1} \nDueDate: {2} \nShipDate: {3}", order.SalesOrderID, order.OrderDate, order.DueDate, order.ShipDate); var objContext = new ObjectContext("name=AdventureWorksEntities"); var queryString = @"SELECT VALUE salesOrders FROM AdventureWorksEntities.SalesOrderHeader AS salesOrders"; var salesQuery1 = new ObjectQuery<SalesOrderHeader>(queryString, objContext, MergeOption.NoTracking); var salesQuery2 = salesQuery1.Where("it.SalesOrderID = 43661"); foreach (var order in salesQuery2) { System.Console.WriteLine("\nSalesOrderID: {0} \nOrderDate: {1} \nDueDate: {2} \nShipDate: {3}", order.SalesOrderID, order.OrderDate, order.DueDate, order.ShipDate); } } else { System.Console.WriteLine("\nSorry! Context is not of type ObjectContext. ESQL Builder is not supported."); } } }
public QueryLogFile(ObjectQuery objQuery) { _querytype = QueryType.ESQL; _ESQL = objQuery.CommandText; _StoreSQL = objQuery.ToTraceString(); if (_ESQL != string.Empty) //if this was cast from LINQ, don\'t log here CreateLogEntry(); }
/// <summary> /// Gets or create the future context that is linked to the underlying <see cref="ObjectContext"/>. /// </summary> /// <param name="objectQuery">The query source to get the future context from.</param> /// <returns>An instance of <see cref="IFutureContext"/> to store waiting future queries.</returns> public IFutureContext GetOrCreate(ObjectQuery objectQuery) { if (objectQuery == null) throw new ArgumentNullException("objectQuery"); var objectContext = objectQuery.Context; if (objectContext == null) throw new ArgumentException("The source ObjectContext can not be null.", "objectQuery"); MaybeCleanup(); int key = RuntimeHelpers.GetHashCode(objectContext); return _futureContext.GetOrAdd(key, k => new FutureContext(objectContext)); }
static void Main(string[] args) { using (var e = new AdventureWorksEntities()) { //http://msdn.microsoft.com/pt-br/library/vstudio/bb387145.aspx const String cmd = "SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C WHERE C.ContactID <= 15"; var contatos = new ObjectQuery<Contato>(cmd, e); foreach (var contato in contatos) { Console.WriteLine(contato.Nome); } } Console.ReadKey(); }
private static void MostrarMergeOption(MergeOption mo) { using (var e = new AdventureWorksEntities()) { var s = new Stopwatch(); s.Start(); var contatos = new ObjectQuery<Contato>("SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C", e, mo); foreach (var contato in contatos) { contato.Nome = contato.Nome; } Console.WriteLine(s.ElapsedMilliseconds); } }
public void InitalizeTable(ObjectQuery<Person> objectQuery) { GetPersonTypes(); GetPersonColumnTitles(); foreach (Person item in objectQuery) { List<object> newList = new List<object>(); for (int i = 0; i < item.Length; i++) { if (item[i] is byte[]) { newList.Add("File"); continue; } newList.Add(item[i]); } valuesList.Add(newList); } }
static void Main() { using (NorthwindEntities northwindEntities = new NorthwindEntities()) { string queryString = @"SELECT VALUE Customer FROM NorthwindEntities.Customers AS Customer WHERE Customer.City=='London'"; var customerQuery = new ObjectQuery<Customer>(queryString, northwindEntities); Logger.PrintQueries(customerQuery); foreach (Customer customer in customerQuery) { Console.WriteLine("Company Name: {0}\n Contact Name: {1}\n City: {2}\n{3}", customer.CompanyName, customer.ContactName, customer.City, Logger.SeparatorLine); } } }
static void Main(string[] args) { string queryString = @"SELECT VALUE DISTINCT Customer FROM NorthwindEntities.Customers AS Customer INNER JOIN NorthwindEntities.Orders AS Order ON Customer.CustomerID=Order.CustomerID WHERE Year(Order.OrderDate) == 1997 && Order.ShipCountry=='Canada'"; var customerQuery = new ObjectQuery<Customer>( queryString, DAO.db); foreach (var c in customerQuery) { Console.WriteLine(c.ContactName); Console.WriteLine(c.Address); } }
static void Main() { //4.Implement previous by using native SQL //query and executing it through the ObjectContext. string queryString = @"SELECT VALUE DISTINCT Customer FROM NorthwindEntities.Customers AS Customer INNER JOIN NorthwindEntities.Orders AS Order ON Customer.CustomerID=Order.CustomerID WHERE Year(Order.OrderDate) == 1997 && Order.ShipCountry=='Canada'"; var customerQuery = new ObjectQuery<Customer>( queryString, DAO.db); foreach (var q in customerQuery) { Console.WriteLine("The customer {0} have order made in 1997 and live in Canada.", q.ContactName); } }
private void Form1_Load(object sender, EventArgs e) { gb_gmm_xid = gb.Gb_GMMSM_XID; gb_xid = gb.Gb_XID; }
/// <summary> /// Get multi line query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="fieldName">Name of the field</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <param name="isLikeSearch">Is or not a search with like statement</param> /// <returns>Object query of Guid</returns> private static ObjectQuery<Guid> GetMultiLineQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch) { string multilineValue = formCollectionFiltered[fieldName]; string queryMultiText = string.Empty; if (isLikeSearch) { queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue like '%" + multilineValue + "%' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; } else { queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue= '" + multilineValue + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; } ObjectQuery<Guid> objQueryMultiText = new ObjectQuery<Guid>(queryMultiText, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryMultiText); else tempQuery = tempQuery.Intersect(objQueryMultiText); return tempQuery; }
/// <summary> /// Get integer query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="fieldName">Name of the field</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <returns>Object query of Guid</returns>s private static ObjectQuery<Guid> GetIntegerQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result) { int intValue = int.Parse(formCollectionFiltered[fieldName]); string queryInteger = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.IntegerValue= " + intValue.ToString() + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; ObjectQuery<Guid> objQueryInteger = new ObjectQuery<Guid>(queryInteger, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryInteger); else tempQuery = tempQuery.Intersect(objQueryInteger); return tempQuery; }
/// <summary> /// Get date range query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <returns>Object query of Guid</returns> private static ObjectQuery<Guid> GetDateRangeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result) { string[] OrderDatefromValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("from")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries); string[] OrderDateToValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries); string fieldName = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Key; fieldName = fieldName.Remove(fieldName.Length - 2, 2); DateTime OrderDatefrom = new DateTime(int.Parse(OrderDatefromValue[2]), int.Parse(OrderDatefromValue[1]), int.Parse(OrderDatefromValue[0])); DateTime OrderDateTo = new DateTime(int.Parse(OrderDateToValue[2]), int.Parse(OrderDateToValue[1]), int.Parse(OrderDateToValue[0])); OrderDatefrom = OrderDatefrom.Date; OrderDateTo = OrderDateTo.Date.AddDays(1).AddSeconds(-1); string queryDate = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false and FormValue.DateValue >= DATETIME '" + OrderDatefrom.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + "' and FormValue.DateValue <= DATETIME '" + OrderDateTo.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; ObjectQuery<Guid> objQueryDate = new ObjectQuery<Guid>(queryDate, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryDate); else tempQuery = tempQuery.Intersect(objQueryDate); return tempQuery; }
/// <summary> /// Get catalogue query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="fieldName">Name of the field</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <param name="isLikeSearch">Is or not a search with like statement</param> /// <returns>Object query of Guid</returns> private static ObjectQuery<Guid> GetCatalogueQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch) { Guid guidValue = Guid.Empty; string likeValue = string.Empty; string queryCatalogue = string.Empty; if (isLikeSearch) { likeValue = formCollectionFiltered[fieldName]; queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue " + " inner join VestalisEntities.CatalogueValues as CatalogueValue on FormValue.CatalogueValueId = CatalogueValue.CatalogueValueId" + " where FormValue.IsDeleted = false" + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL" + " and CatalogueValue.CatalogueValueData like '%" + likeValue + "%' and CatalogueValue.IsDeleted = false"; } else { guidValue = new Guid(formCollectionFiltered[fieldName]); queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.CatalogueValueId =Guid '" + guidValue.ToString() + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; } ObjectQuery<Guid> objQueryGuid = new ObjectQuery<Guid>(queryCatalogue, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryGuid); else tempQuery = tempQuery.Intersect(objQueryGuid); return tempQuery; }
/// <summary> /// Get time query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="fieldName">Name of the field</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <returns>Object query of Guid</returns> private static ObjectQuery<Guid> GetTimeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result) { string timeValue = formCollectionFiltered[fieldName]; string queryTime = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue= '" + timeValue + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"; ObjectQuery<Guid> objQueryTime = new ObjectQuery<Guid>(queryTime, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryTime); else tempQuery = tempQuery.Intersect(objQueryTime); return tempQuery; }
public static List<TRANSFORMACION> obtenerTransformacion(string nombreTransformada, int codigoGrupoOrigen, int codigoMensajeOrigen, int codigoGrupoDestino, int codigoMensajeDestino) { StringBuilder query = new StringBuilder(""); query.Append("Select value T from TRANSFORMACION as T where " + "T.TRM_NOMBRE like '%" + nombreTransformada + "%'"); if (codigoGrupoOrigen != -1) { query.Append(" and T.MENSAJE_ORIGEN.GRUPO_MENSAJE.GMJ_CODIGO =" + codigoGrupoOrigen); } if (codigoMensajeOrigen != -1) { query.Append(" and T.MENSAJE_ORIGEN.MEN_CODIGO =" + codigoMensajeOrigen); } if (codigoGrupoDestino != -1) { query.Append(" and T.MENSAJE_DESTINO.GRUPO_MENSAJE.GMJ_CODIGO =" + codigoGrupoDestino); } if (codigoMensajeDestino != -1) { query.Append(" and T.MENSAJE_ORIGEN.MEN_CODIGO =" + codigoMensajeDestino); } using (Switch contexto = new Switch()) { contexto.TRANSFORMACION.MergeOption = MergeOption.NoTracking; ObjectQuery<TRANSFORMACION> Transformacion = new ObjectQuery<TRANSFORMACION>(query.ToString(), contexto); return Transformacion.Include("MENSAJE_ORIGEN").Include("MENSAJE_ORIGEN.GRUPO_MENSAJE") .Include("MENSAJE_DESTINO").Include("MENSAJE_DESTINO.GRUPO_MENSAJE") .ToList(); } }
//生成菜单项数据 private List<MenuDataItem> GetMenuData(ObjectQuery DataSource) { List<MenuDataItem> menuData = new List<MenuDataItem>(); int PrevGroupIndex = default(int); int CurrGroupIndex = default(int); Guid? PrevParent = default(Guid); Guid? CurrParent = default(Guid); foreach (View_Menu_Permission_Role item in DataSource) { CurrGroupIndex = item.GroupBy == null ? 0 : (int)item.GroupBy; CurrParent = item.ParentNode; if (CurrGroupIndex != PrevGroupIndex && CurrParent == PrevParent) { menuData.Add(new MenuDataItem(Guid.NewGuid(), item.ParentNode, "", "", "True")); } menuData.Add(new MenuDataItem(item.MenuID, item.ParentNode, item.MenuName, item.URL, "False")); PrevGroupIndex = item.GroupBy == null ? 0 : (int)item.GroupBy; PrevParent = item.ParentNode; } return menuData; }
/// <summary> /// This method get dinamically the result according of the filters /// </summary> /// <param name="context">Database context</param> /// <param name="inspectionReportId">if of inspection report item</param> /// <param name="parameters">Parameters</param> /// <returns></returns> private static List<Guid?> GetInspectionReportsFiltered(VestalisEntities context, Guid inspectionReportId, ParameterSearchInspectionReport parameters) { List<Guid?> result = new List<Guid?>(); string query1 = string.Empty; //filter FormCollection object to get a dictionary only with the key that have a value Dictionary<string, string> formCollectionFiltered = parameters.Collection.ToFilledDictionary(); //get the types of the filters var queryServiceOrder = (from formValue in context.FormValues join inspectionReportItem in context.InspectionReportItems on formValue.InspectionReportItemId equals inspectionReportItem.InspectionReportItemId where formCollectionFiltered.Keys.Contains(formValue.FieldName) && inspectionReportItem.InspectionReportId == inspectionReportId select new { FieldName = formValue.FieldName, TypeField = formValue.FieldType }).Distinct(); //this query filter the result by inspection report if (parameters.RolesForUser.Contains("Client")) { query1 = "select VALUE DISTINCT inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" + " inner join VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" + " inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" + " inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" + " where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" + " and ApprovalItem.IsDeleted = false and ApprovalItem.ApprovalStatus = 3 and ApprovalItem.CanPublish = true" + " and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'"; } else { string roles = string.Empty; foreach (string item in parameters.RolesForUser) { if (item == parameters.RolesForUser.Last()) roles += "ApprovalItem.RoleName = '" + item + "'"; else roles += "ApprovalItem.RoleName = '" + item + "' or "; } query1 = "select VALUE inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" + " inner join VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" + " inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" + " inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" + " where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" + " and ApprovalItem.IsDeleted = false and (" + roles + ") and (ApprovalItem.ApprovalStatus = 3 or ApprovalItem.ApprovalStatus = 2)" + " and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'"; } ObjectQuery<Guid> query1Result = new ObjectQuery<Guid>(query1, context); ObjectQuery<Guid> tempQuery = null; //for each result of filters, the systems perform a query to filter the corresponding value, but this iteration is not valid when in the filters exist a date range foreach (var item in queryServiceOrder) { string fieldName = item.FieldName; switch (item.TypeField) { case (int)FieldType.Boolean: tempQuery = GetBooleanQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.Catalogue: tempQuery = GetCatalogueQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName)); break; case (int)FieldType.Decimal: tempQuery = GetDecimalQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.Integer: tempQuery = GetIntegerQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.MultipleTextLine: tempQuery = GetMultiLineQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName)); break; case (int)FieldType.RegularExpressionText: tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName)); break; case (int)FieldType.SingleTextLine: tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName)); break; case (int)FieldType.Time: tempQuery = GetTimeQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; default: break; } } //if in the filter exist a date range, the system will perform the query for filter the results if (formCollectionFiltered.Keys.Any(key => key.EndsWith("to") || key.EndsWith("from"))) { tempQuery = GetDateRangeQuery(context, formCollectionFiltered, tempQuery, query1Result); } //retreive the results of the dynamic query if (tempQuery != null) { var tempResult = tempQuery.ToList(); if (tempResult.Count > 0) result = tempResult.Cast<Guid?>().ToList(); } return result; }
/// <summary> /// Get decimal query /// </summary> /// <param name="context">Vestalis context</param> /// <param name="formCollectionFiltered">Form collection</param> /// <param name="fieldName">Name of the field</param> /// <param name="tempQuery">Temporal query</param> /// <param name="query1Result">First query</param> /// <returns>Object query of Guid</returns> private static ObjectQuery<Guid> GetDecimalQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result) { decimal decimalValue = decimal.Parse(formCollectionFiltered[fieldName]); string queryDecimal = "select VALUE FormValue.InspectionReportItemId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.DecimalValue= " + decimalValue.ToString() + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NOT NULL"; ObjectQuery<Guid> objQueryDecimal = new ObjectQuery<Guid>(queryDecimal, context); if (tempQuery == null) tempQuery = query1Result.Intersect(objQueryDecimal); else tempQuery = tempQuery.Intersect(objQueryDecimal); return tempQuery; }
/// <summary> /// Get import file headers for profiling /// </summary> private void GetImportFileHeaders(int ImportFileId) { m_objImportFileHeaders = null; m_objImportFileHeaders = DataImportUtility.GetImportFileColumns(ImportFileId, true); this.ClearProfilingHeaderDropdown(); riImportFileHeader.DataSource = m_objImportFileHeaders.Execute(MergeOption.AppendOnly); riImportFileHeader.DisplayMember = "column_name"; riImportFileHeader.ValueMember = "id"; riImportFileHeader.Columns.Add(new LookUpColumnInfo("column_name")); riImportFileHeader.ShowHeader = false; riImportFileHeader.ShowFooter = false; }
private void Form1_Load(object sender, EventArgs e) { //mpos_gb_gz0511Entities mpos_gb = new mpos_gb_gz0511Entities(); GuangZhou_GnEntities gn = new GuangZhou_GnEntities(); Guangzhou_GbEntities gb = new Guangzhou_GbEntities(); gb.CommandTimeout = 0; sqlserver_mpos_gb_gz = gn.mpos_gb_gz; gb_dns_syn = gb.Gb_DNS_Syn; gb_pdp_fin = gn.Gb_PDP_Fin; gb_cell_syn = gn.Gb_Cell_Syn; gb_cell_repeat = gn.Gb_Cell_Repeat; mytcpsession = gb.myTcpSession; //gb_gz = mpos_gb.gb_gz_tousu; }
/// <summary> /// This method get dinamically the result according of the filters /// </summary> /// <param name="context">Database context</param> /// <param name="parameters">Parameters</param> /// <returns></returns> private static List<Guid?> GetServiceOrdersFiltered(VestalisEntities context, ParameterSearchServicerOrder parameters) { List<Guid?> result = new List<Guid?>(); //filter FormCollection object to get a dictionary only with the key that have a value Dictionary<string, string> formCollectionFiltered = parameters.FormCollection.ToFilledDictionary(); //get the types of the filters var queryServiceOrder = context.FormValues.Where(data => formCollectionFiltered.Keys.Contains(data.FieldName)) .Select(data => new { FieldName = data.FieldName, TypeField = data.FieldType }).Distinct(); //this query filter the result by business application string query1 = "select VALUE ServiceOrder.ServiceOrderId from VestalisEntities.ServiceOrders as ServiceOrder where ServiceOrder.IsDeleted = false AND ServiceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'"; ObjectQuery<Guid> query1Result = new ObjectQuery<Guid>(query1, context); ObjectQuery<Guid> tempQuery = null; //for each result of filters, the systems perform a query to filter the corresponding value, but this iteration is not valid when in the filters exist a date range foreach (var item in queryServiceOrder) { string fieldName = item.FieldName; switch (item.TypeField) { case (int)FieldType.Boolean: tempQuery = GetBooleanQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.Catalogue: tempQuery = GetCatalogueQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName)); break; case (int)FieldType.Decimal: tempQuery = GetDecimalQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.Integer: tempQuery = GetIntegerQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; case (int)FieldType.MultipleTextLine: tempQuery = GetMultiLineQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName)); break; case (int)FieldType.RegularExpressionText: tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName)); break; case (int)FieldType.SingleTextLine: tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName)); break; case (int)FieldType.Time: tempQuery = GetTimeQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result); break; default: break; } } //if in the filter exist a date range, the system will perform the query for filter the results if (formCollectionFiltered.Any(keyPair => keyPair.Key.EndsWith("from")) && formCollectionFiltered.Any(keyPair => keyPair.Key.EndsWith("to"))) { tempQuery = GetDateRangeQuery(context, formCollectionFiltered, tempQuery, query1Result); } //retreive the results of the dynamic query if (tempQuery != null) { var tempResult = tempQuery.ToList(); if (tempResult.Count > 0) result = tempResult.Cast<Guid?>().ToList(); } return result; }
/// <summary> /// Execute the sql statement that comes from autocomplete field /// </summary> /// <param name="command">Command to execute</param> /// <returns>List of strings</returns> public static List<string> GetAutoCompleteItemSource(string command) { using (VestalisEntities context = new VestalisEntities()) { //The system creates the object that will be executed for getting the result. ObjectQuery<string> result = new ObjectQuery<string>(command, context); //The system performs the execution of the query. return result.ToList(); } }
static void Main(string[] args) { using (var context = new SampleModel1Container()) { TryGenerateSampleData(context); IList<Project> list1 = new List<Project>(); IList<Project> list2 = new List<Project>() { new Project() { Id = 1 } }; // Must read the data from db then using join var total = context.Projects.AsEnumerable().Join(list2, p => p.Id, p => p.Id, (x, y) => new { X = x, Y = y }); var z = list1.Join(list2, p => p.Id, p => p.Id, (p1, p2) => new { }); var q = new ObjectQuery<Project>("SELECT VALUE p FROM Projects as p WHERE p.Id > @projectId", ((IObjectContextAdapter)context).ObjectContext); var r = q.Select("it", new ObjectParameter("projectId", 0)).ToList(); // Create the query var queryString = "SELECT VALUE p FROM Projects as p WHERE p.Id > @projectId"; var objectQuery = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Project>(queryString, new ObjectParameter("projectId", 0)); // Get all projects satified the query var projects = objectQuery.ToList(); // Select only first item var top1 = objectQuery.Top("1").FirstOrDefault(); // Skip and Top, it's different than Top and Skip var top2 = objectQuery.Skip("it.Name DESC", "0").Top("1").ToList(); // Get two fields var records = objectQuery.Select("it.Id, it.Name").Skip("it.Name", "2").Top("2"); foreach (var record in records) { Console.WriteLine("Id / Name: {0} / {1}", record["Id"], record["Name"]); } // var results = context.Projects.Where(p => p.Id > 0) .Select(x => new { x.Id, x.Name }) .OrderByDescending(x => x.Name) .Skip(2) .Take(2) .ToList(); var employees = context.Employees.ToList(); } Console.ReadLine(); //EntityConnection con = new EntityConnection(); //SampleModel1Container c = new SampleModel1Container(); //c.Database.Connection.BeginTransaction(); //EntityCommand ec = new EntityCommand(); //ec.ExecuteScalarAsync(); //SqlCommand sc = new SqlCommand(); //sc.ExecuteReaderAsync(); //System.Data.OleDb.OleDbCommand oc = new OleDbCommand(); //oc.ExecuteReaderAsync(); }