/// <summary> /// Example LINQ to SQL /// - All object object types /// - Count of object types /// - Percentage of object types /// - Total count /// </summary> /// <param name="provider"></param> /// <param name="connectionString"></param> /// <returns></returns> public static DataTable RunLinq2Db(IDataProvider provider, string connectionString) { //DataConnection.DefaultSettings = new hoLinq2DBSettings(provider, connectionString); try { { using (var db = new DataModels.EaDataModel(provider, connectionString)) { var count = db.t_object.Count(); var q = (from c in db.t_object.AsEnumerable() group c by c.Object_Type into g orderby g.Key select new { Type = g.Key, Prozent = $"{ (float)g.Count() * 100 / count:00.00}%", Count = g.Count(), Total = count }); return(q.ToDataTable()); } } } catch (Exception e) { MessageBox.Show($"Provider: {provider}\r\nConnection: {connectionString}\r\n\r\n{e}", "Error Linq2DB"); return(new DataTable()); } }
/// <summary> /// Get the object count of all t_object /// </summary> /// <returns></returns> private int GetObjectCount() { int count; // get connection string of repository string connectionString = LinqUtil.GetConnectionString(_repository, out var provider); using (var db = new DataModels.EaDataModel(provider, connectionString)) { count = (from o in db.t_object select o.Name).Count(); } return(count); }
/// <summary> /// Query the EA model and returns the results in the EA Search Window /// </summary> /// <returns></returns> private bool QueryModel() { // get connection string of repository string connectionString = LinqUtil.GetConnectionString(_repository, out var provider); DataTable dt; using (var db = new DataModels.EaDataModel(provider, connectionString)) { dt = (from o in db.t_object orderby new { o.Name, o.Object_Type, o.Stereotype } select new { CLASSGUID = o.ea_guid, CLASSTYPE = o.Object_Type, Name = o.Name, Type = o.Object_Type, Stereotype = o.Stereotype } ).Distinct() .ToDataTable(); } // 2. Order, Filter, Join, Format to XML string xml = LinqUtil.QueryAndMakeXmlFromTable(dt); // 3. Out put to EA _repository.RunModelSearch("", "", "", xml); return(true); }
/// <summary> /// Get the groups for the current user /// </summary> /// <param name="rep"></param> public EaGroup(EA.Repository rep) { if (!rep.IsSecurityEnabled) { Groups = new DataTable(); return; } string user = rep.GetCurrentLoginUser(); // get connection string of repository string connectionString = LinqUtil.GetConnectionString(rep, out var provider); using (var db = new DataModels.EaDataModel(provider, connectionString)) { Groups = (from grp in db.t_secgroup join grpUser in db.t_secusergroup on grp.GroupID equals grpUser.GroupID join cUser in db.t_secuser on grpUser.UserID equals cUser.UserID where cUser.UserLogin == user orderby grp.GroupName select new { Name = grp.GroupName ?? "" }).ToDataTable(); } }
/// <summary> /// Get Dictionary of nested elements for a package GUID. /// </summary> /// <param name="provider"></param> /// <param name="guid"></param> /// <param name="connectionString"></param> /// <returns></returns> private static Dictionary <int, NestedObject> GetPackageNestedElements(string connectionString, IDataProvider provider, string guid) { using (var db = new DataModels.EaDataModel(provider, connectionString)) { // optimize Access to database, without this the query takes > 30 seconds // Split query and group into two queries. // - The first query make the db acccess and writes to array // - The second query makes the grouping var reqTvAll0 = (from r in db.t_object join tv2 in db.t_objectproperties on r.Object_ID equals tv2.Object_ID into tv1 from tv in tv1.DefaultIfEmpty() join pkg in db.t_package on r.Package_ID equals pkg.Package_ID where pkg.ea_guid == guid orderby r.Object_ID, tv.Property select new { r, tv }).ToArray(); return((from r in reqTvAll0 group r by new { r.r.Object_ID, r.r.ParentID, r.r.TPos, r.r.Name, r.r.Alias, r.r.Note, r.r.ea_guid, r.r.Object_Type } into grp1 select new { Id = grp1.Key.Object_ID, Property = new NestedObject( grp1.Key.ParentID ?? 0, grp1.Key.TPos ?? 0, grp1.Key.Name ?? "", grp1.Key.Alias ?? "", grp1.Key.Note ?? "", grp1.Key.ea_guid ?? "", grp1.Key.Object_Type ?? "", // Tagged Value grp1.Select(g => new Tv( g?.tv?.Property ?? "", ((g?.tv?.Value ?? "") == "<memo>") ? g?.tv?.Notes ?? "" : g?.tv?.Value ?? "") ).ToList()) }).ToDictionary(ta => ta.Id, ta => ta.Property)); } }
/// <summary> /// Example LINQ to SQL /// All object types and all Requirement Types /// - First object types than requirement types /// - Count of object types /// - Percentage of object types /// - Total count /// /// Strategies to see: /// - Write multiple queries /// - Combine multiple queries /// - Use C# code for advanced features /// </summary> /// <param name="provider"></param> /// <param name="connectionString"></param> /// <returns></returns> public static DataTable RunLinq2DbAdvanced(IDataProvider provider, string connectionString) { // set provider and connection string // DataConnection.DefaultSettings = new hoLinq2DBSettings(provider, connectionString); try { { using (var db = new DataModels.EaDataModel(provider, connectionString)) { // Total amount of Object_Types var countObjectTypes = db.t_object.Count(); // All object_types summary: // - Type // - Count // - Percentage // - Total count of object_types var q = (from c in db.t_object.AsEnumerable() group c by c.Object_Type into g orderby g.Key select new { Type = $"{g.Key}", Prozent = $"{ (float)g.Count() * 100 / countObjectTypes:00.00}%", Count = g.Count(), Total = countObjectTypes }); // Requirement summary: // - Type // - Count // - Percentage // - Total count of requirements var countReq = db.t_object.Where(e => e.Object_Type == "Requirement").Count(); var q1 = (from c in db.t_object.AsEnumerable() where c.Object_Type == "Requirement" group c by c.Stereotype into g orderby g.Key select new { Type = $"Req:<<{g.Key}>>", Prozent = $"{ (float)g.Count() * 100 / countReq:00.00}%", Count = g.Count(), Total = countReq }); // Concatenate Object Types with Requirement Types var sum = q.Concat(q1); return(sum.ToDataTable()); } } } catch (Exception e) { MessageBox.Show($"Provider: {provider}\r\nConnection: {connectionString}\r\n\r\n{e}", "Error Linq2DB"); return(new DataTable()); } }