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